LEAD and LAG taking hours on a very large table!

2hrs and 44mins.

If i understand correctly

Shouldn't be using the direct-path load. That is the reason i created an index on all the columns that are used. That way you will be using index lookup's.

Basically if you are running 10 jobs in parallel the 9 will be waiting for the lock to be released. I might be wrong but that is what my understanding is.

Also try to have a bucket of a size that is acceptable.

What you can do is run the query in sql session and see how long it takes with different bucket size.

Glad to hear that your table got loaded.

@Sam_P How many rows are you getting when you do CHUNK_BY_ROWID.

So you are running one bucket at a time. The example you mentioned is running 10 in parallel.

Depending upon your server try 4-5 in parallel with smaller buckets. It is trail an error to get the optimum size.

Try having 150M/2000 as number of buckets and run 4-5 in parallel. See how you go.

Anyway if you have got it working and happy with it then it is fine.

Yes you can try that. Anyway you are executing serially for around 50000 times. This way you will run it in parallel and since it is just index lookup it might be lot faster. No harm in trying.

Also i don't think it is possible to get the right results for Chunks_by_rowid in your case as all the values for

ULIMORE

Might not be in the same chunk. That way lead and lag will not work as there is a where condition for start_id and end_id.

If you don't want to use ora_hash you can create a bucket table.

Code is attached.

DROP TABLE TB_SOURCE PURGE;
CREATE TABLE TB_SOURCE(
              SEQNUM      VARCHAR2(18),
              ULIMORE      VARCHAR2(15),
              VISIT_DATE_DT   DATE,
              ED_UCC_DT     DATE,
              ED_UCC_SEQNUM   VARCHAR2(18),
              DAD_ACUTE_DISP_DT DATE,
              DAD_ACUTE_SEQNUM VARCHAR2(18)
              )
 /
--Creating sample data.              
INSERT INTO tb_source
  SELECT
    ROWNUM
   , 
      dbms_random.string(
        'x'
       , 5
      )
     
   , sysdate - ROWNUM
   , sysdate - ROWNUM
   , ROWNUM
   , sysdate - ROWNUM
   , ROWNUM
    
  FROM
    dual
  CONNECT BY
    level < 100000;     


 truncate table tb_source_final;      


 CREATE TABLE TB_SOURCE_final(
              SEQNUM      VARCHAR2(18),
              ULIMORE      VARCHAR2(15),
              VISIT_DATE_DT   DATE,
              ED_UCC_DT_prev     DATE,
              ED_UCC_DT_next     DATE,
              ED_UCC_SEQNUM_prev   VARCHAR2(18),
              ED_UCC_SEQNUM_next   VARCHAR2(18),
              DAD_ACUTE_DISP_DT_prev DATE,
               DAD_ACUTE_DISP_DT_next DATE,
              DAD_ACUTE_SEQNUM_prev VARCHAR2(18),
               DAD_ACUTE_SEQNUM_next VARCHAR2(18)
             )
 /
 
 Create table tb_bucket as
 WITH t AS (
  SELECT DISTINCT
    ulimore
  FROM
    tb_source
), t1 AS (
  SELECT
    ulimore
   , NTILE(75000)
     OVER(
      ORDER BY
        ulimore
     ) bucket
  FROM
    t
)
SELECT bucket start_id , bucket end_id, 
  MIN(ulimore) min_ulimore
 , MAX(ulimore) max_ulimore
FROM
  t1
GROUP BY
  bucket;
create index tb_bucket_ix1 on tb_bucket(start_id,end_id,min_ulimore,max_ulimore);
 
 create index tb_source_ix1 on tb_source(
              ulimore     ,
              VISIT_DATE_DT   ,
              SEQNUM,
              ED_UCC_DT     ,
              ED_UCC_SEQNUM   ,
              DAD_ACUTE_DISP_DT ,
              DAD_ACUTE_SEQNUM  
              ) parallel(degree 8);
Alter index tb_source_ix1 noparallel;

create or replace PROCEDURE insert_parallel_execute AS
  l_chunk_sql VARCHAR2(1000);
  l_sql_stmt VARCHAR2(32000);
  l_try    NUMBER;
  l_status  NUMBER;
BEGIN
  BEGIN
    dbms_parallel_execute.drop_task('insert_aps');
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
 -- Create the TASK
  dbms_parallel_execute.create_task('insert_aps');


 -- Chunk the table by MANAGER_ID
  l_chunk_sql := 'select start_id , end_id from tb_bucket';
   
  dbms_parallel_execute.create_chunks_by_sql(
                       'insert_aps'
                      , l_chunk_sql
                      , false
  );


 -- Execute the DML in parallel
 --  the WHERE clause contain a condition on manager_id, which is the chunk
 --  column. In this case, grouping rows is by manager_id.
  l_sql_stmt := ' insert into TB_SOURCE_final
 SELECT 
    p.SEQNUM,
    p.ULIMORE,
    p.VISIT_DATE_DT,
    LAG(p.ED_UCC_DT     IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS PREV_ED_UCC_DT,
    LEAD(p.ED_UCC_DT     IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS NEXT_ED_UCC_DT,
    LAG(p.ED_UCC_SEQNUM   IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS PREV_ED_UCC_SEQNUM,
    LEAD(p.ED_UCC_SEQNUM   IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS NEXT_ED_UCC_SEQNUM,
    LAG(p.DAD_ACUTE_DISP_DT IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS PREV_DAD_DISP_AC_DT,
    LEAD(p.DAD_ACUTE_DISP_DT IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS NEXT_DAD_DISP_AC_DT,
    LAG(p.DAD_ACUTE_SEQNUM  IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS PREV_DAD_DISP_AC_SEQNUM,
    LEAD(p.DAD_ACUTE_SEQNUM IGNORE NULLS) OVER (PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)   AS NEXT_DAD_DISP_AC_SEQNUM
  FROM
    TB_SOURCE p, tb_bucket
    where ulimore between min_ulimore and max_ulimore
    and start_id between :start_id and :end_id';
       
  dbms_parallel_execute.run_task(
                 'insert_aps'
                , l_sql_stmt
                , dbms_sql.native
                , parallel_level => 5
  );
END insert_parallel_execute;
/

 select status,count(*) from user_parallel_execute_chunks
 where task_name = 'insert_aps'
 group by status;

@Sam_P My reason is that you are partition by it.

PARTITION BY p.ULIMORE

So lets see an example like this

There are approximately 73K records between the 2 rowid's. If the chunk is 3000 records then this won't be picked up.

You need next and prev date for each ulimore order by visit_date_dt.

Hope this helps.

@Solomon Yakobson

Good catch.

Interesting to see the expand_sql from @User_H3J7U on this one - the lead and lag conversions to nth_value(xxx,1) look totally symmetrical so one might expect their perfomance to be identical, but it looks as if Oracle must be doing something like stepping through rows for one but copying out the required range and then walking it for the other.

Do you have a MOS Doc Id that mentions this?

Regards

Jonathan Lewis