Analyzing stats would be the first thing to try,
but I suspect the non-elimination is the way the SQL is written.
Assuming you did not miss any brackets around the
"or call_id", the "or" part of the statement would cause a visit to all
partitions. Re-write the "or" section as a join so the CBO will see the calldate
as an elimination column. Either eliminate the "in" altogether, or if you cannot
do that due to one-to-many causing duplicate rows, at least join within the "in"
(...).
Your sub-hash column customerinfoId played no part
in the query at all. Is it required for other queries? Otherwise, it would be
better to include spare7 (interesting choice of column name), or verified, or
even both as sub-hash column(s). As long as they are not too
skewed.
How selective is account_no? If you have an index
on this column, access could be a lot faster, as long as it is selective and not
too skewed.
call_id <> '0' ? Is it a number or
varchar2? If you are in a habit of including this in all your queries -- why
load the row in the first place? Look at putting them in a different table for
exception reporting.
Is audit_table partitioned? You can possibly get
further benefits with partitioning. And why is
"event_id" a DATE datatype? Typo?
Is the match to audit_table a common requirement?
If so, time for a redesign -- look as flattening both tables into one, thus
avoiding the join at query time altogether. Trade-off between space and time.
There has been a lot of info regarding performance analysis and diagnosis
floating around, but nothing can compensate for "inappropriate" design in the
first place.
|
- Forcing CBO to look at partition ... laura pena
- Re: Forcing CBO to look at partition ... Arup Nanda
- Re: Forcing CBO to look at partition ... Binley Lim
- Re: Forcing CBO to look at partition ... laura pena