| Help- slow running sql [message #295893] |
Wed, 23 January 2008 17:22  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
Following is the slow running SQL statement in our system.
it is taking many seconds to execute.
However it picks correct index.
select MIN ( dtdue )
FROM com_pol_due
WHERE strpolnbr = :b2
AND dtdue >= '01-mar-2008'
AND NVL ( nisrvpassed, 0 ) = 0
AND NVL ( dtotdueamnt, 0 )
- NVL ( dtotpaidamnt, 0 )
- NVL ( dtotwaivedamnt, 0 )
- NVL ( dtotadjustamnt, 0 ) > 0
The cardinality of the index is good.
Since the sql is picking correct index I believe there will not be use of gathering stats of table or index
Also index is composite index on 5 columns out of which first column is Strpolnbr and third is Dtdue.
the index stats updated and values are as following
num rows 65890654
distinct keys 18624247
leaf blocks 574195
clustering factor 47786333
also table data is
NUM ROES 66710368
BLOCKS 1484926
Can anybody please suggest on this?
Thanks and Regards,
OraSaket
|
|
|
|
| Re: Help- slow running sql [message #295899 is a reply to message #295893] |
Wed, 23 January 2008 20:55  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
- How many rows have strpolnbr = :b2
- How many of those rows have dtdue >= '01-mar-2008'
- What does the SQL return if you select COUNT(*) intead of MIN(dtdue)?
You've been around long enough now to know that you should format your SQL with CODE tags - make sure you do next time.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
