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!

Create table as select (CTAS) takes forever, even its select query takes just 10 minutes

R4C_SuteeSDec 27 2019 — edited Jan 2 2020

I have the attached CTAS query which takes forever to run (the most I have waited was 24 hours+).
But when I try run just only the select query alone, it takes only 10 minutes to complete (I also try to export data to excel via SQL developer, it only take 10 minutes as well).
I also have tried use PARALLEL at CTAS only, at select query only and both (as below) bu still have the issue).

So, I'm not sure why CTAS takes forever to complete. Any idea?

CREATE TABLE XX_XXX_XXXXX01 PARALLEL NOLOGGING AS

(SELECT

/*+ PARALLEL */

LEFT_CODE.SEQ,

LEFT_CODE.C1 AS C1,

LEFT_CODE.C2 AS C2,

LEFT_CODE.C3 AS C3,

LEFT_CODE.C4 AS C4,

LEFT_CODE.C5 AS C5,

LEFT_CODE.C6 AS C6,

LEFT_CODE.C7 AS C7,

LEFT_CODE.C8 AS C8,

LEFT_CODE.C9 AS C9,

LEFT_CODE.C10 AS C10,

LEFT_CODE.C11 AS C11,

LEFT_CODE.C12 AS C12,

LEFT_CODE.C13 AS C13,

LEFT_CODE.C14 AS C14,

LEFT_CODE.C15 AS C15,

LEFT_CODE.C16 AS C16,

LEFT_CODE.C17 AS C17,

LEFT_CODE.C18 AS C18,

LEFT_CODE.C19 AS C19,

LEFT_CODE.C20 AS C20,

LEFT_CODE.C21 AS C21,

RIGHT_CODE.C11 AS C22,

RIGHT_CODE.C12 AS C23,

RIGHT_CODE.C13 AS C24,

RIGHT_CODE.C14 AS C25,

RIGHT_CODE.C15 AS C26,

RIGHT_CODE.C11 AS C27,

RIGHT_CODE.C12 AS C28,

RIGHT_CODE.C13 AS C29,

RIGHT_CODE.C14 AS C30,

RIGHT_CODE.C15 AS C31,

RIGHT_CODE.C16 AS C32,

RIGHT_CODE.C17 AS C33,

RIGHT_CODE.C18 AS C34,

RIGHT_CODE.C19 AS C35,

RIGHT_CODE.C20 AS C36,

RIGHT_CODE.C21 AS C37,

RIGHT_CODE.C22 AS C38,

RIGHT_CODE.C23 AS C39,

RIGHT_CODE.C24 AS C40,

RIGHT_CODE.C25 AS C41,

RIGHT_CODE.C26 AS C42

FROM

(SELECT T1.*,

(SELECT COL2

FROM IMPORT_METADATA

WHERE SNAPSHOT_ID=2

AND TYPE ='USER'

AND COL1 =T1.XXX_LAST_UPDATED_BY

) AS LAST_UPDATED_BY_NAME,

(SELECT COL2

FROM IMPORT_METADATA

WHERE SNAPSHOT_ID=2

AND TYPE ='USER'

AND COL1 =T1.XXX_CREATED_BY

) AS CREATED_BY_NAME

FROM

(SELECT *

FROM XX_XXX_T3351

WHERE SNAPSHOT_ID =2

AND ( ( XXX_INV_ORG_ID IS NOT NULL

OR XXX_OU_ID IS NOT NULL

OR XXX_LEDGER_ID IS NOT NULL

OR XXX_BG_ID IS NOT NULL )

OR ( XXX_INV_ORG_ID IS NULL

AND XXX_OU_ID IS NULL

AND XXX_LEDGER_ID IS NULL

AND XXX_BG_ID IS NULL ) )

AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

OR XXX_CREATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

) T1

ORDER BY SEQ

) LEFT_CODE

LEFT JOIN

(SELECT T1.*,

(SELECT COL2

FROM IMPORT_METADATA

WHERE SNAPSHOT_ID=2

AND TYPE ='USER'

AND COL1 =T1.XXX_LAST_UPDATED_BY

) AS LAST_UPDATED_BY_NAME,

(SELECT COL2

FROM IMPORT_METADATA

WHERE SNAPSHOT_ID=2

AND TYPE ='USER'

AND COL1 =T1.XXX_CREATED_BY

) AS CREATED_BY_NAME

FROM

(SELECT *

FROM XX_XXX_T3353

WHERE SNAPSHOT_ID =2

AND ( ( XXX_INV_ORG_ID IS NOT NULL

OR XXX_OU_ID IS NOT NULL

OR XXX_LEDGER_ID IS NOT NULL

OR XXX_BG_ID IS NOT NULL )

OR ( XXX_INV_ORG_ID IS NULL

AND XXX_OU_ID IS NULL

AND XXX_LEDGER_ID IS NULL

AND XXX_BG_ID IS NULL ) )

AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

OR XXX_CREATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

) T1

ORDER BY SEQ

) RIGHT_CODE

ON 1 =1

AND NVL(LEFT_CODE.C8,'X') = NVL(RIGHT_CODE.C8,'X')

AND NVL(LEFT_CODE.C10,'X') = NVL(RIGHT_CODE.C10,'X')

AND NVL(LEFT_CODE.C1,'X') = NVL(RIGHT_CODE.C1,'X')

AND NVL(LEFT_CODE.C1,'X') = 'VALUE SET'

AND NVL(LEFT_CODE.C8,'X') IS NOT NULL

);

Kamal Kishore

Try adding the PRAGMA to the function.

FUNCTION function_name(...) RETURN VARCHAR2 ;
    PRAGMA RESTRICT_REFERENCES(function_name, WNDS, RNDS, WNPS,RNPS);

APC

There are a number of things it could be.

My #1 candidate is, we can only use functions in SQL statements that are public i.e. declared in a package spec. This is the case even for SQL statements executed within the same Package Body. The reson is that SQL statements are executed by a different engine which can only see publicly declared functions.

Cheers, APC

32685

Hi Andrew

That's exactly it! I moved the functions back to the original package, published them in the spec and it worked. Excellent!

Cheers

David

446518

Great!!

Helped me a lot

Though the message - function XX may not be used in SQL, is not relevent.

Krishna

564378

Wow, that cost me some time.
Thanks a lot, it saved my day.

450441

My god, users who search the forum before posting.

Surely it's a sign of the End of Days.

Amritpal

Thanks a lot, I just Googled the error PLS-00231, the very first link brought me here, and you were spot on. Declare the functions in the Package Spec to make them Global, and that solved the issue.

Thanks.
Amrit

659130

I also have this problem.

Making the function public helps ... but I can not make the function public. This violates the design.

The PRAGMA also says that it must be right behind the function definition AND in the package specification.

Is there a other - a private - way?

(Oracle 8i / PLSQL 8.1.7.4.0)

I also can not split the call from the SQL Statement, because it is part of a cursor loop:

FOR a IN (SELECT asd,
                         asaa,
                         asdasda,
                         h.sssde,
                         HLP_FNC_GET_BLABLUBB(j.adasdassss) dasdasssss
                  FROM   T_fhexxxsadfast h,
                         t_FFasee112w_SA   j
                  WHERE  h.soasde = j.soasde) 
        LOOP

anonymized code.

Edited by: user5828099 on 10.09.2008 07:57

Edited by: user5828099 on 10.09.2008 08:02

1 - 8

Added on Dec 27 2019

16 comments

9,790 views

SQL & PL/SQL

Create table as select (CTAS) takes forever, even its select query takes just 10 minutes

Comments

Post Details