Skip to Main Content
  • Questions
  • Indexes on single Partition of the table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, arun.

Asked: April 02, 2008 - 3:17 pm UTC

Last updated: October 09, 2012 - 12:33 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

I have a table which has been partitioned by the months.Also in the same
table i have an indexes in 4 columns. I used partition as we will be loading the data everymonth. We will be having around 9 million rows everymonth.
While inserting the rows its being slow as i have indexes.The main reason for having index is that the data is being pulled every now and then for different purposes.
As of now, i am dropping the index while loading the data and re Creating once the data has been loaded. But what i see here is everytime i am dropping the index it is dropping the index and recreating in the whole table.It does not matter much right now as i only have data for the partition of JAn and Feb.

But as time goes and my data is being filled in every partition, dropping the index and recreating index again would cost more than not doing it.
SO the question is how can i can drop the index for a single partition of the table and recreate it. so that i dont have to recreate the index for all the partition.
IF not ,what would be the best solution?

and Tom said...

you can set an individual local index partition unusable, load partition and then just rebuild that local index partition.

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  -- subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) LOCAL;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select to_date('12-mar-2003','dd-mon-yyyy'), rownum, object_name from user_objects;

298 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select 'part1', count(*) from t partition(part1) union all select 'part2', count(*) from t partition(part2);

'PART   COUNT(*)
----- ----------
part1        298
part2          0

ops$tkyte%ORA10GR2> select partition_name, status from user_ind_partitions where index_name='T_IDX' order by 1;

PARTITION_NAME                 STATUS
------------------------------ --------
PART1                          USABLE
PART2                          USABLE

ops$tkyte%ORA10GR2> alter index t_idx modify partition part2 unusable;

Index altered.

ops$tkyte%ORA10GR2> select partition_name, status from user_ind_partitions where index_name='T_IDX' order by 1;

PARTITION_NAME                 STATUS
------------------------------ --------
PART1                          USABLE
PART2                          UNUSABLE

ops$tkyte%ORA10GR2> show parameter skip

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE
ops$tkyte%ORA10GR2> insert into t select to_date('13-mar-2003','dd-mon-yyyy'), rownum, object_name from user_objects;

298 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select 'part1', count(*) from t partition(part1) union all select 'part2', count(*) from t partition(part2);

'PART   COUNT(*)
----- ----------
part1        298
part2        298

ops$tkyte%ORA10GR2> select partition_name, status from user_ind_partitions where index_name='T_IDX' order by 1;

PARTITION_NAME                 STATUS
------------------------------ --------
PART1                          USABLE
PART2                          UNUSABLE

ops$tkyte%ORA10GR2> alter index t_idx rebuild partition part2 ;

Index altered.

ops$tkyte%ORA10GR2> select partition_name, status from user_ind_partitions where index_name='T_IDX' order by 1;

PARTITION_NAME                 STATUS
------------------------------ --------
PART1                          USABLE
PART2                          USABLE

Rating

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

If you have more indexes ...

Dragos Craciun, April 04, 2008 - 4:18 pm UTC

To make all local indexes on a partition unusable:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> UNUSABLE LOCAL INDEXES;

To rebuild:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> REBUILD UNUSABLE LOCAL INDEXES;

arun pradhan, April 07, 2008 - 10:17 am UTC

Hi Tom,
Thanks And its a great solution...I have been using temporary table and exchanging the partition till now... I beleive, making index unusable is same as having no indexes at all.
Arun Pradhan

arun pradhan, April 07, 2008 - 10:17 am UTC

Hi Tom,
Thanks And its a great solution...I have been using temporary table and exchanging the partition till now... I beleive, making index unusable is same as having no indexes at all.
Arun Pradhan

Local partitions

reader, July 23, 2009 - 10:52 am UTC

This is fantastic - I never knew you could do that!

Thanks Tom

Sachin, July 30, 2009 - 2:26 pm UTC

i tried what you said to set partition index unusable. but when i run select query optimiser won't consider indexes at all.
Tom Kyte
August 03, 2009 - 5:40 pm UTC

explain what you mean?

it won't use unusable indexes - this is true (new default setting:

ops$tkyte%ORA10GR2> show parameter unusable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE


it is true by default) - but so what? It isn't usable so we skip it?

If you want it to fail, unset that default setting, set it to false.

local index dropped

A reader, August 23, 2010 - 3:07 am UTC

Hi Tom,

I have following confusions about local indexes. It would be great if you can explain the same.

we have a range partitoned table and loacl index in the following way.
craete table tab1
(....)
partiton(...)
(.. part1..,
..part2..,
..part3..)

create index idx ...
local
(.. part1..,
..part2..,
..part3..)

1. If we drop one partition of a table why all the local indexes(part1,part2,part3) get dropped but idx remains in database? I couldn't find index in dba_part_indexes view
2. In the above case if we add a new partion how we will create indexes using idx.
3. What is the difference of creating local indexes by
create index CREATE INDEX year_idx
on all_facts (order_year)
LOCAL

and

CREATE INDEX year_idx
on all_facts (order_year)
LOCAL
(partion_name,
....
)
Because in both the cases i found sub_objects are created as par partiton names.
Tom Kyte
August 26, 2010 - 9:59 am UTC

it does not work the way you describe:

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
 11  )
 12  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) local
  2  ( partition I_part1, partition I_part2 );

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
------------------------------
PART1
PART2

ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME
------------------------------
I_PART1
I_PART2

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t drop partition part1;

Table altered.

ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
------------------------------
PART2

ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME
------------------------------
I_PART2

<b>that shows if you drop a table partition, only the affected index partition goes away... so that is counter to your #1 </b>


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add partition part3 values less than (to_date('15-mar-2003','dd-mon-yyyy'));

Table altered.

ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
------------------------------
PART3
PART2

ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME
------------------------------
PART3
I_PART2

<b>that shows if you add a table partition, we automagically add the local index partition for you - #2 </b>


and the difference between just "local" and "local (partition x, partition y)" is that the former local names the partitions after the table partitions (sensible) and the latter local (partition ....) lets you name the partitions differently (not as sensible in my opinion)


Partition On table.

Snehasish Das, September 02, 2010 - 11:56 pm UTC

Hi Tom,

I was thinking if we could set the partition of the tables as unused. Suppose we have a partition in which we think the data is corrupted and we dont want our query to fetch data from that partition. Is there any way we can set the table partition to be unused. I think we can set the tablespace offline for that partition but that is a dba stuff. Can you tell us if it is possible like setting the index unused.

Regards,
Snehasish Das
Tom Kyte
September 09, 2010 - 7:03 pm UTC

create an empty table that looks like the partitioned table, create indexes on it to match the local indexes on the partitioned table, exchange that empty table with the partition you believe to be logically corrupt.

if the partition is physically corrupt, you would have to media recovery it or drop it (and then restore it later and add it back in)

you cannot "set unused" a partition, you have to remove it. setting an index unused doesn't "change the answer to queries", setting a table partition 'unused' would. therefore you need to drop it.

Index on Partition

Ananth, October 07, 2011 - 4:21 pm UTC

Hi Tom,

Can i create index only on one of the partitions.
we Have 66 paritions and we dont require indexes on 65 paritions.

Is there any way to create index only on 66th partition..?

Regards
Ananth

Why index is not getting used

Rajeshwaran Jeyabal, August 26, 2012 - 6:07 am UTC

Tom,

Can you help me why the index is not getting used here?

on every data load we
1) Truncate the partition
2) load data into each partition
3) Gather stats on partition with granulairy ='GLOBAL AND PARTITION'
CREATE TABLE RPT_BLNKEXCLUDED_SPC_PHI 
( 
  rpt_blnkexcluded_spc_seq NUMBER  NOT NULL , 
  Header_Filename VARCHAR2 (100 )   , 
  Detail_filename VARCHAR2 (100 )   , 
  Type VARCHAR2 (40 )  NOT NULL , 
  source_cd VARCHAR2 (20 )  NOT NULL , 
  encounter_nbr VARCHAR2 (36 )  NOT NULL , 
  Encounter_key NUMBER  NOT NULL , 
  member_nbr VARCHAR2 (42 ) , 
  Hic_nbr VARCHAR2 (20 ) , 
  from_dos DATE , 
  to_dos DATE , 
  diag_cd_count NUMBER , 
  diag_cd VARCHAR2 (400 ) , 
  enc_create_dt date,
  provider_nbr VARCHAR2 (16 ) , 
  specialty_cd VARCHAR2 (10 ) , 
  cms_specialty_cd VARCHAR2 (6 ) , 
  roi_project VARCHAR2 (20 ),
  client      varchar2(20),
  error_key   number
) 
partition by list(client)
( partition p_HEALTHNET values ('H') ,
 partition p_TUFTS  values ('T') ,
 partition p_WINDSOR values ('W'),
 partition p_max  values ( default ) )
 nologging ;
 
create index idx_rpt_blnk_phi_01
on RPT_BLNKEXCLUDED_SPC_PHI(rpt_blnkexcluded_spc_seq,error_key)
LOCAL nologging;


test@ORA10GR2>
test@ORA10GR2> select count(rpt_blnkexcluded_spc_seq)
  2  from rpt_blnkexcluded_spc_phi
  3  where client ='H';

1 row selected.

Elapsed: 00:00:01.96

Execution Plan
----------------------------------------------------------
Plan hash value: 1800485289

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |     1 |    10 |    55   (0)| 00:00:01 |    |          |
|   1 |  SORT AGGREGATE        |                          |     1 |    10 |            |          |    |          |
|   2 |   PARTITION LIST SINGLE|                          |  5304 | 53040 |    55   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | RPT_BLNKEXCLUDED_SPC_PHI |  5304 | 53040 |    55   (0)| 00:00:01 |  1 |        1 |
-------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        359  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

test@ORA10GR2>
test@ORA10GR2>
test@ORA10GR2> select /*+ index(t,IDX_RPT_BLNK_PHI_01) */ count(rpt_blnkexcluded_spc_seq)
  2  from rpt_blnkexcluded_spc_phi t
  3  where client ='H'
  4  /

1 row selected.

Elapsed: 00:00:01.98

Execution Plan
----------------------------------------------------------
Plan hash value: 144971883

--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |     1 |    10 |   149   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE        |                     |     1 |    10 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|                     |  5304 | 53040 |    15   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    INDEX FULL SCAN     | IDX_RPT_BLNK_PHI_01 |  5304 | 53040 |    15   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        359  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

test@ORA10GR2>
test@ORA10GR2> select count(*) from rpt_blnkexcluded_spc_phi;

  COUNT(*)
----------
      5304

1 row selected.

Elapsed: 00:00:01.20

Tom Kyte
August 29, 2012 - 1:18 pm UTC

these are so tiny, I don't even see how this could take the 1.98 seconds and 1.2 seconds.

You are doing 174 logical IO's, that should return in milliseconds.

are you running this on a cellphone or something? (I'm semi serious here - why is this taking 2 seconds????)


basically, it is getting the estimated cardinality wrong. You unfortunately decided to NOT share the number of matching rows with us (why?????)

I see the estimated cardinality is 5,304. I see the number of rows in the table is 5,304. I doubt the count is 5,304 - it is probably closer to zero.

so, are your statistics representative of the data in the table?

Why index is not getting used

Rajeshwaran, Jeyabal, September 09, 2012 - 8:37 am UTC

so, are your statistics representative of the data in the table? - So here is my Testcase to help you.

Questions
1) The Num_Rows in user_tab_statistics (and even in user_ind_statistics) says that each partition has 14501 rows, but estimated cardinality in Explain plan says 145. Why does this happens even if i say estimate_percent=>100 in Gather stats?

2)Here object_id column is NOT NULL, Why the optimizer pickup, Full Table Scan on partition p1 rather than Index full scan on partition p1. using index we get 34 LIO better than table scan we get 216 LIO.

3)Here this index is skinny version of Table. I feel index read should be best than table read. whats your take on this?

rajesh@ORA11GR2> create table t
  2  partition by list(id)
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4),
  7    partition p5 values (5),
  8    partition pmax values (default)
  9  ) nologging as
 10  select a.*, mod(rownum,5)+1 as id
 11  from all_objects a;

Table created.

Elapsed: 00:00:07.87
rajesh@ORA11GR2> create index t_ind
  2  on t(object_id)
  3  nologging local;

Index created.

Elapsed: 00:00:00.56
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     (ownname=>user,
  4      tabname=>'T',
  5      estimate_percent=>100,
  6      method_opt=>'for all indexed columns size 254',
  7      cascade=>true);
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,
  2  partition_name,num_rows
  3  from user_tab_statistics;

TABLE PARTI NUM_ROWS
----- ----- --------
T              72505
T     PMAX         0
T     P5       14501
T     P4       14501
T     P3       14501
T     P2       14501
T     P1       14501

7 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> select index_name, partition_name, num_rows
  2  from user_ind_statistics;

INDEX_NAME PARTI NUM_ROWS
---------- ----- --------
T_IND               72505
T_IND      P1       14501
T_IND      P2       14501
T_IND      P3       14501
T_IND      P4       14501
T_IND      P5       14501
T_IND      PMAX         0

7 rows selected.

Elapsed: 00:00:01.10
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
OBJECT_ID                 255

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from t
  2  where id = 1;

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2831600127

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |    65   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |   145 |  1885 |    65   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T    |   145 |  1885 |    65   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        216  consistent gets
        215  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */
  2  count(*) from t
  3  where id = 1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2422430388

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    13 |   257   (0)| 00:00:04 |       |       |
|   1 |  SORT AGGREGATE        |       |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|       |   145 |  1885 |    34   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    INDEX FULL SCAN     | T_IND |   145 |  1885 |    34   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select count(*) from t where id = 1;

COUNT(*)
--------
   14501

Elapsed: 00:00:00.46
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Elapsed: 00:00:00.12
rajesh@ORA11GR2>

Tom Kyte
September 26, 2012 - 11:24 am UTC

it is due to the way you gathered statistics.

we have column stats on exactly one column - the column you happen to not use in the where clause.

by using "for all indexed columns size 254", you preclude any column level stats on ID, hence we "guess"

get rid of your method opt
get rid of your cascade (we already have stats on the index, when you created it we computed them)
get rid of your estimate percent (it is already going to be pretty much compute in 11g)

you don't want to use those parameters typically (except maybe to demonstrate a concept). you need statistics on columns you use in where clauses (not just indexed columns). you want to let things default especially in 11g (else you are using the old 10g slow algorithms to gather statistics).


ops$tkyte%ORA11GR2> create table t
  2  partition by list(id)
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4),
  7    partition p5 values (5),
  8    partition pmax values (default)
  9  ) nologging as
 10  select a.*, mod(rownum,5)+1 as id
 11  from all_objects a;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_ind
  2  on t(object_id)
  3  nologging local;

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     (ownname=>user,
  4      tabname=>'T' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select table_name,
  2  partition_name,num_rows
  3  from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
T                                                                  72826
T                              PMAX                                    0
T                              P5                                  14565
T                              P4                                  14565
T                              P3                                  14565
T                              P2                                  14566
T                              P1                                  14565

7 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select index_name, partition_name, num_rows
  2  from user_ind_statistics;

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
T_IND                                                              72826
T_IND                          P1                                  14565
T_IND                          P2                                  14566
T_IND                          P3                                  14565
T_IND                          P4                                  14565
T_IND                          P5                                  14565
T_IND                          PMAX                                    0

7 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  group by column_name;

COLUMN_N   COUNT(*)
-------- ----------
SUBOBJEC          2
T_NAME

TIMESTAM          2
P

STATUS            2
OBJECT_I          2
D

CREATED           2
GENERATE          2
D

SECONDAR          2
Y

LAST_DDL          2
_TIME

DATA_OBJ          2
ECT_ID

OBJECT_T          2
YPE

NAMESPAC          2
E

ID                2
OWNER             2
OBJECT_N          2
AME

TEMPORAR          2
Y


15 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain statistics;
ops$tkyte%ORA11GR2> select count(*) from t
  2  where id = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2831600127

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     3 |    65   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      | 14565 | 43695 |    65   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T    | 14565 | 43695 |    65   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        217  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select /*+ index(t,t_ind) */
  2  count(*) from t
  3  where id = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2422430388

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |     3 |   415   (1)| 00:00:05 |       |       |
|   1 |  SORT AGGREGATE        |       |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|       | 14565 | 43695 |    34   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    INDEX FULL SCAN     | T_IND | 14565 | 43695 |    34   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace off;
ops$tkyte%ORA11GR2> select count(*) from t where id = 1;

  COUNT(*)
----------
     14565

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Why index is not getting used

A reader, September 20, 2012 - 8:53 am UTC

Tom,

The above question looks very interesting, we would like to hear from you. Can you please answer above questions, also the test-case looks very promising.

Tom Kyte
September 26, 2012 - 11:16 am UTC

way to pump up your own comment! excellent. of course it looks interesting to you, you wrote it!

Why index is not getting used

Rajeshwaran, Jeyabal, September 27, 2012 - 8:05 am UTC

Tom,

So whats the deal here? you did 11g method of stats gathering (leaving out all to params to default). we say partition pruning got kicked for both queries (one without index hint and other with index hint).

1) But why does oracle doesn't pick up the plan with 34 IO's rather it picked up plan with 217 IO's by default?

2) what addtional information do i need to supply to this 11g optimizer to pickup this index plan ? is that am i missing out something in metadata? if yes please point me that.
Tom Kyte
September 27, 2012 - 9:09 am UTC

because it wanted to use multiblock IO, not single block IO.

and these segments are so so so small it doesn't really matter.

Why index is not getting used

Rajeshwaran, Jeyabal, September 27, 2012 - 9:36 pm UTC

loaded some high volumn of data into each partition, and now i am able to see Index scan.

scott@ORCL> create table t
  2  partition by list(id)
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4),
  7    partition p5 values (5),
  8    partition pmax values (default) )
  9  nologging as
 10  select a.*, mod(rownum,5)+1 as id
 11  from all_objects a;

Table created.

Elapsed: 00:00:08.90
scott@ORCL> variable x number;
scott@ORCL> exec :x := 5000000;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
scott@ORCL>
scott@ORCL> declare
  2     l_count number := 0;
  3  begin
  4     select count(*) into l_count from t;
  5     while (l_count <= :x)
  6     loop
  7             dbms_application_info.set_client_info('l_count ='||l_count);
  8             insert /*+ append */ into t
  9             select owner,object_name,subobject_name,object_id,
 10             data_object_id,object_type,created,last_ddl_time,
 11             timestamp,status,temporary,generated,secondary,namespace,
 12             edition_name,rownum + l_count
 13             from t
 14             where rownum <= ( :x - l_count);
 15             exit when sql%rowcount =0;
 16             l_count := l_count + sql%rowcount ;
 17             commit;
 18     end loop;
 19     commit;
 20  end;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.84
scott@ORCL>
scott@ORCL> select count(*) from t;

  COUNT(*)
----------
   5000000

Elapsed: 00:00:06.92
scott@ORCL>
scott@ORCL> begin
  2     dbms_stats.gather_table_stats
  3     (ownname=> user,
  4      tabname=>'T');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.39
scott@ORCL> create index t_ind_01x
  2  on t(id) nologging
  3  local ;

Index created.

Elapsed: 00:00:09.60
scott@ORCL> set autotrace traceonly explain statistics;
scott@ORCL> select count(*) from t where id = 2;

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1263209374

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     3 |     9   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           | 14333 | 42999 |     9   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    INDEX FAST FULL SCAN| T_IND_01X | 14333 | 42999 |     9   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
         28  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

index on specified partition(s)

Charlie 木匠, October 05, 2012 - 12:38 pm UTC

After Oracle 12c, we can create index on partitons level.

http://jonathanlewis.wordpress.com/2012/10/02/partitioning-12c/

"partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”."

Tom Kyte
October 09, 2012 - 12:33 pm UTC

we could do that to a degree in 11g as well.

http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21asktom-312223.html
see partition plans

Partial Paritioned index -12c

Rajeshwaran Jeyabal, January 20, 2014 - 7:01 pm UTC

Tom:

I am reading about partial Index for partitioned table from 12c product docs. http://docs.oracle.com/cd/E16655_01/server.121/e17633/schemaob.htm#sthref490 I don't see the below 3 points mentioned in docs doesn't takes place. Is that I am missing something?

You can turn indexing on or off for the individual partitions of a table.

1) A partial local index does not have usable index partitions for all table partitions that have indexing turned off. - This is not happening Usable status for all index partitions even though indexing turned off

2) A global index, whether partitioned or not, excludes the data from all partitions that have indexing turned off. - This doesn't happen, global index T_IND2 has 46 leaf rows even though indexing turned off on some partition

3) The database does not support partial indexes for indexes that enforce unique constraints. - again this doesn't happen at all see below
rajesh@PDB1> create table t
  2  partition by list(x)
  3  (
  4     partition p1 values (1) indexing off,
  5     partition p2 values (2) indexing on,
  6     partition p3 values (3) indexing off
  7  )
  8  as
  9  select t.*, mod(rownum,3)+1 x
 10  from all_users t ;

Table created.

rajesh@PDB1>
rajesh@PDB1> create index t_ind on t(username) local;

Index created.

rajesh@PDB1>
rajesh@PDB1> select TABLE_NAME,partition_name
  2  from user_tab_partitions
  3  where table_name ='T';

TABLE_NAME PARTITION_
---------- ----------
T          P1
T          P2
T          P3

3 rows selected.

rajesh@PDB1>
rajesh@PDB1> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name='T_IND';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_IND      P1         USABLE
T_IND      P2         USABLE
T_IND      P3         USABLE

3 rows selected.

rajesh@PDB1>
rajesh@PDB1> create index t_ind2 on t(user_id);

Index created.

rajesh@PDB1> select count(*) from t;

  COUNT(*)
----------
        46

1 row selected.

rajesh@PDB1> analyze index t_ind2 validate structure;

Index analyzed.

rajesh@PDB1> select name,lf_rows from index_stats;

NAME                                                  LF_ROWS
-------------------------------------------------- ----------
T_IND2                                                     46

1 row selected.

rajesh@PDB1>
rajesh@PDB1> alter table t add constraint t_pk
  2  unique(user_id)
  3  using index t_ind2;

Table altered.

rajesh@PDB1>



Partial Paritioned index -12c

Rajeshwaran Jeyabal, January 21, 2014 - 9:59 am UTC

Tom,

I think I missed out the new INDEXING PARTIAL clause of the create index statement.

Bug in unusable indexes?

Dominic Brooks, March 24, 2014 - 1:41 pm UTC

I have found that if you hint (for experimental purposes) an access path of a deliberately unusable index, then you get an ORA-01502.

This has repercussions for SQL Plan Management, etc.

Example:
SQL> create table t1
2 (col1 number)
3 partition by list(col1)
4 (partition p0 values(0),
5 partition pdef values(default));

Table created.

SQL> create index i1 on t1 (col1) local unusable;

Index created.

SQL> alter index i1 rebuild partition pdef;

Index altered.

SQL> select * from t1;

no rows selected

SQL> select * from t1 partition (p0);

no rows selected

SQL> select /*+ index(t1 i1) */ * from t1 partition (p0);

no rows selected

SQL> select * from t1 where col1 = 0;

no rows selected

SQL> select /*+ index(t1 i1) */ * from t1 where col1 = 0;
select /*+ index(t1 i1) */ * from t1 where col1 = 0
*
ERROR at line 1:
ORA-01502: index 'XYZ.I1' or partition of such index is in unusable state

I'm trying to raise this as bug but the analyst insists that this is expected behaviour despite the relevant notes all relating to unique indexes and insert/update activity.
Any thoughts?

This does not work if the index created is a unique index.

Sudip Sarkar, June 03, 2014 - 12:51 pm UTC

Hi tom,
In your example you created a Table T with Partition on DT. Then you created a local index on DT.
I also tried the same on my table but the index I created a UNIQUE local index. So when I make the particular index unusable I am not able to insert nay records. My table structure is
CREATE TABLE PUBLISH_PRINT_RECORD
( PARTITION_CD VARCHAR2(7) NOT NULL ENABLE,
ID_CLIENT NUMBER,
DT_ENROLL DATE)
PARTITION BY LIST (PARTITION_CD)
(
PARTITION "P201403" VALUES ('P201403')
COMPRESS );

CREATE UNIQUE INDEX IX_PUBLISH_PRINT_RECORD_01 ON PUBLISH_PRINT_RECORD(partition_cd,id_client) LOCAL;
ALTER INDEX IX_PUBLISH_PRINT_RECORD_01 MODIFY PARTITION "P201403" UNUSABLE;

INSERT INTO PUBLISH_PRINT_RECORD (partition_cd,id_client)
values('P201403',1);
It gives me the following error
Error report:
SQL Error: ORA-01502: index 'NA_DEV.IX_PUBLISH_PRINT_RECORD_01' or partition of such index is in unusable state
01502. 00000 - "index '%s.%s' or partition of such index is in unusable state"
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition

Thanks a lot for all your help.

Set unusable an rebuild in case of interval partitioning

Andrej, July 15, 2015 - 7:34 am UTC

If I bulk load data into an interval partitioned table with local indexes, new partitions are being created "on the fly" for me. Therefore I cannot set the index partitions to be unusabe, because they don't exist before I start my loading process.

Is it possible to workaround this? I tried to create an index "initially unusable" (as i thought) with:

CREATE INDEX .... UNUSABLE

But this makes it unusable only for existing partition(s). For newly created partitions the corresponding index partition is in usable state.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.