Solved

Different execution plans for a query with RULE hint

Posted on 2000-05-10
10
927 Views
Last Modified: 2011-09-20
I have a query with RULE hint and optimizer cretes different execution plans depending on if statistics are collected for the tables in the query.
My understanding is that the optimizer has to ignore statistincs in case of rule based optimization.
Oracle version: 8.0.5.2.1
Any comments?
0
Comment
Question by:davdonin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 2

Expert Comment

by:AndrewRodionov
ID: 2796025
Hi Denis,

Post your query to analyze it. I've tried to repeat your result but it was not successful ;-)

Andrew
0
 
LVL 3

Expert Comment

by:rkogelhe
ID: 2796391
This sounds like an Oracle bug to me.
0
 
LVL 2

Author Comment

by:davdonin
ID: 2796574
Okay. Please find below a script for creating sample tables.

=== cut here ===
DROP TABLE table2;
DROP TABLE table3;
DROP TABLE table4;
DROP TABLE table1;

CREATE TABLE table3 (
   id                 NUMBER(10)   PRIMARY KEY,
   description        VARCHAR2(30) NULL,
   gl_code            NUMBER(10)   NULL
);

CREATE TABLE table4 (
   id                 NUMBER(10)   PRIMARY KEY,
   description        VARCHAR2(30) NULL,
   gl_code            NUMBER(10)   NULL
);

CREATE TABLE table1 (
   id                 NUMBER(10)   PRIMARY KEY,
   account_id         NUMBER(10)   NOT NULL,
   code               NUMBER(10)   NULL,
   action_date_time   DATE         NOT NULL,
   duration           NUMBER(10)   NOT NULL,
   stream_id          NUMBER(10)   NULL,
   partition_value    NUMBER(10)   NOT NULL
)
PARTITION BY RANGE (partition_value) (
   PARTITION par00_29 VALUES LESS THAN (30),
   PARTITION par30_59 VALUES LESS THAN (60),
   PARTITION par60_99 VALUES LESS THAN (100)
);

CREATE INDEX table1_ind1 ON table1 (account_id, duration, action_date_time);
CREATE INDEX table1_ind2 ON table1 (stream_id, code);

CREATE TABLE table2 (
   id                 NUMBER(10)   PRIMARY KEY,
   table1_id          NUMBER(10)   NOT NULL,
   stream_id          NUMBER(10)   NULL,
   amount1            NUMBER(18,5) NOT NULL,
   amount2            NUMBER(18,5) NOT NULL,
   table3_id          NUMBER(10)   NOT NULL,
   table4_id          NUMBER(10)   NOT NULL,
   --
   CONSTRAINT table2_table1_fk FOREIGN KEY (table1_id) REFERENCES table1 (id),
   CONSTRAINT table2_table3_fk FOREIGN KEY (table3_id) REFERENCES table3 (id),
   CONSTRAINT table2_table4_fk FOREIGN KEY (table4_id) REFERENCES table4 (id)
);

CREATE INDEX table2_ind1 ON table2 (stream_id, table1_id);
CREATE INDEX table2_ind2 ON table2 (table1_id, table3_id, table4_id, stream_id, amount1);
=== cut here ===

Here is the query.
=== cut here ===
SELECT /*+ RULE*/
       NVL (t4.gl_code, t3.gl_code),
       SUM (t2.amount2)
  FROM table1 t1,
       table3 t3,
       table2 t2,
       table4 t4
 WHERE t1.code = p_code
   AND t1.stream_id = p_stream_id
   AND t1.account_id = p_account_id
   AND t1.partition_value = MOD (p_account_id, 100)
   AND t1.id = t2.table1_id
   AND t2.table3_id = t3.id
   AND t2.table4_id = t4.id
 GROUP BY NVL (t4.gl_code, t3.gl_code)
=== cut here ===

Now, if I execute this query with NO statistics collected I get the following execute plan:
=== cut here ===
SELECT STATEMENT   Cost = 6
  SORT GROUP BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS BY GLOBAL INDEX ROWID TABLE1
            INDEX RANGE SCAN TABLE1_IND1
          TABLE ACCESS FULL TABLE2
        TABLE ACCESS BY INDEX ROWID TABLE3
          INDEX UNIQUE SCAN SYS_C0020398
      TABLE ACCESS BY INDEX ROWID TABLE4
        INDEX UNIQUE SCAN SYS_C0020399
=== cut here ===

Then I collect statistics for TABLE1:
ANALYZE TABLE table1 ESTIMATE STATISTICS;
and the plan looks like:
=== cut here ===
SELECT STATEMENT   Cost = 5
  SORT GROUP BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS FULL TABLE2
          TABLE ACCESS BY GLOBAL INDEX ROWID TABLE1
            INDEX UNIQUE SCAN SYS_C0020404
        TABLE ACCESS BY INDEX ROWID TABLE3
          INDEX UNIQUE SCAN SYS_C0020398
      TABLE ACCESS BY INDEX ROWID TABLE4
        INDEX UNIQUE SCAN SYS_C0020399
=== cut here ===

Strange, isn't it?

NB: No records were inserted into the tables.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:sbenyo
ID: 2797097
Try changing the init.ora parameter optimizer_mode to RULE and see if statistics change anything.

You can also change this parameter for the session only by:

ALTER SESSION SET OPTIMIZER_MODE = RULE;

I'd like to know if the hint works any different than setting the parameter.
0
 
LVL 1

Expert Comment

by:nitinpai
ID: 2797752
It appears that ur query is not using the 'RULE' optimizer mode. Bcos if optimizer is using 'RULE'  then we won't have 'Cost' in the plan.
0
 
LVL 5

Accepted Solution

by:
sbenyo earned 100 total points
ID: 2797858
It's because you're using Partitions!
Look at this from oracle's docs:

                   Which Optimizer is Used ?
                   -------------------------

Whenever a query is parsed a series of steps is followed to determine
whether the rule-based or cost-based optimiser should be used. The flow
chart below shows how this decisition is made.


                            QUERY IN

                                |
                                |
                               \ /
                 -----------------------------------
                | Is there a hint for table access, |
                | an ORDERED, FIRST_ROWS or         |
                | ALL_ROWS hint ?                   |
                |                                   |  yes
                | 7.3+ Does any table have PARALLEL |-------> COST
                |      set ?                        |
                |                                   |
                | 8.0+ Are any index-only or        |
                |      partition tables involved ?  |
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------   yes
                | Is there a RULE hint ?            |-------> RULE
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Is RULE set at SYSTEM or SESSION  |  yes
                | level and no CHOOSE hint ?        |-------> RULE
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Is ALL_ROWS or FIRST_ROWS set at  |  yes
                | SYSTEM or SESSION level and no    |-------> COST
                | CHOOSE hint ?                     |
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Are there statistics for any of   |  yes
                | the tables ?                      |-------> COST
                 -----------------------------------
                                |
                                | no
                                |

                               RULE



Notes :
-------

(1) Hints in view definitions or in the SELECT clause of an INSERT or
    CREATE TABLE are included for the flowchart.

(2) Recursive SQL choose the goal base on the instance setting.
    This includes SQL called from PL/SQL. Of course hints
    can be used in any SQL called by PL/SQL to override this default.

        Instance        Recursive
        --------        ---------
        RULE            RULE

        FIRST_ROWS      CHOOSE
        ALL_ROWS        CHOOSE
        CHOOSE          CHOOSE

 (3) If any table in a query has a parallel degreee greater than one
     (including the default degree), Oracle uses the cost-based optimizer
     for that query--even if OPTIMIZER_MODE = RULE, or if there is a RULE
     hint in the query itself

0
 
LVL 2

Author Comment

by:davdonin
ID: 2799573
sbenyo, could you provide a reference to the documentation? thank you.
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2799664
Hi,
Look it metalink.oracle.com for doc:

Doc ID:  Note:66484.1
Subject:  Which Optimizer is Used ?

 
0
 
LVL 2

Author Comment

by:davdonin
ID: 2799684
thanks a lot. i wonder if it's mentioned anywhere in the standard oracle8 documentation?...
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2799720
Hi,
Look in Oracle8 Server Concepts:
Chapter 19: The optimizer
At page 19-10 under When to Use the Cost-Based Approach
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question