Answer #1

This answer comes from one of my staff, Jun Feng:

Good things about this answer:

  1. The code is simple
  2. It uses the fastest way to assemble the correct account path (starting at the child and working “backwards” up the tree to the ultimate parent)
  3. Realizes that the account_path we want is the one where the parent_account_id is null
  4. Uses the reverse() function — I know it’s undocumented, but boy is it cool!
  5. And heck, 9 consistent gets, 7 sorts and 0 recursive calls is pretty good too.

SQL> set autotrace on
SQL> select rtrim(reverse(sys_connect_by_path(reverse(account_name), ‘.’)),’.’) account_path
  2  from account
  3  where parent_account_id is null
  4  start with account_id = 10100
  5  connect by account_id = prior parent_account_id
  6  ;

ACCOUNT_PATH
——————————————————————————————–
sys.all_apply_progress.apply_name

Execution Plan
———————————————————-
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=19)
  FILTER
    CONNECT BY (WITH FILTERING)
      NESTED LOOPS
        INDEX (UNIQUE SCAN) OF ‘ACCOUNT_PK’ (UNIQUE) (Cost=1 Card=1 Bytes=5)
        TABLE ACCESS (BY USER ROWID) OF ‘ACCOUNT’
      NESTED LOOPS
        BUFFER (SORT)
          CONNECT BY PUMP
        TABLE ACCESS (BY INDEX ROWID) OF ‘ACCOUNT’ (Cost=1 Card=1 Bytes=19)
          INDEX (UNIQUE SCAN) OF ‘ACCOUNT_PK’ (UNIQUE) (Cost=1 Card=1)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        251  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          1  rows processed

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder