| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Forcing CBO to look at partition ...
These are awesome suggestions .. thanks.
Creating a local index on accounts - I can do this. Call_id ='0' it is the number zero and considered a lead row. This row is duped and filled in with a real call_id when a call is placed. Eventually this row with call_id are removed in the archiving processes when the promotion is complete. Naming convention- I am dealing with leagacy design. So I have no idea why event_id referes to a date datatype .... Audit_table - This is an execellent point and if you have any futher insight on my problem I would greatly appreciate the following feedback: I updated this table to be a partitioned table and generated stats. But then queries that joined audit_table with legacy tables ran very slow (no stats on legacy tables) compared to when the audit_table was not partitioned. My suggestion was to analyzed 5% of all tables in the system. Including legacy tables, since it is not known what other tables the audit_table joins with at this time. No time to do this so we kept the table audit_table as a standard table. Our move to 9i time frame to do this is by Q3 and we will analyze all tables...
Many Thanks,
Lizz
Binley Lim <Binley.Lim_at_xtra.co.nz> wrote: 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.
----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Friday, May 30, 2003 10:52 AM
Why not just use the syntax select * from customerinfo partition (<the_part_name>)?
Much better solution, though, is to gather stats of 1% estimate and do the query. The partition elimination will automatically kick in.
Arup Nanda
www.proligence.com
----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Thursday, May 29, 2003 6:10 PM
Hey I currently do not have stats loaded and have a composite partition table corralated with a legacy table. I am wondering if I can force the CBO to use a specific partitions index and hash via a hint.
Is this possible? (partitioned by calldate and hashed by customerinfoId)
Many Thanks,
-Lizz
Here is my sql:
select * from customerinfo
where calldate between
TO_DATE('2003-05-21 00:00:00','YYYY-MM-DD HH24:MI:SS')+ 10/24
and TO_DATE('2003-05-22 00:00:00','YYYY-MM-DD HH24:MI:SS')+ 10/24
and Spare7 = '20'
and verified = 'Y'
and account_no ='864239913' and call_id <> '0'
or call_id in (
select call_id from voicelog.audit_table
WHERE audit_table.event_type = 3
and event_id between
TO_DATE('2003-05-21 00:00:00','YYYY-MM-DD HH24:MI:SS')+ 10/24
and TO_DATE('2003-05-22 00:00:00','YYYY-MM-DD HH24:MI:SS')+ 10/24
and call_id in (
&! nbsp; select call_id from customerinfo
where calldate between TO_DATE('2003-05-21 00:00:00','YYYY-MM-
H24:MI:SS')
and TO_DATE('2003-05-22 00:00:00','YYYY-MM-DD HH24:MI:SS')
and Spare7 = '20'
and verified = 'Y' and account_no ='864239913'
and call_id <> '0'
)
)
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri May 30 2003 - 10:10:25 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |