SQL LEARNING CLASS8

PARTITIONS:

A single logical table can be split into a number of physically separate pieces based on ranges of key values. Each of the parts of the table is called a partition.

A non-partitioned table can not be partitioned later.

TYPES

• Range partitions
• List partitions
• Hash partitions
• Sub partitions

ADVANTAGES

• Reducing downtime for scheduled maintenance, which allows maintenance operations to be carried out on selected partitions while other partitions are available to users.
• Reducing downtime due to data failure, failure of a particular partition will no way affect other partitions.
• Partition independence allows for concurrent use of the various partitions for various purposes.

ADVANTAGES OF PARTITIONS BY STORING THEM IN DIFFERENT TABLESPACES

• Reduces the possibility of data corruption in multiple partitions.
• Back up and recovery of each partition can be done independently.

DISADVANTAGES

• Partitioned tables cannot contain any columns with long or long raw datatypes, LOB types or object types.

RANGE PARTITIONS

a) Creating range partitioned table

SQL> Create table student(no number(2),name varchar(2)) partition by range(no) (partition

p1 values less than(10), partition p2 values less than(20), partition p3 values less

than(30),partition p4 values less than(maxvalue));

** if you are using maxvalue for the last partition, you can not add a partition.

b) Inserting records into range partitioned table

SQL> Insert into student values(1,’a’); -- this will go to p1

SQL> Insert into student values(11,’b’); -- this will go to p2

SQL> Insert into student values(21,’c’); -- this will go to p3

SQL> Insert into student values(31,’d’); -- this will go to p4

c) Retrieving records from range partitioned table

SQL> Select *from student;

SQL> Select *from student partition(p1);

d) Possible operations with range partitions

• Add
• Drop
• Truncate
• Rename
• Split
• Move
• Exchange

e) Adding a partition

SQL> Alter table student add partition p5 values less than(40);

f) Dropping a partition

SQL> Alter table student drop partition p4;

g) Renaming a partition

SQL> Alter table student rename partition p3 to p6;

h) Truncate a partition

SQL> Alter table student truncate partition p6;

i) Splitting a partition

SQL> Alter table student split partition p2 at(15) into (partition p21,partition p22);

j) Exchanging a partition

SQL> Alter table student exchange partition p1 with table student2;

k) Moving a partition

SQL> Alter table student move partition p21 tablespace saketh_ts;

LIST PARTITIONS

a) Creating list partitioned table

SQL> Create table student(no number(2),name varchar(2)) partition by list(no) (partition p1

values(1,2,3,4,5), partition p2 values(6,7,8,9,10),partition p3 values(11,12,13,14,15),

partition p4 values(16,17,18,19,20));

b) Inserting records into list partitioned table

SQL> Insert into student values(1,’a’); -- this will go to p1

SQL> Insert into student values(6,’b’); -- this will go to p2

SQL> Insert into student values(11,’c’); -- this will go to p3

SQL> Insert into student values(16,’d’); -- this will go to p4

c) Retrieving records from list partitioned table

SQL> Select *from student;

SQL> Select *from student partition(p1);

d) Possible operations with list partitions

• Add
• Drop
• Truncate
• Rename
• Move
• Exchange

e) Adding a partition

SQL> Alter table student add partition p5 values(21,22,23,24,25);

f) Dropping a partition

SQL> Alter table student drop partition p4;

g) Renaming a partition

SQL> Alter table student rename partition p3 to p6;

h) Truncate a partition

SQL> Alter table student truncate partition p6;

i) Exchanging a partition

SQL> Alter table student exchange partition p1 with table student2;

j) Moving a partition

SQL> Alter table student move partition p2 tablespace saketh_ts;

HASH PARTITIONS

a) Creating hash partitioned table

SQL> Create table student(no number(2),name varchar(2)) partition by hash(no) partitions

5;

Here oracle automatically gives partition names like

SYS_P1

SYS_P2

SYS_P3

SYS_P4

SYS_P5

b) Inserting records into hash partitioned table

it will insert the records based on hash function calculated by taking the partition key

SQL> Insert into student values(1,’a’);

SQL> Insert into student values(6,’b’);

SQL> Insert into student values(11,’c’);

SQL> Insert into student values(16,’d’);

c) Retrieving records from hash partitioned table

SQL> Select *from student;

SQL> Select *from student partition(sys_p1);

d) Possible operations with hash partitions

• Add
• Truncate
• Rename
• Move
• Exchange

e) Adding a partition

SQL> Alter table student add partition p6 ;

f) Renaming a partition

SQL> Alter table student rename partition p6 to p7;

g) Truncate a partition

SQL> Alter table student truncate partition p7;

h) Exchanging a partition

SQL> Alter table student exchange partition sys_p1 with table student2;

i) Moving a partition

SQL> Alter table student move partition sys_p2 tablespace saketh_ts;

SUB-PARTITIONS WITH RANGE AND HASH

Subpartitions clause is used by hash only. We can not create subpartitions with list and hash partitions.

a) Creating subpartitioned table

SQL> Create table student(no number(2),name varchar(2),marks number(3))

Partition by range(no) subpartition by hash(name) subpartitions 3

(Partition p1 values less than(10),partition p2 values less than(20));

This will create two partitions p1 and p2 with three subpartitions for each partition

P1 – SYS_SUBP1

SYS_SUBP2

SYS_SUBP3

P2 – SYS_SUBP4

SYS_SUBP5

SYS_SUBP6

** if you are using maxvalue for the last partition, you can not add a partition.

b) Inserting records into subpartitioned table

SQL> Insert into student values(1,’a’); -- this will go to p1

SQL> Insert into student values(11,’b’); -- this will go to p2

c) Retrieving records from subpartitioned table

SQL> Select *from student;

SQL> Select *from student partition(p1);

SQL> Select *from student subpartition(sys_subp1);

d) Possible operations with subpartitions

• Add
• Drop
• Truncate
• Rename
• Split

e) Adding a partition

SQL> Alter table student add partition p3 values less than(30);

f) Dropping a partition

SQL> Alter table student drop partition p3;

g) Renaming a partition

SQL> Alter table student rename partition p2 to p3;

h) Truncate a partition

SQL> Alter table student truncate partition p1;

i) Splitting a partition

SQL> Alter table student split partition p3 at(15) into (partition p31,partition p32);

DATA MODEL

• ALL_IND_PARTITIONS
• ALL_IND_SUBPARTITIONS
• ALL_TAB_PARTITIONS
• ALL_TAB_SUBPARTITIONS
• DBA_IND_PARTITIONS
• DBA_IND_SUBPARTITIONS
• DBA_TAB_PARTITIONS
• DBA_TAB_SUBPARTITIONS
• USER_IND_PARTITIONS
• USER_IND_SUBPARTITIONS
• USER_TAB_PARTITIONS
• USER_TAB_SUBPARTITIONS

 SQL*PLUS COMMNANDS:

These commands does not require statement terminator and applicable to the sessions , those will be automatically cleared when session was closed.

BREAK

This will be used to breakup the data depending on the grouping.

Syntax:

Break or bre [on <column_name> on report]

COMPUTE

This will be used to perform group functions on the data.

Syntax:

Compute or comp [group_function of column_name on breaking_column_name or

report]

TTITLE

This will give the top title for your report. You can on or off the ttitle.

Syntax:

Ttitle or ttit [left | center | right] title_name skip n other_characters

Ttitle or ttit [on or off]

BTITLE

This will give the bottom title for your report. You can on or off the btitle.

Syntax:

Btitle or btit [left | center | right] title_name skip n other_characters

Btitle or btit [on or off]

Ex:

SQL> bre on deptno skip 1 on report

SQL> comp sum of sal on deptno

SQL> comp sum of sal on report

SQL> ttitle center 'EMPLOYEE DETAILS' skip1 center '----------------'

SQL> btitle center '** THANKQ **'

SQL> select * from emp order by deptno;

Output:

EMPLOYEE DETAILS

-----------------------

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ------- -------------- -------- ---------- ----------

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000

7934 MILLER CLERK 7782 23-JAN-82 1300

---------- **********

8750 sum

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100

7902 FORD ANALYST 7566 03-DEC-81 3000

7788 SCOTT ANALYST 7566 19-APR-87 3000

7566 JONES MANAGER 7839 02-APR-81 2975

---------- **********

10875 sum

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7900 JAMES CLERK 7698 03-DEC-81 950

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

---------- **********

9400 sum

----------

sum 29025

** THANKQ **

CLEAR

This will clear the existing buffers or break or computations or columns formatting.

Syntax:

Clear or cle buffer | bre | comp | col;

Ex:

SQL> clear buffer

Buffer cleared

SQL> clear bre

Breaks cleared

SQL> clear comp

Computes cleared

SQL> clear col

Columns cleared

CHANGE

This will be used to replace any strings in SQL statements.

Syntax:

Change or c/old_string/new_string

If the old_string repeats many times then new_string replaces the first string only.

Ex:

SQL> select * from det;

select * from det

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> c/det/dept

1* select * from dept

SQL> /

DEPTNO DNAME LOC

---------- ---------------- -----------

10 ACCOUNTING NEW YORK

20 RESEARCH ALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

COLUMN

This will be used to increase or decrease the width of the table columns.

Syntax:

Column or col <column_name> format <num_format|text_format>

Ex:

SQL> col deptno format 999

SQL> col dname format a10

SAVE

This will be used to save your current SQL statement as SQL Script file.

Syntax:

Save or sav <file_name>.[extension] replace or rep

If you want to save the filename with existing filename the you have to use replace option.

By default it will take sql as the extension.

Ex:

SQL> save ss

Created file ss.sql

SQL> save ss replace

Wrote file ss.sql

EXECUTE

This will be used to execute stored subprograms or packaged subprograms.

Syntax:

Execute or exec <subprogram_name>

Ex:

SQL> exec sample_proc

SPOOL

This will record the data when you spool on, upto when you say spool off. By default it will give lst as extension.

Syntax:

Spool on | off | out | <file_name>.[Extension]

Ex:

SQL> spool on

SQL> select * from dept;

DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> spool off

SQL> ed on.lst

SQL> select * from dept;

DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> spool off

LIST

This will give the current SQL statement.

Syntax:

List or li [start_line_number] [end_line_number]

Ex:

SQL> select

2 *

3 from

4 dept;

SQL> list

1 select

2 *

3 from

4* dept

SQL> list 1

1* select

SQL> list 3

3* from

SQL> list 1 3

1 select

2 *

3* from

INPUT

This will insert the new line to the current SQL statement.

Syntax:

Input or in <string>

Ex:

SQL> select *

SQL> list

1* select *

SQL> input from dept

SQL> list

1 select *

2* from dept

APPEND

This will adds a new string to the existing string in the SQL statement without any space.

Syntax:

Append or app <string>

Ex:

SQL> select *

SQL> list

1* select *

SQL> append from dept

1* select * from dept

SQL> list

1* select * from dept

DELETE

This will delete the current SQL statement lines.

Syntax:

Delete or del <start_line_number> [<end_line_number>]

Ex:

SQL> select

2 *

3 from

4 dept

5 where

6 deptno

7 >10;

SQL> list

1 select

2 *

3 from

4 dept

5 where

6 deptno

7* >10

SQL> del 1

SQL> list

1 *

2 from

3 dept

4 where

5 deptno

6* >10

SQL> del 2

SQL> list

1 *

2 dept

3 where

4 deptno

5* >10

SQL> del 2 4

SQL> list

1 *

2* >10

SQL> del

SQL> list

1 *

VARIABLE

This will be used to declare a variable.

Syntax:

Variable or var <variable_name> <variable_type>

Ex:

SQL> var dept_name varchar(15)

SQL> select dname into dept_name from dept where deptno = 10;

PRINT

This will be used to print the output of the variables that will be declared at SQL level.

Syntax:

Print <variable_name>

Ex:

SQL> print dept_name

DEPT_NAME

--------------

ACCOUNTING

START

This will be used to execute SQL scripts.

Syntax:

start <filename_name>.sql

Ex:

SQL> start ss.sql

SQL> @ss.sql -- this will execute sql script files only.

HOST

This will be used to interact with the OS level from SQL.

Syntax:

Host [operation]

Ex:

SQL> host

SQL> host dir

SHOW

Using this, you can see several commands that use the set command and status.

Syntax:

Show all | <set_command>

Ex:

SQL> show all

appinfo is OFF and set to "SQL*Plus"

arraysize 15

autocommit OFF

autoprint OFF

autorecovery OFF

autotrace OFF

blockterminator "." (hex 2e)

btitle OFF and is the first few characters of the next SELECT statement

cmdsep OFF

colsep " "

compatibility version NATIVE

concat "." (hex 2e)

copycommit 0

COPYTYPECHECK is ON

define "&" (hex 26)

describe DEPTH 1 LINENUM OFF INDENT ON

echo OFF

editfile "afiedt.buf"

embedded OFF

escape OFF

FEEDBACK ON for 6 or more rows

flagger OFF

flush ON

SQL> sho verify

verify OFF

RUN

This will runs the command in the buffer.

Syntax:

Run | /

Ex:

SQL> run

SQL> /

STORE

This will save all the set command statuses in a file.

Syntax:

Store set <filename>.[extension] [create] | [replace] | [append]

Ex:

SQL> store set my_settings.scmd

Created file my_settings.scmd

SQL> store set my_settings.cmd replace

Wrote file my_settings.cmd

SQL> store set my_settings.cmd append

Appended file to my_settings.cmd

FOLD_AFTER

This will fold the columns one after the other.

Syntax:

Column <column_name> fold_after [no_of_lines]

Ex:

SQL> col deptno fold_after 1

SQL> col dname fold_after 1

SQL> col loc fold_after 1

SQL> set heading off

SQL> select * from dept;

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAS

30

SALES

CHICAGO

40

OPERATIONS

BOSTON

FOLD_BEFORE

This will fold the columns one before the other.

Syntax:

Column <column_name> fold_before [no_of_lines]

DEFINE

This will give the list of all the variables currently defined.

Syntax:

Define [variable_name]

Ex:

SQL> define

DEFINE _DATE = "16-MAY-07" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "oracle" (CHAR)

DEFINE _USER = "SCOTT" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 –

Production With the Partitioning, OLAP and Data Mining

options" (CHAR)

DEFINE _O_RELEASE = "1001000200" (CHAR)

SPECIAL FILES:

LOGIN.sql

If you would like SQLPLUS to define your own environmental settings, put all the required commands in a file named login.sql. This is a special filename that SQLPLUS always looks for whenever it starts up. If it finds login.sql, it executes any commands in it as if you had entered then by hand. You can put any command in login.sql that you can use in SQLPLUS, including SQLPLUS commands and SQL statements. All ot them executed before SQLPLUS gives you the SQL> prompt.