RE: RULE vs. CHOOSE

From: Gillies, Garry <garry_at_weir.co.uk>
Date: Wed, 01 Aug 2001 07:24:10 -0700
Message-ID: <F001.0035BCA5.20010801063047@fatcity.com>

> 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.

++++++++++++++++++++++++++++++++++++

All internet traffic to this site is
automatically scanned for viruses
and vandals.

++++++++++++++++++++++++++++++++++++

Received on Wed Aug 01 2001 - 09:24:10 CDT