Saturday, August 5, 2017

Oracle 12c Partial Indexes for Partitioned Tables

SQL> CREATE TABLE orders (
  2      order_id       NUMBER(12),
  3      order_date     DATE,
  4      order_mode     VARCHAR2(8),
  5      customer_id    NUMBER(6),
  6      order_status   NUMBER(2),
  7      order_total    NUMBER(8,2),
  8      sales_rep_id   NUMBER(6),
  9      promotion_id   NUMBER(6),
  10       CONSTRAINT order_pk PRIMARY KEY ( order_id )
  11   )
  12       INDEXING OFF
  13           PARTITION BY RANGE ( order_date ) (
  14           PARTITION P2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')) INDEXING OFF,
  15           PARTITION P2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) INDEXING OFF,
  16           PARTITION P2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) INDEXING OFF,
  17           PARTITION P2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) INDEXING OFF,
  18           PARTITION P2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) INDEXING ON,
  19           PARTITION P2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) INDEXING ON
  20           )
  21  /

Table ORDERS created.

SQL> select order_date from oe.orders sample(10);
ORDER_DATE
-------------------------------
14-SEP-06 06.03.04.763452000 AM
17-NOV-06 01.22.11.262552000 AM
12-MAR-07 08.53.54.562432000 PM
29-MAR-07 03.41.20.945676000 PM
07-JUN-07 05.18.08.883310000 AM
16-AUG-07 02.34.12.234359000 PM
10-NOV-07 04.49.25.526321000 AM
27-FEB-08 03.41.45.109654000 AM
26-JUN-08 09.19.43.190089000 PM

9 rows selected.

SQL> insert into orders select * from oe.orders;
105 rows inserted.

SQL> commit;

Commit complete.

SQL> create index order_gi1 on orders (sales_rep_id) global indexing partial;
 

Index ORDER_GI1 created.

SQL> create index order_li1 on orders (customer_id) local indexing partial;

Index ORDER_LI1 created.

SQL> set sqlformat ansiconsole

SQL> select partition_name,indexing from user_tab_partitions where table_name='ORDERS';
PARTITION_NAME  INDEXING
P2004           OFF
P2005           OFF
P2006           OFF
P2007           OFF

P2008           ON
P2009           ON
6 rows selected.

SQL>  select index_name,partition_name,status from user_ind_partitions;
INDEX_NAME  PARTITION_NAME  STATUS
ORDER_LI1   P2004           UNUSABLE
ORDER_LI1   P2005           UNUSABLE
ORDER_LI1   P2006           UNUSABLE
ORDER_LI1   P2007           UNUSABLE

ORDER_LI1   P2008           USABLE
ORDER_LI1   P2009           USABLE


6 rows selected.


SQL> select index_name,indexing from user_indexes;
INDEX_NAME  INDEXING
ORDER_LI1   PARTIAL
ORDER_PK    FULL
ORDER_GI1   PARTIAL


SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                                                                        
Plan hash value: 670661013                                                                                               
                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |           |     1 |    93 |   821   (1)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2   |     2 |   186 |   821   (1)| 00:00:01 |       |       |
|   2 |  UNION-ALL                                  |           |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS    |     1 |    93 |     1   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                         | ORDER_GI1 |     1 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ITERATOR                  |           |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
|*  6 |     TABLE ACCESS FULL                        | ORDERS    |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------------------------------
                
Predicate Information (identified by operation id):                                                                      
---------------------------------------------------                                  
   3 - filter("ORDERS"."ORDER_DATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND        
              "ORDERS"."ORDER_DATE"<TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
   4 - access("SALES_REP_ID"=154)                                                                    
   6 - filter("SALES_REP_ID"=154)  
       
Note                                                                                        
-----                                                                      
   - dynamic statistics used: dynamic sampling (level=2)                        

25 rows selected.



SQL> explain plan for select * from orders where customer_id=104;
Explained.


SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                                                                        
Plan hash value: 4090115495                                                                                              
                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |           |     1 |    93 |   963   (1)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2   |    17 |  1581 |   963   (1)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR                  |           |    16 |  1488 |   143   (0)| 00:00:01 |     5 |     6 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS    |    16 |  1488 |   143   (0)| 00:00:01 |     5 |     6 |
|*  5 |      INDEX RANGE SCAN                        | ORDER_LI1 |    16 |       |     1   (0)| 00:00:01 |     5 |     6 |
|   6 |    PARTITION RANGE ITERATOR                  |           |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
|*  7 |     TABLE ACCESS FULL                        | ORDERS    |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                      
---------------------------------------------------                  
   5 - access("CUSTOMER_ID"=104)                  
   7 - filter("CUSTOMER_ID"=104)                 
                                                                                                                         
Note                                                                                                                     
-----              
   - dynamic statistics used: dynamic sampling (level=2) 


24 rows selected.