Home » RDBMS Server » Performance Tuning » BAD performing query, OR Clause (Oracle 10g)
BAD performing query, OR Clause [message #308248] Sat, 22 March 2008 19:10 Go to next message
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.
Re: BAD performing query, OR Clause [message #308249 is a reply to message #308248] Sat, 22 March 2008 19:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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
Re: BAD performing query, OR Clause [message #308253 is a reply to message #308249] Sat, 22 March 2008 20:28 Go to previous messageGo to next message
stghag
Messages: 22
Registered: March 2006
Location: Mumbai
Junior Member

90% percent of records.
Re: BAD performing query, OR Clause [message #308254 is a reply to message #308253] Sat, 22 March 2008 20:29 Go to previous messageGo to next message
stghag
Messages: 22
Registered: March 2006
Location: Mumbai
Junior Member

approx. 1800000
Re: BAD performing query, OR Clause [message #308255 is a reply to message #308248] Sat, 22 March 2008 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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)!
Re: BAD performing query, OR Clause [message #308274 is a reply to message #308248] Sun, 23 March 2008 07:10 Go to previous message
michael_bialik
Messages: 611
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
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:
  


Current Time: Sun Dec 04 19:08:18 CST 2016

Total time taken to generate the page: 0.11133 seconds