3

I have an hierarchical 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 siblings by 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 ordering the CONNECT BY siblings, then the cost is low (1027):

(Note: I *excluded* this line when I ran query: ORDER SIBLINGS BY CLASSIFICATION)

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 order the CONNECT BY siblings, then the cost is high (16975):

(Note: I *included* this line when I ran query: ORDER SIBLINGS BY CLASSIFICATION)

Plan hash value: 1017899897
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |           | 82500 |    74M| 16975   (1)| 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)

Question:

As a novice, I'm wondering:

Is there is a way to optimize ordering the CONNECT BY siblings for better performance?

  • I've tried adding indexes to the columns that are involved in the ordering/CONNECT BY. But that doesn't seem to help; the indexes don't get used.
4
  • Cost is an estimate. What is the real performance like? – Andrew Sayer Jan 10 at 11:47
  • @AndrewSayer The performance right now seems to be fine. It takes about 1 second to return 20,000 rows. With that said, I'm trying to learn how to optimize the query so that it will perform well in the future with millions of rows. Is that a good approach? Perhaps I should generate more test data (millions of dummy records)? – User1973 Jan 10 at 12:02
  • What are you going to do with 20,000 rows at once? The optimisation approach would generally be - only get the rows you really need, and put in a supporting index to allow Oracle to identify the starting point and (possibly a second) to do the connect by. – Andrew Sayer Jan 10 at 12:48
  • 1
    @User1973 If it's realistic that you'll need to support millions of rows within the next 6 months then you should test with millions of rows now and look at the plan generated for millions of rows to see what can be optimized, not base it off of 20,000 rows as those plans can vary. If you're not expecting to support millions of rows for a few years then there's probably no point in trying to optimize for that now as a lot can change between now and then and you probably have other better things you can focus on. Database performance optimization is an ongoing game, no solution is timeless. – J.D. Jan 10 at 14:11
1

Usually when we add "ORDER BY" to a SELECT, the execution cost increases. We get around this by creating/changing the indexes or the "ORDER BY" clause so that they coincide with each other. But even so, it may not be possible or the execution plan may change with time.

There is another solution, sometimes the application wants to access only a small amount of lines at once, in this case you could do a function that will sort only the part that the application will actually use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.