Home » RDBMS Server » Performance Tuning » Reverse key index
Reverse key index [message #336852] Tue, 29 July 2008 04:07 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

how to use Reverse key index in performance tunning of query ...
Re: Reverse key index [message #336865 is a reply to message #336852] Tue, 29 July 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question

What does your question mean? Explain it.

Regards
Michel
Re: Reverse key index [message #336895 is a reply to message #336852] Tue, 29 July 2008 05:24 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I wrote a query

SELECT *
fROM entity_address_master
Where enty_group_code = 'INSURED'

its plan showed that oracle was doing a full table scan of entity_address_master

CREATE INDEX I_ENTY_LAST_NAME ON entity_address_master(ENTY_LAST_NAME) REVERSE;

According to books i rewrote query as

SELECT *
fROM entity_address_master
Where enty_group_code = 'DERUSNI'; --inverse of 'INSURED'

But query didn't returned any rows

I want to improve performance of query.
Re: Reverse key index [message #337100 is a reply to message #336895] Tue, 29 July 2008 22:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Throw away that book.

Reverse key indexes affect the way the index data is stored; not the way you need to query it.

Oracle is not using the index because it thinks it is better NOT to. Make sure you have gather statistics on the table AND the index with DBMS_STATS.GETHER_TABLE_STATS() then try again.

If it still doesn't work, post the following here:
- Explain Plan
- Number of rows in the tables
- Number of rows with enty_group_code = 'INSURED'

Ross Leishman
Re: Reverse key index [message #337219 is a reply to message #336852] Wed, 30 July 2008 03:21 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

1) Explain plan

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 936 K 11128
PARTITION LIST SINGLE 936 K 294 M 11128 4 4
TABLE ACCESS FULL ERIE_STRESS.ENTITY_ADDRESS_MASTER 936 K 294 M 11128 4 4


2) Total rows in entity_address_master table : 2154650

3) no. of rows satisfying condition "enty_group_code = 'INSURED' " = 937041

Sir i want to ask you one more thing

I wrote a query

Select *
From ENTITY_ADDRESS_MASTER
Where ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%';

I made Reverse key index on column ENTY_LAST_NAME

When i used predicate as ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA' it used reverse key index(as shown in execution plan)

When i used predicate as ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%' it didn't used reverse key index(as shown in execution plan)

Total no. of rows : 2154650
No. of rows satisfying condition ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%' : 119

Can u please guide me on this too?
and i want to use Like operator in my query
Re: Reverse key index [message #337407 is a reply to message #337219] Wed, 30 July 2008 16:45 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Limiting conditions placed on the rows correspond to the leading columns of the index, but the conditions use the LIKE operatorand the value starts with '%' or the value is a bind variable. ex. neither of the following will be able to use the index:

WHERE ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%'
WHERE column LIKE : ABC_BIND_VARIABLE

NOTE: The bind variable may contain trailing '%' or no '%' at all. Regardless, an index will not be used.
Re: Reverse key index [message #337434 is a reply to message #337407] Wed, 30 July 2008 21:36 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Sir please provide me solution for the same.

This following WHERE clause is compulsory

Where ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%'

Thanks
Re: Reverse key index [message #337445 is a reply to message #336852] Wed, 30 July 2008 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


>Sir please provide me solution for the same.
Exactly what "solution" do you expect/desire?
Re: Reverse key index [message #337449 is a reply to message #337219] Wed, 30 July 2008 22:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
getritesh wrote on Wed, 30 July 2008 18:21
2) Total rows in entity_address_master table : 2154650

3) no. of rows satisfying condition "enty_group_code = 'INSURED' " = 937041

You are querying half the table. Indexes are only used for returning small portions of the table. Oracle is right to choose a full scan because the index scan would be MUCH slower.

getritesh wrote on Wed, 30 July 2008 18:21
Select *
From ENTITY_ADDRESS_MASTER
Where ENTY_LAST_NAME LIKE 'TRIO DEVELOPMENT CORPORA%';

I made Reverse key index on column ENTY_LAST_NAME


You cannot perform RANGE SCANs on a REVERSE key index. You can only EQUALS and IN. Make the index normal (not reverse) and it will work.

Ross Leishman
Previous Topic: Performance
Next Topic: ora-04030
Goto Forum:
  


Current Time: Mon Dec 05 10:42:09 CST 2016

Total time taken to generate the page: 0.07957 seconds