oracle – Sorting on CONNECT BY

I have an Oracle 19c query that uses CONNECT BY:

select 
    wonum,
    parent,
    classification,
    classstructureid,
    division,
    worktype,
    status, 
    glaccount, 
    fircode, 
    actstart,
    actfinish,
    siteid,

    connect_by_root wonum as topworkpackage,
    level,
    ltrim(sys_connect_by_path(wonum,'  '),'  ') wohierarchy
from 
    workorder
where
    istask = 0
    and woclass in ('WORKORDER', 'ACTIVITY')
    and siteid = 'SERVICES'

connect by prior wonum = parent
start with parent is null

order by
    topworkpackage,
    level,
    classification

Here’s a sample of some of the relevant columns from the query:

WONUM PARENT CLASSIFICATION TOPWORKPACKAGE LEVEL WOHIERARCHY
WO55016 ROADS WO55016 1 WO55016
WO55015 WO55016 ROADS WINTER WO55016 2 WO55016 WO55015
WO43181 WO55015 ROADS WINTER WO55016 3 WO55016 WO55015 WO43181
WO43183 WO43181 ROADS WINTER A WO55016 4 WO55016 WO55015 WO43181 WO43183
WO37342 WO43181 ROADS WINTER B WO55016 4 WO55016 WO55015 WO43181 WO37342
WO43182 WO43181 ROADS WINTER C WO55016 4 WO55016 WO55015 WO43181 WO43182

Performance:

If I run the query without sorting the CONNECT BY, then the cost is low (1027):

Plan hash value: 1017899897
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |           | 82500 |    74M|  1027  (46)| 00:00:01 |
|*  1 |  FILTER                                  |           |       |       |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|           |       |       |            |          |
|   3 |    TABLE ACCESS FULL                     | WORKORDER | 22158 |  1947K|   566   (2)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ISTASK"=0 AND ("WOCLASS"='WORKORDER' OR "WOCLASS"='ACTIVITY') AND 
              "SITEID"='SERVICES')
   2 - access("PARENT"=PRIOR "WONUM")
       filter("PARENT" IS NULL)

However, if I run the query and sort the CONNECT BY, then the cost is high (16975):

Plan hash value: 2210159076
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           | 82500 |    74M|       | 16975   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                            |           | 82500 |    74M|    80M| 16975   (1)| 00:00:01 |
|*  2 |   FILTER                                  |           |       |       |       |            |          |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|           |       |       |       |            |          |
|   4 |     TABLE ACCESS FULL                     | WORKORDER | 22158 |  1947K|       |   566   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ISTASK"=0 AND ("WOCLASS"='WORKORDER' OR "WOCLASS"='ACTIVITY') AND "SITEID"='SERVICES')
   3 - access("PARENT"=PRIOR "WONUM")
       filter("PARENT" IS NULL)

Question:

As a novice, I’m wondering:

Is there is a way to optimize sorting the CONNECT BY?

  • I’ve tried adding indexes to (wonum), (parent), (wonum,parent), and (parent,wonum). But that doesn’t seem to help; the indexes don’t get used.