Archive
Archive for August, 2011
Oracle Partition Index in 11GR2
August 18, 2011
2 comments
Here is the little new enhancement in 11gR2 11.2.0.2.0 regarding partition. Let’s say we have a partition table with monthly range partition. Mostly, customer query last month and current month data others partition are just rarely used. In 11gr2 you just have to maintain few local indexes others can be compromise. Here is little test case.
drop table test purge;
create table test (col1 number not null, col2 varchar2(100) not null, col3 date not null)
partition by range(col3) (
partition p_01_2011 values less than(to_date('01-JAN-2011','DD-MON-YYYY')),
partition p_02_2011 values less than(to_date('01-FEB-2011','DD-MON-YYYY')),
partition p_03_2011 values less than(to_date('01-MAR-2011','DD-MON-YYYY')),
partition p_04_2011 values less than(to_date('01-APR-2011','DD-MON-YYYY')),
partition p_05_2011 values less than(to_date('01-MAY-2011','DD-MON-YYYY')),
partition p_06_2011 values less than(to_date('01-JUN-2011','DD-MON-YYYY')),
partition p_07_2011 values less than(to_date('01-JUL-2011','DD-MON-YYYY')),
partition p_08_2011 values less than(to_date('01-AUG-2011','DD-MON-YYYY')),
partition p_09_2011 values less than(to_date('01-SEP-2011','DD-MON-YYYY')));
create index test_idx on test (col3) local UNUSABLE;
insert into test
select rownum, rpad(rownum,100,'X'), to_date('01-DEC-2010','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-JAN-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-FEB-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-MAR-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-APR-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-MAY-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-JUN-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-JUL-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000
union all
select rownum, rpad(rownum,100,'X'), to_date('01-AUG-2011','DD-MON-YYYY') + mod(rownum,28) from dual connect by level <= 10000;
commit;
exec dbms_stats.gather_table_stats(user, 'TEST');
Now let’s start with different scenarios
linux6:TARAL.ORALINUX>select partition_name, status from user_ind_partitions where index_name ='TEST_IDX' order by 1;
PARTITION_NAME STATUS
------------------------------ --------
P_01_2011 UNUSABLE
P_02_2011 UNUSABLE
P_03_2011 UNUSABLE
P_04_2011 UNUSABLE
P_05_2011 UNUSABLE
P_06_2011 UNUSABLE
P_07_2011 UNUSABLE
P_08_2011 UNUSABLE
P_09_2011 UNUSABLE
9 rows selected.
linux6:TARAL.ORALINUX>set autotrace on
linux6:TARAL.ORALINUX>select count(*) from test where col3 > to_date('01-JUN-2011', 'DD-MON-YYYY');
COUNT(*)
----------
29643
Execution Plan
----------------------------------------------------------
Plan hash value: 3670302319
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 144 (1)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 29333 | 229K| 144 (1)| 00:00:02 | 7 | 9 |
|* 3 | TABLE ACCESS FULL | TEST | 29333 | 229K| 144 (1)| 00:00:02 | 7 | 9 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COL3">TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
525 consistent gets
0 physical reads
0 redo size
348 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
linux6:TARAL.ORALINUX>set autotrace off
linux6:TARAL.ORALINUX>alter index test_idx rebuild partition p_09_2011 ;
Index altered.
linux6:TARAL.ORALINUX>alter index test_idx rebuild partition p_08_2011 ;
Index altered.
linux6:TARAL.ORALINUX>select partition_name, status from user_ind_partitions where index_name ='TEST_IDX' order by 1;
PARTITION_NAME STATUS
------------------------------ --------
P_01_2011 UNUSABLE
P_02_2011 UNUSABLE
P_03_2011 UNUSABLE
P_04_2011 UNUSABLE
P_05_2011 UNUSABLE
P_06_2011 UNUSABLE
P_07_2011 UNUSABLE
P_08_2011 USABLE
P_09_2011 USABLE
9 rows selected.
linux6:TARAL.ORALINUX>set autotrace on
linux6:TARAL.ORALINUX>select count(*) from test where col3 > to_date('01-JUN-2011', 'DD-MON-YYYY');
COUNT(*)
----------
29643
Execution Plan
----------------------------------------------------------
Plan hash value: 1824872575
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 49 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | VIEW | VW_TE_2 | 29333 | | 49 (0)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR| | 19690 | 153K| 0 (0)| 00:00:01 | 8 | 9 |
|* 5 | INDEX RANGE SCAN | TEST_IDX | 19690 | 153K| 0 (0)| 00:00:01 | 8 | 9 |
| 6 | PARTITION RANGE SINGLE | | 9643 | 77144 | 49 (0)| 00:00:01 | 7 | 7 |
|* 7 | TABLE ACCESS FULL | TEST | 9643 | 77144 | 49 (0)| 00:00:01 | 7 | 7 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TEST"."COL3">=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TEST"."COL3"<TO_DATE(' 2011-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("COL3">TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
231 consistent gets
54 physical reads
0 redo size
348 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As, you can see from this it use nice union all transformation for this query where it use index for indexed partition and full table scan for unusable index partition. This is due to TE transformation that CBO does.
***********************************
Cost-Based Table Expansion
***********************************
TE: Checking validity of TE for query block SEL$1 (#1)
TE: Checking validity of table expansion for query block SEL$1 (#1)
TE: Using search type: linear
Table expansion on query block SEL$1 (#1)
TE: Considering table expansion on query block SEL$1 (#1)
TE: Starting iteration 1, state space = (1) : (0)
FPD: Considering simple filter push in query block SEL$1 (#1)
"TEST"."COL3">TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "TEST"."COL3">TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
TE: Costing transformed query.
....
....
....
TE: after table expansion:******* UNPARSED QUERY IS *******
*** 2011-08-18 16:24:21.660
SELECT COUNT(*) "COUNT(*)" FROM ( (SELECT 0 FROM "TARAL"."TEST" "TEST" WHERE "TEST"."COL3">TO_DATE('01-JUN-2011','DD-MON-YYYY') AND ("TEST"."COL3">=TO_DATE(' 2011-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') AND "TEST"."COL3"<TO_DATE(' 2011-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) UNION ALL (SELECT 0 FROM "TARAL"."TEST" "TEST" WHERE "TEST"."COL3">TO_DATE('01-JUN-2011','DD-MON-YYYY') AND ("TEST"."COL3"<TO_DATE(' 2011-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')))) "VW_TE_1"
Registered qb: SET$11B8F94D 0x7fbdd1e8 (TABLE EXPANSION SEL$1; SEL$1; "TEST"@"SEL$1")
This can be controlled by hidden parameter _optimizer_table_expansion
linux6:TARAL.ORALINUX>set autotrace off
linux6:TARAL.ORALINUX>alter session set "_optimizer_table_expansion" = FALSE;
Session altered.
linux6:TARAL.ORALINUX>set autotrace on
linux6:TARAL.ORALINUX>select count(*) from test where col3 > to_date('01-JUN-2011', 'DD-MON-YYYY');
COUNT(*)
----------
29643
Execution Plan
----------------------------------------------------------
Plan hash value: 3670302319
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 144 (1)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 29333 | 229K| 144 (1)| 00:00:02 | 7 | 9 |
|* 3 | TABLE ACCESS FULL | TEST | 29333 | 229K| 144 (1)| 00:00:02 | 7 | 9 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COL3">TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
525 consistent gets
0 physical reads
0 redo size
348 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
And Even this works
linux6:TARAL.ORALINUX>drop index test_idx;
Index dropped.
linux6:TARAL.ORALINUX>create index test_idx on test (col3) local UNUSABLE;
Index created.
linux6:TARAL.ORALINUX>alter index test_idx rebuild partition p_06_2011 ;
Index altered.
linux6:TARAL.ORALINUX>alter index test_idx rebuild partition p_07_2011 ;
Index altered.
linux6:TARAL.ORALINUX>set autotrace on
linux6:TARAL.ORALINUX>select count(*) from test where col3 > to_date('01-MAY-2011', 'DD-MON-YYYY');
COUNT(*)
----------
39643
Execution Plan
----------------------------------------------------------
Plan hash value: 4227256123
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 96 (0)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | VIEW | VW_TE_2 | 40023 | | 96 (0)| 00:00:02 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR| | 20333 | 158K| 0 (0)| 00:00:01 | 6 | 7 |
|* 5 | INDEX RANGE SCAN | TEST_IDX | 20333 | 158K| 0 (0)| 00:00:01 | 6 | 7 |
| 6 | PARTITION RANGE ITERATOR| | 19690 | 153K| 96 (0)| 00:00:02 | 8 | 9 |
| 7 | TABLE ACCESS FULL | TEST | 19690 | 153K| 96 (0)| 00:00:02 | 8 | 9 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("COL3">TO_DATE(' 2011-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TEST"."COL3"<TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
406 consistent gets
54 physical reads
0 redo size
348 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So, little new but useful feature.
Categories: Performance
