Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS). The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID. I'm an Oracle newbie and thought I could use bind variables to speed up the process, but those are not allowed in DDL statements.

My data is quite large - 500+ million rows.

What is my best option for speed? Currently we are using nologging on the CREATE TABLE statement. Would it be better to explicitly create the table and INSERT with the /*+ append */ hint and bind variables? Or do the gains of nologging outweigh the gains of bind variables?

(I am sure this is an "it depends" scenario, but I don't even know what it depends on...)

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Use execute immediate to make creation of many very similar tables easier.

A little PL/SQL demo:

SQL> 
declare 
table_number number;
ctas_sql varchar(4000);
begin
  ctas_sql:='select sysdate as foo from dual';

  for table_number in 1..10
  loop
    execute immediate('create table foo'||table_number ||' as ('||ctas_sql||')');
  end loop;
end;
/
SQL> 
PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
FOO1
FOO2
FOO3
FOO4
FOO5
FOO6
FOO7
FOO8
FOO9
FOO10

10 rows selected.

SQL> 

The create table statement can contain anything you want - the above is just showing that the statement can be dynamic. So, in your case you'd have simple logic to alter the TEAM_ID and table name for each statement.

Bind variables will offer no gain unless you're executing the same single query multiple times (extremely simplified for the sake of this answer) - in this case you're executing many different queries that are performing large bulk inserts.

Creating tables with NOLOGGING is not advisable if this is a production system. Also be aware that not logging and /*+APPEND*/ can have backup/restore implications. This Ask Tom thread may be of particular interest.

To clarify, using CTAS is the way to go. You only have to parse the CTAS statement once to insert your millions of rows. If you use individual INSERT statements you have to parse each statement over & over again, which adds a massive overhead!

share|improve this answer
    
I thought bind variables offered gain when the only thing that changed in the query was the bind variable? –  JHFB Feb 21 '13 at 16:04
    
They reduce hard parsing (sql_text will be the same), and as a result stop the shared pool from filling up, reduce CPU consumption (no new plan has to be computed) and remove the possibility of SQL injection attacks. –  FreshPhilOfSO Feb 21 '13 at 16:08
    
Thanks, @Phil - but I'm still confused. So in my case, if I make the TEAM_ID a bind variable, is there gain? –  JHFB Feb 21 '13 at 16:11
    
No, there will be no gain unless you are doing lots of individual insert statements. You are not doing so. –  FreshPhilOfSO Feb 21 '13 at 16:15
2  
Using CTAS is the way to go. You only have to parse the CTAS statement once to insert your millions of rows. If you use individual INSERT statements you have to parse each statement over & over again, which adds a massive overhead! –  FreshPhilOfSO Feb 21 '13 at 16:23
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.