OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Reverse key index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reverse key index [message #336852]

Tue, 29 July 2008 04:07

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

how to use Reverse key index in performance tunning of query ...

Report message to a moderator

Re: Reverse key index [message #336865 is a reply to message #336852]

Tue, 29 July 2008 04:29

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does your question mean? Explain it.

Regards
Michel

Report message to a moderator

Re: Reverse key index [message #336895 is a reply to message #336852]

Tue, 29 July 2008 05:24

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.

Report message to a moderator

Re: Reverse key index [message #337100 is a reply to message #336895]

Tue, 29 July 2008 22:03

rleishman
Messages: 3728
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

Report message to a moderator

Re: Reverse key index [message #337219 is a reply to message #336852]

Wed, 30 July 2008 03:21

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

Report message to a moderator

Re: Reverse key index [message #337407 is a reply to message #337219]

Wed, 30 July 2008 16:45

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.

Report message to a moderator

Re: Reverse key index [message #337434 is a reply to message #337407]

Wed, 30 July 2008 21:36

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

Report message to a moderator

Re: Reverse key index [message #337445 is a reply to message #336852]

Wed, 30 July 2008 22:31

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Reverse key index [message #337449 is a reply to message #337219]

Wed, 30 July 2008 22:52

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	Performance
Next Topic:	ora-04030

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 07:11:51 CDT 2024