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.

.
.
.