03 August, 2010

Creating a "Sparse" Index

Without the advantage of segmentless (i.e. zero-sized) index partitions that are available in 11gR2, I still attempt to create "sparse" Index Partitions in this example in 10.2.0.4 :


SQL> drop table MY_ORDERS_TABLE purge;

Table dropped.

SQL>
SQL> create table MY_ORDERS_TABLE
2 (order_id number not null,
3 order_date date not null,
4 customer_id number not null,
5 product_id varchar2(1) not null,
6 quantity number,
7 rate number,
8 comments varchar2(64)
9 )
10 partition by range (order_date)
11 subpartition by list (product_id)
12 (partition ord_2005 values less than (to_date('01-JAN-2006','DD-MON-YYYY'))
13 (subpartition ord_2005_a values ('A'),
14 subpartition ord_2005_b values ('B'),
15 subpartition ord_2005_c values ('C')),
16 partition ord_2006 values less than (to_date('01-JAN-2007','DD-MON-YYYY'))
17 (subpartition ord_2006_a values ('A'),
18 subpartition ord_2006_b values ('B'),
19 subpartition ord_2006_c values ('C')),
20 partition ord_2007 values less than (to_date('01-JAN-2008','DD-MON-YYYY'))
21 (subpartition ord_2007_a values ('A'),
22 subpartition ord_2007_b values ('B'),
23 subpartition ord_2007_c values ('C')),
24 partition ord_2008 values less than (to_date('01-JAN-2009','DD-MON-YYYY'))
25 (subpartition ord_2008_a values ('A'),
26 subpartition ord_2008_b values ('B'),
27 subpartition ord_2008_c values ('C')))
28 /

Table created.

SQL>
SQL> create index MY_ORDERS_IDX on MY_ORDERS_TABLE (order_id,customer_id,product_id) local UNUSABLE;

Index created.

SQL>
SQL> select partition_name, subpartition_name, status from user_ind_subpartitions
2 where index_name = 'MY_ORDERS_IDX'
3 order by 1,2
4 /

PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ --------
ORD_2005 ORD_2005_A UNUSABLE
ORD_2005 ORD_2005_B UNUSABLE
ORD_2005 ORD_2005_C UNUSABLE
ORD_2006 ORD_2006_A UNUSABLE
ORD_2006 ORD_2006_B UNUSABLE
ORD_2006 ORD_2006_C UNUSABLE
ORD_2007 ORD_2007_A UNUSABLE
ORD_2007 ORD_2007_B UNUSABLE
ORD_2007 ORD_2007_C UNUSABLE
ORD_2008 ORD_2008_A UNUSABLE
ORD_2008 ORD_2008_B UNUSABLE
ORD_2008 ORD_2008_C UNUSABLE

12 rows selected.

SQL>
SQL> insert into MY_ORDERS_TABLE
2 select rownum,
3 to_date('30-JUN-2008','DD-MON-YYYY'),
4 decode(mod(rownum,100),0,1,200),
5 decode(mod(rownum,10),0,'A',1,'A',2,'A',3,'A',4,'A',5,'B',6,'B',7,'B',8,'B','C'),
6 4,
7 3,
8 'Orders on 30Jun2008'
9 from dual connect by level < 35000
10 /

34999 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter index MY_ORDERS_IDX rebuild subpartition ord_2008_a ;

Index altered.

SQL> alter index MY_ORDERS_IDX rebuild subpartition ord_2008_b ;

Index altered.

SQL> alter index MY_ORDERS_IDX rebuild subpartition ord_2008_c ;

Index altered.

SQL>
SQL>
SQL> select partition_name, subpartition_name, status from user_ind_subpartitions
2 where index_name = 'MY_ORDERS_IDX'
3 order by 1,2
4 /

PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ --------
ORD_2005 ORD_2005_A UNUSABLE
ORD_2005 ORD_2005_B UNUSABLE
ORD_2005 ORD_2005_C UNUSABLE
ORD_2006 ORD_2006_A UNUSABLE
ORD_2006 ORD_2006_B UNUSABLE
ORD_2006 ORD_2006_C UNUSABLE
ORD_2007 ORD_2007_A UNUSABLE
ORD_2007 ORD_2007_B UNUSABLE
ORD_2007 ORD_2007_C UNUSABLE
ORD_2008 ORD_2008_A USABLE
ORD_2008 ORD_2008_B USABLE
ORD_2008 ORD_2008_C USABLE

12 rows selected.

SQL>
SQL> select partition_name, bytes/1024 from user_segments
2 where segment_type like 'INDEX SUBPARTITION'
3 order by 1
4 /

PARTITION_NAME BYTES/1024
------------------------------ ----------
ORD_2005_A 64
ORD_2005_B 64
ORD_2005_C 64
ORD_2006_A 64
ORD_2006_B 64
ORD_2006_C 64
ORD_2007_A 64
ORD_2007_B 64
ORD_2007_C 64
ORD_2008_A 512
ORD_2008_B 384
ORD_2008_C 128

12 rows selected.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'MY_ORDERS_TABLE',estimate_percent=>100,-
> granularity=>'PARTITION',partname=>'ORD_2008',cascade=>FALSE);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'MY_ORDERS_IDX',estimate_percent=>100,-
> granularity=>'PARTITION',partname=>'ORD_2008');

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select order_date, customer_id from MY_ORDERS_TABLE
3 where
4 order_date = to_date('30-JUN-2008','DD-MON-YYYY')
5 and
6 product_id='C'
7 and order_id between 30 and 100
8 and customer_id=1
9 /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2230053563

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 37 | 3 (0)| 00:00:01 | 4 | 4 |
| 2 | PARTITION LIST SINGLE | | 1 | 37 | 3 (0)| 00:00:01 | 3 | 3 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| MY_ORDERS_TABLE | 1 | 37 | 3 (0)| 00:00:01 | 12 | 12 |
|* 4 | INDEX RANGE SCAN | MY_ORDERS_IDX | 1 | | 2 (0)| 00:00:01 | 12 | 12 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("ORDER_DATE"=TO_DATE(' 2008-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - access("ORDER_ID">=30 AND "CUSTOMER_ID"=1 AND "PRODUCT_ID"='C' AND "ORDER_ID"<=100)
filter("CUSTOMER_ID"=1)

Note
-----
- dynamic sampling used for this statement

22 rows selected.

SQL>


I have to be very careful with my GATHER_%_STATS calls -- any attempt to permit Global statistics fails as Oracle finds some Index partitions unusable.
Also, in the absence of Global statistics, the optimizer still reverts to Dynamic Sampling.

.
.
.

1 comment:

Taral said...

This is good example.

Just to make comment. In 11gr2 there is more enhancement.

Now, with same example if you try this

select order_date, customer_id
from MY_ORDERS_TABLE
where order_date between to_date('30-JUN-2007', 'DD-MON-YYYY') and to_date('30-JUN-2008', 'DD-MON-YYYY')
and product_id = 'C' and order_id between 30 and 100 and customer_id = 1;

You will be amazed how clever optimizer is