Different execution plans for a query with RULE hint

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?
LVL 2
davdoninAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sbenyoConnect With a Mentor Commented:
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
 
Andrei RodionovCommented:
Hi Denis,

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

Andrew
0
 
rkogelheCommented:
This sounds like an Oracle bug to me.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
davdoninAuthor Commented:
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
 
sbenyoCommented:
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
 
nitinpaiCommented:
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
 
davdoninAuthor Commented:
sbenyo, could you provide a reference to the documentation? thank you.
0
 
sbenyoCommented:
Hi,
Look it metalink.oracle.com for doc:

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

 
0
 
davdoninAuthor Commented:
thanks a lot. i wonder if it's mentioned anywhere in the standard oracle8 documentation?...
0
 
sbenyoCommented:
Hi,
Look in Oracle8 Server Concepts:
Chapter 19: The optimizer
At page 19-10 under When to Use the Cost-Based Approach
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.