Search My Oracle Blog

Custom Search

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016