An Answer to #2

I’m not really satisfied with this answer, but so far this is the best I can do.

  1. It attempts to walk the path from top to bottom, starting at the root
  2. It attempts to validate each step on the path, but it does it pretty crudely in my opinion
  3. At least the code is pretty easy to understand

SQL> select account_id
  2  from
  3  (
  4  select account_id, sys_connect_by_path(a.account_name,’.’) account_path
  5  from account a
  6  start with a.account_name =
  7  substr(‘sys.all_apply_progress.apply_name’,1,
  8  instr(‘sys.all_apply_progress.apply_name’,’.’)-1)
  9  and a.parent_account_id is null
 10  connect by prior a.account_id = a.parent_account_id
 11  and instr(‘.’||’sys.all_apply_progress.apply_name’||’.’,’.’||a.account_name||’.’) > 0
 12  )
 13* where account_path = ‘.’||’sys.all_apply_progress.apply_name’
SQL> /

ACCOUNT_ID
———-
     10100
Execution Plan
———————————————————-
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2015)
  VIEW (Cost=1 Card=1 Bytes=2015)
    CONNECT BY (WITH FILTERING)
      NESTED LOOPS
        TABLE ACCESS (BY INDEX ROWID) OF ‘ACCOUNT’ (Cost=2 Card=1 Bytes=15)
          INDEX (RANGE SCAN) OF ‘ACCOUNT_IK_NAME’ (NON-UNIQUE) (Cost=1 Card=4)
        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 (RANGE SCAN) OF ‘ACCOUNT_FK_PARENT’ (NON-UNIQUE) (Cost=1 Card=12)
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        213  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