Oracle Partition Index in 11GR2

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.

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

2 Responses to Oracle Partition Index in 11GR2

  1. Vivek says:

    Taral,

    Nice Post with easy to understand example.

    Regards
    Vivek
    viveklsharma.wordpress.com

  2. Taral says:

    Thanks Vivek For Visiting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: