| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Nested Loop Semi Join
What do the statistics look like on index PAGE_HIT_WH_U1 ? Specifically what values do you have for blevel and leaf_blocks ?
Theo oddity in the plan is this line:
4 2 INDEX (UNIQUE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) Your index hit is being costed as 'free of charge'. This should only be happening for a unique probe into a unique index if the blevel is one.
The no_expand is probably redundant in your hint list. I believe the 'ordered' works because the subquery becomes the first table in the query when it is unnested - and that ordering makes the semi-join impossible. You might get the same effect if you used the hash_sj hint in the subquery - it's a little more precise in its statement of intent (although deprecated in 10g).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
Has anyone seen this behavior on sql's with IN subqueries go into nested loop semi join which is 10x times slower than 8174. However after hinting with /*+ ordered no_expand */ I get much better performance than 8i. Query in 8i with explain plan.
SQL> select count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152);
COUNT(*)
40441
Elapsed: 00:01:28.59
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12814 Card=1 Bytes=19) 1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=12814 Card=3852 Bytes=73188) 3 2 VIEW OF 'VW_NSO_1' (Cost=16 Card=125 Bytes=1625) 4 3 SORT (UNIQUE) (Cost=16 Card=125 Bytes=1375) 5 4 INDEX (RANGE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) (Cost=3 Card=126 Bytes=1386) 6 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12788 Card=34385176Bytes=206311056)
Query in 9205 with optimizer_features_enable=920, cpu_costing = off
SQL> select count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152);
COUNT(*)
40539
Elapsed: 00:13:59.22
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12791 Card=1 Bytes=17) 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=12791 Card=3883 Bytes=66011) 3 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12789 Card=34385176 Bytes=206311056) 4 2 INDEX (UNIQUE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE)
Query in 9205 with optimizer_features_enable=920, cpu_costing = off, after hinting
SQL> select /*+ ordered no_expand */ count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152);
COUNT(*)
40539
Elapsed: 00:01:10.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12816 Card=1 Bytes=17) 1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=12816 Card=3858 Bytes=65586) 3 2 SORT (UNIQUE) 4 3 INDEX (RANGE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) (Cost=3 Card=125 Bytes=1375) 5 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12789 Card=34385176Bytes=206311056)
Thanks,
Stalin
 |
 |