Prefixed local index vs. Non-prefixed local index.

From: Kevin Hale Boyes <kcboyes@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 20 Jul 2010 16:25:37 -0600

According to the Oracle literature
(http://download.oracle.com/docs/cd/B14117_01/server.101/b10736/parpart.htm#sthref301),
a non-prefixed local index is preferred for an OLTP system than a
local index on a partitioned table.
I have a table that is (range) partitioned on archived_date, and
(hash) sub-partitioned on buyer_id.  My query (abbreviated) is as
follows:

SELECT a.*, c.*
…
(SELECT b.another_id
 FROM table B b
 WHERE b.detail_id = a.detail_id
 AND b.action_id = a.action_id
 AND b.buyer_id = a.buyer_id
 AND b.archived_date = a.archived date) C
…
FROM table A
WHERE a.buyer_id = Y
AND a.action_id = X

If I create a local index on table B and define it as:

CREATE INDEX NONPREFIXED_LOCAL ON B (detail_id, action_id) LOCAL;

The overall query executes in 2 seconds.  (Table b has approximately
55 million records in it).

If I remove the non-prefixed index and replace it with a prefixed index:

CREATE INDEX NONPREFIXED_LOCAL ON B (archived_date, buyer_id,
detail_id, action_id) LOCAL ;

And execute the same query, it executes in 16 seconds.

For interest’s sake, I replaced the prefixed local index with a
suffixed local index:

CREATE INDEX SUFFIXED_LOCAL ON B (detail_id, action_id, archived_date,
buyer_id) LOCAL ;

With this index, the query executes in 2 seconds.

The explain plans and tkprof output for all three scenario are nearly
identical (different indexes being used as noted above).

Has anyone encountered this situation before and can shed any light as
to why this is happening?

Thanks
--
//www.freelists.org/webpage/oracle-l

Prefixed local index vs. Non-prefixed local index.

Other related posts: