Oracle database 11GR2 is designed extensively for having different access paths for each partition wise data access. What does it means is that starting with oracle 11g you can index some partitions and not others. If you have index for some partitions oracle may pickup Index range scan for those partitions and full partition table acess for others.
rajesh@ORA11GR2> create table t
2 partition by range(dt)
3 ( partition p1 values less than ( to_date('01-JAN-2011','dd-mon-yyyy') ),
4 partition p2 values less than ( to_date('01-JAN-2012','dd-mon-yyyy') )
5 ) as
6 select a.*,
7 to_date('01-JAN-2010','dd-mon-yyyy') + mod(rownum,350) as dt,
8 rownum as id
9 from all_objects a;
Table created.
Elapsed: 00:00:01.81
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into t
2 select a.*,
3 to_date('01-JAN-2011','dd-mon-yyyy') + mod(rownum,350) as dt,
4 rownum as id
5 from all_objects a;
71230 rows created.
Elapsed: 00:00:01.48
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
We have two partitions P1 and P2 both loaded with some datas. Next we will create a Local Unusable index, meaning this index will exist in data dictionary but wont actually populated with data. The index will *exist* but not consume any storage.
rajesh@ORA11GR2> create index t_ind on
2 t(id) local unusable;
Index created.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>user,
4 tabname=>'T',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.42
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
2 FROM user_segments
3 where segment_name ='T_IND';
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
Now we will have only one acess path for queries, that is Full partition access.
rajesh@ORA11GR2> variable x number;
rajesh@ORA11GR2> exec :x := 42;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = :x;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 220 | 645 (1)| 00:00:08 | | |
| 1 | PARTITION RANGE ALL| | 2 | 220 | 645 (1)| 00:00:08 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | T | 2 | 220 | 645 (1)| 00:00:08 | 1 | 2 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=TO_NUMBER(:X))
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
Now, if we rebuild the index on the partion P2, then plan changes like this.
rajesh@ORA11GR2> alter index t_ind rebuild
2 partition p2 nologging;
Index altered.
Elapsed: 00:00:00.18
rajesh@ORA11GR2>
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
2 FROM user_segments
3 where segment_name ='T_IND';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
T_IND P2 INDEX PARTITION
Elapsed: 00:00:00.01
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>user,
4 tabname=>'T',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254',
8 partname=>'P2');
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.04
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = 42;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3602643409
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart | Pstop |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | |
| 1 | VIEW | VW_TE_2 | 2 | | |
| 2 | UNION-ALL | | | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 2 | 2 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 2 | 2 |
|* 5 | INDEX RANGE SCAN | T_IND | 1 | 2 | 2 |
| 6 | PARTITION RANGE SINGLE | | 1 | 1 | 1 |
|* 7 | TABLE ACCESS FULL | T | 1 | 1 | 1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"=42)
7 - filter("ID"=42)
rajesh@ORA11GR2>
This demonstrates that the optimizer has the ability to develop a query plan with different methods for accessing different partitions, Index range scan for partition P2 and Full partition scan for partition P1.
rajesh@ORA11GR2> create table t
2 partition by range(dt)
3 ( partition p1 values less than ( to_date('01-JAN-2011','dd-mon-yyyy') ),
4 partition p2 values less than ( to_date('01-JAN-2012','dd-mon-yyyy') )
5 ) as
6 select a.*,
7 to_date('01-JAN-2010','dd-mon-yyyy') + mod(rownum,350) as dt,
8 rownum as id
9 from all_objects a;
Table created.
Elapsed: 00:00:01.81
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into t
2 select a.*,
3 to_date('01-JAN-2011','dd-mon-yyyy') + mod(rownum,350) as dt,
4 rownum as id
5 from all_objects a;
71230 rows created.
Elapsed: 00:00:01.48
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
We have two partitions P1 and P2 both loaded with some datas. Next we will create a Local Unusable index, meaning this index will exist in data dictionary but wont actually populated with data. The index will *exist* but not consume any storage.
rajesh@ORA11GR2> create index t_ind on
2 t(id) local unusable;
Index created.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>user,
4 tabname=>'T',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.42
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
2 FROM user_segments
3 where segment_name ='T_IND';
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
Now we will have only one acess path for queries, that is Full partition access.
rajesh@ORA11GR2> variable x number;
rajesh@ORA11GR2> exec :x := 42;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = :x;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 220 | 645 (1)| 00:00:08 | | |
| 1 | PARTITION RANGE ALL| | 2 | 220 | 645 (1)| 00:00:08 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | T | 2 | 220 | 645 (1)| 00:00:08 | 1 | 2 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=TO_NUMBER(:X))
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
Now, if we rebuild the index on the partion P2, then plan changes like this.
rajesh@ORA11GR2> alter index t_ind rebuild
2 partition p2 nologging;
Index altered.
Elapsed: 00:00:00.18
rajesh@ORA11GR2>
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
2 FROM user_segments
3 where segment_name ='T_IND';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
T_IND P2 INDEX PARTITION
Elapsed: 00:00:00.01
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( ownname=>user,
4 tabname=>'T',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254',
8 partname=>'P2');
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.04
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = 42;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3602643409
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart | Pstop |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | |
| 1 | VIEW | VW_TE_2 | 2 | | |
| 2 | UNION-ALL | | | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 2 | 2 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 2 | 2 |
|* 5 | INDEX RANGE SCAN | T_IND | 1 | 2 | 2 |
| 6 | PARTITION RANGE SINGLE | | 1 | 1 | 1 |
|* 7 | TABLE ACCESS FULL | T | 1 | 1 | 1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"=42)
7 - filter("ID"=42)
rajesh@ORA11GR2>
This demonstrates that the optimizer has the ability to develop a query plan with different methods for accessing different partitions, Index range scan for partition P2 and Full partition scan for partition P1.
