RE: RULE vs. CHOOSE

1)I feel each plan depends on init parameters of Oracle. 2) You can statspack of Oracle. It will give u reports of the performance of the DB.

HTH

--

On Thu, 2 Aug 2001 13:01:56   
 VIVEK_SHARMA wrote:


>

>Qs. Is there any Way to Generate the IDEAL OUTLINE Plans Plans in 1

>Database 

>& Send it for Import to ALL the Various Sites World-wide ?

>

>Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to 

>be ANALYZED in some SMALL proportions (Bit by Bit) while Live Operations

>

>are in progress ? What would be the Overhead of Such a Package (if

>Exists) ? 

>

>Thanks so much for the Help

>

>> -----Original Message-----

>> From:        Gillies, Garry [SMTP:garry_at_weir.co.uk]

>> Sent:        Wednesday, August 01, 2001 8:01 PM

>> To:  Multiple recipients of list ORACLE-L

>> Subject:     RE: RULE vs. CHOOSE

>> 

>> 

>> > From: VIVEK_SHARMA [<mailto:VIVEK_SHARMA_at_infy.com>] 

>> > Sent: 01 August 2001 08:05 

>> > Subject: RULE vs. CHOOSE 

>> > 

>> > Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be

>> 

>> > used  for the PARTITIONED Table in the Following Query ? 

>> > 

>> > NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the 

>> > Following SQL :- 

>> > 

>> > SELECT <Some Columns> 

>> > FROM <Partition Table> , <NON-Partitioned Table> 

>> > where <Partition Table>.Column = <NON-Partitioned Table>.Column 

>> > 

>> > Assuming <Partition Table>.Column is the PARTITION KEY 

>> > & BOTH <Partition Table> & <NON-Partitioned Table> are ANALYZED . 

>> > 

>> 

>> Development stopped on RBO at version 7. Partitioning is a version 8 

>> feature. Since RBO has no knowledge of dealing with partitions, CBO 

>> must be used. 

>> 

>> > Qs. Will Only the Respective partition be SCANNED in the Above Query

>> ? 

>> 

>> No. There is nothing in that query that tells the optimiser that only 

>> a particular partition will be required. 

>> 

>> > Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE

>> for 

>> > the Above Query ? 

>> 

>> It would be ignored. The CBO would be used, defaulting to ALL_ROWS. 

>>   

>> > Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan

>> at 

>> > Various Sites where the SAME PRODUCT Exists 

>> 

>> Yes, but you must be using CBO - see CREATE OUTLINE in SQL reference

>> manual. 

>>   

>> > Qs. Is there any PACKAGE etc. which Automatically Causes Statistics

>> to 

>> > be ANALYZED in some SMALL proportions (Bit by Bit) while Live 

>> > Operations 

>> > are in progress ? What would be the Overhead of Such a Package (if 

>> > Exists) ? 

>> 

>> I do not know. 

>> 

>



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: C.S.Venkata Subramanian
  INET: csvenkata_at_lycos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).