SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

LEAD and LAG taking hours on a very large table!

Sam_PApr 1 2022 — edited Apr 3 2022

Hello,
I have a source table (partitioned and indexed) with approx. 156 Million records. I am trying to create a target table using CTAS method by reading records from the source table with a simple SELECT query that does simple LEAD/LAG operations on a date-column and it is taking over 8 hrs!!
Upon searching online, one developer recommended to try the MATCH_RECOGNIZE technique as it is claimed to run faster than the Analytic SQL functions on very large tables. I have not tested this yet as I need help in converting the SQL query into the MATCH_RECOGNIZE statement, which would result in the same result-set.
CREATE TABLE TB_TARGET
PARALLEL 8
NOLOGGING
AS
SELECT /*+ PARALLEL_INDEX(8) PARALLEL(8) */
PAT_ID,
VISIT_DATE_DT,
ROW_NUMBER() OVER (PARTITION BY PAT_ID ORDER BY VISIT_DATE_DT) AS ROW_NUM,
ED_UCC_DT,
LAG(ED_UCC_DT IGNORE NULLS) OVER (PARTITION BY PAT_ID ORDER BY VISIT_DATE_DT) AS PREV_ED_UCC_DT,
LEAD(ED_UCC_DT IGNORE NULLS) OVER (PARTITION BY PAT_ID ORDER BY VISIT_DATE_DT) AS NEXT_ED_UCC_DT
FROM TB_SOURCE; --Very large table!

The result set for a single PAT_ID record (100123456) looks like this:-

Questions:-
1. How do I speed this query up when it runs over 156 Million records with multiple LEAD and LAG operations added into the above query on other columns in addition to the above column?
2. If the fastest solution is to use MATCH_RECOGNIZE, could you please help me with composing a SQL query statement such that it yields the same results as shown above (along with NULLs)?
3. Any other solution is welcomed as long as I can reduce the query execution time to an acceptable duration other than 8 hrs.
Thank you in advance.

This post has been answered by alvinder on Apr 6 2022

Jump to Answer

105967

You have to code it yourself. Check if there is no row returned and if yes set the variable to your desired value.
(just think about it, if there is NO ROW, how can a column be filled ie there is no column to be filled)

MichaelS

e.g.

SQL> var met number
SQL>
SQL> exec :met := 1

PL/SQL procedure successfully completed.

SQL>
SQL> with text_file as
  2  (
  3    select 1 criteria, 'some_text' text from dual
  4  )
  5  select nvl(text,'***empty') text from text_file t1, (select :met met from dual) where t1.criteria(+) = met
  6  /

TEXT
---------
some_text

SQL>
SQL> exec :met := 2

PL/SQL procedure successfully completed.

SQL>
SQL> /

TEXT
---------
***empty

cubmar

Well, I would perhaps do something like this

select text from text_file where criteria=met
UNION
select '** No data' text from dual

But of course only when no data is returned.

cubmar

Soemthing like this works, I guess:

  1  with text_file as
  2  (select 'This' text, 'met' criteria from dual)
  3  select nvl(text,dfttext) text from
  4  (select 1 lnk, text from text_file where criteria='met') qry
  5  ,
  6  (select 1 lnk, '** No data' dfttext from dual) dft
  7* where qry.lnk(+)=dft.lnk
  8  /

TEXT
----------
This

  1  with text_file as
  2  (select 'This' text, 'notmet' criteria from dual)
  3  select nvl(text,dfttext) text from
  4  (select 1 lnk, text from text_file where criteria='met') qry
  5  ,
  6  (select 1 lnk, '** No data' dfttext from dual) dft
  7* where qry.lnk(+)=dft.lnk
SQL> /

TEXT
----------
** No data

It might not look too pretty!

Keith Jamieson

As leo said, you have to code it.

if sql%rowcount = 0
<perform action>
end if;

or a local pl/sql block.

begin
<select statement >
exception
when no_data_found
then
perform action>
end;

cubmar

Keith,

There are two (very similar) examples of how to do it without the need for PL/SQL

Thanks

Keith Jamieson

Try this and see what you get:

select nvl(dummy,'A NULL') from dual where dummy='Y'

The other solutions "Cheat" in that they actually force a row to be found.

But maybe if you posted the problem you're trying to solve, ie the question behind your question we could help further.

105967

... and where you easily can end up with a full table scan, ie a disastrous performance.

BluShadow

And just for fun without using NVL or outer joins...

SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'fred' as nm from dual union all
  2             select 'bob' from dual union all
  3             select 'jim' from dual)
  4  -- end of test data
  5  select nm from (
  6    select rownum rn, nm from t where nm like '&name%'
  7    union
  8    select 0, '~~ NO DATA ~~' from dual
  9    order by 1 desc
 10    )
 11* where (rn > 0 or (rownum = 1 and rn = 0))
SQL> /
Enter value for name: will
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'will%'

NM
-------------
~~ NO DATA ~~

SQL> /
Enter value for name: fred
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'fred%'

NM
-------------
fred

SQL>

MichaelS

... and where you easily can end up with a full table scan, ie a disastrous performance

Why should it be so? Of course a proper Index layout is assumed:

SQL>  create table text_file as select object_id criteria, object_name text from all_objects
/
Table created.

SQL>  create unique index text_file_idx on text_file (criteria)
/
Index created.

SQL>  set autotrace on explain

SQL>  select nvl(text,'***empty') text from text_file t1, (select 15111 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
/df245789_JavaToSQLMapMapClass
1 row selected.

Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)


SQL>  select nvl(text,'***empty') text from text_file t1, (select 106276 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
***empty                      
1 row selected.


Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)

SQL>  drop table text_file
/
Table dropped.

105967

Yes, but I did not think of you for using it the wrong way :-)

The problem lays in your sentence: "Of course a proper Index layout is assumed". If the OP thinks, he/she has now a general solution and he/she will try with any column of the table ...

Roger D

how about something of the form ...

select c1, c2, c3 from tab where ...
union
select '*** empty' c1, null c2, null c3 from dual where not exists (select c1, c2, c3 from tab where ...)

for example:
/* when main query returns data */
select dummy from dual
union all
select '*** empty' dummy from dual where not exists (select dummy from dual)
;

/* when main query returns NO data */
select dummy from dual where 1=2
union all
select '*** empty' dummy from dual where not exists (select dummy from dual where 1=2)
;

663924

Set up your default message in a variable and then overwrite the default data when the query returns data. Try the following example...

SQL> define msg="No data found"
SQL> select '&&msg' from dual;

'NODATAFOUND'
-------------
No data found

SQL> col a new_value msg
SQL> select 'X' a from dual where rownum < 1;

no rows selected

SQL> select '&&msg' from dual;

'NODATAFOUND'
-------------
No data found

SQL> select 'X' a from dual;

A
-
X

SQL> select '&&msg' from dual;

'
-
X

Ganesh Srivatsav

you can use this,

SQL> select ename from emp where empno=7902;

ENAME
----------
FORD

SQL> SELECT ename
  2    FROM emp
  3   WHERE empno = 7902
  4  UNION ALL
  5  SELECT 'EMPTY'
  6    FROM DUAL
  7   WHERE NOT EXISTS ( SELECT ename
  8                       FROM emp
  9                      WHERE empno = 7902);

ENAME
----------
FORD

SQL> SELECT ename
  2    FROM emp
  3   WHERE empno = 7909
  4  UNION ALL
  5  SELECT 'EMPTY'
  6    FROM DUAL
  7   WHERE NOT EXISTS ( SELECT ename
  8                       FROM emp
  9                      WHERE empno = 7909);

ENAME
----------
EMPTY

SQL> 
{code}

1 person found this helpful

836203

SELECT NVL(<select that may return no data>, <default value>) FROM DUAL

635471

cubmar wrote:
Keith,

There are two (very similar) examples of how to do it without the need for PL/SQL

Thanks

You usually try to use SQL rather than PL/SQL because it is more efficient to do so. In this case it is probably not.

BluShadow

user2310677 wrote:
SELECT NVL(<select that may return no data>, <default value>) FROM DUAL

Did you sign up to the forum, just to post that very basic answer to a question that's over 2 years old?

karthiklogica

select nvl(to_char (c1),'no data') from (select case (select 'present'

from Table WHERE column = value ) when 'present'

then (select column

from table WHERE column = value )

else null

end as c1 from dual )

BluShadow

Another one!

This thread is 5 years old!!!! FFS If the OP hasn't got an answer to their question yet, I'd be very concerned.

Locking this thread

1 - 19

Added on Apr 1 2022

67 comments

5,493 views

8 mentions

SQL & PL/SQL

LEAD and LAG taking hours on a very large table!

Comments

Post Details