Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

Best Practice: Building Tables with PL/SQL

I am in the process of converting a bunch of SQL statements that run daily as a called script in SQLPlus into PL/SQL statements called from oracle job schedular.  I'm not too familiar with the advantages that PL/SQL gives over DDL, so first I'll outline what is currently done:
1. DROP TABLE A CASCADE CONSTRAINTS
2. CREATE TABLE A
3. INSERT INTO A (SELECT * FROM B)
4. CREATE INDEX AI1 ON A (...)
5. CREATE INDEX AI2 ON A (...)

So... I'm guessing the original creator dropped the table to rid himself of the indexes, then recreated, inserted, and made the indexes again.  What's the best way to do this with PL/SQL?  About 17million rows will be inserted into this table, which I'm guessing the reason behind dropping the indexes, is that still advisable using plsql?
0
CalDude
Asked:
CalDude
  • 2
2 Solutions
 
sdstuberCommented:
dropping the indexes can definitely help with inserts.  You don't need to drop the table though.
Simply drop the indexes, truncate the table then insert (select...)
Then recreate the indexes.

You can do that in pl/sql with
begin
execute immediate 'truncate table A';
execute immediate 'drop index AI1';
execute immediate 'drop index AI2';
insert into A (select * from B);
execute immediate 'create index AI1 on A (...)';
execute immediate 'create index AI2 on A(...)';
end;
0
 
joebednarzCommented:
As this is a (I'm assuming) re-curring job, I would add to the truncate statement sdstuber outlined...

execute immediate 'truncate table A reuse storage';

Without the "reuse storage" clause TRUNCATE TABLE will also release back to the database any space that was previuosly occupied by the table.

Also, I think the order of statements ststuber meant to include was:

drop index, truncate, insert, create index
0
 
joebednarzCommented:
... and if you use the REUSE STORAGE option, I would also include in your PL/SQL a statement to ANALYZE TABLE to re-gather statistics.  The REUSE option does not re-set the high-water mark on a table.

execute immediate 'ANALYZE TABLE a ESTIMATE STATISTICS';

or using the Oracle package (Oracle 8i or after):

dbms_utility.gather_table_stats( '<schema_name>', 'A' );

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now