Is there a way to test the expansion of the query
with a tset case using trace

ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx1 on t(x,y);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(y);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select /*+ first_rows */ *
  2    from t
  3   where x = 5 or y = 10;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=2 Bytes=78)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=39)
   3    2       INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=2 Card=1)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=39)
   5    4       INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
         62  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        315  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
ops$tkyte@ORA9IR2> select /*+ no_expand */ *
  2    from t
  3   where x = 5 or y = 10;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=78)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=2 Bytes=78)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        315  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off
 

I was testing two queries with a OR predicates
one, with the hint /* NO_EPNAD */ and
another without the hint

I traced it with event 10046 and, the tkprof does not show
the re-write of the query with UNION ALL

Is there a way to test which shows the re-write of the query
with UNION ALL if the hint is not used

and in fact, dbms_xplan shows this much better:

ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for
  2  select /*+ first_rows */ *
  3    from t
  4   where x = 5 or y = 10;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
 
----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     2 |    78 |     2 |
|   1 |  CONCATENATION               |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T           |     1 |    39 |     1 |
|*  3 |    INDEX RANGE SCAN          | T_IDX2      |     1 |       |     2 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T           |     1 |    39 |     1 |
|*  5 |    INDEX RANGE SCAN          | T_IDX1      |     1 |       |     2 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Y"=10)
   5 - access("T"."X"=5)
       filter(LNNVL("T"."Y"=10))
 
Note: cpu costing is off
 
20 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
6 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for
  2  select /*+ no_expand */ *
  3    from t
  4   where x = 5 or y = 10;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     2 |    78 |     2 |
|*  1 |  TABLE ACCESS FULL   | T           |     2 |    78 |     2 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."X"=5 OR "T"."Y"=10)
 
Note: cpu costing is off
 
14 rows selected.
 

Tom,

The CBO behavior I'm experiencing results in a query execution that eats 1/3 of the CPU power. The actual query uses collections "in " subqueries. I was able to reproduce it using lookup tables instead.

Here is setup that simulates this behavior. 
drop table a;
drop table lt1;
drop table lt2;

create table a as select * from all_objects;
insert /*+ append */ into a select * from a;
commit;
insert /*+ append */ into a select * from a;
commit;
insert /*+ append */ into a select * from a;
commit;
insert /*+ append */ into a select * from a;
commit;

create index a_i1 on a(object_id);
create index a_i2 on a(data_object_id);

create table lt1 as select object_id from a where mod(object_id,500)=0 and rownum < 10;
create table lt2 as select data_object_id from a where mod(data_object_id,900)=0 and rownum < 10;

begin
    dbms_stats.gather_table_stats(user,'a',method_opt=>'for all columns size 254',estimate_percent=>100,cascade=>true);
    dbms_stats.gather_table_stats(user,'lt1',method_opt=>'for all columns size 254',estimate_percent=>100,cascade=>true);
    dbms_stats.gather_table_stats(user,'lt2',method_opt=>'for all columns size 254',estimate_percent=>100,cascade=>true);
end;
/

Here are results:

set autotrace traceonly
SELECT 
    A0 . object_name 
    , A0 . object_id 
    , A0 . owner
    , A0 . data_object_id
FROM 
    a A0 
WHERE 
    A0 . object_id IN 
    ( 
        SELECT 
            * 
        FROM lt1 wtot1
    ) 
    OR 
    A0 . data_object_id IN 
    ( 
        SELECT 
            * 
        FROM lt2 wtot2
     );

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=698 Card=42310 Bytes
          =1650090)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'A' (Cost=698 Card=42310 Bytes=16
          50090)

   3    1     TABLE ACCESS (FULL) OF 'LT1' (Cost=2 Card=1 Bytes=3)
   4    1     TABLE ACCESS (FULL) OF 'LT2' (Cost=2 Card=1 Bytes=3)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2277076  consistent gets
       2908  physical reads
          0  redo size
      13923  bytes sent via SQL*Net to client
        864  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        288  rows processed

The query that would be used in case of OR-expansion:

SELECT 
    A0 . object_name 
    , A0 . object_id 
    , A0 . owner
    , A0 . data_object_id
FROM 
    a A0 
WHERE 
    A0 . object_id IN 
    ( 
        SELECT 
            * 
        FROM lt1 wtot1
    ) 
UNION ALL
SELECT 
    A0 . object_name 
    , A0 . object_id 
    , A0 . owner
    , A0 . data_object_id
FROM 
    a A0 
WHERE 
    A0 . data_object_id IN 
    ( 
        SELECT 
            * 
        FROM lt2 wtot2
     )
     AND
    A0 . object_id NOT IN 
    ( 
        SELECT 
            * 
        FROM lt1 wtot1
        WHERE object_id IS NOT NULL
    )
     AND object_id IS NOT NULL;

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38  
288 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=317 Card=288 Bytes=1
          5408)

   1    0   UNION-ALL
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'A' (Cost=17 Card=16 By
          tes=624)

   3    2       NESTED LOOPS (Cost=157 Card=144 Bytes=7488)
   4    3         VIEW OF 'VW_NSO_2' (Cost=2 Card=9 Bytes=117)
   5    4           SORT (UNIQUE)
   6    5             TABLE ACCESS (FULL) OF 'LT1' (Cost=2 Card=9 Byte
          s=27)

   7    3         INDEX (RANGE SCAN) OF 'A_I1' (NON-UNIQUE) (Cost=1 Ca
          rd=16)

   8    1     HASH JOIN (ANTI) (Cost=160 Card=144 Bytes=7920)
   9    8       TABLE ACCESS (BY INDEX ROWID) OF 'A' (Cost=17 Card=16
          Bytes=624)

  10    9         NESTED LOOPS (Cost=157 Card=144 Bytes=7488)
  11   10           VIEW OF 'VW_NSO_1' (Cost=2 Card=9 Bytes=117)
  12   11             SORT (UNIQUE)
  13   12               TABLE ACCESS (FULL) OF 'LT2' (Cost=2 Card=9 By
          tes=27)

  14   10           INDEX (RANGE SCAN) OF 'A_I2' (NON-UNIQUE) (Cost=1
          Card=16)

  15    8       TABLE ACCESS (FULL) OF 'LT1' (Cost=2 Card=9 Bytes=27)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        336  consistent gets
        286  physical reads
          0  redo size
       6183  bytes sent via SQL*Net to client
        864  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        288  rows processed

Is this a bug or I'm assuming too much. Could you think of a way to hint CBO in this case - USE_CONCAT doesn't work.

Thanks. 

Tom,

We run multi-user test that is simulated by SilkPerformer. That's slightly irrelevnt. What is relevant that the whole test takes 1 hour 20 minutes on 8 CPU box. The query in question takes 11000 seconds on CPU, retrieving about 3000 records total in about 350 executions. Mentioned 1/3 is 11000/(80*60*8).

The second query is what I thought would be a perfect rewrite, and that is concatenation. Can CBO do that kind of trick?

Thanks.

Tom,

I verified it. It's note even close to the second query in the original message.

SQL> SELECT
    A0 . object_name
    , A0 . object_id
    , A0 . owner
    , A0 . data_object_id
FROM
    a A0 left join (select distinct object_id lt1_object_id from lt1) lt1 on 
(a0.object_id = lt1_object_id)
    left join (select distinct data_object_id lt2_data_object_id from lt2) lt2 
on (a0.data_object_id = lt2_data_object_id)
 where lt1_object_id is not null
    or lt2_data_object_id is not null
  2    3    4    5    6    7    8    9   10   11   12   13  ;

288 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1826 Card=433952 Byt
          es=28206880)

   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       HASH JOIN (OUTER) (Cost=1199 Card=433952 Bytes=2256550
          4)

   4    3         TABLE ACCESS (FULL) OF 'A' (Cost=698 Card=433952 Byt
          es=16924128)

   5    3         VIEW (Cost=4 Card=9 Bytes=117)
   6    5           SORT (UNIQUE) (Cost=4 Card=9 Bytes=27)
   7    6             TABLE ACCESS (FULL) OF 'LT1' (Cost=2 Card=9 Byte
          s=27)

   8    2       VIEW (Cost=4 Card=9 Bytes=117)
   9    8         SORT (UNIQUE) (Cost=4 Card=9 Bytes=27)
  10    9           TABLE ACCESS (FULL) OF 'LT2' (Cost=2 Card=9 Bytes=
          27)


Statistics
----------------------------------------------------------
       2830  recursive calls
        407  db block gets
       3782  consistent gets
       5331  physical reads
      59048  redo size
       6216  bytes sent via SQL*Net to client
        864  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
        288  rows processed

OR-expansion if possible is so much better. I've tried to mimic it in the second query. In one of two subqueries I needed to get rid of rows that might be brought in the other sub-query. I've used "not in" and "is not null" clauses to achieve that. That's why CBO used anti-join.

Thanks. 

The two queries you have are not equivalent

21:13:36 session_145> set null <null>
21:13:42 session_145> select * from ax;

OBJECT_ID DATA_OBJECT_ID
---------- --------------
1 <null>
<null> 2
1 2
<null> <null>

4 rows selected.

21:13:45 session_145> select * from lt1x;

OBJECT_ID
----------
1

1 row selected.

21:13:49 session_145> select * from lt2x;

DATA_OBJECT_ID
--------------
2

1 row selected.

21:13:52 session_145> SELECT *
21:14:12 2 FROM ax A0
21:14:12 3 WHERE A0.object_id IN ( SELECT object_id FROM lt1x wtot1 )
21:14:12 4 OR A0.data_object_id IN ( SELECT data_object_id FROM lt2x wtot2 )
21:14:12 5 ;

OBJECT_ID DATA_OBJECT_ID
---------- --------------
1 <null>
<null> 2
1 2

3 rows selected.

21:14:12 session_145>
21:14:12 session_145> SELECT *
21:14:12 2 FROM ax A0
21:14:12 3 WHERE A0 . object_id IN ( SELECT * FROM lt1x wtot1 )
21:14:12 4 UNION ALL
21:14:12 5 SELECT *
21:14:12 6 FROM ax A0
21:14:12 7 WHERE A0.data_object_id IN ( SELECT * FROM lt2x wtot2 )
21:14:12 8 AND A0.object_id NOT IN ( SELECT * FROM lt1x wtot1 WHERE object_id IS NOT NULL )
21:14:12 9 AND object_id IS NOT NULL
21:14:12 10 ;

OBJECT_ID DATA_OBJECT_ID
---------- --------------
1 2
1 <null>

2 rows selected.

21:14:13 session_145>

They are not equvalent if object_id contains nulls. Which is not the case in my case. Thanks for mentioning this.

The problem not in this I don't have this query. I have the 1st one and I'd like CBO to OR-expand it.

Tom,

I implemented Vlad’s test case and I have a question (I think it has been asked before but cannot recall) … it is the about the “cr” and “rows” numbers for the FTs in Vlad’s sql … why 114 rows for the FT on LT1 which has 9 rows?

Vladimir,

I included my own re-write of your sql … unless I missed something, it seems to be what you would want Oracle to do?

It seems to work for cases like this where LT1 and LT2 are “strong” filters.


The stats in my system are:

00:15:31 session_147> select table_name,num_rows,blocks
00:15:43 2 from user_tables where table_name in ('A','LT1','LT2');

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
A 718160 10079
LT1 9 4
LT2 9 4

00:15:45 session_147> select index_name,distinct_keys,num_rows,clustering_factor
00:15:57 2 from user_indexes where table_name in ('A','LT1','LT2');

INDEX_NAME DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------- ---------- -----------------
A_I1 44885 718160 718160
A_I2 3678 59424 58930

00:15:59 session_147> alter session set events '10046 trace name context forever, level 12';

And tkprof said …

>>>>>>>
TKPROF: Release 10.1.0.2.0 - Production on Fri Jun 2 00:21:28 2006
…
SELECT
A0 . object_name
, A0 . object_id
, A0 . owner
, A0 . data_object_id
FROM
a A0
WHERE
A0 . object_id IN
(
SELECT
*
FROM lt1 wtot1
)
OR
A0 . data_object_id IN
(
SELECT
*
FROM lt2 wtot2
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 10.76 15.11 9929 2250690 0 240
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 10.76 15.11 9929 2250690 0 240

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
240 FILTER (cr=2250690 pr=9929 pw=0 time=884255 us)
718160 TABLE ACCESS FULL A (cr=9966 pr=9929 pw=0 time=2178045 us)
114 TABLE ACCESS FULL LT1 (cr=2108400 pr=0 pw=0 time=7377763 us)
81 TABLE ACCESS FULL LT2 (cr=132324 pr=0 pw=0 time=461340 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 17 0.00 0.00
db file scattered read 641 0.03 4.43
SQL*Net message from client 17 19.48 19.50
db file sequential read 1 0.00 0.00
********************************************************************************

SELECT
A0 . object_name
, A0 . object_id
, A0 . owner
, A0 . data_object_id
FROM
a A0 left join (select distinct object_id lt1_object_id from lt1) lt1 on
(a0.object_id = lt1_object_id)
left join (select distinct data_object_id lt2_data_object_id from lt2) lt2
on (a0.data_object_id = lt2_data_object_id)
where lt1_object_id is not null
or lt2_data_object_id is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 1.09 5.26 9929 9968 0 240
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 1.12 5.28 9929 9968 0 240

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
240 FILTER (cr=9968 pr=9929 pw=0 time=75929 us)
718160 HASH JOIN RIGHT OUTER (cr=9968 pr=9929 pw=0 time=10076167 us)
9 VIEW (cr=3 pr=0 pw=0 time=233 us)
9 SORT UNIQUE (cr=3 pr=0 pw=0 time=202 us)
9 TABLE ACCESS FULL LT1 (cr=3 pr=0 pw=0 time=147 us)
718160 HASH JOIN RIGHT OUTER (cr=9965 pr=9929 pw=0 time=5766251 us)
6 VIEW (cr=3 pr=0 pw=0 time=178 us)
6 SORT UNIQUE (cr=3 pr=0 pw=0 time=156 us)
9 TABLE ACCESS FULL LT2 (cr=3 pr=0 pw=0 time=107 us)
718160 TABLE ACCESS FULL A (cr=9962 pr=9929 pw=0 time=1456654 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 17 0.00 0.00
db file scattered read 641 0.03 4.38
SQL*Net message from client 17 9.53 9.55
********************************************************************************

with x as (select a.rowid rid, a.* from a where object_id in (select object_id from lt1))
,y as (select a.rowid rid, a.* from a where data_object_id in (select data_object_id from lt2))
,u as (select * from x union select * from y)
select object_name,object_id,owner,data_object_id
from u

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 0.03 2.35 222 325 0 240
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.06 2.37 222 325 0 240

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65

Rows Row Source Operation
------- ---------------------------------------------------
240 VIEW (cr=325 pr=222 pw=0 time=2357892 us)
240 SORT UNIQUE (cr=325 pr=222 pw=0 time=2357163 us)
288 UNION-ALL (cr=325 pr=222 pw=0 time=1255814 us)
144 TABLE ACCESS BY INDEX ROWID OBJ#(68664) (cr=167 pr=130 pw=0 time=935674 us)
154 NESTED LOOPS (cr=23 pr=0 pw=0 time=9192 us)
9 TABLE ACCESS FULL OBJ#(68667) (cr=3 pr=0 pw=0 time=171 us)
144 INDEX RANGE SCAN OBJ#(68665) (cr=20 pr=0 pw=0 time=364 us)(object id 68665)
144 TABLE ACCESS BY INDEX ROWID OBJ#(68664) (cr=158 pr=92 pw=0 time=777543 us)
154 NESTED LOOPS (cr=14 pr=0 pw=0 time=7050 us)
9 TABLE ACCESS FULL OBJ#(68668) (cr=3 pr=0 pw=0 time=150 us)
144 INDEX RANGE SCAN OBJ#(68666) (cr=11 pr=0 pw=0 time=294 us)(object id 68666)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 17 0.00 0.00
db file sequential read 222 0.02 2.34
SQL*Net message from client 17 2.86 2.87

<<<<<<<

Thanks.

Tom,

Can you please, elaborate a little further, why do you think it is not an OR expansion? I got what you meant by "not equivalent", however I don't have nulls in object_id by definition.

I thought that construct:

select ...
from a
where a.c1 = :1 or a.c2 = :2

is equivalent to (exagerated for simplicity)

select ...
from a
where a.c1 in (select :1 from dual)
or a.c2 in (select :2 from dual)

and both can be OR-expanded.

Let's just take your first reply to this thread as an example:

<quote>
or expansion is the use of OR's to process something instead of another method.
for example (P and Q are predicates):

select * from t
where P or Q

can be "or expanded" to something like

select * from t where P
union all
select * from t where Q and NOT(P)

that is or expansion.
</quote>

In my case P is:
A0 . object_id IN
(
SELECT
*
FROM lt1 wtot1
)

and Q is:
A0 . data_object_id IN
(
SELECT
*
FROM lt2 wtot2
);

If we put these into your second query and adjust it to handle null values, we get:

select * from A0 where
A0 . object_id IN
(
SELECT
*
FROM lt1 wtot1
)
union all
select * from A0
where A0 . data_object_id IN
(
SELECT
*
FROM lt2 wtot2
)
and (A0 . object_id NOT IN
(
SELECT
*
FROM lt1 wtot1
WHERE object_id is not null
)
or A0 . object_id IS NULL)

Just to confirm. Should I search further for CBO features that will allow such rewrite, or should I rewrite the query in the sources?

This is sort-kinda related in a way (it looks very much like an or-expansion issue), but I was wondering if you had any insight on this...

I have a development database that runs this query with no problems:

SQL> select db2_product_id
  2        from product
  3        where catsearch(description_long, :gSearchText, null) > 0
  4           or catsearch(description_short, :gSearchText, null) > 0;

----------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                     |  3240 |   234K|   204 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | PRODUCT             |  3240 |   234K|   204 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                     |       |       |       |
|   3 |    BITMAP OR                     |                     |       |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                     |       |       |       |
|   5 |      SORT ORDER BY               |                     |       |       |       |
|*  6 |       DOMAIN INDEX               | IX_PRODUCT_DESCLONG |       |       |     1 |
|   7 |     BITMAP CONVERSION FROM ROWIDS|                     |       |       |       |
|   8 |      SORT ORDER BY               |                     |       |       |       |
|*  9 |       DOMAIN INDEX               | IX_PRODUCT_DESCSHRT |       |       |     1 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("CTXSYS"."CATSEARCH"("DESCRIPTION_LONG",:GSEARCHTEXT,NULL)>0 AND
              "CTXSYS"."CATSEARCH"("DESCRIPTION_SHORT",:GSEARCHTEXT,NULL)>0)
   9 - access("CTXSYS"."CATSEARCH"("DESCRIPTION_SHORT",:GSEARCHTEXT,NULL)>0)



My QA database does this with the same query:

SQL> select db2_product_id
  2        from product
  3        where catsearch(description_long, :gSearchText, null) > 0
  4           or catsearch(description_short, :gSearchText, null) > 0;
select db2_product_id
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation

SQL> explain plan for select db2_product_id
  2        from product
  3        where catsearch(description_long, :gSearchText, null) > 0
  4           or catsearch(description_short, :gSearchText, null) > 0;

Explained.

SQL> select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  3240 |   240K|   178  (21)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| PRODUCT |  3240 |   240K|   178  (21)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CTXSYS"."CATSEARCH"("DESCRIPTION_LONG",:GSEARCHTEXT,NULL)
              >0 OR "CTXSYS"."CATSEARCH"("DESCRIPTION_SHORT",:GSEARCHTEXT,NULL)>0)


Now, I realize that the optimizer is making the execution different based on the statistics it has. However, in both databases the data is the same but organized differently on disk. I can work around the problem by using a different query:

select db2_product_id 
  from product 
  where catsearch(description_long, :gSearchText, null) > 0 
union 
select db2_product_id 
  from product 
  where catsearch(description_short, :gSearchText, null) > 0;

but I wondered if I could force Oracle to use the domain indexes using the first query?
 

I just realized is that my dev environment is Enterprise, my QA is Standard Edition. I don't know if that would be the cause, since I can still use the domain indexes using the second query.

I reviewed the init parameters, and they are not appreciably different; other than the name of the DB, and memory parameters (my QA machine has 512MB and Dev (my laptop) has 2GB).

The physical structure of the table on disk is that on Dev, I modified the table structure (removed a BLOB and VARCHAR2 column) and did not perform a reorg. On QA, I modified the table structure and *did* reorg (alter table move...; rebuilt indexes)

So, I went back and did a reorg on Dev the same way I did for QA, and Dev still works with the old query. Again, both databases have the same data in the table.

I don't want to waste much of your time, as I have a workaround using the second query - it's just not as clean that way...

Tom can you explain what is bitmap conversion to rowids.
I was trying your example and got this plan after analyzing the table t. I have 10.1.0.2.

analyze table t compute statistics
for table
for all indexes
for all indexed columns;

explain plan for
select *
from t
where x = 5 or y = 10

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2024622942

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 39 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | T_IDX1 | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 8 | INDEX RANGE SCAN | T_IDX2 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("X"=5)
filter("X"=5)
8 - access("Y"=10)

22 rows selected.

Some of our SQLs (with ORs and INs) which otherwise take a long time while parsing, works fine (with optimal plan so far) after giving the no_expand hint.

Do we have any other way to force this no_expansion ,at the session level, other than using hints for individual SQLs - like some events or something like that ?

thanks
Anto

Hi Tom,

I need to force BITMAP TO ROWID plan for the following query:

SELECT COUNT(Activity_PK)
FROM   (SELECT Act.Activity_PK
        FROM   TBL_TMX_Activity Act
        INNER  JOIN TBL_SEC_UserVActCacheId VA__TBL ON (VA__TBL.ObjectId   = Act.Activity_PK AND
                                                        VA__TBL.EmpId      = -1              AND
                                                        VA__TBL.ObjectType = 1
                                                       )
        WHERE  ((contains(Act.ActivityName, :vc_Keywords_cleaned) > 0) OR
                (contains(Act.ActivityDesc, :vc_Keywords_cleaned) > 0) OR
                (contains(Act.Code,         :vc_Keywords_cleaned) > 0)
               )
        OR  Act.Activity_PK IN (SELECT ActivityFK
                                FROM   Tbl_LMS_SkillSoftWSResults
                                WHERE  SearchID = :i_SearchID
                               )
       )

Current Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 bytes=273)
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       NESTED LOOPS (Cost=3 Card=1 Bytes=273)
   4    3         INDEX (RANGE SCAN) OF 'PK_TBL_SEC_USERVACTCACHEID' (
          UNIQUE) (Cost=2 Card=1 Bytes=13)
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'TBL_TMX_ACTIVITY'
          (Cost=1 Card=1 Bytes=260)
   6    5           INDEX (UNIQUE SCAN) OF 'PK_TBL_TMX_ACTIVITY' (UNIQUE)
   7    2       TABLE ACCESS (FULL) OF 'TBL_LMS_SKILLSOFTWSRESULTS' (Cost=2 Card=1 Bytes=26)

Approximate Desired Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=299)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=299)
   3    2       NESTED LOOPS (Cost=4 Card=1 Bytes=273)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TBL_TMX_ACTIVITY' (Cost=3 Card=1 Bytes=260)
   5    4           BITMAP CONVERSION (TO ROWIDS)
   6    5             BITMAP OR
   7    6               BITMAP CONVERSION (FROM ROWIDS)
   8    7                 SORT (ORDER BY)
   9    8                   DOMAIN INDEX OF 'FT_TABLE_JY88JR12' (Cost=0)
  10    6               BITMAP CONVERSION (FROM ROWIDS)
  11   10                 SORT (ORDER BY)
  12   11                   DOMAIN INDEX OF 'FT_TABLE_JY88JR15' (Cost=0)
  13    6               BITMAP CONVERSION (FROM ROWIDS)
  14   13                 SORT (ORDER BY)
  15   14                   DOMAIN INDEX OF 'FT_TABLE_JY88JR14' (Cost=0)
  16    3         INDEX (UNIQUE SCAN) OF 'PK_TBL_SEC_USERVACTCACHEID'(UNIQUE)
  17    2       TABLE ACCESS (FULL) OF 'TBL_LMS_SKILLSOFTWSRESULTS' (Cost=2 Card=1 Bytes=26)

Otherwise I have to change the third OR condition to UNION, which is a lot of code to change. I tried all possible hints and their combinations I could think of and was not able to achieve the success.

SELECT COUNT(Activity_PK) AS MaxRecords
FROM   (SELECT Act.Activity_PK
        FROM   TBL_TMX_Activity Act
        INNER  JOIN TBL_SEC_UserVActCacheId VA__TBL ON (VA__TBL.ObjectId = Act.Activity_PK AND
                                                       VA__TBL.EmpId = -1 AND
                                                       VA__TBL.ObjectType = 1)
        WHERE  ((contains(Act.ActivityName, :vc_Keywords_cleaned) > 0) OR
               (contains(Act.ActivityDesc, :vc_Keywords_cleaned) > 0) OR
               (contains(Act.Code, :vc_Keywords_cleaned) > 0))
        UNION
        SELECT Act.Activity_PK
        FROM   TBL_TMX_Activity Act
        INNER  JOIN TBL_SEC_UserVActCacheId VA__TBL ON (VA__TBL.ObjectId = Act.Activity_PK AND
                                                       VA__TBL.EmpId = -1 AND
                                                       VA__TBL.ObjectType = 1)
        WHERE  Act.Activity_PK IN (SELECT ActivityFK FROM Tbl_LMS_SkillSoftWSResults WHERE SearchID = :i_SearchID))

UNION Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=14 Card=2 Bytes=26)
   3    2       SORT (UNIQUE) (Cost=14 Card=2 Bytes=317)
   4    3         UNION-ALL
   5    4           NESTED LOOPS (Cost=4 Card=1 Bytes=273)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'TBL_TMX_ACTIVITY' (Cost=3 Card=1 Bytes=260)
   7    6               BITMAP CONVERSION (TO ROWIDS)
   8    7                 BITMAP OR
   9    8                   BITMAP CONVERSION (FROM ROWIDS)
  10    9                     SORT (ORDER BY)
  11   10                       DOMAIN INDEX OF 'FT_TABLE_JY88JR12' (Cost=0)
  12    8                   BITMAP CONVERSION (FROM ROWIDS)
  13   12                     SORT (ORDER BY)
  14   13                       DOMAIN INDEX OF 'FT_TABLE_JY88JR15' (Cost=0)
  15    8                   BITMAP CONVERSION (FROM ROWIDS)
  16   15                     SORT (ORDER BY)
  17   16                       DOMAIN INDEX OF 'FT_TABLE_JY88JR14' (Cost=0)
  18    5             INDEX (UNIQUE SCAN) OF 'PK_TBL_SEC_USERVACTCACHEID' (UNIQUE)
  19    4           NESTED LOOPS (Cost=2 Card=1 Bytes=44)
  20   19             NESTED LOOPS (Cost=2 Card=1 Bytes=31)
  21   20               TABLE ACCESS (FULL) OF 'TBL_LMS_SKILLSOFTWSRESULTS' (Cost=2 Card=1 Bytes=26)
  22   20               INDEX (UNIQUE SCAN) OF 'PK_TBL_TMX_ACTIVITY' (UNIQUE)
  23   19             INDEX (UNIQUE SCAN) OF 'PK_TBL_SEC_USERVACTCACHEID' (UNIQUE)

Thanks

Any help on above problem ?

Dear Tom,

My friend is experiencing and unwanted "OR Expansion" in a pagination query using composite FBIs based on NLSSORT() functions.

The problem can be fixed with the NO_EXPAND hint, however we would like to know how is it possible for the CBO to miscalculate the cost of the query soooooo much, that it wrongly prefers the "OR expansion" which takes 100x longer to execute.

Without the "OR Expansion" the query is using the composite FBI based on NLSSORT() just like it is supposed to, and it is very quick, using one index range scan.

...HOWEVER with "OR Expansion" the query wrongly uses some other non-FBI, that has very little in common with the sort order of the query. Consequently the query runs 100x longer, using two fast full index scans on the wrong index.

Note: The CBO does not make the mistake of choosing the "OR Expansion" soon after the FBI index is dropped and recreated. It happens only after a day of so after the database sits idle.

The data and indexes are analyzed and the data does NOT change between the different queries. Nobody else uses the database.
Also the following is set: QUERY_REWRITE_ENABLED=TRUE, QUERY_REWRITE_INTEGRITY=TRUSTED

How can the CBO be so wrong about the OR Expansion?


Regards,
George

P.S.
Below is a test case, (except for the huge data table because it contains many german characters).

SQL> CREATE INDEX ORAUSER.IDX_ENAM_FAMI_ID_GEB ON ORAUSER.PATIENT(ENAM, FAMILIEN, ID, GEB);

SQL> CREATE UNIQUE INDEX ENAM_FAMI_IDDE ON PATIENT (NLSSORT("ENAM",'nls_sort=''GERMAN'''), NLSSORT("FAMILIEN",'nls_sort=''GERMAN'''), ID);


SQL> SELECT
  2      ENAM,FAMILIEN,GEBURT, i
  3  FROM    (  
  4            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS NO_EXPAND*/
  5               row_number() OVER (PARTITION BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
  6               ENAM,
  7               FAMILIEN,
  8               GEBURT
  9            FROM PATIENT
 10            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND (NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
 11            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
 12           ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR  NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

ENAM                                               FAMILIEN                                                                         GEBURT             I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               BANANA                                                                           05-MAY-69          3
ANNA                                               BANANA                                                                           06-JUN-69          4
ANNA                                               BANANA                                                                           08-AUG-69          5
ANNA                                               Banger                                                                           05-JAN-72          1
ANNA                                               Banger                                                                           21-SEP-81          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1193522455

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     5 |   455 |   385K  (1)| 01:17:12 |
|*  1 |  COUNT STOPKEY                 |                |       |       |            |          |
|*  2 |   VIEW                         |                |   382K|    33M|   385K  (1)| 01:17:12 |
|   3 |    WINDOW NOSORT               |                |   382K|    11M|   385K  (1)| 01:17:12 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PATIENT        |   382K|    11M|   382K  (1)| 01:16:31 |
|*  5 |      INDEX RANGE SCAN          | ENAM_FAMI_IDDE |   382K|       |  2763   (2)| 00:00:34 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('1455551400010
              1010100')  OR NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>HEXTORAW('1A145514551400010101010
              10100') )
   5 - access(NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100')
              AND NLSSORT("ENAM",'nls_sort=''GERMAN''') IS NOT NULL)
       filter(NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>=HEXTORAW('1A14551455140001010101
              010100')  OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514000101010100') )


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed



SQL> 
SQL> 
SQL> 
SQL> SELECT
  2      ENAM,FAMILIEN,GEBURT, i
  3  FROM    (  
  4            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
  6               ENAM,
  7               FAMILIEN,
  8               GEBURT
  9            FROM PATIENT
 10            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND (NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
 11            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
 12           ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR  NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

ENAM                                               FAMILIEN                                                                         GEBURT             I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               BANANA                                                                           05-MAY-69          3
ANNA                                               BANANA                                                                           06-JUN-69          4
ANNA                                               BANANA                                                                           08-AUG-69          5
ANNA                                               Banger                                                                           05-JAN-72          1
ANNA                                               Banger                                                                           21-SEP-81          2


Execution Plan
----------------------------------------------------------
Plan hash value: 54679478

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     5 |   455 |  8667   (3)| 00:01:45 |
|*  1 |  COUNT STOPKEY      |                      |       |       |            |          |
|*  2 |   VIEW              |                      |   416K|    36M|  8667   (3)| 00:01:45 |
|   3 |    WINDOW SORT      |                      |   416K|    12M|  8667   (3)| 00:01:45 |
|   4 |     CONCATENATION   |                      |       |       |            |          |
|*  5 |      INDEX FULL SCAN| IDX_ENAM_FAMI_ID_GEB |   366K|    11M|  2429   (3)| 00:00:30 |
|*  6 |      INDEX FULL SCAN| IDX_ENAM_FAMI_ID_GEB | 49362 |  1542K|  2416   (2)| 00:00:29 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514
              000101010100')  OR NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>HEXTORAW('1A14551455140
              001010101010100') )
   5 - filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514000101010100')
                AND NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100') )
   6 - filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100'
              )  AND NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>=HEXTORAW('1A1455145514000101010101
              0100')  AND LNNVL(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('145555140001010101
              00') ))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4740  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

Just tested.
The plan instability occurs also without the bind variables. (with hardcoded strings).

Also, it still remains a mystery why the CBO chooses to do "OR Expansion" when it is so much less expensive not to do so.


Regards,
George

After some more experimenting we noticed that the unwanted "OR Expansion" does not happen right after the FBI is created, HOWEVER it starts happening immediately after the underlying table is analyzed, with:

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'PATIENT');

Curiously, analyzing only the FBI after its creation with the DBMS_STATS package (see below), does NOT cause the CBO to do unwanted "OR Expansion"

exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'ENAM_FAMI_IDDE');

The table must get analyzed after some idle time,and that's why the problem appears spontaneously after some time.

Is there a way to peek what the CBO is thinking ?


Regards,
George

Tom,

We found that analyzing the table provokes the undesirable plan change immediately, however this does not mean that this change does not happen by itself after a day or so (indeed it does happen even without analyzing the table explicitly).

Analyzing the table is just a quick way to bring on the problem without waiting a day.

I'll send you a test case in the next post.

SQL> alter session set nls_sort=GERMAN;
Session altered.

SQL> alter session set nls_comp=ANSI;
Session altered.

SQL> alter session set QUERY_REWRITE_ENABLED=TRUE;
Session altered.

SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; 
Session altered.

SQL> select d.parameter Dparameter,
  2         d.value     DatabaseVal,
  3         i.value     InstanceVal,
  4         s.value     SessionVal
  5    from nls_database_parameters d, nls_instance_parameters i,
  6         nls_session_parameters s
  7   where d.parameter = i.parameter (+)
  8     and d.parameter = s.parameter (+)
  9  order by DParameter;

DPARAMETER                     DATABASEVAL                              INSTANCEVAL                              SESSIONVAL
------------------------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
NLS_CALENDAR                   GREGORIAN                                                                         GREGORIAN
NLS_CHARACTERSET               EE8ISO8859P1
NLS_COMP                       BINARY                                                                            ANSI
NLS_CURRENCY                   $                                                                                 $
NLS_DATE_FORMAT                DD-MON-RR                                                                         DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN                                                                          AMERICAN
NLS_DUAL_CURRENCY              $                                                                                 $
NLS_ISO_CURRENCY               AMERICA                                                                           AMERICA
NLS_LANGUAGE                   AMERICAN                                 GERMAN                                   AMERICAN
NLS_LENGTH_SEMANTICS           BYTE                                     BYTE                                     BYTE
NLS_NCHAR_CHARACTERSET         UTF8
NLS_NCHAR_CONV_EXCP            FALSE                                    FALSE                                    FALSE
NLS_NUMERIC_CHARACTERS         .,                                                                                .,
NLS_RDBMS_VERSION              10.2.0.3.0
NLS_SORT                       BINARY                                                                            GERMAN
NLS_TERRITORY                  AMERICA                                  GERMANY                                  AMERICA
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                                    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                                          DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                                      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                                                HH.MI.SSXFF AM TZR

20 rows selected.



SQL> CREATE UNIQUE INDEX IDX_ENAM_FAMI_ID_GEB ON PATIENT (ENAM, FAMILIEN, ID, GEBURT);
Index created.

SQL> CREATE UNIQUE INDEX ENAM_FAMI_IDDE ON PATIENT (NLSSORT("ENAM",'nls_sort=''GERMAN'''), NLSSORT("FAMILIEN",'nls_sort=''GERMAN'''), ID);
Index created.


SQL> set autotrace on;


SQL> SELECT
  2      ENAM,FAMILIEN,GEBURT, i
  3  FROM    (  
  4            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
  6               ENAM,
  7               FAMILIEN,
  8               GEBURT
  9            FROM PATIENT
 10            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND (NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
 11            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
 12           ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR  NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

ENAM                                               FAMILIEN                                                                         GEBURT             I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               BANANA                                                                           05-MAY-69          3
ANNA                                               BANANA                                                                           06-JUN-69          4
ANNA                                               BANANA                                                                           08-AUG-69          5
ANNA                                               Banger                                                                           03-APR-81          1
ANNA                                               Banger                                                                           30-DEC-99          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1193522455

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     5 |   455 |   401   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY                 |                |       |       |            |          |
|*  2 |   VIEW                         |                |  2050 |   182K|   401   (1)| 00:00:05 |
|   3 |    WINDOW NOSORT               |                |  2050 | 65600 |   401   (1)| 00:00:05 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PATIENT        |  2050 | 65600 |   400   (1)| 00:00:05 |
|*  5 |      INDEX RANGE SCAN          | ENAM_FAMI_IDDE |   369 |       |    31   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('1455551400010
              1010100')  OR NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>HEXTORAW('1A145514551400010101010
              10100') )
   5 - access(NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100')
              AND NLSSORT("ENAM",'nls_sort=''GERMAN''') IS NOT NULL)
       filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514000101010100')  OR
              NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>=HEXTORAW('1A14551455140001010101010100') )


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


/* Analyzing the index does not change anything */
SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'ENAM_FAMI_IDDE');
PL/SQL procedure successfully completed.


SQL> SELECT
  2      ENAM,FAMILIEN,GEBURT, i
  3  FROM    (  
  4            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
  6               ENAM,
  7               FAMILIEN,
  8               GEBURT
  9            FROM PATIENT
 10            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND (NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
 11            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
 12           ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR  NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

ENAM                                               FAMILIEN                                                                         GEBURT             I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               BANANA                                                                           05-MAY-69          3
ANNA                                               BANANA                                                                           06-JUN-69          4
ANNA                                               BANANA                                                                           08-AUG-69          5
ANNA                                               Banger                                                                           03-APR-81          1
ANNA                                               Banger                                                                           30-DEC-99          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1193522455

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     5 |   455 |   401   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY                 |                |       |       |            |          |
|*  2 |   VIEW                         |                |  2050 |   182K|   401   (1)| 00:00:05 |
|   3 |    WINDOW NOSORT               |                |  2050 | 65600 |   401   (1)| 00:00:05 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PATIENT        |  2050 | 65600 |   400   (1)| 00:00:05 |
|*  5 |      INDEX RANGE SCAN          | ENAM_FAMI_IDDE |   369 |       |    31   (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('1455551400010
              1010100')  OR NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>HEXTORAW('1A145514551400010101010
              10100') )
   5 - access(NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100')
              AND NLSSORT("ENAM",'nls_sort=''GERMAN''') IS NOT NULL)
       filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514000101010100')  OR
              NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>=HEXTORAW('1A14551455140001010101010100') )


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


/*  We can wait a day or analyze the table below to get the undesirable CBO bahvior immediately */
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'PATIENT');
PL/SQL procedure successfully completed.


SQL> SELECT
  2      ENAM,FAMILIEN,GEBURT, i
  3  FROM    (  
  4            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS */
  5               row_number() OVER (PARTITION BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
  6               ENAM,
  7               FAMILIEN,
  8               GEBURT
  9            FROM PATIENT
 10            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND (NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
 11            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
 12           ) a
 13  WHERE rownum<=5 AND (a.I>=3 OR  NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

ENAM                                               FAMILIEN                                                                         GEBURT             I
-------------------------------------------------- -------------------------------------------------------------------------------- --------- ----------
ANNA                                               BANANA                                                                           05-MAY-69          3
ANNA                                               BANANA                                                                           06-JUN-69          4
ANNA                                               BANANA                                                                           08-AUG-69          5
ANNA                                               Banger                                                                           03-APR-81          1
ANNA                                               Banger                                                                           30-DEC-99          2


Execution Plan
----------------------------------------------------------
Plan hash value: 54679478

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |     5 |   455 |  8663   (3)| 00:01:44 |
|*  1 |  COUNT STOPKEY      |                      |       |       |            |          |
|*  2 |   VIEW              |                      |   413K|    35M|  8663   (3)| 00:01:44 |
|   3 |    WINDOW SORT      |                      |   413K|    12M|  8663   (3)| 00:01:44 |
|   4 |     CONCATENATION   |                      |       |       |            |          |
|*  5 |      INDEX FULL SCAN| IDX_ENAM_FAMI_ID_GEB |   363K|    11M|  2429   (3)| 00:00:30 |
|*  6 |      INDEX FULL SCAN| IDX_ENAM_FAMI_ID_GEB | 50488 |  1577K|  2416   (2)| 00:00:29 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   2 - filter("A"."I">=3 OR NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514
              000101010100')  OR NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>HEXTORAW('1A14551455140
              001010101010100') )
   5 - filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('14555514000101010100')
                AND NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100') )
   6 - filter(NLSSORT("ENAM",'nls_sort=''GERMAN''')>=HEXTORAW('14555514000101010100'
              )  AND NLSSORT("FAMILIEN",'nls_sort=''GERMAN''')>=HEXTORAW('1A1455145514000101010101
              0100')  AND LNNVL(NLSSORT("ENAM",'nls_sort=''GERMAN''')>HEXTORAW('145555140001010101
              00') ))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
          
         

This is a specific scenario heavily used by the Business users of a telco operator to search for CDRs having calls from numbers belonging to multiple Hotlists. The query gets formed by the application with an "OR clause" and a "IN sub query" .The Oracle access path taken is a Full table scan.The columns on which the queries are getting fired are indexed.The search criteria without combining using OR then the behavior is for a Index scan.When the combination of the search criteria is using OR then the full table scan is the result.This particular issue is not dependent on the Load in the tables and as we understand from the Oracle Support interactions this is a basic flow related behavior decided by the Optimizer.

"dev closed bug as not a bug with this explanation:
...
When a query has ORed predicates, it can only be applied at table level if OR expansion is chosen. In this query, the OR expansion cannot be chosen as the second OR branch (IN subquery) cannot use index. This is due to heuristics.
Hence the ORed predicate cannot be broken up into OR expansion and are applied as a post filter on top of FTS on BIG table.This is expected behavior.

There is an enhancement request that has gone in from the developement.

Please suggest any workaround till we get this as an enhancement.

Tom,

The test on the same is provided below

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table big( id number, name varchar2(100));

Table created.

SQL> insert into big(id, name) select rownum, object_name from all_objects;
47176 rows created.

SQL> commit;
Commit complete.

SQL> create table small as select id from big where rownum < 10;
Table created.

SQL> set autot traceonly explain
SQL> create index big_index on big(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(null, 'big');
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(null, 'small');
PL/SQL procedure successfully completed.
 

SQL> select * from big where id in ( select id from small);

Execution Plan

----------------------------------------------------------

Plan hash value: 1778027926

 

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |     9 |   306 |    14   (8)| 00:00:01 |

|   1 |  NESTED LOOPS                |           |       |       |            |          |

|   2 |   NESTED LOOPS               |           |     9 |   306 |    14   (8)| 00:00:01 |

|   3 |    SORT UNIQUE               |           |     9 |    27 |     3   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL        | SMALL     |     9 |    27 |     3   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | BIG_INDEX |     1 |       |     1   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID| BIG       |     1 |    31 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ID"="ID")

 


SQL>  select * from big where id = 45;

Execution Plan

----------------------------------------------------------

Plan hash value: 1588331918

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |    31 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BIG       |     1 |    31 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | BIG_INDEX |     1 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
 

SQL>  select * from big where id = 45 or id in ( select id from small);

Execution Plan
----------------------------------------------------------

Plan hash value: 2082761722

----------------------------------------------------------------------------

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |  2360 | 73160 |    48   (3)| 00:00:01 |

|*  1 |  FILTER            |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL| BIG   | 47176 |  1428K|    48   (3)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| SMALL |     1 |     3 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=45 OR  EXISTS (SELECT 0 FROM "SMALL" "SMALL" WHERE

              "ID"=:B1))

   3 - filter("ID"=:B1)

Hi Tom,

I tried to test the OR-expansion, but sometimes got 'BITMAP OR'.

So what's the magic logic behind indicating ORACLE to choose one rather than another.

Per my below testing, seems equal condition will use 'BITMAP OR', correct?

Also even with 'no_expand', it got 'BITMAP OR' rather than 'full table scan', why not same as your example?

BTW, i was told by some senior colleague that 'modify in/or query to union all to utilize index, otherwise will be FTS'.
I think this is same as what you are talking about here - OR EXPANSION.
Per your comments here, oracle is wise/smart enough, so no need for us to follow senior colleague's suggestion, right?

create table test1 (id int, a varchar2(10), b date, c number);
create index ind1 on test1(a);
create index ind2 on test1(b);
create index ind3 on test1(c);
exec dbms_stats.set_table_Stats(ownname=>user, tabname=>'TEST1',numrows=>100000,numblks=>100000);
select * from test1 where a='11' or b=sysdate or c=10;
-- BITMAP OR
select * from test1 where a='11' or b=sysdate or c>=10;
-- CONCATENATION
select /*+ use_concat */ * from test1 where a='11' or b=sysdate or c=10;
-- CONCATENATION
select /*+ no_expand */ * from test1 where a='11' or b=sysdate or c=10;
-- BITMAP OR

I did not find this explanation here so may be it would be interesting for others.
Some notes regarding cost-based vs heuristic nature of OR-expansion:
https://sqlmdx.wordpress.com/2013/12/08/query-transformations-in-oracle-or-expansion/

Step 1:
Original query: 

Original query: (has one full table scan)

Select 
BAKR_ACCRRTT.ACCT_TYPE_ID,BAKR_ACCRRTT.ROUTING_NUMBER,BAKR_ACCRRTT.BANK_TRANSFER_CODE_ID,BAKR_ACCRRTT.ACCOUNT_CLASSIFICATION_ID,BAKR_ACCRRTT.FULL_ACCOUNT_NUMBER,BAKR_ACCRRTT.ITTE_ACCRRTT_ID,BAKR_ACCRRTT.PAYMENT_ACCOUNT_NUMBER,BAKR_ACCRRTT.UNMASKED_ACCOUNT_NUMBER 
from 
BAKR_ACCRRTT LEFT JOIN ITTE_ACCRRTT ON ITTE_ACCRRTT.ITTE_ACCRRTT_ID =BAKR_ACCRRTT.ITTE_ACCRRTT_ID LEFT JOIN ITEMACCT_CACHEITEM_MAP ON ITEMACCT_CACHEITEM_MAP.ITTE_ACCRRTT_ID = ITTE_ACCRRTT.ITTE_ACCRRTT_ID
WHERE ITTE_ACCRRTT.item_account_ID = 201223928  AND 
IS_DELETED = 0 AND 
ITTE_ACCRRTT_STATUS_ID IN (1,5,6) or 
ITEMACCT_CACHEITEM_MAP.CACHE_ITEM_ID = 201223928;

Execution Plan
----------------------------------------------------------
Plan hash value: 3670180359

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |  4673 |   328K|   171G  (3)|999:59:59 |
|*  1 |  FILTER                        |                          |       |       |            |          |
|   2 |   NESTED LOOPS OUTER           |                          |  4673 |   328K|   171G  (3)|999:59:59 |
|   3 |    NESTED LOOPS OUTER          |                          |   163M|  9052M|   513M  (2)| 94:04:27 |
|   4 |     TABLE ACCESS FULL          | BAKR_ACCRRTT             |   163M|  7491M|    18M  (5)| 03:20:54 |
|   5 |     TABLE ACCESS BY INDEX ROWID| ITTE_ACCRRTT             |     1 |    10 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | ITTE_ACCRRTT_PK          |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    INDEX FULL SCAN             | ITMCACMP_CACITMISACT_IDX |     1 |    14 |  1045   (3)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ITTE_ACCRRTT"."ITTE_ACCRRTT_ID"=201223928 AND "BAKR_ACCRRTT"."IS_DELETED"=0 AND
              ("ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=1 OR "ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=5 OR
              "ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=6) OR "ITEMACCT_CACHEITEM_MAP"."CACHE_ITEM_ID"=201223928)
   6 - access("ITTE_ACCRRTT"."ITTE_ACCRRTT_ID"(+)="BAKR_ACCRRTT"."ITTE_ACCRRTT_ID")
   7 - access("ITEMACCT_CACHEITEM_MAP"."ITTE_ACCRRTT_ID"(+)="ITTE_ACCRRTT"."ITTE_ACCRRTT_ID")
       filter("ITEMACCT_CACHEITEM_MAP"."ITTE_ACCRRTT_ID"(+)="ITTE_ACCRRTT"."ITTE_ACCRRTT_ID")

SQL> 


Step 2:
Intended Plan - same functionality - query modified with union all clause:
 
SQL> Select 
  2   BAKR_ACCRRTT.ACCT_TYPE_ID,BAKR_ACCRRTT.ROUTING_NUMBER,BAKR_ACCRRTT.BANK_TRANSFER_CODE_ID,BAKR_ACCRRTT.ACCOUNT_CLASSIFICATION_ID,BAKR_ACCRRTT.FULL_ACCOUNT_NUMBER,BAKR_ACCRRTT.ITTE_ACCRRTT_ID,BAKR_ACCRRTT.PAYMENT_ACCOUNT_NUMBER,BAKR_ACCRRTT.UNMASKED_ACCOUNT_NUMBER 
  3  from 
  4  BAKR_ACCRRTT LEFT JOIN ITTE_ACCRRTT ON ITTE_ACCRRTT.ITTE_ACCRRTT_ID =BAKR_ACCRRTT.ITTE_ACCRRTT_ID LEFT JOIN ITEMACCT_CACHEITEM_MAP ON ITEMACCT_CACHEITEM_MAP.ITTE_ACCRRTT_ID = ITTE_ACCRRTT.ITTE_ACCRRTT_ID
  5  WHERE 
  6  IS_DELETED = 0 AND 
  7  item_account.item_account_ID in(
  8  select 
  9   item_account_ID 
 10  from 
 11   item_account 
 12  where 
 13  ITTE_ACCRRTT.item_account_ID = 201223928  AND 
 14  ITTE_ACCRRTT_STATUS_ID IN (1,5,6) 
 15  union all
 16  select ITTE_ACCRRTT_ID from ITEMACCT_CACHEITEM_MAP where CACHE_ITEM_ID = 201223928
 17  );

Execution Plan
----------------------------------------------------------
Plan hash value: 4254299732

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     3 |   225 |  3008   (3)| 00:00:02 |
|   1 |  NESTED LOOPS OUTER               |                          |     3 |   225 |  3008   (3)| 00:00:02 |
|   2 |   NESTED LOOPS                    |                          |     3 |   204 |    23   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                          |     3 |    60 |    11   (0)| 00:00:01 |
|   4 |     VIEW                          | VW_NSO_1                 |     3 |    39 |     5   (0)| 00:00:01 |
|   5 |      HASH UNIQUE                  |                          |     3 |    38 |     5   (0)| 00:00:01 |
|   6 |       UNION-ALL                   |                          |       |       |            |          |
|*  7 |        TABLE ACCESS BY INDEX ROWID| ITTE_ACCRRTT             |     1 |    10 |     4   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | ITTE_ACCRRTT_PK          |     1 |       |     3   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN           | ITMCACMP_CACITMISACT_IDX |     2 |    28 |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN             | ITTE_ACCRRTT_PK          |     1 |     7 |     2   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | BAKR_ACCRRTT             |     1 |    48 |     4   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN              | BANKACC_ITEMACCID_IDX    |     1 |       |     3   (0)| 00:00:01 |
|* 13 |   INDEX FULL SCAN                 | ITMCACMP_MEMITM_IDX      |     1 |     7 |   995   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Step 3:
Original query - with concat hint - to force the plan with union all @ step 2 - does not work for below - please suggest what else needs to be incorporated here




SQL> Select /*+ USE_CONCAT */ 
  2   BAKR_ACCRRTT.ACCT_TYPE_ID,BAKR_ACCRRTT.ROUTING_NUMBER,BAKR_ACCRRTT.BANK_TRANSFER_CODE_ID,BAKR_ACCRRTT.ACCOUNT_CLASSIFICATION_ID,BAKR_ACCRRTT.FULL_ACCOUNT_NUMBER,BAKR_ACCRRTT.ITTE_ACCRRTT_ID,BAKR_ACCRRTT.PAYMENT_ACCOUNT_NUMBER,BAKR_ACCRRTT.UNMASKED_ACCOUNT_NUMBER 
  3  from 
  4  BAKR_ACCRRTT LEFT JOIN ITTE_ACCRRTT ON ITTE_ACCRRTT.ITTE_ACCRRTT_ID =BAKR_ACCRRTT.ITTE_ACCRRTT_ID LEFT JOIN ITEMACCT_CACHEITEM_MAP ON ITEMACCT_CACHEITEM_MAP.ITTE_ACCRRTT_ID = ITTE_ACCRRTT.ITTE_ACCRRTT_ID
  5  WHERE ITTE_ACCRRTT.item_account_ID = 201223928  AND 
  6  IS_DELETED = 0 AND 
  7  ITTE_ACCRRTT_STATUS_ID IN (1,5,6) OR 
  8  ITEMACCT_CACHEITEM_MAP.CACHE_ITEM_ID = 201223928;

Execution Plan
----------------------------------------------------------
Plan hash value: 1896440389

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |  4674 |   328K|       |    48M  (5)| 08:53:56 |
|   1 |  CONCATENATION            |                          |       |       |       |            |          |
|*  2 |   FILTER                  |                          |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER  |                          |  4673 |   328K|       |    24M  (5)| 04:27:02 |
|   4 |     INDEX FAST FULL SCAN  | ITMCACMP_CACITMISACT_IDX | 75227 |  1028K|       |   578   (4)| 00:00:01 |
|*  5 |     HASH JOIN RIGHT OUTER |                          |   163M|  9052M|  4040M|    24M  (5)| 04:26:46 |
|   6 |      TABLE ACCESS FULL    | ITTE_ACCRRTT             |   192M|  1836M|       |  5173K  (3)| 00:56:50 |
|   7 |      TABLE ACCESS FULL    | BAKR_ACCRRTT             |   163M|  7491M|       |    18M  (5)| 03:20:54 |
|*  8 |   FILTER                  |                          |       |       |       |            |          |
|*  9 |    HASH JOIN OUTER        |                          |     1 |    72 |       |    24M  (5)| 04:26:54 |
|* 10 |     FILTER                |                          |       |       |       |            |          |
|* 11 |      HASH JOIN RIGHT OUTER|                          |     1 |    58 |  4040M|    24M  (5)| 04:26:54 |
|  12 |       TABLE ACCESS FULL   | ITTE_ACCRRTT             |   192M|  1836M|       |  5173K  (3)| 00:56:50 |
|* 13 |       TABLE ACCESS FULL   | BAKR_ACCRRTT             |   163M|  7490M|       |    18M  (5)| 03:21:02 |
|  14 |     INDEX FAST FULL SCAN  | ITMCACMP_CACITMISACT_IDX | 75227 |  1028K|       |   578   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

I want to modify the original query with hints to get the same optimum plan that I got with the union all clause(step2)

Please can you suggest some high level inputs here 

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select * from dbms_xplan.display_cursor(format=>'+OUTLINE');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  ggqns3c1jz86c, child number 0
-------------------------------------
select * from scott.emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

Hi Tom and team,

Any inputs/example on the above subject would be very helpful.
We have a bad query and a good query that give the same output - one with the "or" clause and the other with "union all" clause.
How can we make the one with the "or" clause get to the same plan as with the "union all" clause.
Use of use_concat does not help here.
We tried to use the "coe_xfr_profile" to copy the plans from the latter to the former - did not work.
Just curious if you can help us here.
If you need further inputs from my end, please let me know.

Regards,
K.Diwakar

Something like this? if so then optimizer team have gone about this in detail at https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion

drop table t purge;
create table t 
as 
select *
from all_objects;

create unique index t_idx on t(object_id);

variable b1 number
variable b2 number 
exec :b1 := 55; :b2 := 56;
set serveroutput off

demo@XEPDB1> select object_name
  2  from t
  3  where object_id = :b1 or
  4  object_id = :b2;

OBJECT_NAME
------------------------------
I_CDEF3
I_CDEF4

demo@XEPDB1> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  fycax4qajcvnz, child number 1
-------------------------------------
select object_name from t where object_id = :b1 or object_id = :b2

Plan hash value: 2130245068

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     4 (100)|          |
|   1 |  INLIST ITERATOR             |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     2 |    80 |     4   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_IDX |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OBJECT_ID"=:B1 OR "OBJECT_ID"=:B2))


20 rows selected.

demo@XEPDB1> select object_name
  2  from t
  3  where object_id = :b1
  4  union all
  5  select object_name
  6  from t
  7  where object_id = :b2;

OBJECT_NAME
------------------------------
I_CDEF3
I_CDEF4

demo@XEPDB1> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  dabmjz1kzf588, child number 0
-------------------------------------
select object_name from t where object_id = :b1 union all select
object_name from t where object_id = :b2

Plan hash value: 1078341690

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     4 (100)|          |
|   1 |  UNION-ALL                   |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    40 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    40 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=:B1)
   5 - access("OBJECT_ID"=:B2)


24 rows selected.

demo@XEPDB1> select /*+ USE_CONCAT(OR_PREDICATES(1)) */ object_name
  2  from t
  3  where object_id = :b1 or object_id = :b2;

OBJECT_NAME
------------------------------
I_CDEF4
I_CDEF3

demo@XEPDB1> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  1f949fsav85p1, child number 0
-------------------------------------
select /*+ USE_CONCAT(OR_PREDICATES(1)) */ object_name from t where
object_id = :b1 or object_id = :b2

Plan hash value: 559108845

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     4 (100)|          |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    40 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    40 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=:B2)
   5 - access("OBJECT_ID"=:B1)
       filter(LNNVL("OBJECT_ID"=:B2))


25 rows selected.

demo@XEPDB1>

Hi Connor McDonald,

Thank you for your reply and the suggestion.
It looks like the query needs to be supplied with something more than the set of hints extracted through the above display_cursor/outline on the union_all query. Not sure what exactly - this option did not work - the full table scan is see as below:



SQL> Select
2 /*+
4 BEGIN_OUTLINE_DATA
5 IGNORE_OPTIM_EMBEDDED_HINTS
6 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
7 DB_VERSION('11.2.0.4')
8 OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
9 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
10 RBO_OUTLINE
11 FORCE_XML_QUERY_REWRITE
12 XML_DML_RWT_STMT
13 XMLINDEX_REWRITE
14 XMLINDEX_REWRITE_IN_SELECT
15 NO_COST_XML_QUERY_REWRITE
16 OUTLINE_LEAF(@"SEL$F5BB74E1")
17 MERGE(@"SEL$2")
18 OUTLINE(@"SEL$1")
19 OUTLINE(@"SEL$2")
20 FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
21 END_OUTLINE_DATA
22 */
23 BAKR_ACCRRTT.ACCT_TYPE_ID,BAKR_ACCRRTT.ROUTING_NUMBER,BAKR_ACCRRTT.BANK_TRANSFER_CODE_ID,BAKR_ACCRRTT.ACCOUNT_CLASSIFICATION_ID,BAKR_ACCRRTT.FULL_ACCOUNT_NUMBER,BAKR_ACCRRTT.ITTE_ACCRRTT_ID,BAKR_ACCRRTT.PAYMENT_ACCOUNT_NUMBER,BAKR_ACCRRTT.UNMASKED_ACCOUNT_NUMBER
24 from
25 BAKR_ACCRRTT LEFT JOIN ITTE_ACCRRTT ON ITTE_ACCRRTT.ITTE_ACCRRTT_ID =BAKR_ACCRRTT.ITTE_ACCRRTT_ID LEFT JOIN ITEMACCT_CACHEITEM_MAP ON ITEMACCT_CACHEITEM_MAP.ITTE_ACCRRTT_ID = ITTE_ACCRRTT.ITTE_ACCRRTT_ID
26 WHERE ITTE_ACCRRTT.item_account_ID = 201223928 AND
27 IS_DELETED = 0 AND
28 ITTE_ACCRRTT_STATUS_ID IN (1,5,6) or
29 ITEMACCT_CACHEITEM_MAP.CACHE_ITEM_ID = 201223928;

Execution Plan
----------------------------------------------------------
Plan hash value: 1083278677

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4673 | 328K| | 24M (5)| 04:34:01 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN OUTER | | 4673 | 328K| 10G| 24M (5)| 04:34:01 |
|* 3 | HASH JOIN OUTER | | 163M| 9052M| 9364M| 24M (5)| 04:26:48 |
| 4 | TABLE ACCESS FULL | BAKR_ACCRRTT | 163M| 7491M| | 18M (5)| 03:20:54 |
| 5 | TABLE ACCESS FULL | ITTE_ACCRRTT | 192M| 1836M| | 5173K (3)| 00:56:50 |
| 6 | INDEX FAST FULL SCAN| ITMCACMP_CACITMISACT_IDX | 75227 | 1028K| | 578 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ITTE_ACCRRTT"."ITTE_ACCRRTT_ID"=201223928 AND "BAKR_ACCRRTT"."IS_DELETED"=0 AND
("ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=1 OR "ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=5 OR
"ITTE_ACCRRTT"."ITTE_ACCRRTT_STATUS_ID"=6) OR "ITEMACCT_CACHEITEM_MAP"."CACHE_ITEM_ID"=201223928)
2 - access("ITEMACCT_CACHEITEM_MAP"."ITTE_ACCRRTT_ID"(+)="ITTE_ACCRRTT"."ITTE_ACCRRTT_ID")
3 - access("ITTE_ACCRRTT"."ITTE_ACCRRTT_ID"(+)="BAKR_ACCRRTT"."ITTE_ACCRRTT_ID")

SQL>

Hi Rajeshwaran Jeyabal,

Thank you for your response - please find the observation below:

The use_concat was an attempt to split the query to generate a plan using "union all"
However it looks like the aggregation in the view (vw_nso_1) needs to be forced in some way.

Any hint to force it - I am just thinking loud.

My attempt is to force a good plan on the bad query and profile that plan to the same query.


I have gone through the query transformation link you have posted. Let me investigate this a little further and get back

Sir,

Please refer to step1 and step2 of the original note:

"use_concat - need some inputs around this query
Diwakar Krishnakumar, August 10, 2021 - 1:38 am UTC"

Step1 - bad query and bad plan

step2 - has the modified query with the good plan (union all clause)

intention - copy/profile the plan of the query @step 2 to the query @ step1
- we want to tweak the plan without changing the structure of the query from the code

Attempted:

coe_xfr_profile - did not work

hint the query @ step1 to get to the same plan of step2 - did not work