Reordering the from-clause changes the query plan?

From: <fredrik.groups_at_gmail.com>
Date: 15 Aug 2005 09:03:35 -0700
Message-ID: <1124121815.929797.125010@o13g2000cwo.googlegroups.com>

Hi all!

I am using Oracle 10.1.0.3 on a Red Hat Enterprise Linux server (see below for kernel version and so on).

I have a complex query (joining quite a few tables) that is using full table scans in some places where I think that access by indexes would be more efficient. While experimening, I noticed that changing the order of the tables in the from-clause sometimes caused the query plan to change.

I was under the impression that changing the order of the tables in the from-clause should not change the execution of the query while using the cost-based optimizer. Is this not correct?

A (hopefully) complete test case follows below. Since this is my first usenet post in several years, I apologize in advance if I have messed up the formatting.

The queries below are (obviously) simplified versions of the original queries. Right now, I am most interested in the possible reasons for the changing plan when only reordering the from-clause.

Thank you in advance for any ideas or pointers to documentation.

/Fredrik

SQL*Plus: Release 10.1.0.4.0 - Production on Mon Aug 15 17:12:12 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Server: Red Hat Enterprise Linux ES release 3 (Taroon Update 2) Linux xxx.yyy.zzz 2.4.21-32.0.1.EL #1 SMP Tue May 17 17:52:26 EDT 2005

                  x86_64 x86_64 x86_64 GNU/Linux

drop table t;
drop table t_ws;

Create tables used for test create table t ( dp_oid number(10) not null, dp_otype number(10) not null, dp_ctype number(10) not null, dp_subtype number(10) default 1, dp_occurrence number(10) default 1, dp_lock number(10) default 0, dp_deleted number(10) default 0 not null, dp_mod timestamp(6) default systimestamp not null, dp_sign varchar2(30) default user not null, dp_display_flag number(10), dp_state number(10) default 0 not null, parent_oid number(10), parent_otype number(10) );

create table t_ws as select * from t where 1 = 2;

Insert test data

insert into t
select rownum, 1, 1, 1, 1, 0, 0, systimestamp, user, 0, 0, rownum, 1 from all_objects where rownum < 25000;

insert into t_ws
select rownum, 1, 1, 1, 1, 183541, 0, systimestamp, user, 0, 0, rownum, 1
from all_objects where rownum < 25000;

Create the indexes we want the queries to use

create index t_idx on t (parent_oid, parent_otype); create index t_ws_idx on t_ws (parent_oid, parent_otype);

create unique index t_ws_unique_cid_idx on t_ws (dp_lock, dp_otype, dp_oid, dp_ctype, dp_occurrence);
create unique index t_unique_cid_idx on t (dp_otype, dp_oid, dp_ctype, dp_occurrence);

Gather statistics
Change FREDDE to appropriate schema name exec dbms_stats.gather_schema_stats('FREDDE', 99, method_opt =>'for all columns', cascade=> true );
slow running version of query select 1 from (select * from t where dp_lock <> 183541 union all select * from t_ws where dp_lock = 183541) t1, (select * from t where dp_lock <> 183541 union all select * from t_ws where dp_lock = 183541) t2 where t1.dp_oid = t2.dp_oid and t1.dp_otype = t2.dp_otype and t1.parent_otype = 1 and t1.parent_oid = 4711;
Gives the following exection plan:
(when running the second time, to ignore the parsing) Execution Plan
rows will be truncated 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=43 Card=10 Bytes=780) 1 0 HASH JOIN (Cost=43 Card=10 Bytes=780) 2 1 VIEW (Cost=4 Card=2 Bytes=104) 3 2 UNION-ALL 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE)
(Cost=2 Card=1 Bytes=18)
5 4 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX)
(Cost=1 Card=1)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_WS'
(TABLE) (Cost=2 Card=1 Bytes=21)
7 6 INDEX (RANGE SCAN) OF 'T_WS_IDX' (INDEX)
(Cost=1 Card=1)
8 1 VIEW (Cost=38 Card=49994 Bytes=1299844) 9 8 UNION-ALL 10 9 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=25 Card=25006 Bytes=250060) 11 9 INDEX (FAST FULL SCAN) OF 'T_WS_UNIQUE_CID_IDX' (INDEX (UNIQUE)) (Cost=13 Card=24988 Bytes=324844)

rows will be truncated

Statistics

          0  recursive calls
          0  db block gets
        315  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Fast running version of query, the *only* change is to change
the order of t1 and t2 select 1 from (select * from t where dp_lock <> 183541 union all select * from t_ws where dp_lock = 183541) t2, (select * from t where dp_lock <> 183541 union all select * from t_ws where dp_lock = 183541) t1 where t1.dp_oid = t2.dp_oid and t1.dp_otype = t2.dp_otype and t1.parent_otype = 1 and t1.parent_oid = 4711;
This gives a different execution plan:
(when running the second time, to ignore the parsing) Execution Plan
rows will be truncated 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=10 Bytes=940) 1 0 NESTED LOOPS (Cost=14 Card=10 Bytes=940) 2 1 VIEW (Cost=4 Card=2 Bytes=172) 3 2 UNION-ALL 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE)
(Cost=2 Card=1 Bytes=18)
5 4 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX)
(Cost=1 Card=1)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_WS'
(TABLE) (Cost=2 Card=1 Bytes=21)
7 6 INDEX (RANGE SCAN) OF 'T_WS_IDX' (INDEX)
(Cost=1 Card=1)
8 1 VIEW (Cost=5 Card=1 Bytes=8) 9 8 UNION ALL PUSHED PREDICATE 10 9 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE)
(Cost=3 Card=1 Bytes=18)
11 10 INDEX (RANGE SCAN) OF 'T_UNIQUE_CID_IDX'
(INDEX (UNIQUE)) (Cost=2 Card=1)
12 9 INDEX (RANGE SCAN) OF 'T_WS_UNIQUE_CID_IDX'
(INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=21)

rows will be truncated

Statistics

          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Received on Mon Aug 15 2005 - 11:03:35 CDT