Skip to Main Content
  • Questions
  • Table creation script on a Select statement take longer than Select Statement time

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, KK.

Asked: June 09, 2017 - 4:11 pm UTC

Last updated: June 12, 2017 - 8:29 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi,

I have a Select Statement completing in 20 minutes, but when I try to create a Table with the Select statement(more than 10M Records) it takes more than 2 hours to complete. I have ti use the Table in a PL/SQL program for Data manipulation. I understand the Table creation involves selection and insertion of data. Is there anyway I can reduce the amount of time it takes to create the able ?

What would be the ideal way to proceed,

1. Table creation on the select statement and use the Table in the PL/SQL manipulation.
2. Create a View, and use the View or a select statement as a cursor and use BULK Collect to manipulate the data.

Please advise.

Thanks,
K


and Chris said...

To suggest a way to make it faster, we need to know why it's slow. So we need to see what the CTAS is doing.

To find that out, trace your session. You can do this by running:

exec DBMS_monitor.session_trace_enable ( null, null, true, true );
***your code here***
exec DBMS_monitor.session_trace_disable;


Do this for both the stand alone select statement and the create table as select. Then get the trace file and format it with TKPROF. This will show you what's different between the two and why the CTAS is taking so much longer.

If you're still struggling when you've got these, post the TKPROF results here and we'll see what we can do.

If you want to know more about tracing and TKPROF, read: https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_MONITOR

More on PL/SQL routine DBMS_MONITOR here