Oracle Musings

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

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

First of all, I want to thank everyone for participating in my little quiz — I was amazed at the number and creativity of responses.

Also, I realized that I didn’t really give you a good set of test data — shame on me.  My silly little table of Red Sox pitchers really didn’t cut the mustard.

Here are some commands I used to create a test table for this quiz:

SQL> create table account (
  2  account_id number(10) not null,
  3  parent_account_id number(10),
  4  account_name varchar2(30) not null,
  5  constraint account_pk primary key (account_id),
  6  constraint account_fk_parent foreign key (parent_account_id)
  7  references account (account_id)
  8* )
SQL> /

Table created.

SQL> create index account_fk_parent on account (parent_account_id);

Index created.

SQL> create index account_ik_name on account (account_name);

Index created.

SQL> insert into account values (1,null,’sys’);

1 row created.

SQL> insert into account
  2  select rownum+1, 1, account_name
  3  from
  4  (
  5  select lower(table_name) account_name
  6  from dba_tables where owner = ‘SYS’
  7  union all
  8  select lower(view_name) account_name
  9  from dba_views where owner = ‘SYS’
 10  );

2319 rows created.

SQL> insert into account
  2  select rownum+10000, a.account_id, lower(b.column_name)
  3  from account a, dba_tab_columns b
  4  where b.owner = ‘SYS’
  5  and lower(b.table_name) = a.account_name;

24833 rows created.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'ACCOUNT’,cascade=>true);

PL/SQL procedure successfully completed.

Answers from my staff coming up in the next post — I’ll include execution statistics so you can compare against them.

Sorry I’ve been away for so long — travel and what not — and I don’t have one of those fancy EVDO cards

Anyway, here’s a quiz to pass the time:

Given a table as follows:

Account (
account_id number,
parent_account_id number,
account_name varchar2(20)
)

And sample data as follows:

account_id, parent_account_id, account_name

1,null,young
26653,1,radatz
150406,26653,stanley
150507,26653,smith
150508,26653,nipper
27854,1,longborg
160403,27854,smith

You need to produce 2 queries using only SQL (no PL/SQL functions):

1. Given an account_id, return the path of account_names from the root to the account, delimited by ‘.’
2. Given a path of account_names delimited by ‘.’, return the account_id of the account

Be able to explain why your queries are the most efficient possible.

I bet Hotblack Desiato would love this keyboard.