Prefix v/s Non-Prefix index

[Skippy]

There's a lot of debate about the usage of Prefixed and Non Prefixed Local Indexes. I have come across a misconception many dba's have about these indexes when it comes to performance.

 

Basically, a Local index is called as a Prefixed Local Index when the partitioned column is a part of the index and is a leading column of the Index. Non-Prefixed Local Indexes are with (non-leading) or without partitioned column in the indexes.

 

Hence, if a table is range partitioed on tran_date, a Local Index :

 

(tran_date, doc_num) will be a prefixed index, whereas,

(doc_num, tran_date) & (doc_type) will be a non-prefixed Index.

 

From Oracle 9i onwards, there is no difference between the two indexes in terms of performance. Hence, the indexing strategy is to be designed based on the application queries.

Based on the above example, if most of my application queries are executed on DOC_NUM and DOC_NUM and TRAN_DATE then obviously my Indexing strategy should include a single composite index on doc_num, tran_date without bothering on a debate between prefix and non-prefix. Performancewise there is no difference. Both of these Indexes support partition pruning (elimination) as long as the partitioned column is included in the query in case of non-prefixed indexes.

 

 

The only difference between the two comes when it comes to Availability. For example,

 

create table test (object_id number, doc_num number, object_name varchar2(20))

partition by range(object_id)

(partition first_partition values less than (1000),

partition second_partition values less than (2000));

 

insert into test select object_id, round(dbms_random.value(1,100),0), object_name

from all_objects where rownum<=2000; create index non_prefix_test on test(doc_num) tablespace users local; exec dbms_stats.gather_table_stats(user,'TEST', granularity=>'ALL', cascade=>TRUE);

 

Now, in this case, all the three queries will be able to use non-prefixed Local Index, except for the fact that the first query will not go for partitioning pruning and will do ALL partition scan via Local Index which is not a good idea but the point to prove is that the indexing startegy should be based on my application queries.

 

select object_name from test where doc_num=98;

select object_name from test where doc_num=98 and object_id=789;

select object_name from test where doc_num=98 and object_id=1289;

 

As a better alternative, I can create a non-prefix index on (doc_num, object_id) . This index will again be used all the three queries and will bring down the amount of work done (I/O's) by the 2nd and 3rd query.

 

Now, if I drop and recreate the Index on object_id, doc_num (prefix), then the first query will go for Full Table Scan of All the partitions and the index will only be used by the 2nd and 3rd queries. When I checked the amount of consistent reads done by an Index on (doc_num, object_id) and (object_id, doc_num), these were exactly same.

 

The only difference comes into picture is the AVAILABILITY. In the above example, if any of the partitions of a table or index is OFFLINE or UNUSABLE, then the Index scan of DOC_NUM will fail with error "ORA-01502" as it has to scan all the partitions.

 

Conclusion : Basically there are no differences between prefix and non-prefix indexes when it comes to performance. These strategies needs to be designed purely on the basis of application queries.

[Zooty]

 

The 1st sentence lost me.

[isleseeya]

oh my ...sorry i do not understand Greek

[Big John]

oh my ...sorry i do not understand Greek

but claim to have some sort of Greek body?

[Kid Cid]

So exactly how do you create a non-prefixed index? Your create index statement doesn't specify the type of index at all.

[isleseeya]

but claim to have some sort of Greek body?

 

 

thats true ..cant help that