Different execution plans for a query with RULE hint

Hi Denis,

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

Andrew

This sounds like an Oracle bug to me.

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.

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.

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.

sbenyo, could you provide a reference to the documentation? thank you.

Hi,
Look it metalink.oracle.com for doc:

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

 

thanks a lot. i wonder if it's mentioned anywhere in the standard oracle8 documentation?...

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