1

I have the following query with high execution time, where current indexes are created on individual columns ENTER_TIME and EXIT_TIME and location_id is primary_key on both the tables.

Database server: Oracle Database 19c Standard Edition 2

Version : 19.11.0.0.0

SELECT
    trp.location,
    trp.enter_time,
    trp.exit_time
    SUM(TIMEDIFF(trp.enter_time,trp.exit_time)) AS stay_time
FROM
    trip_route_point trp
INNER JOIN 
    location l ON trp.location_id = l.location_id
WHERE 
    trp.enter_time BETWEEN '20221010070000' AND '20221108070000' 
    AND trp.exit_time IS NOT NULL 
    AND trp.exit_time >= trp.enter_time
GROUP BY 
    trp.location_id
HAVING 
    SUM(TIMEDIFF(trp.enter_time, trp.exit_time)) > 0 
ORDER BY 
    stay_time DESC

Query performance is at 3 secs with 2.5 million rows in the trip_route_point table.

I suspect trp.exit_time >= trp.enter_time condition is not making use of the indexes.

From the execution plan I can see the query requires full table scan.

Please advise the best indexes to use to improve the query performance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jaya
  • 13
  • 5
  • 1
    Including the execution plan, and tables and indexes, as text might be helpful - see [How to describe performance issue in relational database?](https://stackoverflow.com/a/34975420/266304). Are enter/exit times really strings, not dates or timestamps? How is your `timediff` function manipulating those? And why are you joining to `location` as you don't seem to refer to it again? – Alex Poole Nov 08 '22 at 17:33
  • 1
    An [`explain`](https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231) will give you an execution plan which will tell you why its slow. That said, `(exit_time, enter_time)` and `(enter_time)` should cover your bases. Though it could be the `order by`. – Schwern Nov 08 '22 at 17:40
  • 1
    If the semantic of exit_time and entry_time is respected by the data, the condition trp.exit_time >= trp.enter_time is probably always true for not null exit_time, so if the DB considers the proportion of matching rows is large enough, a full scan is probably more efficient. – p3consulting Nov 08 '22 at 17:43
  • `AND trp.exit_time IS NOT NULL` is in excess there. You can remove it. – The Impaler Nov 08 '22 at 20:31

2 Answers2

0

Is not null tends to be bad performing for me. Since you are already qualifying that column as > trp.enter_time, you would not get back null results anyway. Try removing the is not null.

Jett Pesson
  • 16
  • 2
0

"trp.enter_time BETWEEN '20221010070000' AND '20221108070000'": the implicit conversion hidden behind this expression probably invalidates the usage of any index on the enter_time column.
You should post the data types of the involved columns.

p3consulting
  • 2,721
  • 2
  • 12
  • 10