2

every time I execute this query it takes like 2 minutes to execute:

select * from CPOB_Monitoring_Dashboard
where  VOYAGE_STRT_DT >= TO_TIMESTAMP('2014-07-03 00:00:00.000','YYYY-MM-DD HH24:MI:SS.FF') 
and    VOYAGE_STRT_DT <= TO_TIMESTAMP('2018-07-03 00:00:00.000','YYYY-MM-DD HH24:MI:SS.FF')

However if I change it to use TO_DATE instead of TO_TIMESTAMP is really fast. Linq is generating the query using TOTIMESTAMP and I've not found yet a way to change that to use TO_DATE, is there any way that I can optimize the TOTIMESTAMP query?? Here is the Execution Plan for the query using TOTIMESTAMP:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 246273147

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           | 21842 |  4820K|       |  1336   (1)| 00:00:17 |
|   1 |  VIEW                           |  CPOB_Monitoring_Dashboard| 21842 |  4820K|       |  1336   (1)| 00:00:17 |
|   2 |   HASH UNIQUE                   |                           | 21842 |  3988K|  4384K|  1336   (1)| 00:00:17 |
|   3 |    NESTED LOOPS                 |                           | 21842 |  3988K|       |   442   (1)| 00:00:06 |
|   4 |     NESTED LOOPS                |                           |    47 |  7661 |       |   160   (1)| 00:00:02 |
|*  5 |      TABLE ACCESS FULL          |      VOYAGE_INFO          |    46 |  1012 |       |    68   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID|      PROCESS_CTRL         |     1 |   141 |       |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          |      VOYAGE_ID_IDX        |     1 |       |       |     1   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN            |      PLY_IDX2             |   467 | 11208 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(INTERNAL_FUNCTION("CPVI"."VOYAGE_STRT_DT")>=TIMESTAMP' 2014-07-03 00:00:00.000000000' AND 
              INTERNAL_FUNCTION("CPVI"."VOYAGE_STRT_DT")<=TIMESTAMP' 2018-07-03 00:00:00.000000000')
   7 - access("CPC"."VOYAGE_ID"="CPVI"."VOYAGE_ID")
   8 - access("CPC"."BRAND_NAME"="CPOB"."BRAND_ID" AND "CPC"."SHIP_NAME"=""SHIP_NAME")
       filter("CPC"."SHIP_NAME"="CPOB"."SHIP_NAME")

24 rows selected.
David Faber
  • 12,277
  • 2
  • 29
  • 40
AlexGH
  • 2,735
  • 5
  • 43
  • 76
  • 2
    Your query is using a column named DATESTART but your execution plan is showing VOYAGE_STRT_DT. Plus references to BRAND_NAME and SHIP_NAME. Does not compute. Apples and Oranges. – Stilgar Jul 09 '18 at 19:51
  • code updated... – AlexGH Jul 09 '18 at 20:07
  • 2
    Your execution plan is still showing a reference to a second table. Your code snippet doesn't match your execution plan. Is VOYAGE_STRT_DT defined as DATE or is it defined as TIMESTAMP? – Stilgar Jul 09 '18 at 21:08
  • What is `TOTIMESPAN` mentioned in the question title? – William Robertson Jul 09 '18 at 21:27
  • @Stilgar looks like it was a `date` column in [another question](https://stackoverflow.com/q/51246131/230471). – William Robertson Jul 09 '18 at 21:35
  • 2
    My guess is that the column `VOYAGE_STRT_DT` is an Oracle DATE, not a TIMESTAMP, so comparing it to a TIMESTAMP results in an implicit conversion of the DATE to a TIMESTAMP and, therefore, doesn't use the index on the column. – David Faber Jul 09 '18 at 21:35
  • Perhaps you should ask the question of how to make Linq use `TO_DATE`. – Andreas Jul 09 '18 at 21:40
  • The execution plan is obviously not from the query you posted. For example there is a condition on the `SHIP_NAME` column shown in the plan, but there is no such condition in the query. I recommended that your question be closed as "not clear what you are asking" - you've already had three hours to fix this. If you don't care enough to ask a meaningful question, why should anyone care to help you? –  Jul 09 '18 at 23:54
  • 2
    @DavidFaber that's correct, I'm querying against a view... Sorry I've not been following a lot this thread I've been working in something else too. I'm going to also try to make Linq use `TO_DATE` but I'm not sure if I'll be successful with that... I'll keep updates in here about the next steps I'm working on... I've made the column `VOYAGE_STRT_DT` to use `TIMESTAMP Datatype` just testing and the query is way faster.. that could be a choice... – AlexGH Jul 10 '18 at 03:26

0 Answers0