Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, cosimo.

Asked: February 23, 2016 - 8:49 pm UTC

Last updated: March 06, 2019 - 2:22 am UTC

Version: 11?

Viewed 1000+ times

You Asked

Hello everyone!

I have a puzzle that has stumped me. I have played with variations of nested selects, hierarchical queries, analytic functions, and have gotten close to what I need, but not all the way there.
I am sure that I am missing something easy here.

The data organized by BLOCKID. Inside that BLOCKID it is sorted by SCHED_DEP_TIME and then by ID. There are other fiels, but these are all that is needed for this question.
I would like to get (sample is below) is the same data back with the next ID and its time in the same BLOCKID by time.

Thanks for any help!


Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9434281',to_timestamp('09-FEB-16 03.58.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9434281',to_timestamp('09-FEB-16 04.09.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421120',to_timestamp('09-FEB-16 04.10.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421120',to_timestamp('09-FEB-16 04.17.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421120',to_timestamp('09-FEB-16 04.40.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421120',to_timestamp('09-FEB-16 04.45.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421120',to_timestamp('09-FEB-16 04.53.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421223',to_timestamp('09-FEB-16 05.10.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421223',to_timestamp('09-FEB-16 05.18.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421223',to_timestamp('09-FEB-16 05.50.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421223',to_timestamp('09-FEB-16 05.57.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421223',to_timestamp('09-FEB-16 06.08.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 06.27.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 06.39.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 06.47.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 07.05.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 07.13.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421073',to_timestamp('09-FEB-16 07.27.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421224',to_timestamp('09-FEB-16 07.48.00.000000000 AM'),'001HL001');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421224',to_timestamp('09-FEB-16 07.57.00.000000000 AM'),'001HL001');

Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421933',to_timestamp('09-FEB-16 08.24.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421933',to_timestamp('09-FEB-16 08.32.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421933',to_timestamp('09-FEB-16 08.42.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421933',to_timestamp('09-FEB-16 09.10.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9422613',to_timestamp('09-FEB-16 09.10.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9422613',to_timestamp('09-FEB-16 09.12.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421132',to_timestamp('09-FEB-16 09.28.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421132',to_timestamp('09-FEB-16 09.29.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421132',to_timestamp('09-FEB-16 09.54.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421132',to_timestamp('09-FEB-16 09.56.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421952',to_timestamp('09-FEB-16 10.23.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421952',to_timestamp('09-FEB-16 10.26.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9422609',to_timestamp('09-FEB-16 10.34.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421126',to_timestamp('09-FEB-16 10.48.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421126',to_timestamp('09-FEB-16 10.49.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421126',to_timestamp('09-FEB-16 11.20.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421126',to_timestamp('09-FEB-16 11.26.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421126',to_timestamp('09-FEB-16 11.38.00.000000000 AM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421958',to_timestamp('09-FEB-16 12.10.00.000000000 PM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421958',to_timestamp('09-FEB-16 12.20.00.000000000 PM'),'001HL002');
Insert into EXPORT_TABLE (ID,SCHED_DEP_TIME,BLOCKID) values ('9421958',to_timestamp('09-FEB-16 12.26.00.000000000 PM'),'001HL002');


The desired output is as follows:

ID, SCHED_DEP_TIME, BLOCKID,NEXTID,NEXTIDTIME

where NEXTID is the next distinct ID from withing the same blockID, ordered by SCHED_DEP_TIME
and NEXTIDTIME is that NEXTID's SCHED_DEP_TIME


like this:


9434281,09-FEB-16 03.58.00.000000000 AM,001HL001,9421120,09-FEB-16 04.10.00.000000000 AM
9434281,09-FEB-16 04.09.00.000000000 AM,001HL001,9421120,09-FEB-16 04.10.00.000000000 AM
9421120,09-FEB-16 04.10.00.000000000 AM,001HL001,9421223,09-FEB-16 05.10.00.000000000 AM
9421120,09-FEB-16 04.17.00.000000000 AM,001HL001,9421223,09-FEB-16 05.10.00.000000000 AM
9421120,09-FEB-16 04.40.00.000000000 AM,001HL001,9421223,09-FEB-16 05.10.00.000000000 AM
9421120,09-FEB-16 04.45.00.000000000 AM,001HL001,9421223,09-FEB-16 05.10.00.000000000 AM
9421120,09-FEB-16 04.53.00.000000000 AM,001HL001,9421223,09-FEB-16 05.10.00.000000000 AM
9421223,09-FEB-16 05.10.00.000000000 AM,001HL001,9421073,09-FEB-16 06.27.00.000000000 AM
9421223,09-FEB-16 05.18.00.000000000 AM,001HL001,9421073,09-FEB-16 06.27.00.000000000 AM
9421223,09-FEB-16 05.50.00.000000000 AM,001HL001,9421073,09-FEB-16 06.27.00.000000000 AM
9421223,09-FEB-16 05.57.00.000000000 AM,001HL001,9421073,09-FEB-16 06.27.00.000000000 AM
9421223,09-FEB-16 06.08.00.000000000 AM,001HL001,9421073,09-FEB-16 06.27.00.000000000 AM
9421073,09-FEB-16 06.27.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421073,09-FEB-16 06.39.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421073,09-FEB-16 06.47.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421073,09-FEB-16 07.05.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421073,09-FEB-16 07.13.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421073,09-FEB-16 07.27.00.000000000 AM,001HL001,9421224,09-FEB-16 07.48.00.000000000 AM
9421224,09-FEB-16 07.48.00.000000000 AM,001HL001,null,null
9421224,09-FEB-16 07.57.00.000000000 AM,001HL001,null,null

9421933,09-FEB-16 08.24.00.000000000 AM,001HL002,9422613,09-FEB-16 09.10.00.000000000 AM
9421933,09-FEB-16 08.32.00.000000000 AM,001HL002,9422613,09-FEB-16 09.10.00.000000000 AM
9421933,09-FEB-16 08.42.00.000000000 AM,001HL002,9422613,09-FEB-16 09.10.00.000000000 AM
9421933,09-FEB-16 09.10.00.000000000 AM,001HL002,9422613,09-FEB-16 09.10.00.000000000 AM
9422613,09-FEB-16 09.10.00.000000000 AM,001HL002,9421132,09-FEB-16 09.28.00.000000000 AM
9422613,09-FEB-16 09.12.00.000000000 AM,001HL002,9421132,09-FEB-16 09.28.00.000000000 AM
9421132,09-FEB-16 09.28.00.000000000 AM,001HL002,9421952,09-FEB-16 10.23.00.000000000 AM
9421132,09-FEB-16 09.29.00.000000000 AM,001HL002,9421952,09-FEB-16 10.23.00.000000000 AM
9421132,09-FEB-16 09.54.00.000000000 AM,001HL002,9421952,09-FEB-16 10.23.00.000000000 AM
9421132,09-FEB-16 09.56.00.000000000 AM,001HL002,9421952,09-FEB-16 10.23.00.000000000 AM
9421952,09-FEB-16 10.23.00.000000000 AM,001HL002,9422609,09-FEB-16 10.34.00.000000000 AM
9421952,09-FEB-16 10.26.00.000000000 AM,001HL002,9422609,09-FEB-16 10.34.00.000000000 AM
9422609,09-FEB-16 10.34.00.000000000 AM,001HL002,9421126,09-FEB-16 10.48.00.000000000 AM
9421126,09-FEB-16 10.48.00.000000000 AM,001HL002,9421958,09-FEB-16 12.10.00.000000000 PM
9421126,09-FEB-16 10.49.00.000000000 AM,001HL002,9421958,09-FEB-16 12.10.00.000000000 PM
9421126,09-FEB-16 11.20.00.000000000 AM,001HL002,9421958,09-FEB-16 12.10.00.000000000 PM
9421126,09-FEB-16 11.26.00.000000000 AM,001HL002,9421958,09-FEB-16 12.10.00.000000000 PM
9421126,09-FEB-16 11.38.00.000000000 AM,001HL002,9421958,09-FEB-16 12.10.00.000000000 PM
9421958,09-FEB-16 12.10.00.000000000 PM,001HL002,null,null
9421958,09-FEB-16 12.20.00.000000000 PM,001HL002,null,null
9421958,09-FEB-16 12.26.00.000000000 PM,001HL002,null,null

and Connor said...

Is this what you're after ?

SQL> with t as (
  2  select
  3    ID,
  4    min(SCHED_DEP_TIME) min_sched,
  5    max(SCHED_DEP_TIME) max_sched,
  6    BLOCKID
  7  from export_table
  8  group by id,blockid
  9  ),
 10  next_Details as (
 11  select ID,
 12    BLOCKID,
 13    min_sched,
 14    max_sched,
 15    lead(id) over (partition by blockid order by min_sched ) as next_id,
 16    lead(min_sched) over ( partition by blockid order by min_sched ) as next_sched
 17  from  t
 18  )
 19  select e.ID,
 20    e.BLOCKID,
 21    e.SCHED_DEP_TIME,
 22    n.next_id,
 23    n.next_sched
 24  from  next_Details n,
 25    export_table e
 26  where n.id = e.id
 27  and   n.blockid = e.blockid
 28  and   e.SCHED_DEP_TIME between n.min_sched and n.max_sched
 29  order by BLOCKID, SCHED_DEP_TIME;

ID                   BLOCKID              SCHED_DEP_TIME                           NEXT_ID           NEXT_SCHED
-------------------- -------------------- ---------------------------------------- -------------------- --------------------------
----
9434281              001HL001             09-FEB-16 03.58.00.000000 AM             9421120           09-FEB-16 04.10.00.000000 AM
9434281              001HL001             09-FEB-16 04.09.00.000000 AM             9421120           09-FEB-16 04.10.00.000000 AM
9421120              001HL001             09-FEB-16 04.10.00.000000 AM             9421223           09-FEB-16 05.10.00.000000 AM
9421120              001HL001             09-FEB-16 04.17.00.000000 AM             9421223           09-FEB-16 05.10.00.000000 AM
9421120              001HL001             09-FEB-16 04.40.00.000000 AM             9421223           09-FEB-16 05.10.00.000000 AM
9421120              001HL001             09-FEB-16 04.45.00.000000 AM             9421223           09-FEB-16 05.10.00.000000 AM
9421120              001HL001             09-FEB-16 04.53.00.000000 AM             9421223           09-FEB-16 05.10.00.000000 AM
9421223              001HL001             09-FEB-16 05.10.00.000000 AM             9421073           09-FEB-16 06.27.00.000000 AM
9421223              001HL001             09-FEB-16 05.18.00.000000 AM             9421073           09-FEB-16 06.27.00.000000 AM
9421223              001HL001             09-FEB-16 05.50.00.000000 AM             9421073           09-FEB-16 06.27.00.000000 AM
9421223              001HL001             09-FEB-16 05.57.00.000000 AM             9421073           09-FEB-16 06.27.00.000000 AM
9421223              001HL001             09-FEB-16 06.08.00.000000 AM             9421073           09-FEB-16 06.27.00.000000 AM
9421073              001HL001             09-FEB-16 06.27.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421073              001HL001             09-FEB-16 06.39.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421073              001HL001             09-FEB-16 06.47.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421073              001HL001             09-FEB-16 07.05.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421073              001HL001             09-FEB-16 07.13.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421073              001HL001             09-FEB-16 07.27.00.000000 AM             9421224           09-FEB-16 07.48.00.000000 AM
9421224              001HL001             09-FEB-16 07.48.00.000000 AM
9421224              001HL001             09-FEB-16 07.57.00.000000 AM
9421933              001HL002             09-FEB-16 08.24.00.000000 AM             9422613           09-FEB-16 09.10.00.000000 AM
9421933              001HL002             09-FEB-16 08.32.00.000000 AM             9422613           09-FEB-16 09.10.00.000000 AM
9421933              001HL002             09-FEB-16 08.42.00.000000 AM             9422613           09-FEB-16 09.10.00.000000 AM
9421933              001HL002             09-FEB-16 09.10.00.000000 AM             9422613           09-FEB-16 09.10.00.000000 AM
9422613              001HL002             09-FEB-16 09.10.00.000000 AM             9421132           09-FEB-16 09.28.00.000000 AM
9422613              001HL002             09-FEB-16 09.12.00.000000 AM             9421132           09-FEB-16 09.28.00.000000 AM
9421132              001HL002             09-FEB-16 09.28.00.000000 AM             9421952           09-FEB-16 10.23.00.000000 AM
9421132              001HL002             09-FEB-16 09.29.00.000000 AM             9421952           09-FEB-16 10.23.00.000000 AM
9421132              001HL002             09-FEB-16 09.54.00.000000 AM             9421952           09-FEB-16 10.23.00.000000 AM
9421132              001HL002             09-FEB-16 09.56.00.000000 AM             9421952           09-FEB-16 10.23.00.000000 AM
9421952              001HL002             09-FEB-16 10.23.00.000000 AM             9422609           09-FEB-16 10.34.00.000000 AM
9421952              001HL002             09-FEB-16 10.26.00.000000 AM             9422609           09-FEB-16 10.34.00.000000 AM
9422609              001HL002             09-FEB-16 10.34.00.000000 AM             9421126           09-FEB-16 10.48.00.000000 AM
9421126              001HL002             09-FEB-16 10.48.00.000000 AM             9421958           09-FEB-16 12.10.00.000000 PM
9421126              001HL002             09-FEB-16 10.49.00.000000 AM             9421958           09-FEB-16 12.10.00.000000 PM
9421126              001HL002             09-FEB-16 11.20.00.000000 AM             9421958           09-FEB-16 12.10.00.000000 PM
9421126              001HL002             09-FEB-16 11.26.00.000000 AM             9421958           09-FEB-16 12.10.00.000000 PM
9421126              001HL002             09-FEB-16 11.38.00.000000 AM             9421958           09-FEB-16 12.10.00.000000 PM
9421958              001HL002             09-FEB-16 12.10.00.000000 PM
9421958              001HL002             09-FEB-16 12.20.00.000000 PM
9421958              001HL002             09-FEB-16 12.26.00.000000 PM

41 rows selected.


I collapsed the data into 1 row per ID, so I can use lead to get the logical "next" ID, and the join back to get the original rows.

Rating

  (6 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Analytics and Model

Rajeshwaran Jeyabal, February 24, 2016 - 7:02 am UTC 

rajesh@ORA11G> set feedback off
rajesh@ORA11G> create table export_table(id int,SCHED_DEP_TIME timestamp,BLOCKID varchar2(10));
rajesh@ORA11G> Insert into EXPORT_TABLE values (9434281,to_timestamp('09-FEB-16 03.58.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9434281,to_timestamp('09-FEB-16 04.09.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421120,to_timestamp('09-FEB-16 04.10.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421120,to_timestamp('09-FEB-16 04.17.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421120,to_timestamp('09-FEB-16 04.40.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421120,to_timestamp('09-FEB-16 04.45.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421120,to_timestamp('09-FEB-16 04.53.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421223,to_timestamp('09-FEB-16 05.10.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421223,to_timestamp('09-FEB-16 05.18.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421223,to_timestamp('09-FEB-16 05.50.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421223,to_timestamp('09-FEB-16 05.57.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421223,to_timestamp('09-FEB-16 06.08.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 06.27.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 06.39.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 06.47.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 07.05.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 07.13.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421073,to_timestamp('09-FEB-16 07.27.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421224,to_timestamp('09-FEB-16 07.48.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421224,to_timestamp('09-FEB-16 07.57.00.000000000 AM'),'001HL001');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421933,to_timestamp('09-FEB-16 08.24.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421933,to_timestamp('09-FEB-16 08.32.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421933,to_timestamp('09-FEB-16 08.42.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421933,to_timestamp('09-FEB-16 09.10.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9422613,to_timestamp('09-FEB-16 09.10.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9422613,to_timestamp('09-FEB-16 09.12.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421132,to_timestamp('09-FEB-16 09.28.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421132,to_timestamp('09-FEB-16 09.29.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421132,to_timestamp('09-FEB-16 09.54.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421132,to_timestamp('09-FEB-16 09.56.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421952,to_timestamp('09-FEB-16 10.23.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421952,to_timestamp('09-FEB-16 10.26.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9422609,to_timestamp('09-FEB-16 10.34.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421126,to_timestamp('09-FEB-16 10.48.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421126,to_timestamp('09-FEB-16 10.49.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421126,to_timestamp('09-FEB-16 11.20.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421126,to_timestamp('09-FEB-16 11.26.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421126,to_timestamp('09-FEB-16 11.38.00.000000000 AM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421958,to_timestamp('09-FEB-16 12.10.00.000000000 PM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421958,to_timestamp('09-FEB-16 12.20.00.000000000 PM'),'001HL002');
rajesh@ORA11G> Insert into EXPORT_TABLE values (9421958,to_timestamp('09-FEB-16 12.26.00.000000000 PM'),'001HL002');
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G>


Model

rajesh@ORA11G> column sched_dep_time format a35
rajesh@ORA11G> column next_timestmp format a35
rajesh@ORA11G> select id,sched_dep_time,blockid,next_id,next_tstmp
  2  from export_table t
  3  model
  4    partition by (blockid)
  5    dimension by (row_number() over(partition by blockid order by sched_dep_time desc,id desc) r)
  6    measures( id,sched_dep_time, cast(null as int) next_id, cast(null as timestamp) next_tstmp)
  7    rules
  8    ( next_id[r>1] order by r = case when id[cv(r)] = id[cv(r)-1]
  9                                        then next_id[cv(r)-1]
 10                                        else id[cv(r)-1] end ,
 11      next_tstmp[r>1] order by r = case when id[cv(r)] = id[cv(r)-1]
 12                                        then next_tstmp[cv(r)-1]
 13                                        else sched_dep_time[cv(r)-1] end )
 14  order by blockid,r desc
 15  /

        ID SCHED_DEP_TIME                      BLOCKID       NEXT_ID NEXT_TSTMP
---------- ----------------------------------- ---------- ---------- ----------------------------------------------------------------
   9434281 09-FEB-16 03.58.00.000000 AM        001HL001      9421120 09-FEB-16 04.10.00.000000 AM
   9434281 09-FEB-16 04.09.00.000000 AM        001HL001      9421120 09-FEB-16 04.10.00.000000 AM
   9421120 09-FEB-16 04.10.00.000000 AM        001HL001      9421223 09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.17.00.000000 AM        001HL001      9421223 09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.40.00.000000 AM        001HL001      9421223 09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.45.00.000000 AM        001HL001      9421223 09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.53.00.000000 AM        001HL001      9421223 09-FEB-16 05.10.00.000000 AM
   9421223 09-FEB-16 05.10.00.000000 AM        001HL001      9421073 09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.18.00.000000 AM        001HL001      9421073 09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.50.00.000000 AM        001HL001      9421073 09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.57.00.000000 AM        001HL001      9421073 09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 06.08.00.000000 AM        001HL001      9421073 09-FEB-16 06.27.00.000000 AM
   9421073 09-FEB-16 06.27.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 06.39.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 06.47.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.05.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.13.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.27.00.000000 AM        001HL001      9421224 09-FEB-16 07.48.00.000000 AM
   9421224 09-FEB-16 07.48.00.000000 AM        001HL001
   9421224 09-FEB-16 07.57.00.000000 AM        001HL001
   9421933 09-FEB-16 08.24.00.000000 AM        001HL002      9422613 09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 08.32.00.000000 AM        001HL002      9422613 09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 08.42.00.000000 AM        001HL002      9422613 09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 09.10.00.000000 AM        001HL002      9422613 09-FEB-16 09.10.00.000000 AM
   9422613 09-FEB-16 09.10.00.000000 AM        001HL002      9421132 09-FEB-16 09.28.00.000000 AM
   9422613 09-FEB-16 09.12.00.000000 AM        001HL002      9421132 09-FEB-16 09.28.00.000000 AM
   9421132 09-FEB-16 09.28.00.000000 AM        001HL002      9421952 09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.29.00.000000 AM        001HL002      9421952 09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.54.00.000000 AM        001HL002      9421952 09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.56.00.000000 AM        001HL002      9421952 09-FEB-16 10.23.00.000000 AM
   9421952 09-FEB-16 10.23.00.000000 AM        001HL002      9422609 09-FEB-16 10.34.00.000000 AM
   9421952 09-FEB-16 10.26.00.000000 AM        001HL002      9422609 09-FEB-16 10.34.00.000000 AM
   9422609 09-FEB-16 10.34.00.000000 AM        001HL002      9421126 09-FEB-16 10.48.00.000000 AM
   9421126 09-FEB-16 10.48.00.000000 AM        001HL002      9421958 09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 10.49.00.000000 AM        001HL002      9421958 09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.20.00.000000 AM        001HL002      9421958 09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.26.00.000000 AM        001HL002      9421958 09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.38.00.000000 AM        001HL002      9421958 09-FEB-16 12.10.00.000000 PM
   9421958 09-FEB-16 12.10.00.000000 PM        001HL002
   9421958 09-FEB-16 12.20.00.000000 PM        001HL002
   9421958 09-FEB-16 12.26.00.000000 PM        001HL002

41 rows selected.


Analytics

rajesh@ORA11G> select id,sched_dep_time,blockid,
  2    last_value(next_id ignore nulls) over(partition by blockid
  3          order by sched_dep_time desc,id desc) as next_id,
  4    last_value(next_timestmp ignore nulls) over(partition by blockid
  5        order by sched_dep_time desc,id desc) as next_timestmp
  6  from (
  7  select t.* ,
  8      decode(id,lag(id) over(partition by blockid order by sched_dep_time desc,id desc),null,
  9            lag(id) over(partition by blockid order by sched_dep_time desc,id desc)) as next_id ,
 10      decode(id,lag(id) over(partition by blockid order by sched_dep_time desc,id desc),null,
 11            lag(sched_dep_time) over(partition by blockid order by sched_dep_time desc,id desc)) as next_timestmp
 12  from export_table t
 13        )
 14  order by blockid,sched_dep_time,id;
                                                                     
        ID SCHED_DEP_TIME                      BLOCKID    NEXT_ID     NEXT_TIMESTMP
---------- ----------------------------------- ---------- ----------- ----------------------------------
   9434281 09-FEB-16 03.58.00.000000 AM        001HL001   9421120     09-FEB-16 04.10.00.000000 AM
   9434281 09-FEB-16 04.09.00.000000 AM        001HL001   9421120     09-FEB-16 04.10.00.000000 AM
   9421120 09-FEB-16 04.10.00.000000 AM        001HL001   9421223     09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.17.00.000000 AM        001HL001   9421223     09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.40.00.000000 AM        001HL001   9421223     09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.45.00.000000 AM        001HL001   9421223     09-FEB-16 05.10.00.000000 AM
   9421120 09-FEB-16 04.53.00.000000 AM        001HL001   9421223     09-FEB-16 05.10.00.000000 AM
   9421223 09-FEB-16 05.10.00.000000 AM        001HL001   9421073     09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.18.00.000000 AM        001HL001   9421073     09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.50.00.000000 AM        001HL001   9421073     09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 05.57.00.000000 AM        001HL001   9421073     09-FEB-16 06.27.00.000000 AM
   9421223 09-FEB-16 06.08.00.000000 AM        001HL001   9421073     09-FEB-16 06.27.00.000000 AM
   9421073 09-FEB-16 06.27.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 06.39.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 06.47.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.05.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.13.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421073 09-FEB-16 07.27.00.000000 AM        001HL001   9421224     09-FEB-16 07.48.00.000000 AM
   9421224 09-FEB-16 07.48.00.000000 AM        001HL001              
   9421224 09-FEB-16 07.57.00.000000 AM        001HL001              
   9421933 09-FEB-16 08.24.00.000000 AM        001HL002   9422613     09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 08.32.00.000000 AM        001HL002   9422613     09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 08.42.00.000000 AM        001HL002   9422613     09-FEB-16 09.10.00.000000 AM
   9421933 09-FEB-16 09.10.00.000000 AM        001HL002   9422613     09-FEB-16 09.10.00.000000 AM
   9422613 09-FEB-16 09.10.00.000000 AM        001HL002   9421132     09-FEB-16 09.28.00.000000 AM
   9422613 09-FEB-16 09.12.00.000000 AM        001HL002   9421132     09-FEB-16 09.28.00.000000 AM
   9421132 09-FEB-16 09.28.00.000000 AM        001HL002   9421952     09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.29.00.000000 AM        001HL002   9421952     09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.54.00.000000 AM        001HL002   9421952     09-FEB-16 10.23.00.000000 AM
   9421132 09-FEB-16 09.56.00.000000 AM        001HL002   9421952     09-FEB-16 10.23.00.000000 AM
   9421952 09-FEB-16 10.23.00.000000 AM        001HL002   9422609     09-FEB-16 10.34.00.000000 AM
   9421952 09-FEB-16 10.26.00.000000 AM        001HL002   9422609     09-FEB-16 10.34.00.000000 AM
   9422609 09-FEB-16 10.34.00.000000 AM        001HL002   9421126     09-FEB-16 10.48.00.000000 AM
   9421126 09-FEB-16 10.48.00.000000 AM        001HL002   9421958     09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 10.49.00.000000 AM        001HL002   9421958     09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.20.00.000000 AM        001HL002   9421958     09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.26.00.000000 AM        001HL002   9421958     09-FEB-16 12.10.00.000000 PM
   9421126 09-FEB-16 11.38.00.000000 AM        001HL002   9421958     09-FEB-16 12.10.00.000000 PM
   9421958 09-FEB-16 12.10.00.000000 PM        001HL002              
   9421958 09-FEB-16 12.20.00.000000 PM        001HL002              
   9421958 09-FEB-16 12.26.00.000000 PM        001HL002

41 rows selected.

rajesh@ORA11G>

Learning something new

Cosimo, February 24, 2016 - 7:41 pm UTC

The solution to my question worked well. It used the WITH statement that I had basically ignored until now. I have used subqueries before but always as some difficult to read join usually. I will make sure to remember the WITH statement from now on. Thanks!
Chris Saxon
February 25, 2016 - 3:50 am UTC

Glad we could help.

If your 'export-table' is huge, consider Rajesh's analytic option for a single pass

Analytics Vs Model

Rajeshwaran Jeyabal, February 25, 2016 - 9:04 am UTC

Team,

I tried bench marking both Analytic Vs Model approach. But model seem to be more CPU, would you help us to understand why it is? 


select id,sched_dep_time,blockid,
     last_value(next_id ignore nulls) over(partition by blockid
           order by sched_dep_time desc,id desc) as next_id,
     last_value(next_timestmp ignore nulls) over(partition by blockid
         order by sched_dep_time desc,id desc) as next_timestmp
   from (
   select t.* ,
       decode(id,lag(id) over(partition by blockid order by sched_dep_time desc,id desc),null,
             lag(id) over(partition by blockid order by sched_dep_time desc,id desc)) as next_id ,
       decode(id,lag(id) over(partition by blockid order by sched_dep_time desc,id desc),null,
             lag(sched_dep_time) over(partition by blockid order by sched_dep_time desc,id desc))as next_timestmp
   from export_table t
         )
   order by blockid,sched_dep_time,id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1121      0.60       0.61          0        673          0      167936
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1123      0.60       0.61          0        673          0      167936

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    167936     167936     167936  SORT ORDER BY (cr=673 pr=0 pw=0 time=625628 us cost=9783 size=12091392 card=167936)
    167936     167936     167936   WINDOW SORT (cr=673 pr=0 pw=0 time=5038223 us cost=9783 size=12091392 card=167936)
    167936     167936     167936    VIEW  (cr=673 pr=0 pw=0 time=403956 us cost=2061 size=12091392 card=167936)
    167936     167936     167936     WINDOW SORT (cr=673 pr=0 pw=0 time=298864 us cost=2061 size=4366336 card=167936)
    167936     167936     167936      TABLE ACCESS FULL EXPORT_TABLE (cr=673 pr=0 pw=0 time=23650 us cost=358 size=4366336 card=167936)



select id,sched_dep_time,blockid,next_id,next_tstmp
   from export_table t
   model
     partition by (blockid)
     dimension by (row_number() over(partition by blockid order by sched_dep_time desc,id desc) r)
     measures( id,sched_dep_time, cast(null as int) next_id, cast(null as timestamp) next_tstmp)
     rules
     ( next_id[r>1] order by r = case when id[cv(r)] = id[cv(r)-1]
                                         then next_id[cv(r)-1]
                                         else id[cv(r)-1] end ,
       next_tstmp[r>1] order by r = case when id[cv(r)] = id[cv(r)-1]
                                         then next_tstmp[cv(r)-1]
                                         else sched_dep_time[cv(r)-1] end )
   order by blockid,sched_dep_time,id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1121      1.51       1.54          0        673          0      167936
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1123      1.51       1.54          0        673          0      167936

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    167936     167936     167936  SORT ORDER BY (cr=673 pr=0 pw=0 time=1548144 us cost=3765 size=4366336 card=167936)
    167936     167936     167936   SQL MODEL ORDERED (cr=673 pr=0 pw=0 time=66480104 us cost=3765 size=4366336 card=167936)
    167936     167936     167936    WINDOW SORT (cr=673 pr=0 pw=0 time=234877 us cost=3765 size=4366336 card=167936)
    167936     167936     167936     TABLE ACCESS FULL EXPORT_TABLE (cr=673 pr=0 pw=0 time=36799 us cost=358 size=4366336 card=167936)

Connor McDonald
February 25, 2016 - 11:47 am UTC

I ran both queries with the MONITOR hint, and had a look through the SQL Monitor report (format=ACTIVE).

80% of the cpu is consumed for the "SQL MODEL ORDERED" rowsource execution line.

Since the logical IO's (the other main cause for cpu) for both queries is the same, we can reasonably assume that the extra CPU is the processing of the model clause rules.

Hope this helps

SQL Model Ordered - not only CPU but also PIO.

Rajeshwaran Jeyabal, February 25, 2016 - 12:02 pm UTC

It is not only the "SQL MODEL ORDERED" is CPU intensive, but also do more PIO, from the below trace pr=4987944 from sql model ordered is most producer of PIO - any reason why does it produces more PIO?

I was under the impression that MODEL are pretty cool than Analytic but that not the case here.

select owner,object_name,object_type,object_id,id,
         last_value(rn1 ignore nulls) over(partition by owner
                            order by object_id desc,id desc) next_object_id,
         last_value(rn2 ignore nulls) over(partition by owner
                            order by object_id desc,id desc) next_object_name
from (
select owner,object_name,object_type,object_id,id,
  case when lag(object_id) over(partition by owner
                    order by object_id desc,id desc) <> object_id
      then lag(object_id) over(partition by owner
                    order by object_id desc,id desc) end rn1 ,
  case when lag(object_id) over(partition by owner
                    order by object_id desc,id desc) <> object_id
      then lag(object_name) over(partition by owner
                    order by object_id desc,id desc) end rn2
from big_table
      )
order by owner,object_id,id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3335      3.18      37.64      25326       7637         12      500000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3337      3.19      37.64      25326       7639         12      500000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    500000     500000     500000  SORT ORDER BY (cr=7637 pr=25326 pw=17230 time=41233689 us cost=51130 size=56057222 card=555022)
    500000     500000     500000   WINDOW SORT (cr=7637 pr=19709 pw=11613 time=7818710 us cost=51130 size=56057222 card=555022)
    500000     500000     500000    VIEW  (cr=7637 pr=15018 pw=7386 time=5631951 us cost=16667 size=56057222 card=555022)
    500000     500000     500000     WINDOW SORT (cr=7637 pr=15018 pw=7386 time=5281676 us cost=16667 size=39406562 card=555022)
    500000     500000     500000      TABLE ACCESS FULL BIG_TABLE (cr=7637 pr=7632 pw=0 time=509828 us cost=4043 size=39406562 card=555022)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    3335        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                              162        0.14          1.14
  direct path write temp                        242        1.46          4.75
  direct path read temp                        1940        1.53         28.27
  SQL*Net message from client                  3335        0.00          1.94
********************************************************************************

select *
from big_table
model
  partition by (owner)
  dimension by ( row_number() over(partition by owner
                      order by object_id desc,id desc) r )
  measures( object_name,object_type,object_id,id,
                cast(null as number) as next_object_id,
                cast(null as varchar2(30)) as next_object_name)
  rules
  ( next_object_id[r>1] order by r = case when object_id[cv()] = object_id[cv()-1] then
                                            next_object_id[cv()-1]
                                            else object_id[cv()-1] end ,
   next_object_name[r>1] order by r = case when object_id[cv()] = object_id[cv()-1] then
                                            next_object_name[cv()-1]
                                            else object_name[cv()-1] end )
order by owner,object_id,id 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3335     26.53     233.49    4993932       7637          8      500000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3337     26.53     233.49    4993932       7639          8      500000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    500000     500000     500000  SORT ORDER BY (cr=7637 pr=4993932 pw=19387 time=232747373 us cost=29290 size=39406562 card=555022)
    500000     500000     500000   SQL MODEL ORDERED (cr=7637 pr=4987944 pw=13399 time=6681252 us cost=29290 size=39406562 card=555022)
    500000     500000     500000    WINDOW SORT (cr=7637 pr=11328 pw=3696 time=8210605 us cost=29290 size=39406562 card=555022)
    500000     500000     500000     TABLE ACCESS FULL BIG_TABLE (cr=7637 pr=7632 pw=0 time=400076 us cost=4043 size=39406562 card=555022)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    3335        0.00          0.00
  direct path read                              138        0.18          2.34
  direct path write temp                        343        0.28          2.95
  direct path read temp                      160872        0.23        200.42
  SQL*Net message from client                  3335        0.00          3.71



********************************************************************************
rajesh@ORA11G> @printtbl "select * from dba_temp_files"
FILE_NAME                     : "D:\APP\VNAMEIT\ORADATA\ORA11G\TEMP01.DBF"
FILE_ID                       : "1"
TABLESPACE_NAME               : "TEMP"
BYTES                         : "2285895680"
BLOCKS                        : "279040"
STATUS                        : "ONLINE"
RELATIVE_FNO                  : "1"
AUTOEXTENSIBLE                : "YES"
MAXBYTES                      : "34359721984"
MAXBLOCKS                     : "4194302"
INCREMENT_BY                  : "80"
USER_BYTES                    : "2284847104"
USER_BLOCKS                   : "278912"
-----------------

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              75
Full Blocks        .....................           7,557
Total Blocks............................           7,840
Total Bytes.............................      64,225,280
Total MBytes............................              61
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               6
Last Used Ext BlockId...................           9,088
Last Used Block.........................             112

PL/SQL procedure successfully completed.

rajesh@ORA11G> @printtbl "select * from v$sort_usage"
USERNAME                      : "RAJESH"
USER                          : "RAJESH"
SESSION_ADDR                  : "000007FF1AF7F2D0"
SESSION_NUM                   : "293"
SQLADDR                       : "000007FF0E2FF030"
SQLHASH                       : "1029988163"
SQL_ID                        : "9babjv8yq8ru3"
TABLESPACE                    : "TEMP"
CONTENTS                      : "TEMPORARY"
SEGTYPE                       : "DATA"
SEGFILE#                      : "201"
SEGBLK#                       : "276480"
EXTENTS                       : "1"
BLOCKS                        : "128"
SEGRFNO#                      : "1"
-----------------
USERNAME                      : "RAJESH"
USER                          : "RAJESH"
SESSION_ADDR                  : "000007FF1AF7F2D0"
SESSION_NUM                   : "293"
SQLADDR                       : "000007FF0E2FF030"
SQLHASH                       : "1029988163"
SQL_ID                        : "9babjv8yq8ru3"
TABLESPACE                    : "TEMP"
CONTENTS                      : "TEMPORARY"
SEGTYPE                       : "HASH"
SEGFILE#                      : "201"
SEGBLK#                       : "274944"
EXTENTS                       : "75"
BLOCKS                        : "9600"
SEGRFNO#                      : "1"
-----------------

PL/SQL procedure successfully completed.

rajesh@ORA11G>


Script to create "BIG_TABLE" is (in case needed)

set echo on;
drop table big_table purge;

select userenv('sid') from dual;

create table big_table 
parallel 4
nologging as
select a.*,rownum as id
from all_objects a ;

alter session enable parallel dml;

declare
  l_rows int := 0;
begin
  
  dbms_application_info.set_module('Big_Table_Insert','Big_Table_Insert');
  select max(id)
  into l_rows
  from big_table;
  
  while ( l_rows <= &&1 )
  loop
 dbms_application_info.set_client_info('Num_of_rows ='||l_rows);
    insert /*+ append */ into big_table
 ( owner , object_name, subobject_name, object_id,
 data_object_id, object_type, created, last_ddl_time,
 timestamp, status, temporary, generated, secondary,id,namespace,edition_name )
 
    select owner , object_name, subobject_name, object_id,
 data_object_id, object_type, created, last_ddl_time,
 timestamp, status, temporary, generated, secondary, rownum + l_rows,namespace,edition_name
    from big_table
    where rownum <= ( &&1 - l_rows );
    
 EXIT when sql%rowcount = 0;
 
    l_rows := l_rows + sql%rowcount;
    commit;
 dbms_application_info.set_client_info(NULL);
  end loop;
  
  commit;
  dbms_application_info.set_client_info(NULL); 
  dbms_application_info.set_module(NULL,NULL);
end;
/


exec dbms_application_info.set_client_info('Building_Index_big_idx_01');

create index big_idx_01 on big_table(id) parallel 4 nologging;

exec dbms_application_info.set_client_info(NULL);

exec dbms_application_info.set_module(NULL,NULL);

alter table big_table noparallel;
alter index big_idx_01 noparallel;

set echo off;

Connor McDonald
February 29, 2016 - 12:57 am UTC

The autotrace data suggests a possible cause


-- analytic

Statistics
----------------------------------------------------
         72  recursive calls
          0  db block gets
       8031  consistent gets
       7953  physical reads
          0  redo size
   16958584  bytes sent via SQL*Net to client
     367214  bytes received via SQL*Net from client
      33335  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
     500000  rows processed

-- model

Statistics
---------------------------------------------------------
        238  recursive calls
          0  db block gets
       8129  consistent gets
      15641  physical reads
          0  redo size
   19370788  bytes sent via SQL*Net to client
     367214  bytes received via SQL*Net from client
      33335  SQL*Net roundtrips to/from client
         35  sorts (memory)
          0  sorts (disk)
     500000  rows processed


Look at the *number* of sorts performed.

What is also quite interesting, is that the autotrace for model says "0 sorts to disk" - I wonder if its not including model-style sorts, because the trace data suggests otherwise:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    500000     500000     500000  SORT ORDER BY (cr=7957 pr=15641 pw=7688 time=6659408 us cost=2784 size=4713250 card=94265)
    500000     500000     500000   SQL MODEL ORDERED (cr=7957 pr=15641 pw=7688 time=6234442 us cost=2784 size=4713250 card=94265)
    500000     500000     500000    WINDOW SORT (cr=7957 pr=7953 pw=0 time=546758 us cost=2784 size=4713250 card=94265)
    500000     500000     500000     TABLE ACCESS FULL BIG_TABLE (cr=7957 pr=7953 pw=0 time=88461 us cost=460 size=4713250 card=94265)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   33335        0.00          0.02
  direct path read                               12        0.00          0.00
  Disk file operations I/O                        1        0.01          0.01
  direct path write temp                        228        0.03          2.08
  direct path read temp                         245        0.02          0.34
  SQL*Net message from client                 33335        0.00          1.44
********************************************************************************

Benchmarking

Cosimo, February 25, 2016 - 4:06 pm UTC

Thank you Rajesh!

I will definitely be looking closer at your analytics solution.

using pattern matching

Rajeshwaran, Jeyabal, March 05, 2019 - 2:18 pm UTC

was trying to apply pattern matching clause to solve this and was able to get them done using pattern navigation clause like this.

demo@ORA12C> select *
  2  from export_table
  3  match_recognize(
  4    partition by blockid
  5    order by sched_dep_time desc ,id desc
  6    measures
  7      prev(b1.id) as next_id,
  8      prev(b1.sched_dep_time) as next_dep_time
  9    all rows per match
 10    pattern (b1 b2*)
 11    define
 12      b2 as id = b1.id )
 13  order by sched_dep_time,id ;

BLOCKID    SCHED_DEP_TIME                         ID    NEXT_ID NEXT_DEP_TIME
---------- ------------------------------ ---------- ---------- ------------------------------
001HL001   09-FEB-16 03.58.00.000000 AM      9434281    9421120 09-FEB-16 04.10.00.000000 AM
001HL001   09-FEB-16 04.09.00.000000 AM      9434281    9421120 09-FEB-16 04.10.00.000000 AM
001HL001   09-FEB-16 04.10.00.000000 AM      9421120    9421223 09-FEB-16 05.10.00.000000 AM
001HL001   09-FEB-16 04.17.00.000000 AM      9421120    9421223 09-FEB-16 05.10.00.000000 AM
001HL001   09-FEB-16 04.40.00.000000 AM      9421120    9421223 09-FEB-16 05.10.00.000000 AM
001HL001   09-FEB-16 04.45.00.000000 AM      9421120    9421223 09-FEB-16 05.10.00.000000 AM
001HL001   09-FEB-16 04.53.00.000000 AM      9421120    9421223 09-FEB-16 05.10.00.000000 AM
001HL001   09-FEB-16 05.10.00.000000 AM      9421223    9421073 09-FEB-16 06.27.00.000000 AM
001HL001   09-FEB-16 05.18.00.000000 AM      9421223    9421073 09-FEB-16 06.27.00.000000 AM
001HL001   09-FEB-16 05.50.00.000000 AM      9421223    9421073 09-FEB-16 06.27.00.000000 AM
001HL001   09-FEB-16 05.57.00.000000 AM      9421223    9421073 09-FEB-16 06.27.00.000000 AM
001HL001   09-FEB-16 06.08.00.000000 AM      9421223    9421073 09-FEB-16 06.27.00.000000 AM
001HL001   09-FEB-16 06.27.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 06.39.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 06.47.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 07.05.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 07.13.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 07.27.00.000000 AM      9421073    9421224 09-FEB-16 07.48.00.000000 AM
001HL001   09-FEB-16 07.48.00.000000 AM      9421224
001HL001   09-FEB-16 07.57.00.000000 AM      9421224
001HL002   09-FEB-16 08.24.00.000000 AM      9421933    9422613 09-FEB-16 09.10.00.000000 AM
001HL002   09-FEB-16 08.32.00.000000 AM      9421933    9422613 09-FEB-16 09.10.00.000000 AM
001HL002   09-FEB-16 08.42.00.000000 AM      9421933    9422613 09-FEB-16 09.10.00.000000 AM
001HL002   09-FEB-16 09.10.00.000000 AM      9421933    9422613 09-FEB-16 09.10.00.000000 AM
001HL002   09-FEB-16 09.10.00.000000 AM      9422613    9421132 09-FEB-16 09.28.00.000000 AM
001HL002   09-FEB-16 09.12.00.000000 AM      9422613    9421132 09-FEB-16 09.28.00.000000 AM
001HL002   09-FEB-16 09.28.00.000000 AM      9421132    9421952 09-FEB-16 10.23.00.000000 AM
001HL002   09-FEB-16 09.29.00.000000 AM      9421132    9421952 09-FEB-16 10.23.00.000000 AM
001HL002   09-FEB-16 09.54.00.000000 AM      9421132    9421952 09-FEB-16 10.23.00.000000 AM
001HL002   09-FEB-16 09.56.00.000000 AM      9421132    9421952 09-FEB-16 10.23.00.000000 AM
001HL002   09-FEB-16 10.23.00.000000 AM      9421952    9422609 09-FEB-16 10.34.00.000000 AM
001HL002   09-FEB-16 10.26.00.000000 AM      9421952    9422609 09-FEB-16 10.34.00.000000 AM
001HL002   09-FEB-16 10.34.00.000000 AM      9422609    9421126 09-FEB-16 10.48.00.000000 AM
001HL002   09-FEB-16 10.48.00.000000 AM      9421126    9421958 09-FEB-16 12.10.00.000000 PM
001HL002   09-FEB-16 10.49.00.000000 AM      9421126    9421958 09-FEB-16 12.10.00.000000 PM
001HL002   09-FEB-16 11.20.00.000000 AM      9421126    9421958 09-FEB-16 12.10.00.000000 PM
001HL002   09-FEB-16 11.26.00.000000 AM      9421126    9421958 09-FEB-16 12.10.00.000000 PM
001HL002   09-FEB-16 11.38.00.000000 AM      9421126    9421958 09-FEB-16 12.10.00.000000 PM
001HL002   09-FEB-16 12.10.00.000000 PM      9421958
001HL002   09-FEB-16 12.20.00.000000 PM      9421958
001HL002   09-FEB-16 12.26.00.000000 PM      9421958

41 rows selected.

demo@ORA12C>

Connor McDonald
March 06, 2019 - 2:22 am UTC

nice stuff
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.