Hi :
I was create a partitioned index on partitioned table , but partitioned index does not work.
create table table_range (
CUST_FIRST_NAME VARCHAR2(20),
CUST_GENDER CHAR(1),
CUST_CITY VARCHAR2(30),
COUNTRY_ISO_CODE CHAR(2),
COUNTRY_NAME VARCHAR2(40),
COUNTRY_SUBREGION VARCHAR2(30),
PROD_ID NUMBER NOT NULL ,
CUST_ID NUMBER NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID NUMBER NOT NULL ,
PROMO_ID NUMBER NOT NULL ,
QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
partition by range (time_id) (
partition p1 values less than (to_date('2001/01/01','YYYY/MM/DD')) tablespace u01,
partition p2 values less than (to_date('2002/01/01','YYYY/MM/DD')) tablespace u02
);
create index ind_table_range on table2(prod_id)
global partition by range(prod_id)(
partition p1 values less than (100),
partition p2 values less than (maxvalue)
);
SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,HIGH_VALUE,NUM_ROWS from user_tab_partitions;
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE NUM_ROWS
----------- ---------------- ------------------ -------------------------------------------------------------------------------- ----------
TABLE_RANGE P2 0 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 259418
TABLE_RANGE P1 0 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 659425
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_ind_partitions;
INDEX_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ -------------------------- ----------
IND_TABLE_RANGE P1 100 479520
IND_TABLE_RANGE P2 MAXVALUE 439323
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_RANGE');
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_RANGE',GRANULARITY => 'PARTITION');
SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE');
SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE',GRANULARITY => 'PARTITION');
SQL> set autotrace traceonly
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select * from table_range
where prod_id = 127;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16469 | 1334K| 3579 (1)| 00:00:43 | | |
| 1 | PARTITION RANGE ALL| | 16469 | 1334K| 3579 (1)| 00:00:43 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | TABLE_RANGE | 16469 | 1334K| 3579 (1)| 00:00:43 | 1 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROD_ID"=127)
Statistics
----------------------------------------------------------
320 recursive calls
2 db block gets
13352 consistent gets
11820 physical reads
0 redo size
855198 bytes sent via SQL*Net to client
12135 bytes received via SQL*Net from client
1067 SQL*Net roundtrips to/from client
61 sorts (memory)
0 sorts (disk)
15984 rows processed