Home » SQL & PL/SQL » SQL & PL/SQL » unable to reduce the IO Cost of the query (Oracle 10G /Linux)
unable to reduce the IO Cost of the query [message #400603] Wed, 29 April 2009 01:43 Go to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Please help me to reduce the IO Cost of the query.One of the table in the query is not going for index scan.

The query is
Select distinct a.exception_id,
a.changed_by,
b.product_code,
c.prod_name
From ofac_exception_status_audit a,
ofac_exception_source b,
ofac_product_type c
Where c.product_code = b.product_code
and a.changed_by = 'Q198mb' --<ENTER REP ID>--
and a.exception_id = b.exception_id
and b.exception_datetime between '22-Mar-2009' and '23-Mar-2009' --<ENTER DESIRED DATE>--

I have used index hint also.But it is not using index.

Help needed...I have attached both sql and explain plan with this regard.

Regards,
Dhana

Re: unable to reduce the IO Cost of the query [message #400625 is a reply to message #400603] Wed, 29 April 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'22-Mar-2009' and '23-Mar-2009' are not dates but strings.
First remove any hint, gather statistics and change this.

Regards
Michel
Re: unable to reduce the IO Cost of the query [message #402395 is a reply to message #400625] Sat, 09 May 2009 16:45 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are going to paste code, put it through the formatter first and use the code control from above.

After doing the stuff Michel suggested, you might also want to try different indexes. Assuming these are all tables, one of the two sets of indexes below should turn your query into an "index only" lookup thus allowing you to skip actually fetching rows off the tables. This can is some cases cut your physical I/O dramatically. I would expect at least in half. Notice that all columns referenced are in the indexes.

Of course if you posted a "fake" test case, then these indexes will be of no help.

SELECT DISTINCT a.exception_id, 
                a.changed_by, 
                b.product_code, 
                c.prod_name 
FROM   ofac_exception_status_audit a, 
       ofac_exception_source b, 
       ofac_product_type c 
WHERE  c.product_code = b.product_code 
       AND a.changed_by = 'Q198mb'  
       AND a.exception_id = b.exception_id 
       AND b.exception_datetime BETWEEN '22-Mar-2009' AND '23-Mar-2009'
/

Quote:
-- (1)
ofac_exception_status_audit(changed_by,exception_id)
ofac_exception_source(exception_id,exception_datetime,product_code)
ofac_product_type(product_code)

Quote:
-- (2)
ofac_exception_source(exception_datetime,exception_id,product_code)
ofac_exception_status_audit(exception_id,changed_by)
ofac_product_type(product_code)


Good luck, Kevin
Previous Topic: Multiple insert statement or One dynamic insert
Next Topic: Getting Table Hierarchy from a schema
Goto Forum:
  


Current Time: Sat Dec 10 22:13:11 CST 2016

Total time taken to generate the page: 0.08729 seconds