Home » SQL & PL/SQL » SQL & PL/SQL » BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !!
icon4.gif  BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !! [message #184983] Sat, 29 July 2006 09:30 Go to next message
vx_2004
Messages: 10
Registered: July 2006
Junior Member
BULK COLLECT IN PL/SQL ?

We have ORACLE 10g and an OLTP DB and its stand by DB.

I have a Query which uses Create Table as (CTAS) statement.

So For Example

CREATE TABLE TABLE3 AS
SELECT A.FIELD1,
A.FIELD2,
B.FIELD1
FROM TABLE1 A,
TABLE2 B
WHERE A.FIELD1 = B.FIELD1

This Query runs VERY FAST and Creates the Table TABLE3 with 300 MILLION Records in LESS THAN 5 Minutes.

Now My question is if we have a STandby DB then this SQL will generate lot of LOGS !! Is that Correct ??

Since its a fairly new DB, so This query almost KILLED the STANDBY DB as per the DBA.

So the DBA wants me to WRITE A PL/SQL Code with COMMIT AFTER SAY 10,000 Records which I believe is not a good idea and will definitely run for more than 10 Minutes on an average.

I Suggested to USE NOLOGGING Option as

CREATE TABLE TABLE3 AS
nologging
SELECT A.FIELD1,
A.FIELD2,
B.FIELD1
FROM TABLE1 A,
TABLE2 B
WHERE A.FIELD1 = B.FIELD1


But that will not work because of STANDBY DB as STANDBY DB gets updated from LOGS.

So does anyone have experience in a STANDBY DB Environment to handle things like this ??

I would really aprreciate your thoughts and suggestions to MAKE IT WORK !!

So my question is DOES THE DBA's IDea of committing will work ??

Is it a good idea to commit after 10,000 / 20,000 / 100,000

because Tom Kyte mentions that COMMIT Should not be after 10,000 or 20,000 records.

So Can anyone please take a look and let me know if the following PL/SQL code will work for a table with 300 Million Rows or not ?

CODE
====
---------------------------------------------------------------
CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF TABLE3%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT a.field1,
a.field2,
b.field1
FROM TABLE1 A,
TABLE2 B
WHERE A.FIELD1 = B.FIELD1 ;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT INTO TABLE3 VALUES l_data(i);

EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END fast_proc;
---------------------------------------------------------------

Also if the OBJECTIVE is to LOAD 300 MILLION Rows from the
Joins of 2 tables or could be more into TABLE3.

Also when the number of columns ARE Over 150 from the JOINS of
TWO tables, Will the PL/SQL be able to handle it as an array.

???

Another Question is if I want to COMMIT AFter say 10,000 records in between BULK INSERT, IS It ALLOWED ???

What is the Best Way ??

So we just need to create the table TABLE3 and use its rowtype, Correct ???

I appreciate your feedback in advance !!

??

Re: BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !! [message #184991 is a reply to message #184983] Sat, 29 July 2006 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Your DBA is wrong & Tom Kyte is correct.
You should not commit within a loop.
Doing so can result in ORA-01555 errors.
Re: BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !! [message #185016 is a reply to message #184991] Sun, 30 July 2006 01:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
how would committing during the process _reduce_ your amount of log??
Re: BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !! [message #185035 is a reply to message #185016] Sun, 30 July 2006 07:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Um, if you've gotta update the standby database then you've gotta update the standby database.

I didn't think CTAS created that much archive log action. I have only a very rudamentory knowlege of archive logs, but I thought it would log the "action" of the CTAS (which it would apply to the standby) rather than the results (the actual block-writes themselves). Like I say, I could be wrong. However, if I'm right, then the row-by-row will write way more archive logs, so I don't see how the situation is improved.

I don't think I've helped, but good luck anyway.

Ross Leishman
Re: BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !! [message #185037 is a reply to message #185035] Sun, 30 July 2006 07:57 Go to previous message
vx_2004
Messages: 10
Registered: July 2006
Junior Member
I think the amount of log generated would be same.

Since the standby DB almost got killed and when I was running my script, I think what happened was, I did a DROP TABLE statement in a different session of TOAD and Created my table in another session, since some of the fields were needed to be added, so I did it couple of times that day. So I think there was some sort of problem for ORACLE in deciphering the actions needed and may be it got all messed up.

The logic given by DBA is that if you commit after 10,000 records, those commits would apply faster to standby DB also.

So its commiting often which should help the DB in the background.

I wrote a SP to do BULK INSERTS and commits after 10000 records and it is taking about 15-18 minutes to insert records to this new table if there are 300,000 records.

So I don't what the logic is.

Razz


Previous Topic: Sub-partition within the sub-partition
Next Topic: date to DAY
Goto Forum:
  


Current Time: Sun Dec 04 12:27:46 CST 2016

Total time taken to generate the page: 0.05003 seconds