Sub-query unnesting and hierarchical queries oddity

Last week when tuning some query with a CONNECT BY clause in a correlated sub-query and reading it’s execution plan i discovered some strange things.Here is quick test case to demonstrate the findings :

TEST CASE :

Oracle Database version tested 11.2.0.3.9 and 12.1.0.2.0

The test data created have no sense it’s just for demonstration.

create table test_unest as select level as a,level +1 as b    from dual connect by level < 10000;
create table test_unest2 as select level as a,level +1 as b    from dual connect by level < 10000;
create table test_unest3 as select level as a,level +1 as b    from dual connect by level < 10000;

 CREATE OR REPLACE FORCE VIEW test_unest_v
(
a,
b,
l
)
AS
SELECT T.”A”,
T.”B”,
LEVEL
FROM test_unest T
START WITH T.A IN (‘1’, ‘2’, ‘3’)
CONNECT BY PRIOR T.A = T.B;

CREATE INDEX test_unest2_idx ON test_unest2 (a);

exec dbms_stats.gather_table_stats(null,’test_unest’);
exec dbms_stats.gather_table_stats(null,’test_unest2′);
exec dbms_stats.gather_table_stats(null,’test_unest3′);

TEST 1 :

select  tab1.a from test_unest3 tab1  where not exists ( select   /*+ QB_NAME(SUB1) */   1 from test_unest_v tab2,test_unest2 tab3 where tab1.a = tab3.a and tab2.b = tab3.b)  ;

(will return 9996 rows)

TEST 2 (add unnest hint):

select  /*+ UNNEST(@SUB1) */ tab1.a from test_unest3 tab1  where not exists ( select   /*+ QB_NAME(SUB1) */   1 from test_unest_v tab2,test_unest2 tab3 where tab1.a = tab3.a and tab2.b = tab3.b)  ;

Observation :

Wow ! so although there is a better plan (cost 26 and also you may have noticed the huge difference in the estimated number of rows but that’s another subject) the optimizer prefered to use the one with the filter row source (cost 53492). We need to add a hint to force the optimizer to unnest the subquery and do a hash join anti operation which is far better.Let’s take a look at the optimizer trace file .

Query without unnest hint :

$ more ORCL_2_ora_1773_10053_filter.trc | grep -i su:
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Checking validity of unnesting subquery SUB1 (#0)
SU:     SU bypassed: invalidated.
SU:   Validity checks failed.

Query with unnest hint :

$ more ORCL_2_ora_22812_10053_hash.trc | grep -i su:
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Checking validity of unnesting subquery SUB1 (#0)
SU:   Unnesting subquery query block SUB1 (#0)Registered qb: SEL$6B890ED9 0xbf392480 (SUBQ INTO VIEW FOR COMPLEX UNNEST SUB1)

Query after unnest :

So the optimizer for no apparent reason marked the unnesting operation as invalid and when adding the unnest hint it is now valid (Strange).So watch out, i  tried the same test on a 12.1.0.2.0 version and there is no change.

NOTE: By the way I fixed the execution plan in production system by adding an sqlprofile using Carlos Sierra script “coe_load_sql_profile.sql” which reduced the execution time by more than 60%.

That’s it 😀