1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Connect by prior ... performance issue

Discussion in 'SQL PL/SQL' started by uman2631, May 18, 2018.

  1. uman2631

    uman2631 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    KS
    I have a table that contains record details. A simplified version is below. Over time, a record can be adjusted, and a new record with a new record ID is created.
    The "parent" record id is recorded in the REC_ID_ADJ_FROM field. An adjusted record can be adjusted again by having another new record created with a new REC_ID, and the immediate
    parent's rec_id in the REC_ID_ADJ_FROM field. (See examples below).
    A record that has never been adjusted has a space in the REC_ID_ADJ_FROM field.

    What I need to do is, given a record ID, find its original (root) record. For clarity, the rec_ids below are all easy to read, but real record IDs are not and have no pattern.

    Code (Text):

    CREATE TABLE REC_DTL
    (
      REC_ID                          VARCHAR2(12 BYTE) NOT NULL,
      REC_ID_ADJ_FROM                 VARCHAR2(12 BYTE),
      DESCRIPTION                     VARCHAR2(500)
    )
    ;

    CREATE UNIQUE INDEX PK_REC_ID ON REC_DTL
    (REC_ID)
    ;

    CREATE INDEX IX_REC_ID_FROM ON REC_DTL
    (REC_ID_ADJ_FROM, REC_ID)
    ;


    INSERT INTO REC_DTL VALUES ('11111', ' ');
    INSERT INTO REC_DTL VALUES ('11112', '11111');
    INSERT INTO REC_DTL VALUES ('11113', '11112');
    INSERT INTO REC_DTL VALUES ('11114', '11113');
    INSERT INTO REC_DTL VALUES ('22221', ' ');
    INSERT INTO REC_DTL VALUES ('22222', '22221');
    INSERT INTO REC_DTL VALUES ('22223', '22222');
    INSERT INTO REC_DTL VALUES ('22224', '22223');
    INSERT INTO REC_DTL VALUES ('33331', ' ');
    INSERT INTO REC_DTL VALUES ('44441', ' ');
    INSERT INTO REC_DTL VALUES ('44442', '44441');
     
    In the above example, record IDs 11111, 22221, 33331 and 44441 are all original records. The parent of record 11113 is 11112, whose parent is 11111, which is the "root" record.
    The parent of record 22223 is 22222 whose parent is 22221 which is the root record. (As mentioned above, in these examples, it's sequential for clarity, but in real life, they are not).

    The following query was written to find the original record:

    Code (Text):

    select t1.rec_id as cur_rec, connect_by_root(t1.rec_id) as orig_rec
    from rec_dtl t1
    where t1.rec_id = :input_rec_id
    start with t1.rec_id_adj_from = ' ' /* original records have a space in adj_from */
    connect by prior t1.rec_id = t1.rec_id_adj_from  /* child -> parent */
    ;
     
    This query works. However, it is EXTREMELY slow. The table itself has millions of records, and executing this query just 1 time takes 8 minutes or more, which is about 7.5 minutes too long. Any suggestions for improving? We've tried making the IX_REC_ID_ADJ_FROM index be just REC_ID_ADJ_FROM, both REC_ID_ADJ_FROM+REC_ID and REC_ID+REC_ID_ADJ_FROM. Having an index is better than not having an index, but no combination of indexing is helping. We've also tried parallel hints. Small improvement, but not nearly enough.

    Below is the explain plan.

    Code (Text):


    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                         |  2865M|    42G|       |    15M  (1)| 00:10:00 |
    |   1 |  SORT ORDER BY                            |                         |  2865M|    42G|    64G|    15M  (1)| 00:10:00 |
    |*  2 |   FILTER                                  |                         |       |       |       |            |          |
    |*  3 |    CONNECT BY NO FILTERING WITH START-WITH|                         |       |       |       |            |          |
    |   4 |     INDEX FAST FULL SCAN                  | IX_F1_CLAIM_ID_ADJ_FROM |    98M|  1508M|       | 98909   (1)| 00:00:04 |
    -----------------------------------------------------------------------------------------------------------------------------

     
    thanks for any suggestions!

    Ulysses
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,637
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need more than just the plan to see what Oracle is actually doiing; use 'set autotrace on' and execute the query again and you'll see statistics for the execution that could reveal where the slowness is originating. Another option is to set event 10046 at level 12 and run the resulting trace file through tkprof. It isn't the plan that's causing the slowness, it's the waits.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    767
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    if you just need to collect a branch from the child to the parent, you do not need to scan "all parents"
    for a example....,'11114' '44442' '22224'
    Code (SQL):

    SELECT t1.rec_id AS cur_rec, connect_by_root(t1.rec_id) AS orig_rec,sys_connect_by_path(rec_id,'/') path_
    FROM rec_dtl t1
    START WITH t1.rec_id IN ('11114','44442','22224')
    CONNECT BY  t1.rec_id = prior t1.rec_id_adj_from;
    /*child -> parent*/
     
    for to compare :
    Code (SQL):

    --1
    SELECT * FROM (
    SELECT t1.rec_id AS cur_rec, connect_by_root(t1.rec_id) AS orig_rec,sys_connect_by_path(rec_id,'/') path_,ROW_NUMBER () OVER(partition BY connect_by_root(t1.rec_id) ORDER BY level DESC) rn
    FROM rec_dtl t1
    START WITH t1.rec_id IN ('11114','44442','22224')
    CONNECT BY  t1.rec_id = prior t1.rec_id_adj_from
    ) WHERE rn = 1;
    --2
    SELECT t1.rec_id AS cur_rec, connect_by_root(t1.rec_id) AS orig_rec,sys_connect_by_path(rec_id,'/') path_
    FROM rec_dtl t1
    WHERE t1.rec_id IN ('11114','44442','22224')
    START WITH  t1.rec_id_adj_from  = ' '
    CONNECT BY  prior  t1.rec_id =  t1.rec_id_adj_from;
     
     
    Last edited: May 21, 2018