Skip to Main Content

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:-
image.png
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

Comments

thatJeffSmith-Oracle
Answer

Should be out soon.

Marked as Answer by jdoornae · Jun 29 2022
jdoornae

It's there now. Thanks!

Mal Baird

Is JDK 8 or 11 included?
image.pngimage.png

thatJeffSmith-Oracle

11, fixing the typo now

stom

Downloaded SQL Developer 22.2 with JDK 11
Copied my preferences over from the previous installation (21.4.3.063).
When I try to connect to a database from the connection tree, I get this error.
image.png Any ideas?

thatJeffSmith-Oracle

What type of connection is it?
I'm running the exact same thing you are, 64-bit Windows w/Java 11 included, and on an upgrade can connect just fine.
Maybe, try extracting the zip again using native Windows file explorer to a net-new directory, and try connecting from that location?

stom

It is a basic connection. I specify the host, port and service name. I right clicked on the connection name -> properties -> Test
image.pngI extracted to a new location and I also downloaded the "No Java" version. I get the same error on both attempts.

thatJeffSmith-Oracle

Do you have an oracle client defined in the Advanced Preferences, or are you using a TNS connection type or do you have 'OCI' checked on your advanced connection properties?

stom

I am using a basic connection type. I have a TNSNAMES file but I don't use it.
image.pngI have "OCI/Thick driver" checked. We use Radius authentication for our DB logins and I remember it wasn't working without this box checked.

thatJeffSmith-Oracle

That's probably it, get a newer client and try that, a 19c or 21c client.

user-x79xr

Same issue for me - java.lang.NoClassDefFoundError: oracle/jdbc/datasource/impl/OracleDataSource

I'm using 19c Client with "OCI/Thick driver" checked in SQLDeveloper 22.2 but still facing this issue.

1 - 11

Post Details

Added on Apr 1 2022
67 comments
5,543 views