Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sabin.

Asked: February 26, 2013 - 12:23 pm UTC

Last updated: December 22, 2020 - 7:14 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Tom,

Could you please explain for which scenario I should consider non-prefix local index over prefix local index?

Thank you very much for you time.



and we said...

Here is an excerpt from my book "Expert Oracle database Architecture" on this very topic:

<quote>
Local Indexes
Oracle makes a distinction between the following two types of local indexes:
 * Local prefixed indexes: These are indexes whereby the partition keys are on the leading edge of the index definition. For example, if a table is range partitioned on a column named LOAD_DATE, a local prefixed index on that table would have LOAD_DATE as the first column in its column list.
 * Local nonprefixed indexes: These indexes do not have the partition key on the leading edge of their column list. The index may or may not contain the partition key columns.
Both types of indexes are able to take advantage of partition elimination, both can support uniqueness (as long as the non-prefixed index includes the partition key), and so on. The fact is that a query that uses a local prefixed index will always allow for index partition elimination, whereas a query that uses a local non-prefixed index might not. This is why local non-prefixed indexes are said to be “slower” by some people—they do not enforce partition elimination (but they do support it). 
There is nothing inherently better about a local prefixed index as opposed to a local nonprefixed index when that index is used as the initial path to the table in a query. What I mean by that is that if the query can start with “scan an index” as the first step, there isn’t much difference between a prefixed and a nonprefixed index. 
Partition Elimination Behavior
For the query that starts with an index access, whether or not it can eliminate partitions from consideration all really depends on the predicate in your query. A small example will help demonstrate this. The following code creates a table, PARTITIONED_TABLE, that is range partitioned on a numeric column A such that values less than two will be in partition PART_1 and values less than three will be in partition PART_2:
ops$tkyte@ORA11GR2> CREATE TABLE partitioned_table
  2  ( a int,
  3    b int,
  4    data char(20)
  5  )
  6  PARTITION BY RANGE (a)
  7  (
  8  PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
  9  PARTITION part_2 VALUES LESS THAN(3) tablespace p2
 10  )
 11  /
Table created.
We then create both a local prefixed index, LOCAL_PREFIXED, and a local nonprefixed index, LOCAL_NONPREFIXED. Note that the nonprefixed index does not have A on the leading edge of its definition, which is what makes it a nonprefixed index:
ops$tkyte@ORA11GR2> create index local_prefixed on partitioned_table (a,b) local;
Index created.

ops$tkyte@ORA11GR2> create index local_nonprefixed on partitioned_table (b) local;
Index created.
Next, we’ll insert some data into one partition and gather statistics:
ops$tkyte@ORA11GR2> insert into partitioned_table
  2  select mod(rownum-1,2)+1, rownum, 'x'
  3    from all_objects;
72771 rows created.

ops$tkyte@ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     ( user,
  4      'PARTITIONED_TABLE',
  5       cascade=>TRUE );
  6  end;
  7  /
PL/SQL procedure successfully completed.
We take offline tablespace P2, which contains the PART_2 partition for both the tables and indexes:
ops$tkyte@ORA11GR2> alter tablespace p2 offline;
Tablespace altered.
Taking tablespace P2 offline will prevent Oracle from accessing those specific index partitions. It will be as if we had suffered “media failure,” causing them to become unavailable. Now we’ll query the table to see what index partitions are needed by different queries. This first query is written to permit the use of the local prefixed index:
ops$tkyte@ORA11GR2> select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- --------------------
         1          1 x
That query succeeded, and we can see why by reviewing the explain plan. We’ll use the built-in package DBMS_XPLAN to see what partitions this query accesses. The PSTART (partition start) and PSTOP (partition stop) columns in the output show us exactly what partitions this query needs to have online and available in order to succeed:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE SINGLE            |                   |     1 |     1 |     1 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     1 |
|    INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1 |     1 |     1 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"=1 AND "B"=1)
Note The DBMS_XPLAN output has been edited to remove information that was not relevant, in order to permit the examples to fit on the printed page.
So, the query that uses LOCAL_PREFIXED succeeds. The optimizer was able to exclude PART_2 of LOCAL_PREFIXED from consideration because we specified A=1 in the query, and we can see that clearly in the plan PSTART and PSTOP are both equal to 1. Partition elimination kicked in for us. The second query fails, however:
ops$tkyte@ORA11GR2> select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/home/ORA11GR2/.../o1_mf_p2_1dzn8jwp_.dbf'
no rows selected
And using the same technique, we can see why:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE ALL               |                   |     1 |     1 |     2 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     2 |
|    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |     1 |     2 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=1)
Here the optimizer was not able to remove PART_2 of LOCAL_NONPREFIXED from consideration—it needed to look in both the PART_1 and PART_2 partitions of the index to see if B=1 was in there. Herein lies a performance issue with local nonprefixed indexes: they do not make you use the partition key in the predicate as a prefixed index does. It is not that prefixed indexes are better; it’s just that in order to use them, you must use a query that allows for partition elimination.
If we drop the LOCAL_PREFIXED index and rerun the original successful query as follows:
ops$tkyte@ORA11GR2> drop index local_prefixed;
Index dropped.

ops$tkyte@ORA11GR2> select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- --------------------
         1          1 x
it succeeds, but as we’ll see, it used the same index that just a moment ago failed us. The plan shows that Oracle was able to employ partition elimination here—the predicate A=1 was enough information for the database to eliminate index partition PART_2 from consideration:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE SINGLE            |                   |     1 |     1 |     1 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     1 |
|    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |     1 |     1 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"=1)
   3 - access("B"=1)
Note the PSTART and PSTOP column values of 1 and 1.This proves that the optimizer is able to perform partition elimination even for nonprefixed local indexes.
If you frequently query the preceding table with the following queries:
select ... from partitioned_table where a = :a and b = :b;
select ... from partitioned_table where b = :b;
then you might consider using a local nonprefixed index on (b,a). That index would be useful for both of the preceding queries. The local prefixed index on (a,b) would be useful only for the first query.
The bottom line here is that you should not be afraid of nonprefixed indexes or consider them as major performance inhibitors. If you have many queries that could benefit from a nonprefixed index as outlined previously, then you should consider using one. The main concern is to ensure that your queries contain predicates that allow for index partition elimination whenever possible. The use of prefixed local indexes enforces that consideration. The use of nonprefixed indexes does not. Consider also how the index will be used. If it will be used as the first step in a query plan, there are not many differences between the two types of indexes. 


</quote>

Rating

  (8 ratings)

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

Comments

wld my query use the index ??

Pradeep Sorari, June 11, 2013 - 2:07 pm UTC

We have a front end error report which is getting 504 gateway timeout error due to poor performance of backend query (inside a package).

As a resolution I have created a temp table which is storing all error data and frontend wld now directly quering this table. now I am planning to cerate a index in a date field which is used in where clause as filter.....

Do I need to analyse index OR need to do anyhing else (apart from just creating index) to make this index available for use. I mean in general if we create a index on table do we need performe any other operation on it and why ??? I have seen you analyzing table after that.....
Tom Kyte
June 18, 2013 - 2:45 pm UTC

wld? what is that word?



since 10g, a create index or rebuild index automagically computes statistics for the index. There is no need to gather statistics for it initially.

However, as the data changes over time, you may need to gather statistics on the index (and table) so the optimizer has access to representative statistics for the objects.

Problem With Non-Prefix Local Index In Flat Table

Vahid Sadeghi, April 18, 2014 - 7:19 pm UTC

Hi,
This is my Flat Table , an FAT Table

CREATE TABLE FLAT_TABLE
(
ID NUMBER,
.
.
. ,
F_YEARCODE ,
F_SEASONCODE,
.
.
)
PARTITION BY RANGE ( F_YEARCODE )
(
PARTITION PART_1 VALUES LESS THAN(2007),
PARTITION PART_2 VALUES LESS THAN(2008),
PARTITION PART_3 VALUES LESS THAN(2009),
PARTITION PART_4 VALUES LESS THAN(2010),
PARTITION PART_5 VALUES LESS THAN(2011),
PARTITION PART_6 VALUES LESS THAN(2012),
PARTITION PART_7 VALUES LESS THAN(2013),
PARTITION PART_8 VALUES LESS THAN(2014)
) ;

Also I create a NON-Prefix Index on it :

CREATE INDEX local_nonprefixed ON FLAT_TABLE ( F_SEASONCODE ) LOCAL;

But when i explain plan the following query , i see that oracle doesn't use the local_nonprefixed index ?

SELECT * FROM FLAT_TABLE WHERE F_YEARCODE = 2010 AND F_SEASONCODE = 1;

OR

SELECT count(*) FROM FLAT_TABLE WHERE F_YEARCODE = 2010 AND F_SEASONCODE = 1;


Tom Kyte
April 18, 2014 - 9:26 pm UTC

it works dandy for me - unless you've defined f_seasoncode as a string of course.

as long as f_seasoncode is a number (which you unfortunately didn't say :( .....), it will partition eliminate and as long as the index is "selective" (eg: where f_seasoncode = 1 returns a SMALL number of rows) - it would go for it:

ops$tkyte%ORA11GR2> drop table flat_table;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE FLAT_TABLE
  2  (
  3  ID NUMBER,
  4  F_YEARCODE number,
  5  F_SEASONCODE number,
  6  data char(200)
  7  )
  8  PARTITION BY RANGE ( F_YEARCODE )
  9  (
 10  PARTITION PART_1 VALUES LESS THAN(2007),
 11  PARTITION PART_2 VALUES LESS THAN(2008),
 12  PARTITION PART_3 VALUES LESS THAN(2009),
 13  PARTITION PART_4 VALUES LESS THAN(2010),
 14  PARTITION PART_5 VALUES LESS THAN(2011),
 15  PARTITION PART_6 VALUES LESS THAN(2012),
 16  PARTITION PART_7 VALUES LESS THAN(2013),
 17  PARTITION PART_8 VALUES LESS THAN(2014)
 18  ) ;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE INDEX local_nonprefixed ON FLAT_TABLE ( F_SEASONCODE ) LOCAL;

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from flat_table where f_yearcode = 2010 and f_seasoncode = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1474341237

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |   241 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |   241 |     1   (0)| 00:00:01 |     5 |     5 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| FLAT_TABLE        |     1 |   241 |     1   (0)| 00:00:01 |     5 |     5 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     1   (0)| 00:00:01 |     5 |     5 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("F_YEARCODE"=2010)
   3 - access("F_SEASONCODE"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off



however, if you have seasoncode as a string type, it will not be able to use the index - due to the implicit conversion (compare strings to strings, numbers to numbers, dates to dates - and never ever allow implicit conversions to happen!)

ops$tkyte%ORA11GR2> drop table flat_table;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE FLAT_TABLE
  2  (
  3  ID NUMBER,
  4  F_YEARCODE number,
  5  F_SEASONCODE varchar2(5),
  6  data char(200)
  7  )
  8  PARTITION BY RANGE ( F_YEARCODE )
  9  (
 10  PARTITION PART_1 VALUES LESS THAN(2007),
 11  PARTITION PART_2 VALUES LESS THAN(2008),
 12  PARTITION PART_3 VALUES LESS THAN(2009),
 13  PARTITION PART_4 VALUES LESS THAN(2010),
 14  PARTITION PART_5 VALUES LESS THAN(2011),
 15  PARTITION PART_6 VALUES LESS THAN(2012),
 16  PARTITION PART_7 VALUES LESS THAN(2013),
 17  PARTITION PART_8 VALUES LESS THAN(2014)
 18  ) ;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE INDEX local_nonprefixed ON FLAT_TABLE ( F_SEASONCODE ) LOCAL;

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from flat_table where f_yearcode = 2010 and f_seasoncode = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1109107995

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |   232 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|            |     1 |   232 |     2   (0)| 00:00:01 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | FLAT_TABLE |     1 |   232 |     2   (0)| 00:00:01 |     5 |     5 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("F_YEARCODE"=2010 AND TO_NUMBER("F_SEASONCODE")=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off

Problem With Non-Prefix Local Index In Flat Table

Vahid Sadeghi, April 19, 2014 - 10:05 am UTC

Oh , sorry
The F_YEARCODE , F_SEASONCODE are number .

The partition that it's key value ( F_YEARCODE ) contains 10000000 records , that abut 4000000 records have F_SEASONCODE = 1 , it means about 1/2 of records.
Does it true because about 1/2 of all records has F_SEASONCODe = 1 , oracle bypass the index ?

Clarification

kasturi, May 25, 2014 - 2:11 pm UTC

Tom:

How can a prefixed local Index help for queries performance where partition key is not in the where clause of the query? in that case should not the local non prefixed index with partition key as secondary better/preferred? Can you please explain me? Why do we need a preferred index then?

The consistent gets associated with index range scan
when partition key is not in where clause is almost twice. The problem is even worse in case of a composite partition.

In a very high active and large OLTP system where each partition has close to 30 million records with 6 global indexes having a global index is a pain. The Global update Indexes can be a tough challenge.

I am not sure how we can ever solve this problem?

Thanks and Regards,

Non-Prefixed - Many partitions (> 400)

Brian Blades, September 19, 2014 - 5:20 pm UTC

One thing to look out for. I had a query that selected a few rows from a large non-partitioned table. When the schema was moved to 11g, the table and the aforementioned index were partitioned by day for a column resulting in > 400 partitions. Since the partitioned index did not include the partitioning column, the query that only required 4 consistent gets before (this was executed a lot) now consumed almost 200K consistent gets and really slowed things down. Recreating the index as GLOBAL restored the performance.

Vijay, February 05, 2015 - 9:25 pm UTC

select /*+ index(partitioned_table,local_prefixed) */ b from partitioned_table
where a = 1
and b = 1;

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 8 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | 8 | 1 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1 AND "B"=1)

select /*+ index(partitioned_table,local_nonprefixed) */ b from partitioned_table
where a = 1
and b = 1;

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A"=1)
3 - access("B"=1)

Why is the additional "TABLE ACCESS BY LOCAL INDEX ROWID" step required while reading the non-prefixed index?

Non prefixed local index Vs global Index

Ramar, April 30, 2015 - 5:06 pm UTC

Tom,
Thanks for explaining.

Would you please help me in understanding the difference between non prefixed local index vs Global Index.

18c/19c: Be Careful with Non-Prefixed Local Indexes and Parallel Queries

Elaine O'Brien, December 20, 2020 - 1:42 pm UTC

Partition pruning with local indexes is tricky. It is advisable to test. The answer provided above seems outdated (18/19c) if you have parallel processing enabled on the table. A local non-prefixed index tends to cause parallel scans of many local partitioned indexes, even though the where clause includes the partition key as well as the foreign key in the local non-prefixed index. Changing the local index to be prefixed by the partition key enables the partition pruning again so parallel scans do not kick in when really only accessing a single partition.
Connor McDonald
December 22, 2020 - 7:14 am UTC

We'd need to see your test case

More to Explore

VLDB

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