1

I'm curious to know what is the reason behind this issue -

I had a query for example

SELECT * FROM A WHERE A.DATE = (SELECT B.DATE FROM B)

Here B.DATE returned a single row and it's type is TIMESTAMP (3) while A.DATE is a DATE column.

So A table is huge but for one date it has only 100k rows. The above SQL took around 1000s to process.

When I did this it took only 10s -

SELECT * FROM A WHERE A.DATE = (SELECT CAST(B.DATE AS DATE) FROM B)

Can someone please explain why the CAST fixed the SQL run time?

Oracle Version - 19c

mindwrapper
  • 81
  • 1
  • 8
  • I think `select * from table(dbms_xplan.diaplay_cursor(format => 'TYPICAL+PREDICATE ALLSTATS LAST'))` can explain you when it wil be executed right after each of your statements. But first you need to add `/*+gather_plan_statistics*/` hint to your queries. It will show you executed plan of each query with runtime of each operation. Then you just need to compare them to start the real question you need to answer. – astentx Jun 27 '21 at 21:41
  • 1
    See [here](https://stackoverflow.com/a/34975420/4808122) how you can investigate the execution plan of both queries and resolve the issue – Marmite Bomber Jun 27 '21 at 21:56

1 Answers1

2

The type conversion between DATE and TIMESTAMP prevent the index usage that you have defined on the "DATE" column (BTW do not use reserved words for column names - DATE must be quoted)

See exaples below

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
SELECT * FROM A WHERE A."DATE" =  DATE'2021-01-02';
--    
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

 
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| AI   |     1 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DATE"=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))

;

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
SELECT * FROM A WHERE A."DATE" =  TIMESTAMP'2021-01-02 00:00:00.000';
--    
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |  8000 |    56   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A    |  1000 |  8000 |    56   (6)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(INTERNAL_FUNCTION("A"."DATE")=TIMESTAMP' 2021-01-02 
              00:00:00.000000000')

So if the subquery returns DATE the index can be used, but if it returns a TIMESTAMP the column value must be conevrted to TIMESTAMPwhich prevents the index access (second example).

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • I think it is worth to add that this is because [this](https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-F29074EF-81CA-48C5-A060-2789EF439256) datatype precedence (which for date is outside of the SQL reference for some reason). – astentx Jun 27 '21 at 22:08
  • 1
    Excellent! I checked the plan this is exactly what is happening @astentx Correct if A.DATE were timestamp and B.DATE were DATE. The single row B.DATE row would've been converted to TIMESTAMP easily and sql time wouldn've been the same. – mindwrapper Jun 28 '21 at 02:50