2

I have a table that looks like this:

Bundleref Subbundleref
123 456
456 789

Starting from a certain reference (e.g. 123), I want a list of all descendants, all the way until the leaf values.

In Oracle, I can use a CONNECT BY clause like this:

select subbundleref from store.tw_bundles
start with bundleref = 2201114
connect by prior subbundleref = bundleref

For compatibility reasons, I am trying to convert it to a recursive CTE, like this:

WITH bundles(br,sr)
AS
(
   SELECT bundleref, subbundleref
   FROM store.tw_bundles where bundleref = 2201114
   UNION ALL
   SELECT bundleref, subbundleref
   FROM store.tw_bundles twb
   inner join bundles on twb.bundleref = bundles.sr
  )
 select sr from bundles

This gives me the same result. There is one problem though: the CONNECT BY query takes 300 ms, the recursive query takes about 50 seconds. Am I doing something inefficient here or is this not being optimized? (I'm using Oracle 19c.)

Explain plan for first query:

Plan hash value: 4216745508
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |   218 |  5668 |    36   (6)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|                          |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | TW_BUNDLES_BUNDLEREF_IDX |    14 |   168 |     3   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |                          |   204 |  5100 |    31   (0)| 00:00:01 |
|   4 |    CONNECT BY PUMP        |                          |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | TW_BUNDLES_BUNDLEREF_IDX |    15 |   180 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("BUNDLEREF"=PRIOR "SUBBUNDLEREF")
   2 - access("BUNDLEREF"=2201114)
   5 - access("connect$_by$_pump$_002"."prior subbundleref "="BUNDLEREF")
 
Note
-----
   - this is an adaptive plan

And for the second one:

Plan hash value: 1467025167
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                          |     1 |       |       |   259K  (3)| 00:00:11 |
|   1 |  SORT AGGREGATE                            |                          |     1 |       |       |            |          |
|   2 |   VIEW                                     |                          |  1975M|       |       |   259K  (3)| 00:00:11 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|                          |       |       |       |            |          |
|*  4 |     INDEX RANGE SCAN                       | TW_BUNDLES_BUNDLEREF_IDX |    14 |   168 |       |     3   (0)| 00:00:01 |
|*  5 |     HASH JOIN                              |                          |  1975M|    45G|   258M|   259K  (3)| 00:00:11 |
|   6 |      BUFFER SORT (REUSE)                   |                          |       |       |       |            |          |
|   7 |       TABLE ACCESS FULL                    | TW_BUNDLES               |    11M|   129M|       |  9208   (1)| 00:00:01 |
|   8 |      RECURSIVE WITH PUMP                   |                          |       |       |       |            |          |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("BUNDLEREF"=2201114)
   5 - access("TWB"."BUNDLEREF"="BUNDLES"."SR")
wasmachien
  • 969
  • 1
  • 11
  • 28
  • can you run explain plans on both and post the results? – OldProgrammer Feb 06 '22 at 17:27
  • To explain a difference between 300 ms and 500 ms could be a challange, but a difference to 50 sec. will have most probably a *trivial* cause. You may check how to get explain plan [here](https://stackoverflow.com/a/34975420/4808122) – Marmite Bomber Feb 06 '22 at 18:09
  • I've added the query plans. I am probably making a mistake in joining the tables, but I'm not quite sure how the replicate the first query without it. – wasmachien Feb 06 '22 at 18:28
  • You are returning millions of rows and processing 45G of data in the 2nd query. Something is wrong with your joins for sure. – OldProgrammer Feb 06 '22 at 19:02
  • It seems the second plan is trying to acces `tw_bundles.bundleref`. Does the table have an index on that column? – The Impaler Feb 07 '22 at 00:46
  • This is a subject I always have had doubts about. If you don't get answers I can offer a bounty later on to attract more attention. – The Impaler Feb 07 '22 at 01:11
  • You need a "where" clause on the second part of the CTE, as in `where br is not null`. I am surprised you didn't get an infinite loop error. – Brian Leach Feb 07 '22 at 19:56

0 Answers0