Oracle v19c: hierarchical searches take forever and a day-CodePudding

We are working on a ticketing system ("tickets" in the sense of IT tickets where we capture issues or customer contacts, not tickets that allow entry to some event).

These tickets can have references to some other (parent-)ticket, such that these tickets can form a tree. The tree depth (or height?) is typically rather low. Maybe 20% of the tickets have 1, 2 or maybe up to 3 ancestors.

Tickets that have no ancestor (i.e. where "TICKET_VORGAENGER_ID" = null / "Vorgaenger" means "ancestor" in German) are called root tickets. We have a query that searches the root ticket for any give ticket-id.

The SQL reads like so:

    SELECT CONNECT_BY_ROOT TICKET_ID AS TICKET_ID
        FROM TICKET
        WHERE TICKET_ID = :ticketId
        START WITH TICKET_VORGAENGER_ID IS NULL
        CONNECT BY PRIOR TICKET_ID = TICKET_VORGAENGER_ID;

:ticketId is the id of the ticket for which to search its root ancestor.

Both columns, TICKET_ID (PK) and TICKET_VORGAENGER_ID have defined indices.

For some odd reason these queries take on average 40 and can take up to 80(!) seconds on our DB. Currently we have about 3.5 million tickets in the DB and the DB server is quite potent.

Why on earth are these queries taking so long? What's wrong with it? It was taken more or less 1:1 from an Oracle example page.

The query's execution plan looks like so:

Executionplan
---------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |        |  6390K|   158M| 22765  (62)| 00:00:01 |
|*  1 |  FILTER                                  |        |       |       |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|        |       |       |            |          |
|   3 |    TABLE ACCESS FULL                     | TICKET |  3654K|    31M|  8877   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("TICKET_ID"=TO_NUMBER(:1))
   2 - access("TICKET_VORGAENGER_ID"=PRIOR "TICKET_ID")
       filter("TICKET_VORGAENGER_ID" IS NULL)

What's puzzling me: why is there a "TABLE ACCESS FULL" here, if we have indices on all columns mentioned in this query?

Any idea or advise how to speed this up anyone?

CodePudding user response：

If :ticketId is the root then "START WITH TICKET_ID = :ticketId" and no need of the WHERE clause that will not use any index on ticket_id alone anyway since you are looking for NULL and NULLs are not in the index, that should trigger the usage of the correct index.