Re: Forcing CBO to look at partition ...

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 ----- From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Friday, May 30, 2003 10:52 AM Subject: Re: Forcing CBO to look at partition ...

  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 ----- From: laura pena To: Multiple recipients of list ORACLE-L Sent: Thursday, May 29, 2003 6:10 PM Subject: Forcing CBO to look at partition ...

    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'
               )
         )
     /



----------------------------------------------------------------------------

    Do you Yahoo!?
    Free online calendar with sync to Outlook(TM).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).