OraFAQ Forum: Performance Tuning » Performance tuning with indexes

Home » RDBMS Server » Performance Tuning » Performance tuning with indexes

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

Performance tuning with indexes [message #312827]

Wed, 09 April 2008 20:14

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

Report message to a moderator

Performance tuning with indexes [message #312828 is a reply to message #312827]

Wed, 09 April 2008 20:18

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

Report message to a moderator

Re: Performance tuning with indexes [message #312831 is a reply to message #312827]

Wed, 09 April 2008 20:30

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

Report message to a moderator

Re: Performance tuning with indexes [message #312833 is a reply to message #312831]

Wed, 09 April 2008 20:33

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

Report message to a moderator

Re: Performance tuning with indexes [message #312843 is a reply to message #312833]

Wed, 09 April 2008 21:55

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

Report message to a moderator

Re: Performance tuning with indexes [message #312873 is a reply to message #312843]

Thu, 10 April 2008 00:34

swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar

Senior Member

each query fetches on an average 10000 records

Report message to a moderator

Re: Performance tuning with indexes [message #312949 is a reply to message #312843]

Thu, 10 April 2008 03:27

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

Report message to a moderator

Re: Performance tuning with indexes [message #312955 is a reply to message #312949]

Thu, 10 April 2008 03:47

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

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.

Report message to a moderator

Re: Performance tuning with indexes [message #313056 is a reply to message #312827]

Thu, 10 April 2008 09:08

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

Report message to a moderator

Re: Performance tuning with indexes [message #313397 is a reply to message #312827]

Fri, 11 April 2008 12:23

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Try using 1 index:

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

HTH.
Michael

Report message to a moderator

Re: Performance tuning with indexes [message #313491 is a reply to message #312827]

Sun, 13 April 2008 03:01

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:54:08 CST 2024