| QUERY HELP [message #53412] |
Wed, 18 September 2002 08:44  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
Hi Guys,
I need help to tune the following query at the present time it take 1.5 hour to run. It use to take on 25 minutes before. The query is as follows:
select count(*) from invt_trn where fy_cd = 2002 and pd_no = 4 and
s_invt_trn_type = 'I' and ord_id = 'UNITS HS' and invt_trn_id not in
(select usage_doc_id from units_usage_hs);
Record count in the tables:
UNITS_USAGE_HS = 201567
INVT_TRN = 231280
I do have indexes on the selected columns but still no help even rebuilding indexes did not help too.
Thanks for your advise in advance.
Thanks
Jay
|
|
|
|
|
|
| Re: QUERY HELP [message #53421 is a reply to message #53412] |
Wed, 18 September 2002 21:05  |
puneet
Messages: 76
Registered: August 2002
|
Member |
|
|
check if theres any way as per your DB design that you can avoid using that INVT_TRN table join. may be u might need to look in to the way u maintain your data.
otherwise Seeing the amount of data u r having partationing is a better option
since it was taking less time earlier see if the tables are analyzed or not or compute table/index statistics again this should probably help.
try to run it under rule optimize mode select /*+ RULE */
sometime it helps .
Also instead of count(*) use count(1) since when u use count(*) oracle internally subsitutes the column name from data dictionary which consumes resources..
|
|
|
|
| Re: QUERY HELP [message #53448 is a reply to message #53412] |
Fri, 20 September 2002 01:31 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
|
Could exactly define which indexes you have and show the explain plan for this query to see if any of theses indexes are used
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
