Home » SQL & PL/SQL » SQL & PL/SQL » Connect by clause taking lot of time (Oracle 11g, Linux)
Connect by clause taking lot of time [message #674880] Wed, 20 February 2019 12:13 Go to next message
rajivn786
Messages: 150
Registered: January 2010
Senior Member
Hi,

I have this connect by query which takes lot of time, It keeps on executing and never ends.

Create bitmap index got_next_r_num_idx on got_next_r_num(mm);
Create bitmap index got_next_r_nnum_idx on got_next_r_num(next_r_num);
Create bitmap index got_next_rn_num_idx on got_next_r_num(r_num);

Drop table aki_resolution_dates1 purge;
Create table aki_resolution_dates1 as
SELECT   --+ parallel(g 128) index(g)
 mm, date1, date2, sysdate dt
FROM      got_next_r_num g
START WITH  r_num  = 1
CONNECT BY  mm= PRIOR mm
        AND r_num                 = PRIOR next_r_num;

Table "got_next_r_num" has around 287753 rows.

Attaching only 13k due to size limitations.
  • Attachment: ff.csv
    (Size: 890.21KB, Downloaded 207 times)
Re: Connect by clause taking lot of time [message #674881 is a reply to message #674880] Wed, 20 February 2019 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You should use parallel 1024.

Re: Connect by clause taking lot of time [message #674882 is a reply to message #674880] Wed, 20 February 2019 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 26566
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Connect by clause taking lot of time [message #674883 is a reply to message #674882] Wed, 20 February 2019 12:39 Go to previous messageGo to next message
rajivn786
Messages: 150
Registered: January 2010
Senior Member
create table GOT_NEXT_R_NUM
(
  medical_record_number VARCHAR2(20) not null,
  date1                 DATE,
  date2                 DATE,
  r_num                 NUMBER,
  next_r_num            NUMBER
)

Create bitmap index got_next_r_num_idx on got_next_r_num(mm);
Create bitmap index got_next_r_nnum_idx on got_next_r_num(next_r_num);
Create bitmap index got_next_rn_num_idx on got_next_r_num(r_num);

Plan hash value: 109065721
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |  1434 | 80304 |  2080   (1)| 00:00:25 |
|*  1 |  CONNECT BY WITH FILTERING     |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | GOT_NEXT_R_NUM      |   717 | 22227 |   149   (1)| 00:00:02 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                     |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE  | GOT_NEXT_RN_NUM_IDX |       |       |            |          |
|   5 |   NESTED LOOPS                 |                     |   717 | 40152 |  1930   (1)| 00:00:24 |
|   6 |    CONNECT BY PUMP             |                     |       |       |            |          |
|   7 |    TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM      |     1 |    31 |  1930   (1)| 00:00:24 |
|   8 |     BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|   9 |      BITMAP AND                |                     |       |       |            |          |
|* 10 |       BITMAP INDEX SINGLE VALUE| GOT_NEXT_RN_NUM_IDX |       |       |            |          |
|* 11 |       BITMAP INDEX SINGLE VALUE| GOT_NEXT_R_NUM_IDX  |       |       |            |          |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("MEDICAL_RECORD_NUMBER"=PRIOR "MEDICAL_RECORD_NUMBER" AND "R_NUM"=PRIOR 
              "NEXT_R_NUM")
   4 - access("R_NUM"=1)
  10 - access("R_NUM"="connect$_by$_pump$_002"."PRIOR next_r_num ")
  11 - access("MEDICAL_RECORD_NUMBER"="connect$_by$_pump$_002"."PRIOR 
              medical_record_nu$POS190")




I dont think I have permissions enabled to see tkprof/trace.
Let me know if this helps.
Re: Connect by clause taking lot of time [message #674884 is a reply to message #674883] Wed, 20 February 2019 13:03 Go to previous messageGo to next message
John Watson
Messages: 7954
Registered: January 2010
Location: Global Village
Senior Member
What if you get rid of the hint? It is obviously stupid and you might get better plan without it.
Re: Connect by clause taking lot of time [message #674885 is a reply to message #674884] Wed, 20 February 2019 13:06 Go to previous messageGo to next message
rajivn786
Messages: 150
Registered: January 2010
Senior Member
Looks like nothing changed

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 109065721
|   0 | SELECT STATEMENT               |                     |  1434 | 80304 |  2080   (1)| 00:00:25 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | GOT_NEXT_R_NUM      |   717 | 22227 |   149   (1)| 00:00:02 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                     |       |       |            |          |
|   5 |   NESTED LOOPS                 |                     |   717 | 40152 |  1930   (1)| 00:00:24 |
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
|*  1 |  CONNECT BY WITH FILTERING     |                     |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE  | GOT_NEXT_RN_NUM_IDX |       |       |            |          |
|   6 |    CONNECT BY PUMP             |                     |       |       |            |          |
|   7 |    TABLE ACCESS BY INDEX ROWID | GOT_NEXT_R_NUM      |     1 |    31 |  1930   (1)| 00:00:24 |
|   8 |     BITMAP CONVERSION TO ROWIDS|                     |       |       |            |          |
|   9 |      BITMAP AND                |                     |       |       |            |          |
|* 10 |       BITMAP INDEX SINGLE VALUE| GOT_NEXT_RN_NUM_IDX |       |       |            |          |
|* 11 |       BITMAP INDEX SINGLE VALUE| GOT_NEXT_R_NUM_IDX  |       |       |            |          |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("MEDICAL_RECORD_NUMBER"=PRIOR "MEDICAL_RECORD_NUMBER" AND "R_NUM"=PRIOR 
              "NEXT_R_NUM")
   4 - access("R_NUM"=1)
  10 - access("R_NUM"="connect$_by$_pump$_002"."PRIOR next_r_num ")
  11 - access("MEDICAL_RECORD_NUMBER"="connect$_by$_pump$_002"."PRIOR 
              medical_record_nu$POS159")
Re: Connect by clause taking lot of time [message #674886 is a reply to message #674880] Wed, 20 February 2019 13:10 Go to previous messageGo to next message
EdStevens
Messages: 1082
Registered: September 2013
Senior Member
Also posted at https://community.oracle.com/thread/4202462
Re: Connect by clause taking lot of time [message #674887 is a reply to message #674881] Wed, 20 February 2019 14:07 Go to previous messageGo to next message
rajivn786
Messages: 150
Registered: January 2010
Senior Member
Didn't work .
Re: Connect by clause taking lot of time [message #674888 is a reply to message #674887] Wed, 20 February 2019 14:54 Go to previous message
BlackSwan
Messages: 26566
Registered: January 2009
Location: SoCal
Senior Member
based upon linear extrapolation the fully query should complete in 4585 HOURS below

1* select 1434/25*287753/60/60 hours from dual
SQL> /

HOURS
----------
4584.86447
Previous Topic: Retrun a limit set of rows
Next Topic: How to Update the Col2 for below Table Structure
Goto Forum:
  


Current Time: Sun Jul 21 16:47:34 CDT 2019