OraFAQ Forum: Performance Tuning » BAD performing query, OR Clause

Home » RDBMS Server » Performance Tuning » BAD performing query, OR Clause (Oracle 10g)

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

BAD performing query, OR Clause [message #308248]

Sat, 22 March 2008 19:10

stghag
Messages: 22
Registered: March 2006
Location: Mumbai

Junior Member

One of our query is performing very bad, given below:

select c1, c2, c3
from TAB t
where t.org_type = 'ACCOUNT'
and (t.exp_sales_cd = :1 OR t.imp_sales_cd = :2)

:1 = '1234'
:2 = '1234'

few facts about above query:
1. table TAB has index on columns exp_sales_cd & imp_sales_cd.
2. explain plan is showing FULL TABLE SCAN on table TAB
3. actual query is very big, this query is focusing on pain area
4. query can not be modified, this is generated by product(Siebel).
5. This is in production.

Product customization has caused this query to be generated and hence causing poor performance in few transactions.
Immediate change in product customization would take another 7 days.

Hence i want to know by any means:
1. Performance can be improved by some extent.
2. Can we avoid full table scan ?

and this has to accomplish without changing/modifying query.

Sincere help is requested.

Report message to a moderator

Re: BAD performing query, OR Clause [message #308249 is a reply to message #308248]

Sat, 22 March 2008 19:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from the following SQL:

select t.org_type, count(*) from TAB t group by t.org_type

Report message to a moderator

Re: BAD performing query, OR Clause [message #308253 is a reply to message #308249]

Sat, 22 March 2008 20:28

stghag
Messages: 22
Registered: March 2006
Location: Mumbai

Junior Member

90% percent of records.

Report message to a moderator

Re: BAD performing query, OR Clause [message #308254 is a reply to message #308253]

Sat, 22 March 2008 20:29

stghag
Messages: 22
Registered: March 2006
Location: Mumbai

Junior Member

approx. 1800000

Report message to a moderator

Re: BAD performing query, OR Clause [message #308255 is a reply to message #308248]

Sat, 22 March 2008 20:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>2. Can we avoid full table scan ?
NO

You choose to not provide requested results, I choose to not respond further.
You're On Your Own (YOYO)!

Report message to a moderator

Re: BAD performing query, OR Clause [message #308274 is a reply to message #308248]

Sun, 23 March 2008 07:10

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Quote:

table TAB has index on columns exp_sales_cd & imp_sales_cd.

If you mean that there is a single index like:

CREATE INDEX ... ON (exp_sales_cd , imp_sales_cd)

then try:
1. Drop that index.
2. Create 2 new indexes:

CREATE INDEX INDEX1...(org_type, exp_sales_cd )...
CREATE INDEX INDEX2...(org_type, imp_sales_cd )...

3. Post explain.

HTH

Report message to a moderator

Previous Topic:	ADDM Findings from OEM
Next Topic:	When I should increase the size of datafile,tablespace,redolog files as well as sort area size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 21:20:23 CDT 2024