Oracle-L: Re: Forcing CBO to look at partition ...

From: Arup Nanda <orarup_at_hotmail.com>
Date: Thu, 29 May 2003 14:52:01 -0800
Message-ID: <F001.005A670B.20030529145201@fatcity.com>

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: Arup Nanda
INET: orarup_at_hotmail.com

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). Received on Thu May 29 2003 - 17:52:01 CDT