SQL> drop table p purge; Table P dropped. SQL> SQL> create table p nologging 2 as 3 with generator as ( 4 select --+ materialize 5 rownum id 6 from dual 7 connect by 8 level <= 1e4 9 ) 10 select ROWNUM rypyid, 11 cast (trim(to_char(MOD(ROWNUM, 10000), '099999999')) as nvarchar2(10)) ryglba from 12 generator v1, 13 generator v2 14 where rownum <= 400000; Table P created. SQL> SQL> alter table p add constraint p_pk primary key(rypyid); Table P altered. SQL> exec dbms_stats.gather_table_stats(null, 'p'); PL/SQL procedure successfully completed. SQL> SQL> drop table c1 purge; Table C1 dropped. SQL> SQL> create table c1 nologging 2 as 3 with generator as ( 4 select --+ materialize 5 rownum id 6 from dual 7 connect by 8 level <= 1e4 9 ) 10 select 11 cast (trim(to_char(MOD(ROWNUM, 10000000), '099999999')) as nvarchar2(10)) RZVR01, 12 MOD(ROWNUM, 1000000) RZPYID, 13 rpad('X', 255, 'X') padding 14 from 15 generator v1, 16 generator v2 17 where 18 rownum <= 16000000; Table C1 created. SQL> SQL> CREATE INDEX c1_I ON c1 (RZVR01, RZPYID); Index C1_I created. SQL> SQL> exec dbms_stats.gather_table_stats(null, 'c1'); PL/SQL procedure successfully completed. SQL> SQL> drop table c2 purge; Table C2 dropped. SQL> SQL> create table c2 nologging 2 as 3 with generator as ( 4 select --+ materialize 5 rownum id 6 from dual 7 connect by 8 level <= 1e4 9 ) 10 select CAST(ROWNUM AS NCHAR(16)) gmaid, 11 cast (trim(to_char(MOD(ROWNUM, 1038), '099999999')) as nchar(12)) gmobj, 12 cast (trim(to_char(MOD(ROWNUM, 1441), '099999999')) as nchar(16)) gmsub, 13 rpad('X', 34, 'X') PADDING from 14 generator v1, 15 generator v2 16 where rownum <= 1000000; Table C2 created. SQL> SQL> alter table c2 add constraint c2_pk primary key (gmaid); Table C2 altered. SQL> create index c2_i on c2(gmobj, gmsub, gmaid); Index C2_I created. SQL> exec dbms_stats.gather_table_stats(null, 'c2') PL/SQL procedure successfully completed. SQL> SQL> SQL> explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion' 'off') P1 */ null FROM P, C1, C2 WHERE 2 ( ( C1.RZVR01 = :c1 AND C2.GMOBJ BETWEEN '2201' AND '2299') OR 3 ( C1.RZVR01 = :c2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' ) ) AND 4 ( P.rypyid = C1.RZPYID AND P.RYGLBA = C2.GMAID ); Explained. SQL> SQL> select * from dbms_xplan.display(); PLAN_TABLE_OUTPUT ____________________________________________________________________________________________ Plan hash value: 2640408289 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 417 | 23 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | NESTED LOOPS | | 1 | 139 | 9 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 139 | 9 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 115 | 7 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | C2_I | 1 | 91 | 3 (0)| 00:00:01 | | 6 | INLIST ITERATOR | | | | | | |* 7 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | P | 1 | 24 | 2 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 139 | 7 (0)| 00:00:01 | | 11 | NESTED LOOPS | | 1 | 139 | 7 (0)| 00:00:01 | | 12 | NESTED LOOPS | | 1 | 48 | 6 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID| P | 1 | 24 | 2 (0)| 00:00:01 | |* 15 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | C2_PK | 1 | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 91 | 1 (0)| 00:00:01 | | 18 | NESTED LOOPS | | 1 | 139 | 7 (0)| 00:00:01 | | 19 | NESTED LOOPS | | 1 | 139 | 7 (0)| 00:00:01 | | 20 | NESTED LOOPS | | 1 | 48 | 6 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)| 00:00:01 | | 22 | TABLE ACCESS BY INDEX ROWID| P | 1 | 24 | 2 (0)| 00:00:01 | |* 23 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)| 00:00:01 | |* 24 | INDEX UNIQUE SCAN | C2_PK | 1 | | 1 (0)| 00:00:01 | |* 25 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 91 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT _________________________________________________________________________________________________ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT') 7 - access("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2)) filter("C1"."RZVR01"=SYS_OP_C2C(:C1) AND "C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ"<=U'2299' OR "C1"."RZVR01"=SYS_OP_C2C(:C2) AND "C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT') 8 - access("P"."RYPYID"="C1"."RZPYID") 9 - filter("P"."RYGLBA"="C2"."GMAID") 13 - access("C1"."RZVR01"=SYS_OP_C2C(:C2)) filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2)) 15 - access("P"."RYPYID"="C1"."RZPYID") 16 - access("P"."RYGLBA"="C2"."GMAID") 17 - filter("C2"."GMSUB"=U'IDT' AND "C2"."GMOBJ"=U'2019' AND "C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ"<=U'2299' AND (LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019'))) 21 - access("C1"."RZVR01"=SYS_OP_C2C(:C1)) filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2)) 23 - access("P"."RYPYID"="C1"."RZPYID") 24 - access("P"."RYGLBA"="C2"."GMAID") 25 - filter("C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' AND (LNNVL("C1"."RZVR01"=SYS_OP_C2C(:C2)) OR LNNVL("C2"."GMOBJ"=U'2019') OR LNNVL("C2"."GMSUB"=U'IDT')) AND (LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019'))) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (E - Syntax error (1)) --------------------------------------------------------------------------- 1 - SEL$1 E - P1 66 rows selected.