Re: Reordering the from-clause changes the query plan?

<fredrik.groups_at_gmail.com> wrote in message news:1124121815.929797.125010_at_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
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
> 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
>

Notice the PUSHED PREDICATE in the cheaper query (line 9). I think you've found a bug in predicate pushing lurking somewhere in the region where the you join two "union all" views. If you take out one of the halves of the UNION ALL in the t1 view in the expensive query, you will find that Oracle pushes predicates there too, and the cost will drop from 43 to (roughly) 7 - i.e. half the cost of the cheaper query where predicate pushing is working very well.

I'd guess that some piece of code has lost track of what's going on in the recursive descent through the query when the two views are in the 'wrong' order.

File an iTAR - it's got to be a bug, and you've got a perfect reproducible test case

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005