Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Query not using index

Laurent Schneider
Member Posts: 5,220 Bronze Trophy
Hi,
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.
Thanks
Laurent
PS: I am using 9.2.0.8
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.
Thanks
Laurent
PS: I am using 9.2.0.8
SQL> create table lsc_t1(a number primary key, b number); Table created. SQL> create table lsc_t2(a number primary key, b number); Table created. SQL> create table lsc_t3(a number primary key, b number); Table created. SQL> insert into lsc_t1(a,b) select rownum, dbms_random.value from dual connect by level<10000; 9999 rows created. SQL> insert into lsc_t2(a,b) select rownum, dbms_random.value from dual connect by level<1000; 999 rows created. SQL> insert into lsc_t3(a,b) select rownum, dbms_random.value from dual connect by level<1000; 999 rows created. SQL> commit; Commit complete. SQL> begin 2 dbms_stats.gather_table_stats(user,'LSC_T1'); 3 dbms_stats.gather_table_stats(user,'LSC_T2'); 4 dbms_stats.gather_table_stats(user,'LSC_T3'); 5 end; 6 / PL/SQL procedure successfully completed. SQL> set timi on SQL> select --+ INDEX(lsc_t1) 2 * from lsc_t1 3 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); A B ---------- ---------- 140 .138460932 161 .928661302 281 .830010122 440 .071851157 443 .355640404 516 .791276412 598 .508219846 600 .158621147 643 .748363911 747 .777346635 836 .390349029 869 .292453677 874 .914353702 881 .930301092 14 rows selected. Elapsed: 00:00:00.03 SQL> select 2 * from lsc_t1 3 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); A B ---------- ---------- 140 .138460932 161 .928661302 281 .830010122 440 .071851157 443 .355640404 516 .791276412 598 .508219846 600 .158621147 643 .748363911 747 .777346635 836 .390349029 869 .292453677 874 .914353702 881 .930301092 14 rows selected. Elapsed: 00:00:00.26 SQL> set timi off autot trace exp SQL> select --+ INDEX(lsc_t1) 2 * from lsc_t1 3 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=17 Bytes=476) 1 0 NESTED LOOPS (Cost=19 Card=17 Bytes=476) 2 1 VIEW OF 'VW_NSO_1' (Cost=2 Card=17 Bytes=68) 3 2 SORT (UNIQUE) 4 3 UNION-ALL 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=8 Bytes=192) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C001186489' (UNIQUE) SQL> select 2 * from lsc_t1 3 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=17 Bytes=476) 1 0 NESTED LOOPS (Cost=6 Card=17 Bytes=476) 2 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=6 Card=9999 Bytes=239976) 3 1 VIEW OF 'VW_NSO_1' 4 3 SORT (UNIQUE) 5 4 UNION-ALL (PARTITION) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24) 7 6 INDEX (UNIQUE SCAN) OF 'SYS_C001186490' (UNIQUE) (Cost=1 Card=1) 8 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24) 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C001186491' (UNIQUE) (Cost=1 Card=1)
Best Answer
-
Laurent,
if you check my post carefully you'll notice that the "exists" rewrite using the the filter condition outside the "union all" generates a plan that is - although not identical due to the "SORT UNIQUE" operation being outside instead of inside the view definition - from an "execution" perspective "equal" from my point of view. Please let me know if you think this is not the case.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Edited by: Randolf Geist on Sep 22, 2008 10:09 AM
Tubby's solution is not equal due to performing the sort after the NESTED LOOP
Answers
-
I assume re-writing the query is ok?
I tested this out on 9.2.0.8.....with data as
(
select *
from lsc_t2
union all
select *
from lsc_t3
)
select
distinct l.*
from lsc_t1 l, data d
where l.a = d.a
and d.b < .01 ; -
You have not gathered statistics for your Index on the tables.
You need to use cascade=>true.SQL> begin 2 dbms_stats.gather_table_stats(user,'LSC_T1',cascade=>true); 3 dbms_stats.gather_table_stats(user,'LSC_T2',cascade=>true); 4 dbms_stats.gather_table_stats(user,'LSC_T3',cascade=>true); 5 end; 6 /
Thanks,
Karthick. -
Laurent Schneider wrote:
Hi,
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.SQL> select 2 * 3 from lsc_t1 4 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=20 Bytes=560) 1 0 NESTED LOOPS (Cost=6 Card=20 Bytes=560) 2 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=6 Card=9999 Bytes=239976) 3 1 VIEW OF 'VW_NSO_1' 4 3 SORT (UNIQUE) 5 4 UNION-ALL (PARTITION) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24) 7 6 INDEX (UNIQUE SCAN) OF 'SYS_C003365' (UNIQUE) (Cost=1 Card=1) 8 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24) 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C003366' (UNIQUE) (Cost=1 Card=1) SQL> alter session set optimizer_mode=rule; Session altered. SQL> select 2 * 3 from lsc_t1 4 where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 VIEW OF 'VW_NSO_1' 3 2 SORT (UNIQUE) 4 3 UNION-ALL 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003364' (UNIQUE)
;-)
Nicolas. -
Interesting subtle differences:
SQL> SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production Elapsed: 00:00:00.01 SQL> SQL> drop table lsc_t1; Table dropped. Elapsed: 00:00:00.08 SQL> SQL> drop table lsc_t2; Table dropped. Elapsed: 00:00:00.03 SQL> SQL> drop table lsc_t3; Table dropped. Elapsed: 00:00:00.03 SQL> SQL> create table lsc_t1(a number primary key, b number); Table created. Elapsed: 00:00:00.03 SQL> SQL> create table lsc_t2(a number primary key, b number); Table created. Elapsed: 00:00:00.02 SQL> SQL> create table lsc_t3(a number primary key, b number); Table created. Elapsed: 00:00:00.02 SQL> SQL> insert into lsc_t1(a,b) 2 select rownum, dbms_random.value from dual connect by level<10000; 9999 rows created. Elapsed: 00:00:00.19 SQL> SQL> insert into lsc_t2(a,b) 2 select rownum, dbms_random.value from dual connect by level<1000; 999 rows created. Elapsed: 00:00:00.03 SQL> SQL> insert into lsc_t3(a,b) 2 select rownum, dbms_random.value from dual connect by level<1000; 999 rows created. Elapsed: 00:00:00.03 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.01 SQL> SQL> begin 2 dbms_stats.gather_table_stats(user,'LSC_T1', cascade=>true); 3 dbms_stats.gather_table_stats(user,'LSC_T2', cascade=>true); 4 dbms_stats.gather_table_stats(user,'LSC_T3', cascade=>true); 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.11 SQL> SQL> set timi on autotrace traceonly echo on SQL> SQL> select --+ INDEX(lsc_t1) 2 * from lsc_t1 3 where a in ( 4 select a 5 from lsc_t2 6 where b<.01 7 union all 8 select a 9 from lsc_t3 10 where b<.01 11 ); 20 rows selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=19 Bytes=532) 1 0 NESTED LOOPS (Cost=21 Card=19 Bytes=532) 2 1 VIEW OF 'VW_NSO_1' (Cost=2 Card=19 Bytes=76) 3 2 SORT (UNIQUE) 4 3 UNION-ALL 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 57 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> select 2 * from lsc_t1 3 where a in ( 4 select a 5 from lsc_t2 6 where b<.01 7 union all 8 select a 9 from lsc_t3 10 where b<.01 11 ); 20 rows selected. Elapsed: 00:00:00.15 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=19 Bytes=532) 1 0 NESTED LOOPS (Cost=8 Card=19 Bytes=532) 2 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976) 3 1 VIEW OF 'VW_NSO_1' 4 3 SORT (UNIQUE) 5 4 UNION-ALL (PARTITION) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24) 7 6 INDEX (UNIQUE SCAN) OF 'SYS_C003348' (UNIQUE) (Cost=1 Card=1) 8 5 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24) 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 42043 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 9999 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> select 2 * from lsc_t1 3 where a in ( 4 select a 5 from lsc_t2 6 where b<.01 7 union 8 select a 9 from lsc_t3 10 where b<.01 11 ); 20 rows selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=19 Bytes=703) 1 0 HASH JOIN (Cost=24 Card=19 Bytes=703) 2 1 VIEW OF 'VW_NSO_1' (Cost=16 Card=19 Bytes=247) 3 2 SORT (UNIQUE) (Cost=16 Card=19 Bytes=456) 4 3 UNION-ALL 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) 7 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> select 2 * from lsc_t1 3 where a in ( 4 select 5 distinct a 6 from ( 7 select a 8 from lsc_t2 9 where b<.01 10 union all 11 select a 12 from lsc_t3 13 where b<.01 14 ) 15 ); 20 rows selected. Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=37) 1 0 HASH JOIN (SEMI) (Cost=15 Card=1 Bytes=37) 2 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976) 3 1 VIEW OF 'VW_NSO_1' (Cost=4 Card=19 Bytes=247) 4 3 VIEW (Cost=4 Card=19 Bytes=247) 5 4 UNION-ALL 6 5 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 7 5 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
-
Continued due to permanent HTTP 500 error messages when posting as one message...
SQL> SQL> select 2 * from lsc_t1 o 3 where exists ( 4 select null 5 from ( 6 select a, b 7 from lsc_t2 8 where b<.01 9 union all 10 select a, b 11 from lsc_t3 12 where b<.01 13 ) i 14 where i.a = o.a 15 ); 20 rows selected. Elapsed: 00:00:00.08 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=9999 Bytes=279972) 1 0 NESTED LOOPS (SEMI) (Cost=8 Card=9999 Bytes=279972) 2 1 TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976) 3 1 VIEW 4 3 UNION-ALL (PARTITION) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24) 6 5 INDEX (UNIQUE SCAN) OF 'SYS_C003348' (UNIQUE) (Cost=1 Card=1) 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 42022 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> select 2 * from lsc_t1 o 3 where exists ( 4 select null from ( 5 select a, b 6 from lsc_t2 7 union all 8 select a, b 9 from lsc_t3) i 10 where i.a = o.a 11 and i.b<.01 12 ); 20 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931) 1 0 NESTED LOOPS (Cost=28 Card=19 Bytes=931) 2 1 SORT (UNIQUE) 3 2 VIEW (Cost=3 Card=19 Bytes=475) 4 3 UNION-ALL (PARTITION) 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 57 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> with data as 2 ( 3 select * 4 from lsc_t2 5 union all 6 select * 7 from lsc_t3 8 ) 9 select 10 distinct l.* 11 from lsc_t1 l, data d 12 where l.a = d.a 13 and d.b < .01 ; 20 rows selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931) 1 0 SORT (UNIQUE) (Cost=28 Card=19 Bytes=931) 2 1 NESTED LOOPS (Cost=22 Card=19 Bytes=931) 3 2 VIEW (Cost=3 Card=19 Bytes=475) 4 3 UNION-ALL (PARTITION) 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 58 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> select 2 distinct l.* 3 from lsc_t1 l, 4 ( 5 select * 6 from lsc_t2 7 union all 8 select * 9 from lsc_t3 10 ) d 11 where l.a = d.a 12 and d.b < .01 ; 20 rows selected. Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931) 1 0 SORT (UNIQUE) (Cost=28 Card=19 Bytes=931) 2 1 NESTED LOOPS (Cost=22 Card=19 Bytes=931) 3 2 VIEW (Cost=3 Card=19 Bytes=475) 4 3 UNION-ALL (PARTITION) 5 4 TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216) 6 4 TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 58 consistent gets 0 physical reads 0 redo size 1145 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> spool off
So when using the CBO Tubby's approach works (and the corresponding "normal" inline view version) as well as the "exists" clause with the filter condition outside the "union all". Depending on the size of the tables the "hash join" used for the "union" or "select distinct" approach also seems to be not too bad.
10.2.0.4 and 11.1.0.6 don't show this behaviour... They only switch between the "good" nested loop plan and the hash join. I haven't checked the 10053 trace yet but I guess 9.2.0.8 somehow loses the cost for the inner nested loop lookup when messing it up.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Edited by: Randolf Geist on Sep 20, 2008 11:16 AM
"Normal" inline view added, 10g + 11g comment -
Thanks for your suggestion.
I tried with cascade=true but it did not help. I also tried with cascade=>true,method_opt=>'for all columns size 254' but it is still not too good.
The rule hint rules, thanks Nicolas. It is however not the expected answer
About rewriting with distinct or unique or union, it is interesting. Still it does not generate the same plan as with /*INDEX(LSC_T1)*/ or /*RULE*/. -
Laurent,
if you check my post carefully you'll notice that the "exists" rewrite using the the filter condition outside the "union all" generates a plan that is - although not identical due to the "SORT UNIQUE" operation being outside instead of inside the view definition - from an "execution" perspective "equal" from my point of view. Please let me know if you think this is not the case.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Edited by: Randolf Geist on Sep 22, 2008 10:09 AM
Tubby's solution is not equal due to performing the sort after the NESTED LOOP -
You are right, I missed that one.
Thanks a lot
Laurent
This discussion has been closed.