Create and manage Oracle partition tables and local indexes

On the Internet, you can see an article about partition tables and local indexes written by eygle.Article, I think the summary is quite good.

OracleOfPartitioning technologyIt can be greatly improved under some conditions.QuerySo it is widely used. In terms of products, the Partitioning technology is an independent charging method in Oracle Enterprise Edition.Components. Partition andLocal index.

Create a test Partition Table Based on 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 ('2014/1/123', 'dd/MM/yyyy ')), 8 partition dbobjs_07 values less than (to_date ('2017/123456', '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 the index of different partitions can be specified to create differentTablespace:

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 the following:

Create index dbobjs_idx on dbobjs (created) Local (Partition dbobjs_06 tablespace users, Partition dbobjs_07 tablespace users ) Tablespace users;

 

Use the unified tablespace clause to specify the tablespace for the index.

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 the query performance differences between partitioned tables and non-partitioned tables through queries:

SQL> set autotrace on SQL> select count (*) from dbobjs where created <to_date ('2017/123456', '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 cballs 0 dB block gets 25 consistent gets 0 physical reads 0 redo size 380 bytes sent via SQL * Net to client 503 bytes encoded ed 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 ('2017/123456', '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 cballs 0 dB block gets 24 consistent gets 0 physical reads 0 redo size 380 bytes sent via SQL * Net to client 503 bytes encoded ed 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 ('2017/123', '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) 2 1 Table Access (by 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 cballs 0 dB block gets 101 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL * Net to client 503 bytes encoded ed via SQL * Net From Client 2 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 1 rows processed

Test for non-partition tables:

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 ('2017/123', 'dd/MM/yyyy '); Count (distinct (object_name )) ---------------------------- 4753 Execution Plan ---------------------------------------------------------- 0 SELECT statement ptimizer = choose 1 0 sort (group) 2 1 Table Access (by index rowid) of 'dbobjs2' 3 2 Index (range scan) of 'dbobjs _ idx2 '(NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive cballs 0 dB block gets 2670 consistent gets 0 physical reads 1332 redo size 400 bytes sent via SQL * Net to client 503 bytes encoded ed via SQL * Net From Client 2 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 1 rows processed

When table partitions are added, the local index is automatically maintained:

SQL> ALTER TABLE dbobjs 2 add partition dbobjs_08 values less than (to_date ('2017/123', '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 have created several Oracle QQ groups. You are welcome to join this group. Oracle expert qq1 group: 60632593 Oracle expert qq2 group: 60618621 Oracle expert qq3 group: 23145225

-The end-