disj spool

disj_spool.sql

SQL> select/*+ full(xt) */ count(*)
  2  from xt
  3  where
  4     date1 > trunc(sysdate) - 1 or
  5     date2 > trunc(sysdate) - 1 or
  6     date3 > trunc(sysdate) - 1 or
  7     date4 > trunc(sysdate) - 1 or
  8     date5 > trunc(sysdate) - 1;

  COUNT(*)
----------
      7340

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  9mvtaxfu15pgz, child number 0
-------------------------------------
Plan hash value: 627935069

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:04.79 |    9871 |   9868 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:04.79 |    9871 |   9868 |
|*  2 |   TABLE ACCESS FULL| XT   |      1 |   7312 |   7340 |00:00:04.79 |    9871 |   9868 |
----------------------------------------------------------------------------------------------

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

   2 - filter(("DATE1">TRUNC(SYSDATE@!)-1 OR "DATE4">TRUNC(SYSDATE@!)-1 OR
              "DATE3">TRUNC(SYSDATE@!)-1 OR "DATE5">TRUNC(SYSDATE@!)-1 OR
              "DATE2">TRUNC(SYSDATE@!)-1))


SQL> select count(*)
  2  from xt
  3  where
  4     greatest(date1, date2, date3, date4, date5) > trunc(sysdate) - 1;

  COUNT(*)
----------
      7340

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  fwfsnq78kf6ga, child number 1
-------------------------------------
Plan hash value: 2527608477

----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.06 |      22 |     22 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.06 |      22 |     22 |
|*  2 |   INDEX RANGE SCAN| I$XT$DATES2 |      1 |   7116 |   7340 |00:00:00.06 |      22 |     22 |
----------------------------------------------------------------------------------------------------

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

   2 - access("XT"."SYS_NC00008$">TRUNC(SYSDATE@!)-1)


SQL> select/*+ NO_TRANSFORM_DISTINCT_AGG */ count(distinct rid)
  2  from (
  3        select d1.rowid rid from xt d1 where d1.date1 > trunc(sysdate) - 1
  4        union all
  5        select d2.rowid from xt d2 where d2.date2 > trunc(sysdate) - 1
  6        union all
  7        select d3.rowid from xt d3 where d3.date3 > trunc(sysdate) - 1
  8        union all
  9        select d4.rowid from xt d4 where d4.date4 > trunc(sysdate) - 1
 10        union all
 11        select d5.rowid from xt d5 where d5.date5 > trunc(sysdate) - 1
 12    );

COUNT(DISTINCTRID)
------------------
              7340

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3qmsrc2bqshh6, child number 0
-------------------------------------
Plan hash value: 4043828305

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |      1 |        |      1 |00:00:00.19 |      35 |     35 |       |       |          |
|   1 |  SORT GROUP BY      |            |      1 |      1 |      1 |00:00:00.19 |      35 |     35 |   478K|   448K|  424K (0)|
|   2 |   VIEW              |            |      1 |   7297 |   7366 |00:00:00.15 |      35 |     35 |       |       |          |
|   3 |    UNION-ALL        |            |      1 |        |   7366 |00:00:00.14 |      35 |     35 |       |       |          |
|*  4 |     INDEX RANGE SCAN| I$XT$DATE1 |      1 |   1458 |   1444 |00:00:00.03 |       7 |      7 |       |       |          |
|*  5 |     INDEX RANGE SCAN| I$XT$DATE2 |      1 |   1466 |   1519 |00:00:00.03 |       7 |      7 |       |       |          |
|*  6 |     INDEX RANGE SCAN| I$XT$DATE3 |      1 |   1439 |   1523 |00:00:00.02 |       7 |      7 |       |       |          |
|*  7 |     INDEX RANGE SCAN| I$XT$DATE4 |      1 |   1473 |   1414 |00:00:00.03 |       7 |      7 |       |       |          |
|*  8 |     INDEX RANGE SCAN| I$XT$DATE5 |      1 |   1461 |   1466 |00:00:00.02 |       7 |      7 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("D1"."DATE1">TRUNC(SYSDATE@!)-1)
   5 - access("D2"."DATE2">TRUNC(SYSDATE@!)-1)
   6 - access("D3"."DATE3">TRUNC(SYSDATE@!)-1)
   7 - access("D4"."DATE4">TRUNC(SYSDATE@!)-1)
   8 - access("D5"."DATE5">TRUNC(SYSDATE@!)-1)