Re: Optimizer question

just to clear this up, and I appreciate the help, this drove me to look a little more but I found this under the 9i docs. And i'm going out now to check out Tom's book to see if it is just something different in 8i and below, but .... so I sortof answered my question. But it seems like it will still use the DBO but use some defaults that were given below

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.

      See Also:
      Chapter 3, "Gathering Optimizer Statistics" for more information on

................................

Missing Statistics
When statistics do not exist, the optimizer uses the following default values. Table 3-2 shows the defaults you can expect when statistics are missing.

Table 3-2 Default Table and Index Values When Statistics are Missing

      Statistic  Default Value Used by Optimizer
      Tables

        a.. Cardinality

        b.. Average row length

        c.. Number of blocks

        d.. Remote cardinality

        e.. Remote average row length


      100 rows

      20 bytes

      100

      2000 rows

      100 bytes

      Indexes

        a.. Levels

        b.. Leaf blocks

        c.. Leaf blocks/key

        d.. Data blocks/key

        e.. Distinct keys

        f.. Clustering factor


      1

      25

      1

      1

      100

      800 (8 * number of blocks)

"Tyler D. Muth" <tyler_muth_at_hotmail.com> wrote in message news:1afec35e.0209200436.76a6bb15_at_posting.google.com...
> "dkrpata1" <dkrpata1_at_cox.net> wrote in message
 news:<awui9.72640$TX5.2224055_at_news1.east.cox.net>...
> > Let's see how much dirt this brings up.
> >
> > Here's the situtation. We have a SQL statement that we force a hint on,
 but
> > there are no statistics in the database. Let's even say there's another
> > database that's the same way. From what I've read the docs say if you
 force
> > a hint, then it will use the CBO, but since there are no statistics how
 does
> > it figure the execution path to take. What assumptions, and are they
> > documented anywhere (I have looked for about a day now) that the CBO
 will
> > use. And why if it really does, would we see different execution paths
 for 2
> > databases, both with no statistics that were collected on them.
>
> When you force a hint on a table with no stats, the hint is not used.
> You will simply use the RBO.
>
> Many factors will affect execution plan, but if you are looking to
> maintain the same query plan across multiple databases, pick up the
> following book, there's a whole chapter on it:
>
http://www.amazon.com/exec/obidos/ASIN/1861004826/asktom-20/102-6049530-1609 752
>
> There's a wealth of other useful info in there too. Try
> http://asktom.oracle.com for this and other questions you may have.
>
> Good luck.
Received on Fri Sep 20 2002 - 21:23:32 CEST