Home » RDBMS Server » Performance Tuning » Performance tuning with indexes
Performance tuning with indexes [message #312827] Wed, 09 April 2008 20:14 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

I have 3 sets of queries where in the where condition i have one usual column and some columns where nvl,rtrim

Ist

WHERE abacc_legal_entity_code = CUGTable(i).sme_id
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND NVL(abacc_cni_ind, ' ') = ' '



2nd

abacc_legal_entity_code = CUGTable(i).sme_id

AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '



3rd

abacc_legal_entity_code = CUGTable(i).sme_id

AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '


abacc_legal_entity_code,NVL(abacc_cni_ind, ' ') these are common in all the queries used in the application

I created a non unique index on abacc_legal_entity_code
then i tried to create other functional indexes,i find than in the explain plan only first index (nonunique) is used,it doesn't make use of the second functional index

i tried for the following ones

create index fn_ind1 on table
((NVL(abacc_cni_ind, ' '));

I need help here as this being a production issue,how can i create at least one more index to improve performance,
this table processes 20 million records.

Please suggests

thanks
Performance tuning with indexes [message #312828 is a reply to message #312827] Wed, 09 April 2008 20:18 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

I have 3 sets of queries where in the where condition i have one usual column and some columns where nvl,rtrim

Ist

WHERE abacc_legal_entity_code = CUGTable(i).sme_id
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND NVL(abacc_cni_ind, ' ') = ' '



2nd

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '


3rd

abacc_legal_entity_code = CUGTable(i).sme_id
AND NVL(RTRIM(abacc_dnm_consent), 'C') = 'C'
AND ((NVL(abacc_std_business_name,' ') <> ' ') OR
(LENGTH(NVL(abacc_main_tel_no,' ')) > )
AND LENGTH(NVL(abacc_best_postcode,' ')) > 4
AND NVL(abacc_cni_ind, ' ') = ' '

abacc_legal_entity_code,NVL(abacc_cni_ind, ' ') these are common in all the queries used in the application

I created a non unique index on abacc_legal_entity_code
then i tried to create other functional indexes,i find than in the explain plan only first index (nonunique) is used,it doesn't make use of the second functional index

i tried for the following ones

create index fn_ind1 on table
((NVL(abacc_cni_ind, ' '));

I need help here as this being a production issue,how can i create at least one more index to improve performance,
this table processes 20 million records.

Please suggests

thanks
Re: Performance tuning with indexes [message #312831 is a reply to message #312827] Wed, 09 April 2008 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Read & FOLLOW posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions in above URL did you try & what were the results?
Re: Performance tuning with indexes [message #312833 is a reply to message #312831] Wed, 09 April 2008 20:33 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
sorry for posting it twice but as i didn't had much time to check and wanted some help so i had posted twice,will take care nxt time
thank you
Re: Performance tuning with indexes [message #312843 is a reply to message #312833] Wed, 09 April 2008 21:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle can generally only use one b-tree index to access a single table (there are exceptions, but they do not apply here).

The table contains 20 million records you say.
- How many rows does each query return?
- Which of the WHERE predicates are the most restrictive, ie. filter out the most rows. And how many rows does each one filter out.

Ross Leishman
Re: Performance tuning with indexes [message #312873 is a reply to message #312843] Thu, 10 April 2008 00:34 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
each query fetches on an average 10000 records
Re: Performance tuning with indexes [message #312949 is a reply to message #312843] Thu, 10 April 2008 03:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 10 April 2008 12:55

The table contains 20 million records you say.
- How many rows does each query return?
- Which of the WHERE predicates are the most restrictive, ie. filter out the most rows. And how many rows does each one filter out.


Ross Leishman
Re: Performance tuning with indexes [message #312955 is a reply to message #312949] Thu, 10 April 2008 03:47 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

No Message Body

[Updated on: Thu, 10 April 2008 04:31]

Report message to a moderator

Re: Performance tuning with indexes [message #313043 is a reply to message #312827] Thu, 10 April 2008 08:24 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
There are also several things to consider when using function based indexes, some of which may be version specific. Read the appropriate sections in the administrators guide and the application developers guide for your version.
Re: Performance tuning with indexes [message #313056 is a reply to message #312827] Thu, 10 April 2008 09:08 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
The version of oracle is the latest one 10.2,I am sure it can be created properly,unfortunately i am not aware of the exact working of function based indexes
Re: Performance tuning with indexes [message #313397 is a reply to message #312827] Fri, 11 April 2008 12:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try using 1 index:

CREATE INDEX ... ON ...
  ( abacc_legal_entity_code,
    NVL(abacc_cni_ind, ' '))


HTH.
Michael
Re: Performance tuning with indexes [message #313491 is a reply to message #312827] Sun, 13 April 2008 03:01 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Many Thanks

I had thought of this option but i have a confusionLet me know if i am going wrong.

I thought if we don't have the indexing column sequence same as in the where clause then the second column index won't work,so in this case whether the functional index on the nvl(column) work??

Thank you
Previous Topic: 'DISTINCT' vs 'GROUP by'
Next Topic: Poor performance of query with VPD policy
Goto Forum:
  


Current Time: Fri Dec 09 04:13:27 CST 2016

Total time taken to generate the page: 0.11425 seconds