Create and manage Oracle's partitioned tables and local indexes __oracle

On the Internet to see Eygle wrote about the partition table and local index of the article, feeling summed up very well, special turned over.

Oracle Partitioning technology can greatly improve the performance of queries under certain conditions, so it is widely used. From the product, partitioning technology is an independent charge component of Oracle Enterprise Edition. The following is an example of a partition and a local index .

First, create a test partition table from the dictionary table:

Sql> Connect Eygle/eygle Connected. sql> CREATE TABLE Dbobjs 2 (object_id number not NULL, 3 object_name varchar2 (128), 4 CREATED DATE not NULL 5) 6 PARTITION by RANGE (CREATED) 7 (PARTITION dbobjs_06 VALUES less THAN (to_date (' 01/01/2007 ', ' dd/mm/yyyy ')), 8 PARTITION dbobjs_07 VALUES less THAN (to_date (' 01/01/2008 ', ' dd/mm/yyyy ')); Table created. Sql> COL Segment_name for A20 Sql> COL Partition_name for A20 Sql> SELECT segment_name, Partition_name, Tablespace_name 2 from Dba_segments 3 WHERE segment_name = ' dbobjs '; Segment_name Partition_name Tablespace_name -------------------- -------------------- ------------------------------ DBOBJS dbobjs_06 Eygle DBOBJS dbobjs_07 Eygle

Create a local index, note that you can specify that the indexes of different partitions be created into different tablespace spaces :

Sql> CREATE INDEX dbobjs_idx on DBOBJS (created) local 2 (PARTITION dbobjs_06 tablespace users, 3 PARTITION dbobjs_07 tablespace users 4); Index created.

This clause can be further adjusted to resemble:

CREATE INDEX Dbobjs_idx on DBOBJS (created) local (PARTITION dbobjs_06 tablespace users, PARTITION dbobjs_07 tablespace Users ) tablespace users;

Specifies the table space for the index through the Uniform tablespace clause.

Sql> COL Segment_name for A20 Sql> COL Partition_name for A20 Sql> SELECT segment_name, Partition_name, Tablespace_name 2 from Dba_segments 3 WHERE segment_name = ' dbobjs_idx '; Segment_name Partition_name Tablespace_name -------------------- -------------------- ------------------------------ Dbobjs_idx dbobjs_06 USERS Dbobjs_idx dbobjs_07 USERS sql> INSERT INTO DBOBJS 2 Select object_id,object_name,created 3 from Dba_objects where created 6227 rows created. Sql> commit; Commit complete. Sql> Select COUNT (*) from DBOBJS partition (DBOBJS_06); COUNT (*) ---------- 6154 Sql> Select COUNT (*) from DBOBJS partition (dbobjs_07); COUNT (*) ---------- 73

We can compare query performance differences between partitioned and non-partitioned tables through queries:

Sql> set Autotrace on Sql> Select COUNT (*) from DBOBJS where created < To_date (' 01/01/2008 ', ' dd/mm/yyyy '); COUNT (*) ---------- 6227 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=choose (cost=1 card=1 bytes=9) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (All) 3 2 INDEX (RANGE SCAN) of ' Dbobjs_idx ' (non-unique) (cost=2 card=8 bytes=72) Statistics ---------------------------------------------------------- 0 Recursive calls 0 db Block gets Consistent gets 0 physical Reads 0 Redo Size 380 Bytes sent via sql*net to client 503 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 1 rows processed Sql> Select COUNT (*) from DBOBJS where created < To_date (' 01/01/2007 ', ' dd/mm/yyyy '); COUNT (*) ---------- 6154 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=choose (cost=1 card=1 bytes=9) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) of ' Dbobjs_idx ' (non-unique) (cost=2 card=4 bytes=36) Statistics ---------------------------------------------------------- 0 Recursive calls 0 db Block gets Consistent gets 0 physical Reads 0 Redo Size 380 Bytes sent via sql*net to client 503 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 0 Sorts (memory) 0 Sorts (disk) 1 rows processed Sql> Select COUNT (Distinct (object_name)) from Dbobjs where created < To_date (' 01/01/2007 ', ' dd/mm/yyyy '); COUNT (DISTINCT (object_name)) ---------------------------- 4753 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=choose (cost=1 card=1 bytes=75) 1 0 SORT (GROUP by) 2 1 TABLE ACCESS (by the local INDEX ROWID) of ' Dbobjs ' (cost=1 card=4 bytes=300) 3 2 INDEX (RANGE SCAN) of ' Dbobjs_idx ' (non-unique) (cost=2 card=1) Statistics ---------------------------------------------------------- 0 Recursive calls 0 db Block gets Gets consistent 0 physical Reads 0 Redo Size Bytes sent via sql*net to client 503 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 1 Sorts (memory) 0 Sorts (disk) 1 rows processed

For non-partitioned table tests:

sql> CREATE TABLE Dbobjs2 2 (object_id number not NULL, 3 object_name VARCHAR2 (128), 4 created DATE not NULL 5); Table created. Sql> CREATE INDEX dbobjs_idx2 on DBOBJS2 (created); Index created. sql> INSERT INTO DBOBJS2 2 Select object_id,object_name,created 3 from Dba_objects where created 6227 rows created. Sql> commit; Commit complete. Sql> Select COUNT (Distinct (object_name)) from Dbobjs2 where created < To_date (' 01/01/2007 ', ' dd/mm/yyyy '); COUNT (DISTINCT (object_name)) ---------------------------- 4753 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Ptimizer=choose 1 0 SORT (GROUP by) 2 1 TABLE ACCESS (by INDEX ROWID) of ' DBOBJS2 ' 3 2 INDEX (RANGE SCAN) of ' dbobjs_idx2 ' (non-unique) Statistics ---------------------------------------------------------- 0 Recursive calls 0 db Block gets 2670 consistent gets 0 physical Reads 1332 Redo Size Bytes sent via sql*net to client 503 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 1 Sorts (memory) 0 Sorts (disk) 1 rows processed

The local index is automatically maintained when the table partition is added:

sql> ALTER TABLE Dbobjs 2 ADD PARTITION dbobjs_08 VALUES less THAN (to_date (' 01/01/2009 ', ' dd/mm/yyyy ')); Table altered. Sql> set Autotrace off Sql> COL Segment_name for A20 Sql> COL Partition_name for A20 Sql> SELECT segment_name, Partition_name, Tablespace_name 2 from Dba_segments 3 WHERE segment_name = ' dbobjs_idx '; Segment_name Partition_name Tablespace_name -------------------- -------------------- ------------------------------ Dbobjs_idx dbobjs_06 USERS Dbobjs_idx dbobjs_07 USERS Dbobjs_idx dbobjs_08 Eygle Sql> SELECT segment_name, Partition_name, Tablespace_name 2 from Dba_segments 3 WHERE segment_name = ' dbobjs '; Segment_name Partition_name Tablespace_name -------------------- -------------------- ------------------------------ DBOBJS dbobjs_06 Eygle DBOBJS dbobjs_07 Eygle DBOBJS dbobjs_08 Eygle

PS: I built a few Oracle QQ group, Welcome to join the database enthusiasts. Oracle Expert QQ1 Group: 60632593 Oracle Expert QQ2 Group: 60618621 Oracle Expert QQ3 Group: 23145225

-the end-