> From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]
> 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. ++++++++++++++++++++++++++++++++++++
