How to Update millions or records in a table", version 8.1.7

不念不忘少年蓝@ 2024-02-17 20:32 30阅读 0赞

You Asked (Jump to Tom's latest followup)

Good Morning Tom.

I need your expertise in this regard. I got a table which contains millions or  
records. I want to update and commit every time for so many records ( say  
10,000 records). I dont want to do in one stroke as I may end up in Rollback  
segment issue(s). Any suggestions please ! ! !

Murali

and we said...

If I had to update millions of records I would probably opt to NOT update.

I would more likely do:

CREATE TABLE new\_table as select <do the update "here"> from old\_table;

index new\_table  
grant on new table  
add constraints on new\_table  
etc on new\_table

drop table old\_table  
rename new\_table to old\_table;

you can do that using parallel query, with nologging on most operations  
generating very little redo and no undo at all -- in a fraction of the time it  
would take to update the data.  
Reviews  
GOTO a page to Bookmark Review | Bottom | Top  
updating millions of records November 11, 2002  
Reviewer: Om from India

Hi Tom,

Could you please elaborate

CREATE TABLE new\_table as select <do the update "here"> from old\_table;

the above statement with a suitable example please

Followup:  
ok, say you wanted to update emp to set ename = lower(ename). Instead, you  
could do this:

[ops$tkyte@ORA817DEV.US.ORACLE.COM][ops_tkyte_ORA817DEV.US.ORACLE.COM]> create table new\_emp as  
2 select empno, LOWER(ename) ename, JOB,  
3 MGR, HIREDATE, SAL, COMM, DEPTNO  
4 from emp;

Table created.

[ops$tkyte@ORA817DEV.US.ORACLE.COM][ops_tkyte_ORA817DEV.US.ORACLE.COM]>  
[ops$tkyte@ORA817DEV.US.ORACLE.COM][ops_tkyte_ORA817DEV.US.ORACLE.COM]> drop table emp;

Table dropped.

[ops$tkyte@ORA817DEV.US.ORACLE.COM][ops_tkyte_ORA817DEV.US.ORACLE.COM]> rename new\_emp to emp;

Table renamed.

GOTO a page to Bookmark Review | Bottom | Top  
Million records update November 11, 2002  
Reviewer: Ramesh G from U.K

What if a table has over 100 million records and if i only want to update 1  
million? If your method is still applicable could you elaborate it.

Many thanks in advance.  
  
Followup:  
most likely -- yes. I don't have a 100million row table to test with for you  
but -- the amount of work required to update 1,000,000 indexed rows is pretty  
large. Fortunately, you are probably using partitioning so you can do this  
easily in parallel -- bit by bit.  
  
GOTO a page to Bookmark Review | Bottom | Top  
"How to Update millions or records in a table", version 8.1.7 November 11, 2002  
Reviewer: John Bittner from Hunt Valley, MD USA

This is absolutely a viable approach, and one we have used repeatedly. One of  
our apps updates a table of several hundred million records.

The cursor..For loop approach for the update was calculated to take 53.7 years  
to complete!  
We institued the Insert into a dummy table append with nologging, and were able  
to complete the "update" in under 30 minutes.

With nologging, if the system aborts, you simply re-run the 'update' again, as  
you have the original data in the main table. When done, we swap the partition  
of original data with the 'dummy' table (the one containing new values),  
rebuild indexes in parallel, and wha-la! Our update is complete.

i.e, to update field2 in a table:

1) First create your dummy hold table: create table xyz\_HOLD as select \* from  
xyz where rownum<1. Alter tablexyz nologging.

2) insert /\*+ append parallel (xyzhold,12) \*/ into xyz\_hold xyzhold (field1,  
field2, field3) select /\*+ parallel (x,12) \*/ xyz.field1,  
my\_new\_value\_for\_field2, xyz.field3 from xyz x where blah blah blah.

3) when done, either rename the table, or swap the partition if your original  
table is partitioned, and you only updated one partition as we do. Obviously  
you need to rebuild indecies, etc as required.

Hope this helps!

GOTO a page to Bookmark Review | Bottom | Top  
updating millions of records November 11, 2002  
Reviewer: A.Ashiq from Trichy,Tamil Nadu ,India

Hi Tom,

As u suggested us to create a new table ,then drop the original table and rename  
the new table to original table instead of updating a table with millions of  
records.But what happen to dependent objects ,everything will get  
invalidated.Yeah ,of course it'll recompile itself when it called next time.But  
again it(dependent objects) has to do parsing.Is it Ok.

Followup:  
It is OK.  
  
GOTO a page to Bookmark Review | Bottom | Top  
in case of delete November 12, 2002  
Reviewer: A reader

We've a similar situation., We delete around 3 million records from 30 million  
rows table everyday.  
There is no logical column to do partition.,  
I guess the insert into a new table will take considerable time with 27 mil  
records.. Please let me know what is the best approach.

Followup:  
wait 10 days so that you are deleting 30 million records from a 60 million  
record table and then this will be much more efficient.

Time it some day though. 3 million records on an indexed table will take  
considerable time. There is a chance that INSERT /\*+ append \*/ select <rows to  
keep> done nologging, parallel with create indexes in parallel could best the  
delete.

GOTO a page to Bookmark Review | Bottom | Top  
November 12, 2002  
Reviewer: A reader

Tom,

Recently I had conducted a interview in which one the dba mentioned that they  
had a table that might conatin 10 million records or might be 1 million. He  
meant to say they delete the records and some time later the table will be  
populated again and viceversa.

Tom according to you do you consider partitions for such tables and if yes which  
type of partition..

Thanks.

Followup:  
hard to tell -- is the data deleted by something that is relatively constant  
(eg: the value in that column doesn't change - so the row doesn't need to move  
from partition to partition). If so -- sure, cause we could just drop  
partitions (fast) instead of deleting the data.

GOTO a page to Bookmark Review | Bottom | Top  
Agree with John - insert append / CTAS / partition swap is the only way to fly November 12, 2002  
Reviewer: Jack Silvey from Richardson, TX

I work with John Bittner, one of the previous reviewers. I second what he said  
absolutely. It is the only way to fly. We also have an absolutely incredible  
stored procedure that rebuilds all of our indexes concurrently after the load,  
using the Oracle job scheduler as the mechanism of allowing separate threads in  
pl/sql.

GOTO a page to Bookmark Review | Bottom | Top  
addendum November 13, 2002  
Reviewer: A reader

This process was introduced to our environment by a master tuner and personal  
friend, Larry Elkins. This was a totally new paradigm for the application, and  
one that saved the entire mission-critical application. The in-place updates  
would not have worked with the terrabytes of data that we have in our database.

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table November 19, 2002  
Reviewer: Boris Milrud from San Jose, CA USA

In response to the Jack Silvey (from Richardson, TX ) review, where he wrote "It  
is the only way to fly. We also have an absolutely incredible stored procedure  
that rebuilds all of our indexes concurrently after the load, using the Oracle  
job scheduler as the mechanism of allowing separate threads in pl/sql":

Could you provide more information about that procedure and how to rebuild  
multiple same-table indexes concurrently using Oracle job scheduler?

Thanks,  
Boris.

Followup:  
instead of

begin  
execute immediate 'alter index idx1 rebuild';  
execute immediate 'alter index idx2 rebuild';  
end;

you can code:

declare  
l\_job number;  
begin  
dbms\_job.submit( l\_job, 'execute immediate ''alter index idx1 rebuild'';' );  
commit;  
dbms\_job.submit( l\_job, 'execute immediate ''alter index idx2 rebuild'';' );  
commit;  
end;

Now, just set job\_queue\_processes > 0 to set the "degree of threads" and in 8i  
and before set job\_queue\_interval to say 60 or so and there you go.

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table November 19, 2002  
Reviewer: Boris Milrud from San Jose, CA USA

Thanks, Tom.  
Your status said that you had a large backlog, so I decided not to wait for your  
response and tried myself using dbms\_job.submit() calls. At first, it did not  
work (job\_queue\_processes was 0), but after I set it to 12 it started working.  
The only one difference between your code and mine is that I issue just one  
commit at the end. It should not matter, right?

I selected 1 mln. rows table and rebuild 5 non-partitioned indexes with 'compute  
statistics parallel nologging' clause.  
Here is the numbers I've got: rebuilding indexes sequentually consistently took  
76 sec., while using dbms\_job.submit() calls took around 40 - 42 sec.

I said "around", because the technique I used may not be perfect, though it  
served the purpose. I recorded the time right after the commit statement at the  
end of PL/SQL block - that's the start time. Then I kept querying user\_jobs view  
every 2 - 3 sec, until the last of the 5 jobs were gone. That's was the end  
time.

The last question on this topic: is user\_jobs view is the right place to look in  
order to determine that's rebuilding is done and how long it took? In package I  
am writing, I do massive delete operation, then rebuilding indexes, then  
starting the next routine. What would be the best way to detect the end of  
rebuiding, in order to proceed with the next call?

Thanks.  
Boris.

Followup:  
You can use user\_jobs or dba\_jobs but -- you might just want to put some  
"logging" into your jobs themselves so you can monitor their progress and record  
their times.

GOTO a page to Bookmark Review | Bottom | Top  
You can even rebuild all the indexes in a partition simultaneously November 21, 2002  
Reviewer: ramakrishna from India

Thanks Tom,  
In our environment, we have partitioned tables and we use:  
ALTER TABLE table\_name MODIFY PARTITION part\_name REBUILD UNUSABLE LOCAL INDEXES

and this rebuilds all the indexes in the partition at one shot. We found this  
much faster than doing the indexes one by one. We will now try out if we can  
submit multiple such jobs in parallel (one for each partition of the table).

regards  
ramakrishna

GOTO a page to Bookmark Review | Bottom | Top  
in case of deletes November 29, 2002  
Reviewer: carl from moondog, mo

Hi Tom,

Thanks so much for your web site and help. It is our  
number 1 reference in times of fear and loathing.

This is what we came up with concerning mass updates

INV 50M  
INVINS 10M  
INVDEL 7M

There are indexes on INV.KEY and INVDEL.KEY

Execution Plan ( for deletes and updates )  
\----------------------------------------------------------  
0 SELECT STATEMENT Optimizer=CHOOSE  
1 0 FILTER  
2 1 TABLE ACCESS (FULL) OF 'INV'  
3 1 INDEX (RANGE SCAN) OF 'IX\_INVDEL01' (NON-UNIQUE)

alter table INVTMP nologging;

\-- INVINS contains delta inserts and updates

insert /\*+ APPEND \*/  
into INVTMP  
select \*  
from INVINS t1;

\-- INVDEL contains delta deletes and updates

insert /\*+ APPEND \*/  
into INVTMP  
select \*  
from INV t1  
where not exists ( select null  
from INVDEL t2  
where t2.KEY = t1.KEY );

alter table INVTMP logging;  
drop table INV;  
rename INVTMP to INV

\-- build indexs etc

This is what we came up with and is to the fastest  
approach we've tested.

Any comments or suggestions are welcome and appreciated.

Followup:  
I might try an anti-join on the NOT EXISTS and doing the work in a single  
parallel CTAS

create table invtmp as  
select \* from invins  
UNION ALL  
select t1.\*  
from inv t1, invdel t2  
where t1.key = t2.key(+)  
and t2.key is null;

add parallel /nologging as needed.

GOTO a page to Bookmark Review | Bottom | Top  
in case of deletes - many thanks December 01, 2002  
Reviewer: carl from moondog, mo

Ran the test cases at home 500K rows in INV and 50K rows in  
INVINS and INVDEL

My way: 16.33 secs  
Your way: 08.35 secs

Many thanks.

GOTO a page to Bookmark Review | Bottom | Top  
A bit confused March 17, 2003  
Reviewer: Choche from England

I was just wandering that none of the reviews made mention if these techniques  
could be applied in a multi-user environment where multiple users could be  
updating the same table at the same time.

Followup:  
sorry -- I though it obvious that in most cases "no" is the answer. we are  
copying the data or locking excessive amounts of it -- or disabling indexes and  
the like.

This is a "batch" process here, to update millions of records.

GOTO a page to Bookmark Review | Bottom | Top  
Replacing PK values July 25, 2003  
Reviewer: Michael from San Diego, CA, USA

We need to replace the main ID for all entries in the database which is the PK  
in about 100 tables, largest of them have about 2 mln rows. New and old values  
for ID are stored in the lookup table, and there about half a million of them.  
The original approach was to replace PK in all tables for one ID value, commit  
the changes and move on to the next ID. This way referential integrity would be  
guaranteed even if update fails at any stage, and we do not need a long rollback  
segments and can restart the process at the point it aborted. But this update is  
too lengthy, and in our case would take almost a month of continuous processing.  
Do you have any suggestions on how to improve the performance of this critical  
update? Would it be better to add new ID as an additional column into one table  
at a time and populate all of it at once as "create table ... as select" (with  
nologging option), and then rename table and drop old ID column?

Followup:  
sounds like I would:

create table new\_table as select <join>;  
create table new\_table2 as select <join>;  
etc etc etc etc

drop old tables  
add indexes  
add primary/fkeys/constraints etc....

that is -- rebuild in bulk, using nologging (no redo, no undo generated), using  
parallel when appropriate.

GOTO a page to Bookmark Review | Bottom | Top  
Update million Rows July 26, 2003  
Reviewer: Reaz from Dhaka, Bangladesh

Dear Tom,

We have this situation, where we load data from external files numbering 7.  
Next we had to run a transformation process, which checks if the row exists in  
the target table and updates the existing row with the new one. Otherwise  
inserts the row to the target table.

Our current process is very slow, sometimes taking a night long to complete for  
say 200,000 rows.

The procedures discussed so far meant of updating only, but in our case we need  
to update and insert too.

I would appreciate your suggestion.

Thanks.  
Reaz.

Followup:  
read about MERGE in the sql reference guide.

GOTO a page to Bookmark Review | Bottom | Top  
A single batch? July 26, 2003  
Reviewer: A reader

Tom,  
Is it still okay, if these steps (create a new table, drop the old one, and them  
rename the new table as old, create indexes etc.) are coded as the batch update  
program? OR would you suggests these should be detailed as the Steps that should  
be performed under the supervision of a DBA? What I would like to know is that  
can we still have the steps in a single batch routine (as update would have had)  
or instead of the single batch these should be DBA instructions instead?  
Thanks

Followup:  
I would have these performed under the watchful eye of someone.

it is a one time bulk manipulation. it won't be a "pretty program" with tons of  
gracefully logging/error handling. You'll be doing it "down and dirty" (at  
least I would be). It would need someone to watch over it.

GOTO a page to Bookmark Review | Bottom | Top  
What about space consideration July 28, 2003  
Reviewer: A reader

Hi Tom,  
I had a similar problem where my table had gone big and i wanted to truncate  
some old data, my table was big more then 20 million records, i could not use  
the create temp table truncate and insert flow as the space of the database was  
not enough to hold 2 copies of the large table, I thought of doing a partition  
on table and then truncate one partition of table but that does not work.  
Can you suggest some way which caters for this space constraint also

Followup:  
why didn't the partitioning work for you.

GOTO a page to Bookmark Review | Bottom | Top  
How to update part of a Very Large Table July 28, 2003  
Reviewer: Mohan K from Bangalore, India

The update operation can be made simple by sepearting out the query and update  
operation. To do that create another table which stores the rowids of the  
records in the original table which has to be updated along with the the value  
to be updated. Then run the PL/SQL script to update the records one by one. To  
test the above method the following scripts can be used.

SQL> create table test\_update(id number, name varchar2(100), description  
varchar2(4000)) storage initial(48M next 4m);

SQL> declare  
v\_n number;  
v\_name number;  
v\_desc number;  
i number;  
begin  
for i in 1..1000000 LOOP  
insert into test\_update(id, name, description) values(i, 'Test Name'||i, 'Test  
Name '||i||' description ');  
END LOOP;  
end;  
/

Elapsed: 00:04:277.23

The above script will insert 1 million rows.

SQL> select count(\*) from test\_update where description like '%5 description%';

COUNT(\*)  
\----------  
100000

Elapsed: 00:00:02.63

SQL> create table test\_update\_rowids as select rowid rid, description from  
test\_update where description like '%5 description%';

Elapsed: 00:00:54.58

The table test\_update\_rowids stores the rowids and the new values that has to be  
updated. ie 100000 rows needs to be updated.

SQL> declare  
begin  
for c1 in(select rid, description from test\_update\_rowids)  
LOOP  
update test\_update set description=c1.description||' after update' where  
rowid=c1.rid;  
END LOOP;  
end;  
/

Elapsed: 00:01:82.17

The above script performs the updation.

Mohan

Followup:  
the above script performs an "updation" in the worst possible way, consuming the  
most resources.

it should have been simply:

update test\_udpate set description = description || ' after update ' where  
description like '%5 description%';

Never do procedurally that which can be done in a single sql statement.

GOTO a page to Bookmark Review | Bottom | Top  
Updating 100K of 1 Million rows July 28, 2003  
Reviewer: Mohan K from Bangalore, India

I ran the update statement as a single SQL statement and the time elapsed is  
slightly more than the above method. It has to be tested against update  
statements containing complex queries or join operations.

SQL> update test\_update set description = description || ' after update ' where  
description like '%5 description%';

Elapsed: 00:01:100.13

Mohan

Followup:

trust me -- that difference is neglible and the amount of time you spent writing  
code wasn't included.

Also, you FORGOT to add in the time to populate your temporary table -- no?

if you can do it in a single sql statment (and you almost always can) you  
SHOULD.

Lets do you benchmark a little better:

[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table test\_update(id number, name varchar2(100),  
description varchar2(4000));

Table created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> insert /\*+ append \*/  
2 into test\_update  
3 select rownum, 'Test Name' || rownum, 'Test Name ' || rownum || '  
description '  
4 from big\_table.big\_table  
5 where rownum <= 1000000;

1000000 rows created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]> commit;

Commit complete.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> select count(\*)  
2 from test\_update  
3 where description like '%5 desc%' ;

COUNT(\*)  
\----------  
100000

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> select count(\*)  
2 from test\_update  
3 where description like '%6 desc%' ;

COUNT(\*)  
\----------  
100000

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> exec runstats\_pkg.rs\_start

PL/SQL procedure successfully completed.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> update test\_update  
2 set description = description || ' after update'  
3 where description like '%6 description%';

100000 rows updated.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> exec runstats\_pkg.rs\_middle

PL/SQL procedure successfully completed.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table test\_update\_rowids  
2 as  
3 select rowid rid, description from test\_update where description like '%5  
description%';

Table created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> begin  
2 for c1 in(select rid, description from test\_update\_rowids)  
3 LOOP  
4 update test\_update  
5 set description=c1.description||' after update'  
6 where rowid=c1.rid;  
7 END LOOP;  
8 end;  
9 /

PL/SQL procedure successfully completed.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> exec runstats\_pkg.rs\_stop  
Run1 ran in 1376 hsecs  
Run2 ran in 9806 hsecs  
run 1 ran in 14.03% of the time

so, according to me, the SINGLE (efficient, effective, correct way) update  
statment runs in about 14% of the time. furthermore:

Name Run1 Run2 Diff  
STAT...recursive cpu usage 5 4,662 4,657  
STAT...CPU used by this sessio 448 5,397 4,949

only 4,949 MORE cpu seconds using the row by row approach...

STAT...CPU used when call star 448 5,397 4,949  
STAT...free buffer inspected 10,162 17,039 6,877  
STAT...db block gets 111,705 104,450 -7,255  
STAT...dirty buffers inspected 9,016 16,620 7,604  
STAT...calls to kcmgas 7,755 68 -7,687  
STAT...Cached Commit SCN refer 7,718 0 -7,718  
STAT...switch current to new b 7,719 0 -7,719  
STAT...cleanouts only - consis 0 7,720 7,720  
STAT...immediate (CR) block cl 0 7,720 7,720  
STAT...commit txn count during 0 7,721 7,721  
STAT...cleanout - number of kt 3 7,726 7,723  
STAT...consistent gets - exami 3 7,760 7,757  
LATCH.undo global data 221 7,985 7,764  
STAT...redo entries 100,700 108,727 8,027  
LATCH.redo allocation 100,795 108,945 8,150  
STAT...db block changes 202,723 210,907 8,184  
STAT...Elapsed Time 1,385 9,825 8,440  
STAT...physical reads 7,894 16,476 8,582  
LATCH.checkpoint queue latch 26,845 38,012 11,167  
LATCH.cache buffers lru chain 35,638 52,827 17,189  
STAT...no work - consistent re 7,741 100,072 92,331  
STAT...table scan blocks gotte 7,719 107,721 100,002  
STAT...table scan rows gotten 1,000,000 1,100,002 100,002  
STAT...execute count 27 100,092 100,065  
STAT...buffer is not pinned co 7,763 107,836 100,073  
STAT...calls to get snapshot s 56 100,241 100,185  
LATCH.shared pool 172 100,778 100,606  
STAT...session logical reads 119,503 220,165 100,662  
STAT...consistent gets 7,798 115,715 107,917  
LATCH.library cache pin 233 200,865 200,632  
STAT...recursive calls 177 200,870 200,693  
LATCH.library cache 332 201,322 200,990  
LATCH.cache buffers chains 587,377 835,167 247,790  
STAT...redo size 31,323,804 31,836,904 513,100  
STAT...session pga memory max 0 524,824 524,824  
STAT...session pga memory 0 1,049,112 1,049,112

Run1 latches total versus runs -- difference and pct  
Run1 Run2 Diff Pct  
754,602 1,553,986 799,384 48.56%

PL/SQL procedure successfully completed.

and worse, it used 2x the number of latches! (scalability inhibitors)

There is nothing good about row at a time processing, it is something to do ONLY  
when a set based operation isn't feasible

GOTO a page to Bookmark Review | Bottom | Top  
This is very good example for updating the records July 28, 2003  
Reviewer: Bipin Ganar from INDIA

Hi Tom,  
Yes this is good example to update the record but my problem  
is i have 1Million records in one table. I want update and give hike of 10% of  
their salaries in one table but that table is accessed by n no of people at the  
same moment. I can not drop the table and update and second how can i define  
tablespace in this script  
Create Table a as select \* from emp\_sal;

Followup:  
create table a  
tablespace foo  
as  
select \* from emp\_sal;

GOTO a page to Bookmark Review | Bottom | Top  
slightly different problem! July 28, 2003  
Reviewer: Srikanth Adiga from Pune, India

My table:  
5M records are inserted each month.

Operations:  
5M records inserted on the first day of the month  
5M records updated on rest of the days on the month.  
Note that this update is update each row with diff values.

I am using OCI and using batch operations. Can you please suggest me what else  
can I do within my program to improve the performance of my updates? I tried  
multithreading, but that did not help.

anything else?

Followup:  
it only takes a couple of minutes to update 5 million rows on my desktop pc --  
so, there must be something more involved here.

I'd be able to do that in minutes -- why is it taking you so long -- what is  
your bottleneck.

GOTO a page to Bookmark Review | Bottom | Top  
space considerations July 28, 2003  
Reviewer: A reader

could not create another temp table which holds the data same as teh table being  
partitioned as there was not enuff space in the harddisk to hold 2 or 1.5 to be  
precise copies of the same huge table,is there a way to partition without having  
to drop of truncate or transfer data to some temp location and then partition

Followup:  
disk is cheap.

time is money.

your time spent just thinking about this problem cost much more then the disk to  
solve it would.

penny wise, pound foolish. How big is this 20million row table (assuming you  
are the same "a reader" from above). Maybe 1-3 gig?

GOTO a page to Bookmark Review | Bottom | Top  
slightly different problem! July 29, 2003  
Reviewer: Srikanth Adiga from Pune,India

couple of minutes!!! Oh dear, thats really too good.

Let me reitterate my case:  
My table schema is something like this:  
SubscriberVsQtyTable:  
int client-id  
int year  
int month  
int quantity  
primary key - (client-id, year,month)

This table has 5M client ids.

Now every day, I have update the quantity field with the txns made for that day.  
Note that, I have to update one subscriber at a time since the number of txns  
made by each subscriber if different.

So, even now do you believe you can do this in couple of minutes? My app takes  
about 30 to 50 mins even on higher machines like 8 CPU.

As I said, this is a C++ app, running Solaris + Oracle 8i/9i. We are using OCI.

Thanks,

Followup:  
if you are doing that work with more then a single sql statement, I could see it  
taking a while.

If you do that work in a single update, no, I don't see it taking more then a  
couple of minutes.

GOTO a page to Bookmark Review | Bottom | Top  
Time for Update July 29, 2003  
Reviewer: Mohan K from Bangalore, India

It is my understanding that the time for update depends on the following  
factors. There may be something else also involved.

1. Row Chaining/Migration caused by update  
2. The where cluase is used to select the rows to be updated.  
3. Percentage rows updated in a block. The more the better. Even if one row is  
updated in a block containing 10 records the entire block has to written by DBWR  
and LGWR.

So just the number of records is not that matters. 100 million recorsd may takes  
days for one table and may take less than a hour for another table with few  
columns.

Mohan

Followup:  
1) updating a quantity (number) field is unlikely to do that.  
2) correct  
3) correct

no, i disagree with the last part. to update 5million rows, a single unindexed  
column, should not take "a really long time". I was answering their direct  
question -- they update every row, on every block. so \#1 and \#3 really don't  
come into play. The biggest factor is \#2 here

GOTO a page to Bookmark Review | Bottom | Top  
Verifing if neccesary to update, before update July 29, 2003  
Reviewer: juan carlos reyes from Bolivia

Hi Tom, I had seen several times that you can  
increase the performance by evaluating if you really need to update, or maybe it  
was only my impression.

For example  
Update table set income = 500  
where level = 7  
AND NOT INCOME = 500

The AND NOT INCOME = 500, allows to avoid unncesary updates,  
my point is a question

How advisable is it, does it really increase performance?

As you are always thinking how to increase performance, tt could be a good idea  
in newer version of Oracle, that if Oracle verifies that the value was set to  
that value, not update  
:)

Followup:  
if there are more then a handful of records where income = 500, then yes, this  
will definitely increase performance by

a) decreasing the latching  
b) decreasing the redo  
c) decreasing the undo

we cannot make that optimization for you -- it would stop triggers and other  
EXPECTED side effects from happening.

GOTO a page to Bookmark Review | Bottom | Top  
July 29, 2003  
Reviewer: A reader

:) Thanks,

Maybe then it could be an optional feature  
UPDATE\_ONLY\_IF\_DIFFERENT hint  
I think it could interest several people  
who don't use trigger, neither constraints ,etc.

GOTO a page to Bookmark Review | Bottom | Top  
July 29, 2003  
Reviewer: Stu Charlton from Toronto Canada

"Note that, I have to update one subscriber at a time since the number of txns  
made by each subscriber if different."

That still seems doable in 1 SQL statement, even if you wanted to merge the  
updates. Couldn't you just do a COUNT(\*) grouped by client\_id, year, and month?

Followup:  
I agree -- most things CAN be done in a single sql statement.

GOTO a page to Bookmark Review | Bottom | Top  
easier said then done in real life situations July 29, 2003  
Reviewer: A reader

Hi Tom,  
I agree you may think getting a disk is cheaper, that is only when u are  
considering getting up or just going to the store and getting a disk,  
In real life conditions specially corporates this is not as easy as it seems nor  
is it less costly.  
A Simple purchase of disk has to first be justified  
which is quite difficuult, i dont think i have justification as yet)then be  
approved then be purchased by purchase dept then be installed or fixed by the  
server teams, server team may want to take the system down which means more  
dollars much more.These are just things off my head, when we acutually inititate  
the purchase process , there will be definitly some more steps and processes.

Coming back to my question, am i right in saying that we can't partion an  
existing table without copy the data to some temp location,Let me know if there  
is any other way.

Thanks

Followup:  
justification

you pay me alot  
disk is cheap.  
think about it.

(sometimes it really can be that simple)

we are talking about a couple of gig (like 1 to 3 -- small, tiny -- my /tmp has  
more room then that) here, something my LAPTOP would not have an issue with.

in order to partition -- think about it -- you'll need the SOURCE DATA and the  
PARTITIONED DATA -- at the same time, for a period of time. no magic there.

GOTO a page to Bookmark Review | Bottom | Top  
slightly confused ! July 30, 2003  
Reviewer: Srikanth Adiga from Pune, India.

Thanks, Stu and rest for your updates.

Taking this buit more further, since I am confused :)

\[Sri\]  
"Note that, I have to update one subscriber at a time since the number of txns  
made by each subscriber if different."  
\[Sri\]

\[Stu Charlton\]  
That still seems doable in 1 SQL statement, even if you wanted to merge the  
updates. Couldn't you just do a COUNT(\*) grouped by client\_id, year, and month?

Followup:  
I agree -- most things CAN be done in a single sql statement.  
\[Stu Charlton\]

Sorry, how would one do this?  
If my table has two rows;  
clientid =1,year=july,year=2003,quantity=10  
clientid =2,year=july,year=2003,quantity=20  
Now I have to update (clientid=1)'s quantity by 15 and (clientid=2)'s quantity  
by 25.  
How would you manage this in a single SQL? Like this there would be 5M rows to  
be updated.

Btw, my table is indexed on clientid,year and month.

Followup:  
what is the OTHER table you are updating from? you must have a detail table  
elsewhere from where you derive that 15 and 25. So, assuming something like  
this:

[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table t ( clientid int, month int, year int, quantity  
int );

Table created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table txns ( clientid int, month int, year int );

Table created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> insert into t values ( 1, 7, 2003, 10 );

1 row created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]> insert into t values ( 2, 7, 2003, 20 );

1 row created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> insert into txns select 1, 7, 2003 from all\_objects where  
rownum <= 15;

15 rows created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]> insert into txns select 2, 7, 2003 from all\_objects where  
rownum <= 25;

25 rows created.

[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> select \* from t;

CLIENTID MONTH YEAR QUANTITY  
\---------- ---------- ---------- ----------  
1 7 2003 10  
2 7 2003 20

[ops$tkyte@ORA920][ops_tkyte_ORA920]> update t  
2 set quantity = quantity + ( select count(\*)  
3 from txns  
4 where txns.clientid = t.clientId  
5 and txns.month = t.month  
6 and txns.year = t.year );

2 rows updated.

[ops$tkyte@ORA920][ops_tkyte_ORA920]> select \* from t;

CLIENTID MONTH YEAR QUANTITY  
\---------- ---------- ---------- ----------  
1 7 2003 25  
2 7 2003 45

there you go.

(it is a shame you are using the wrong datatypes -- only a DATE should be used  
to hold, well, DATES. Using numbers to hold a year and a month isn't a good  
practice)

GOTO a page to Bookmark Review | Bottom | Top  
SCN,CHECK POINT July 30, 2003  
Reviewer: AATIF from PAKISTAN

i ask about whta is difference SCN system commit number,systemt change number  
and check point.  
Which base oracle sync the database SCN or CHECKPOINT ,  
when check point ocur who number write in control file headre,datafile and log  
file header.  
dear tom.  
your web site say me not ask more question now but i need clear my point.  
AATIF.

Followup:  
er?

i remember you asking this on another page -- yes.

and I also remember answering this on that other page -- yes.

I fail to see how this relates to this page.

anyway there IS no difference between

scn  
system commit number  
system change number

they are the same.

as for the rest of the comment -- it doesn't "make sense" (i do suggest a read  
through of the server concepts guide, many questions are answered there!)

GOTO a page to Bookmark Review | Bottom | Top  
More clarification August 01, 2003  
Reviewer: Srikanth Adiga from Pune,India

>what is the OTHER table you are updating from? you must  
>have a detail table elsewhere from where you derive that  
>15 and 25.  
It is coming from an OCI application after processing some files.

Followup:  
use bulk processing - update between 100 and 500 rows at a time in a single call  
using array processing.

Or, put the data where it belongs - not in files, in tables -- and process the  
data in the database rather then in C

GOTO a page to Bookmark Review | Bottom | Top  
August 06, 2003  
Reviewer: Srikanth Adiga from Pune, India

>what is the OTHER table you are updating from? you must  
>have a detail table elsewhere from where you derive that  
>15 and 25.  
It is coming from an OCI application after processing some files.

>Followup:  
>use bulk processing - update between 100 and 500 rows at a >time in a single  
call using array processing.  
Yes, that is what we do. It takes about 60 mins to update 5M records on a 2CPU  
machine. Is this much expected?

If I do the same in multiple threads I do not see any performance improvement  
i.e. 1M records parallely updated in 5 threads.  
Any idea why?

Followup:  
are you sure they are going in parallel and not blocking/locking each other.

verify that all 5 sessions are in fact not only ACTIVE (v$session) at the same  
time but that you are not blocking yourself.

and you have taken some statspacks during your testing to see if you have any  
obvious bottlenecks right?

GOTO a page to Bookmark Review | Bottom | Top  
Explicit commit ! August 07, 2003  
Reviewer: Nathan from London

Tom,

Why do we commit explicitly after submitting jobs ?

Thanks  
Nathan  
/\*------------------------------------------------  
Followup:  
instead of

begin  
execute immediate 'alter index idx1 rebuild';  
execute immediate 'alter index idx2 rebuild';  
end;

you can code:

Followup:  
because if you do not YOUR session can "see" the job in the job queue but the  
job queue processes cannot!

How many of us have sat there for minutes going "when is the stupid thing going  
to run" and until we exit sqlplus it does nothing :)

GOTO a page to Bookmark Review | Bottom | Top  
Explicit Commit August 07, 2003  
Reviewer: Nathan from London

Apologies,  
I must withdraw my previous question... i was obviously not thinking straight. I  
was thinking about a similar sitaution where a commit was issued ( before  
dbms\_job.submit ) with a comment /\* do not remove \*/ without any  
explanations... I'm still pondering why the commit should be there.

Sorry for the trouble.

Regards  
Nathan

GOTO a page to Bookmark Review | Bottom | Top  
I have to take issue.... August 07, 2003  
Reviewer: cheaper than disk from USA

Your response about justification for buying disk space is outdated...

"justification

you pay me alot  
disk is cheap.  
think about it.

(sometimes it really can be that simple)"

Anybody else out there NOT paid alot ANYMORE?!

;-)

GOTO a page to Bookmark Review | Bottom | Top  
Re: Explicit Commit - To Nathan August 07, 2003  
Reviewer: A reader

Tom is out till weekend..  
"I'm still pondering why the commit should be there. "  
The commit after dbms\_job is requierd so that other sessions can see the  
job\_queue...which typically is the  
requirement...As usual, Tom can confirm..

Menon:)

GOTO a page to Bookmark Review | Bottom | Top  
Reader August 10, 2003  
Reviewer: A reader

"  
create table invtmp as  
select \* from invins  
UNION ALL  
select t1.\*  
from inv t1, invdel t2  
where t1.key = t2.key(+)  
and t2.key is null;  
"

Like to know why  
"  
where t1.key = t2.key(+)  
and t2.key is null  
"  
better than  
where t1.key != t2.key with ALWAYS\_ANTI\_JOIN hint

Followup:  
because there is no "always anti join" hint?

you could of course use the CBO, and a NOT IN subquery in which case it'll do it  
itself.

GOTO a page to Bookmark Review | Bottom | Top  
August 14, 2003  
Reviewer: Su Baba

So if I have a big table, x, that needs to be updated, I would

create table y nologging as select \* from x...  
retrieve index definition from x  
build indexes on y  
drop table x  
rename y to x

If I have all these done in a PL/SQL batch job, what's the best way to retrieve  
index definition and form create index statement from table x? I want to create  
indexes on y that have the exact storage parameter, tablespace, maybe partition,  
etc. as the ones on table x.

By the way, the database version is 8.1.7.

thanks

Followup:  
you COULD, not WOULD, it is an option.

you don't have your create indexes somewhere?

guess I would exp rows=n, imp indexfile=foo to get them -- or  
DBMS\_METADATA.get\_ddl in 9i

GOTO a page to Bookmark Review | Bottom | Top  
How to update part of a Very Large Table August 28, 2003  
Reviewer: Mohan K from Bangalore, India

please refer "How to update part of a Very Large Table July 28, 2003"

When updating a large table in a database having comparatively less buffer cache  
one thing to make sure is that blocks are updated in a continuous order. Suppose  
some records of block1 is updated first in the beginning and next after updating  
some other blocks, then block1 will be written twice by DBWR and LGWR. After the  
first update block1 may get flushed out by LRU algorithm. If all records in  
block1 is updated at a time then block1 is wriiten by LGWR and DBWR only once.  
If the update statement is doing a full table scan then bloacks are updated in  
order. But the order can be different when the is using an index for selecting  
the rows.

The SQL statement that makes sure of that is

update (select id, description from test\_update where rowid in(select rowid from  
(select rowid from test\_update order by rowid))) set description=description||'  
new val' where description like '%5%';

Followup:  
not necessarily so -- first lgwr doesn't write blocks -- it writes redo.  
second, dbwr may or may not write the block more then once.

I would never never never use your approach. why I would want to

a) read the entire table  
b) sort the rowids  
c) distinct that set (implied)  
d) join to it (and you are making the assumption the table in the IN will be the  
driving table and that a hash join will not be used and ..... lots of other  
stuff)

when a simple:

update T set d = d || 'x' where d like '%5%'

will do it -- if you are worried about the index on d being used - you would  
simply FULL hint it -- but doubtful that it would be necessary.

the additional work you add would more then offset the work you are trying to  
save.

GOTO a page to Bookmark Review | Bottom | Top  
update large table August 28, 2003  
Reviewer: Vera from D.C.

Hi Tom and thank you again and again for all your work.

My problem is that I cannot simply replace update with creating a new table. The  
table I need to update - I'll call it 'pension table' - has about 1.5 mln rows.  
I have to update "amount" fields for 1994, 1995,1995 etc. years, and the way how  
I update these fields depends on status of the participant at each particular  
year. To determine this status I built a function that looks through another  
tables and takes several input parameneters including each particular financial  
year because it determines not just status of a participant, but status he/she  
had in particular FY. (This function I call a "box of cockroaches"). Basically,  
I ask Oracle to bring me annual monthly amount for Mr/Mrs 'X' for, let's say,  
1994 and if 'X' had status 'retired' in 1994 then I want this amount to be  
calculated one way, if 'X' was 'deferred' -another way and if 'X' was 'dead'  
third way.  
As you alredy guessed, updates take long time - about an hour for each year. I  
wonder what approach would you use in a situation like this?  
Thank you  
By the way, what is a status of your new book?

Followup:  
I would use a CASE statement in SQL without using PLSQL to compute the value.

optionally -- i would turn it inside out. instead of sql calling plsql, this  
might be one of the times when plsql calling sql is more appropriate. build up  
procedurally an array of values to update -- forall update them -- and do it  
again until the rows are processed. with a little foresight, you can even  
parallelize this processing and run your procedure N times -- each instance of  
it running against a different slice of the table...

book is "real soon" -- should be in trucks right now.

GOTO a page to Bookmark Review | Bottom | Top  
update large table August 28, 2003  
Reviewer: Luc Gyselinck from Belgium

For large tables, if I have to update more than 20 percent of the rows, I do it  
like Tom: write a query to insert the 'updated' records in a new table, using  
the /\*+ APPEND \*/ hint. You have no indexes to update, no constraints to  
validate and you generate almost no redo. Next, I truncate the old table, after  
I have disabled all constraints and triggers (once again, almost no redo, very  
fast), and then I inject, again with the /\*+ APPEND \*/ hint, the data from the  
new table into the old (very fast, little redo). Indexes get automaticaly  
rebuild AFTER the extents are populated. I reenable the triggers, the  
constraints (with or without validation, as you wish).

If the new values for the columns being updated must come from other tables, I  
NEVER write something like

update t1  
set c1 = (select c2 from t2 where t2.c3 = t1.c4)

In fact, you are performing NESTED LOOPs. Using functions to get the new values  
for the columns is much the same : NESTED LOOPS (even worse: SQL / PLSQL engine  
context switches, open/close cursors in PL/SQL, NO read consistency).

Whenever I find myself in such a situation (typicaly when writing batch  
procedures, during data migrations, data transformations), I make sure my  
queries use HASH joins, I give the session more resources (higher  
SORT\_AREA\_SIZE, higher HASH\_AREA\_SIZE, DB\_MULTIBLOCK\_READ\_COUNT to the max),  
avoid the INDEX RANGE SCANS, do FULL (yes FULL) table scans, do FAST FULL INDEX  
scans, thus bypassing the buffer pool which otherwise gets flushed by the data  
from my queries. And by the way, use LMTs with uniform size extents.

As an example, I rewrote a batch procedure that took 3 days to complete (once,  
many years ago), written the old way (see the update statement above), that now  
does the same job in only 4 hours, on the same (old) hardware, same database,  
but different Oracle version: Oracle 7 / Oracle 8i.

GOTO a page to Bookmark Review | Bottom | Top  
Yes, you are right, August 28, 2003  
Reviewer: Vera from D.C.

but right now I don't see how can I perform my task without update statement and  
a function. In other cases I do exactly the same thing you do on large tables -  
create new table rather than update old one and then rename tables.  
As for LM tablespaces I was begging our DBAs to switch on LMT since I don't know  
when and they keep promising to do it.

GOTO a page to Bookmark Review | Bottom | Top  
Does parallel insert just imply direct? September 08, 2003  
Reviewer: Dennis from Missouri, USA

Tom (or anyone),

Does a parallel insert imply direct (append) insert? I read something in the  
documentation (or is that forbidden territory) ;) awhile back about the append  
hint not being needed on a parallel insert because it was implied.

I issued the following:  
INSERT /\*+ append, nologging, parallel(ext, 8) \*/ INTO  
hrdmadm.ext\_xpayd\_pay\_details ext  
SELECT /\*+ parallel(ext2, 8) \*/ \*  
FROM [hrdmadm.ext\_xpayd\_pay\_details@hip2][hrdmadm.ext_xpayd_pay_details_hip2] ext2

and while I saw the parallel processes spawn on hip2 (for the select), I didn't  
notice any spawn where I was.

I was wondering if that was because parallel insert was synonymous with direct  
insert, or did I mess up the syntax somehow? Is the insert in fact parallel,  
and it's just 'hidden' from us?

Thanks,  
Dennis

Followup:

[http://download-west.oracle.com/docs/cd/B10501\_01/server.920/a96524/c21dlins.htm\#10629][http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96524_c21dlins.htm_10629]

quote:

Serial and Parallel Direct-Path INSERT

When you are inserting in parallel DML mode, direct-path INSERT is the  
default. In order to run in parallel DML mode, the following requirements must  
be met:  
......

GOTO a page to Bookmark Review | Bottom | Top  
Thanks! September 08, 2003  
Reviewer: Dennis from Missouri, USA

My bad was that I didn't alter session. Thanks Tom for your valuable insight.  
I guess I was just too afraid to read down a few more pages when I was in the  
documentation, with that being forbidden territory and all ;) Well maybe not  
forbidden...just hard to put that 'haunted/fear-inspiring' word that would fit  
in there.

Thanks,  
Dennis

GOTO a page to Bookmark Review | Bottom | Top  
Optimizing Query September 11, 2003  
Reviewer: Mohan

BULK collect and BULK insert can improve the performance. Create SQL objects of  
type t1%rowtype and t2%rowtype.  
BULK collect 100 records at a time to the collection array. Create seperate  
collection arrays to store values that are computed or obtained after searching  
the database.  
Do BULK insert/update into the target table. Search this site using key words  
"bulk insert" or "bulk collect" or "bulk update" for more examples.

Other things are  
1. Improve query performance by proper indexing  
2. Remove fragmentation by "alter table move" command  
3. Rebuild indexes  
4. Allow large pctfree if the update causes row migration/row chaining  
5. Size rollback segments and rollback tablespaces

Mohan

Followup:  
\#2 isn't really "a good thing" in general.  
\#3 is really not "a good thing" in general.

reorgs -- so so overrated.

GOTO a page to Bookmark Review | Bottom | Top  
September 11, 2003  
Reviewer: Kailash from NJ

Can you explain which of the 3 operations will be faster. If I want to update  
millions of rows,  
1. then would delete/reinsert be faster or  
2. mere update will be faster  
3. the one you suggested will be faster.  
Can you advise as to why the method you had suggested will be faster than 1 and  
2.

Can you explain why updating millions of rows is not a good idea. Is it based on  
the amount of redo generated and what if the columns I update are not part of  
the index?

Which operation will be the fastest and I request you to explain the various DML  
operations and the amount of redo they generate. ie. I would appreciate if you  
could explain me which DML generates most redo, followed by the second followed  
by the 3rd which generates the least redo.

Currently we have an application that checks for a condition in a table A and  
obtains the unique id. if a record is found, then flushes all of the dependent  
tables for the given id and re-inserts these records back into the dependent  
tables with a set of new values. Is this a good approach? If so, what would be  
the ideal approach?

Followup:  
I can say yes to all three -- each is the fastest.

each is the slowest.

If you have a large bulk update that is done once and hits most of the of rows  
\-- you may be best off by doing a CTAS, drop old and rename new.

the CTAS, indexing, etc can be done in nologging mode -- no undo, no redo. you  
end up getting done much faster.

GOTO a page to Bookmark Review | Bottom | Top  
updating millions of records October 03, 2003  
Reviewer: b from austin TX

Hi,  
The approach of updating the view instead of a table works great.  
But here i want to update multiple colums of the same table based on different  
table queries.  
we have around 600,000 records in the base table.  
what could be the most inexpensive query.  
We run oralce 9i.

UPDATE user1.UserSummary  
Set  
Column1 = (  
SELECT Count(DISTINCT WebOrderID)  
FROM user1.OrderTotal ot  
WHERE total>0 and  
(ot.ostatus <> -1 and  
ot.ostatus <> -1) and  
ot.UREFITEM = user1.UserSummary.UREFITEM)),  
Column2 = (  
SELECT NVL(Sum(Total),0) FROM  
user1.OrderTotal ot  
WHERE (ot.ostatus <> -1 and ot.ostatus <> -1)  
and ot.UREFITEM = user1.UserSummary.UREFITEM),  
Column3 = (  
SELECT min(sodate) FROM orders.salesorders so left join  
user1.ordertotal ot on ot.sorefitem = so.sorefitem  
WHERE total>0 and (so.ostatus <> -1 and so.ostatus <> -1)  
and so.UREFITEM = user1.UserSummary.UREFITEM),  
Column4 = (  
SELECT max(sodate) FROM orders.salesorders so  
left join user1.ordertotal ot on ot.sorefitem = so.sorefitem  
WHERE total>0 and (so.ostatus <> -1 and so.ostatus <> -1) and  
so.UREFITEM = user1.UserSummary.UREFITEM)  
;  
the above is the sample of my query

thanks in advance  
;;)

Followup:  
if you can come up with a single query that pulls all 4 columns together in a  
single result set -- we can optimize this. Left as an exercise for you -- see  
if you cannot merge the above 4 separate query into a single query

GOTO a page to Bookmark Review | Bottom | Top  
great October 17, 2003  
Reviewer: b from tx

thanks  
i finally made my query simple

GOTO a page to Bookmark Review | Bottom | Top  
Great October 21, 2003  
Reviewer: Raj from San Diego CA USA

Great technique never thought of it.

GOTO a page to Bookmark Review | Bottom | Top  
October 22, 2003  
Reviewer: Anthony Reddy from Malaysia, KL

GOTO a page to Bookmark Review | Bottom | Top  
Updating 1 million records October 22, 2003  
Reviewer: Kailash from NJ, USA

I have a situation where I have a unique key on 3 columns (c1,c2,c3) on a table  
T and I want to update c2 from A to B for some records that match certain  
criteria. There are a total of 30 million records in the table and there will be  
about 1 million records in the table that match the criteria to be updated. The  
table is not partitioned.  
1. Your suggestion to drop the old table and to re-create a new table and  
re-naming it to the old table may not be possible because I am updating only a  
subset of the table. Is there any other method to acheive this ?  
2. Which one of the following will be a better solution?  
a. Drop index and update the rows and recreate the  
index  
b. Delete the rows with C2 = A and insert new rows  
with C2 = B. My DBA says that this will create  
fragmentation and is not a better approach. Is  
that true?  
c. Do you suggest any better solution under these  
scenarios since I keep getting these type of  
questions from my manager often.

Your suggestion in this regard is highly appreciated.

Followup:  
have you considered just "update set c2 = b where ...."?

1,000,000 rows out of 30,000,000 isn't alot. if c2 is indexed, you might  
benefit from a set unusable, skip unusable, update, rebuild.

but it won't take that long to update 1,000,000 rows. couple of minutes on my  
laptop

[big\_table@ORA920][big_table_ORA920]> update big\_table set created = created+1;  
  
1833792 rows updated.  
  
Elapsed: 00:02:13.28

not a big deal.

GOTO a page to Bookmark Review | Bottom | Top  
How about rollback segment problem when updating million rows. October 22, 2003  
Reviewer: Ramasamy from NC USA

Tom ,

Thanks lot for your service.

I have to update a single column NUMBER(9) where  
the table has got more than 80 columns.  
This table has got 30 million rows and i have to update  
around 17 million rows each night.

As you always insisted ,  
i tried with a single update like

UPDATE t1 SET batchno = (A constant )  
WHERE batchno is NULL;

1.)I came across the rollback segment issue.  
2.) It is taking around 3 hrs to update.

Your input is greatly appreciated to solve the above issues.

Thanking You,  
Ramasamy

Followup:  
there is no problem -- other then you haven't sized your system to do the things  
you require it to do!!!!!!!!

1) the rollback segment "issue" is a "non issue". size things for what you do  
and there are NO ISSUES. disk is cheap -- compared to what they pay us all to  
have this discussion. consider that. the amount of time you've spent thinking  
about this already cost your employer much more then the disk it would take to  
do this correctly.

2) you are changing a null to a non-null -- increasing the width of the field.  
make sure you are not causing massive row migration (check your pctfree, make  
sure it is set appropriately here). is batchno "indexed"?

GOTO a page to Bookmark Review | Bottom | Top  
MILLION RECORD INSERT/SELECT/UPDATE TAKES TOO MUCH TIME October 23, 2003  
Reviewer: Santosh Das from Bangalore, INDIA.

Hi Tom,  
I have a table A and soon after creating it 1million records are put into it.  
Then it being in a real time environment its size goes on increasing atleast  
half million each day.However each day a cleanup application cleans half million  
every day.After inserting 1 million at the creation of table its put in the  
realtime environment.When ever a new record is inserted a contuniously listening  
application detects it and applies a select operation on table A. Then it send  
the selected items to some table B and then updates the table A.  
The listening operation may be 3 or 4 in number.How ever the select and update  
operations are taking a lot of time and the initial insertion of 1 million  
records is taking 11 hours to complete so what can be the possible architecture.  
I tried sending the rowid to a queue/pipe after inserting in table A and then  
used it to select and update.But still its taking a lot of time. Plz suggest  
some method.  
Thanks and regards,  
Sandy..

Followup:  
sounds like you should just be using AQ -- plop the record in as a message --  
have as many dequeuers as you like.

not sure what else to say, not a ton of detail here.

GOTO a page to Bookmark Review | Bottom | Top  
Select and update of a table Having Millions of Records. October 23, 2003  
Reviewer: Jack from NY

Hi Tom,  
I have the following queries which execute on a table of million records.That  
table has a PK but its not used in both the queries given below.CMTIME,SAD and  
STATUS all are NUMBER fields.If I make index on cmtime then it takes longer.  
Presently i dont have a index field.Can you plz tellme how i can optimize these  
or where the performance bottleneck is ?

update SD set CMTIME = 1064929834 where (CMTIME is NULL or 1064929834 - CMTIME  
>= 10) and (1064929834 - SAD >= 0) and 1064929834 not in (select CMTIME from SD  
where CMTIME is not NULL)

SELECT \* from SD where (CMTIME is not NULL and (CMTIME = 1064929834 OR  
1064929834 - CMTIME >= 10)) AND (ED - 1064929834 > 0 and (STATUS = 1 OR STATUS =  
0) and 1064929834 - SAD >= 0)

Thanks and regards,  
Jack.

Followup:  
are you using the CBO.

GOTO a page to Bookmark Review | Bottom | Top  
Followup to your question October 23, 2003  
Reviewer: Ramasamy from NC USA

Yes Tom. The Batchno is indexed like  
index on (BATCHno,STATUS);

Will it be worth Droping the index and recreating it?.

Thanks,  
Ramasamy

Followup:  
could definitely help, yes.

GOTO a page to Bookmark Review | Bottom | Top  
October 23, 2003  
Reviewer: George Lee from Hong Kong

I tried to update 30 million records using Tom's solution.  
It works fine. Using approximately 1.5 hours to finish.

George (Broadway Photo Supply Ltd. HK)

GOTO a page to Bookmark Review | Bottom | Top  
whats CBO October 24, 2003  
Reviewer: Jack from NY

Followup:  
cost based optimizer

GOTO a page to Bookmark Review | Bottom | Top  
No October 27, 2003  
Reviewer: jack from NY

We are not using any cost based optimizers.  
It's a simple query which we are using to retrieve data from a table having  
millions of records and it's performance is not satisfactory.  
How to optimize it so that it runs faster ?  
jack

Followup:

you are kidding right?

this goes right up there with "my car won't start, why not?"

there is so much missing from this "request" as to make it impossible to say  
anything sensible.

GOTO a page to Bookmark Review | Bottom | Top  
November 11, 2003  
Reviewer: George Lee from Hong Kong

I have a fact table being partitioned by month. The indexes are built local to  
partition.

The data population is working fine.

But the rebuild index fails at the end.

I don't know how to rebuild indexes in the partitions.  
Can you help me ?

Followup:  
why do you need to rebuild here at all??

but why don't you show us what you are doing -- at the very least -- define what  
"fails at the end" means (error codes, messages, details)

GOTO a page to Bookmark Review | Bottom | Top  
November 12, 2003  
Reviewer: George Lee from Hong Kong

Dear Tom,

Yes, I should provide enough information to you. Sorry for that.

Here is the story,

I create a table as following,

drop table D\_DYNRPT\_SALES\_FACT;

create table D\_DYNRPT\_SALES\_FACT  
(  
TX\_DATE DATE,  
SHOP\_NO VARCHAR2(20),  
ITEM\_NO VARCHAR2(12),  
PARENT\_ITEM\_NO VARCHAR2(12),  
BRAND\_NO VARCHAR2(5),  
VENDOR\_NO VARCHAR2(20),  
ITEM\_GROUP VARCHAR2(5),  
ITEM\_TYPE VARCHAR2(5),  
CONSIGNMENT VARCHAR2(1),  
ITEM\_DESC VARCHAR2(40),  
BRAND\_DESC VARCHAR2(30),  
VENDOR\_DESC VARCHAR2(30),  
CATEGORY\_DESC VARCHAR2(30),  
QTY\_SOLD NUMBER(14,2),  
NET\_SALES\_AMT NUMBER(14,2),  
GROSS\_PROFIT\_AMT NUMBER(14,2)  
)  
PARTITION BY RANGE (TX\_DATE)  
( PARTITION D\_DYNRPT\_SALES\_FACT\_2000\_09 VALUES LESS THAN  
(TO\_DATE('01/10/2000','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2000\_09,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2000\_10 VALUES LESS THAN  
(TO\_DATE('01/11/2000','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2000\_10,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2000\_11 VALUES LESS THAN  
(TO\_DATE('01/12/2000','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2000\_11,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2000\_12 VALUES LESS THAN  
(TO\_DATE('01/01/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2000\_12,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_01 VALUES LESS THAN  
(TO\_DATE('01/02/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_01,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_02 VALUES LESS THAN  
(TO\_DATE('01/03/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_02,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_03 VALUES LESS THAN  
(TO\_DATE('01/04/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_03,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_04 VALUES LESS THAN  
(TO\_DATE('01/05/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_04,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_05 VALUES LESS THAN  
(TO\_DATE('01/06/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_05,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_06 VALUES LESS THAN  
(TO\_DATE('01/07/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_06,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_07 VALUES LESS THAN  
(TO\_DATE('01/08/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_07,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_08 VALUES LESS THAN  
(TO\_DATE('01/09/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_08,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_09 VALUES LESS THAN  
(TO\_DATE('01/10/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_09,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_10 VALUES LESS THAN  
(TO\_DATE('01/11/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_10,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_11 VALUES LESS THAN  
(TO\_DATE('01/12/2001','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_11,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2001\_12 VALUES LESS THAN  
(TO\_DATE('01/01/2002','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2001\_12,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2002\_01 VALUES LESS THAN  
(TO\_DATE('01/02/2002','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2002\_01,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2002\_02 VALUES LESS THAN  
(TO\_DATE('01/03/2002','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2002\_02,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2002\_03 VALUES LESS THAN  
(TO\_DATE('01/04/2002','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2002\_03,  
PARTITION D\_DYNRPT\_SALES\_FACT\_2002\_04 VALUES LESS THAN  
(TO\_DATE('01/05/2002','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2002\_04,  
.  
.  
.  
PARTITION D\_DYNRPT\_SALES\_FACT\_2004\_12 VALUES LESS THAN  
(TO\_DATE('01/01/2005','DD/MM/YYYY'))  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_2004\_12,  
PARTITION D\_DYNRPT\_SALES\_FACT\_MAXVALUE VALUES LESS THAN (maxvalue)  
TABLESPACE D\_DYNRPT\_SALES\_FACT\_MAXVALUE  
)  
/

CREATE INDEX D\_DYNRPT\_SALES\_FACT\_I01 ON D\_DYNRPT\_SALES\_FACT  
(TX\_DATE) LOCAL;

CREATE INDEX D\_DYNRPT\_SALES\_FACT\_I02 ON D\_DYNRPT\_SALES\_FACT  
(TX\_DATE, ITEM\_GROUP, ITEM\_TYPE) LOCAL;

CREATE INDEX D\_DYNRPT\_SALES\_FACT\_I03 ON D\_DYNRPT\_SALES\_FACT  
(TX\_DATE, SHOP\_NO, ITEM\_NO, ITEM\_GROUP) LOCAL;

CREATE INDEX D\_DYNRPT\_SALES\_FACT\_I04 ON D\_DYNRPT\_SALES\_FACT  
(TX\_DATE, BRAND\_NO, ITEM\_GROUP, ITEM\_TYPE) LOCAL;

CREATE INDEX D\_DYNRPT\_SALES\_FACT\_I05 ON D\_DYNRPT\_SALES\_FACT  
(TX\_DATE, ITEM\_NO) LOCAL;

CREATE OR REPLACE PUBLIC SYNONYM D\_DYNRPT\_SALES\_FACT FOR  
BPSADM.D\_DYNRPT\_SALES\_FACT;

then, populate data into the table as the following,

alter index D\_DYNRPT\_SALES\_FACT\_I01 unusable;  
alter index D\_DYNRPT\_SALES\_FACT\_I02 unusable;  
alter index D\_DYNRPT\_SALES\_FACT\_I03 unusable;  
alter index D\_DYNRPT\_SALES\_FACT\_I04 unusable;  
alter index D\_DYNRPT\_SALES\_FACT\_I05 unusable;

alter session set skip\_unusable\_indexes=true;

@pop\_d\_dynrpt\_sales\_fact.sql '2000/09/01' '2000/01/30';  
.  
.  
.  
@pop\_d\_dynrpt\_sales\_fact.sql '2003/11/01' '2003/11/30';

alter index D\_DYNRPT\_SALES\_FACT\_I01 rebuild nologging;  
alter index D\_DYNRPT\_SALES\_FACT\_I02 rebuild nologging;  
alter index D\_DYNRPT\_SALES\_FACT\_I03 rebuild nologging;  
alter index D\_DYNRPT\_SALES\_FACT\_I04 rebuild nologging;  
alter index D\_DYNRPT\_SALES\_FACT\_I05 rebuild nologging;

the data\_population is working fine, but alter index rebuild nologging fail with  
the error as following,

alter index D\_DYNRPT\_SALES\_FACT\_I01 rebuild nologging  
\*  
ERROR at line 1:  
ORA-14086: a partitioned index may not be rebuilt as a whole

alter index D\_DYNRPT\_SALES\_FACT\_I02 rebuild nologging  
\*  
ERROR at line 1:  
ORA-14086: a partitioned index may not be rebuilt as a whole

alter index D\_DYNRPT\_SALES\_FACT\_I03 rebuild nologging  
\*  
ERROR at line 1:  
ORA-14086: a partitioned index may not be rebuilt as a whole

alter index D\_DYNRPT\_SALES\_FACT\_I04 rebuild nologging  
\*  
ERROR at line 1:  
ORA-14086: a partitioned index may not be rebuilt as a whole

alter index D\_DYNRPT\_SALES\_FACT\_I05 rebuild nologging  
\*  
ERROR at line 1:  
ORA-14086: a partitioned index may not be rebuilt as a whole

I checked the reference, it should be something syntax error on my rebuild index  
statement, it is like alter index D\_DYNRPT\_SALES\_FACT\_I05 rebuild partition...,  
I don't know the exact statement, can you help me, thanks.

Followup:  
[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]> CREATE TABLE t  
2 (  
3 data char(255),  
4 temp\_date date  
5 )  
6 PARTITION BY RANGE (temp\_date) (  
7 PARTITION part1 VALUES LESS THAN (to\_date('13-mar-2003','dd-mon-yyyy')) ,  
8 PARTITION part2 VALUES LESS THAN (to\_date('14-mar-2003','dd-mon-yyyy')) ,  
9 PARTITION part3 VALUES LESS THAN (to\_date('15-mar-2003','dd-mon-yyyy')) ,  
10 PARTITION part4 VALUES LESS THAN (to\_date('16-mar-2003','dd-mon-yyyy')) ,  
11 PARTITION part5 VALUES LESS THAN (to\_date('17-mar-2003','dd-mon-yyyy')) ,  
12 PARTITION part6 VALUES LESS THAN (to\_date('18-mar-2003','dd-mon-yyyy')) ,  
13 PARTITION junk VALUES LESS THAN (MAXVALUE)  
14 )  
15 ;

Table created.

[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]>  
[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]> create index t\_idx1 on t(temp\_date) LOCAL nologging;

Index created.

[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]>  
[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]> alter index t\_idx1 unusable;

Index altered.

[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]>  
[ops$tkyte@ORA920LAP][ops_tkyte_ORA920LAP]> begin  
2 for x in ( select 'alter index ' || index\_name ||  
3 ' rebuild partition ' || partition\_name stmt  
4 from user\_ind\_partitions  
5 where index\_name = 'T\_IDX1' )  
6 loop  
7 dbms\_output.put\_line( x.stmt );  
8 execute immediate x.stmt;  
9 end loop;  
10 end;  
11 /  
alter index T\_IDX1 rebuild partition PART1  
alter index T\_IDX1 rebuild partition PART2  
alter index T\_IDX1 rebuild partition PART3  
alter index T\_IDX1 rebuild partition PART4  
alter index T\_IDX1 rebuild partition PART5  
alter index T\_IDX1 rebuild partition PART6  
alter index T\_IDX1 rebuild partition JUNK

PL/SQL procedure successfully completed.

GOTO a page to Bookmark Review | Bottom | Top  
December 11, 2003  
Reviewer: A reader

Hi Tom,  
I am running below update statement from past 24 hr and it's still running..

Table S\_REVN has 1.5 Million and Table S\_OPTY\_PROD has 1.2 Million Record.. It's  
doing full table scan of both table sinace I am using function in where clause..  
We using RULE base optimizer and oracle 8.1.7.4

Here is sql statement

Update siebel.S\_REVN r  
set x\_opty\_prod\_city=(select X\_CITY from siebel.S\_OPTY\_PROD p where  
replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') = r.row\_id),  
X\_opty\_prod\_STATE =(select X\_STATE from siebel.S\_OPTY\_PROD p where  
replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') = r.row\_id),  
X\_PR\_YEAR\_QTY =(select X\_PR\_YEAR\_QTY from siebel.S\_OPTY\_PROD p where  
replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') = r.row\_id)

0 UPDATE STATEMENT Optimizer=RULE  
1 0 UPDATE OF 'S\_REVN'  
2 1 TABLE ACCESS (FULL) OF 'S\_REVN'  
3 0 TABLE ACCESS (FULL) OF 'S\_OPTY\_PROD'  
4 0 TABLE ACCESS (FULL) OF 'S\_OPTY\_PROD'  
5 0 TABLE ACCESS (FULL) OF 'S\_OPTY\_PROD'

Please advice  
Thanks

Followup:  
you understand that it is doing 3 full scans of S\_OPTY\_PROD for EACH and EVERY  
row in S\_REAVN yes???

how about

update t  
set (a,b,c) = (select a,b,c from t2 where ... )

at the very least (reduce the full scans from 3 to 1)

at the very most, consider creating an index:

create index  
temp\_index\_to\_make\_this\_complete\_before\_my\_grandchildren\_have\_grandchildren  
on p(replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$'))

and useing a hint (to let sighbul use the cbo) to get the index used.

GOTO a page to Bookmark Review | Bottom | Top  
December 12, 2003  
Reviewer: A reader

Hi Tom,

I am following your advice on update I've changed sql statement

Update /\*+ INDEX(S\_OPTY\_PROD T\_TEMP) \*/ siebel.S\_REVN r  
set (x\_opty\_prod\_city,X\_opty\_prod\_STATE,X\_PR\_YEAR\_QTY) =  
(select X\_CITY,X\_STATE,X\_PR\_YEAR\_QTY from siebel.S\_OPTY\_PROD p  
where replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') = r.row\_id)

created function based index on S\_OPTY\_PROD

create index T\_TEMP  
on S\_OPTY\_PROD(replace(replace(replace(ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$'))

BUT above sql statement in not using index eventhough I supply hint (Is it b/c  
function based index not reconized by Rule based optimizer?)

Please help  
Thanks

Followup:  
you put the hint in the wrong place :)

and you might not have met all of the requirements for FBI's. read:

[http://asktom.oracle.com/~tkyte/article1/index.html][http_asktom.oracle.com_tkyte_article1_index.html]

but it is not the RBO (the well formed hint -> cbo)....

your hint is wrong (must use the correlation name of P) and in the wrong place  
(should be in the subquery).

you need query\_rewrite\_enabled and (depending on version) might need  
query\_rewrite\_integrity (see the above link).

but here is an example:  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table t1( x\_opty\_prod\_city int, x\_opty\_prod\_state int,  
x\_pr\_year\_qty int, row\_id varchar2(25) );  
  
Table created.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> create table t2( x\_city int, x\_state int, x\_pr\_year\_qty int,  
row\_id varchar2(25) );  
  
Table created.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> create index t2\_idx on t2(  
replace(replace(replace(ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') );  
  
Index created.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> delete from plan\_table;  
  
4 rows deleted.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> ALTER SESSION SET QUERY\_REWRITE\_ENABLED=true;  
  
Session altered.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> explain plan for  
2 Update t1 r  
3 set (x\_opty\_prod\_city,X\_opty\_prod\_STATE,X\_PR\_YEAR\_QTY) =  
4 (select /\*+ INDEX( t2 t2\_idx ) \*/ X\_CITY,X\_STATE,X\_PR\_YEAR\_QTY  
5 from t2 p  
6 where replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$')  
= r.row\_id)  
7 /  
  
Explained.  
  
[ops$tkyte@ORA920][ops_tkyte_ORA920]>  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> prompt @?/rdbms/admin/utlxpls  
@?/rdbms/admin/utlxpls  
[ops$tkyte@ORA920][ops_tkyte_ORA920]> set echo off  
  
PLAN\_TABLE\_OUTPUT  
\---------------------------------------------------------------------------------  
\--------------------------------------------------  
  
\-------------------------------------------------------------------------------  
| Id | Operation | Name | Rows | Bytes |Cost(%CPU)|  
\-------------------------------------------------------------------------------  
| 0 | UPDATE STATEMENT | | 82 | 4346 | 3 (34)|  
| 1 | UPDATE | T1 | | | |  
| 2 | TABLE ACCESS FULL | T1 | 82 | 4346 | 3 (34)|  
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 53 | 2 (50)|  
|\* 4 | INDEX RANGE SCAN | T2\_IDX | 1 | | 2 (50)|  
\-------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
  
4 -  
access(REPLACE(REPLACE(REPLACE("P"."ROW\_ID",'ce','='),'-','='),'@','$')=:B  
1)  
  
16 rows selected.

GOTO a page to Bookmark Review | Bottom | Top  
December 12, 2003  
Reviewer: A reader

hello Tom,I create table as following using the parallel hint,but the cost is  
different:

sql>insert /\*+ append parallel(demo01,4) \*/ into demo01 select /\*+  
parallel(customer,4) \*/ \* from customer;  
the cost --42s  
and the Execution Plan is:  
Execution Plan  
\----------------------------------------------------------  
ERROR&\#65306;  
ORA-12838:  
&\#26080;&\#27861;&\#22312;&\#24182;&\#34892;&\#27169;&\#24335;&\#19979;&\#20462;&\#25913;&  
\#20043;&\#21518;&\#35835;/&\#20462;&\#25913;&\#23545;&\#35937;  
SP2-0612: &\#29983;&\#25104;AUTOTRACE  
EXPLAIN&\#25253;&\#21578;&\#26102;&\#20986;&\#29616;&\#38169;&\#35823;

Statistics  
\----------------------------------------------------------  
1698 recursive calls  
455 db block gets  
8728 consistent gets  
8075 physical reads  
66492440 redo size  
630 bytes sent via SQL\*Net to client  
525 bytes received via SQL\*Net from client  
3 SQL\*Net roundtrips to/from client  
11 sorts (memory)  
0 sorts (disk)  
390126 rows processed

sql>insert \* from demo02 select \* from customer;  
cost --26s  
and the Execution Plan is:  
Execution Plan  
\----------------------------------------------------------  
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=1226 Card=659568 Byt  
es=680674176)

1 0 PARTITION RANGE (ALL)  
2 1 TABLE ACCESS (FULL) OF 'CUSTOMER' (Cost=1226 Card=659568  
Bytes=680674176)

Statistics  
\----------------------------------------------------------  
1939 recursive calls  
44876 db block gets  
20918 consistent gets  
1 physical reads  
64954576 redo size  
646 bytes sent via SQL\*Net to client  
467 bytes received via SQL\*Net from client  
3 SQL\*Net roundtrips to/from client  
7 sorts (memory)  
0 sorts (disk)  
390126 rows processed

The customer table have 0.4 million records and the machine is 2 CPU's SUN  
E420,which runiing Oracle database.

Can you tell me why the physical reads increase so much using parallel hint in  
the DML sql,thanks

Followup:  
well, for a tiny number of records like this, i would not even consider PQ at  
this point.

But -- to answer your questions

the cost, well the cost is affected by thousands of things. HINTS definitely  
affect the cost (That is in part how hints work -- by playing with costs).  
Ignore the cost of two queries that are not the same, they are NOT comparable.

the PIO's -- well, you ran one before the other? That and parallel query  
prefers to checkpoint -- and do direct io many times (you might find PQ doing a  
global checkpoint before the query begins to get current images onto disk in  
order to just all hit slam the data as fast as it can without messing with the  
buffer cache)

but for so few records -- it would seem that regular sql is what you want.

GOTO a page to Bookmark Review | Bottom | Top  
December 13, 2003  
Reviewer: A reader

Hi Tom,  
Followed you advice...WORKS LIKE A CHARM..updated 1.5 Million recod in 10 Min.  
EXCELLENT!!!!!!!

Thanks a LOTTTTTTTTTTT

Update siebel.S\_REVN r  
set (x\_opty\_prod\_city,X\_opty\_prod\_STATE,X\_PR\_YEAR\_QTY) =  
(select /\*+ INDEX(S\_OPTY\_PROD T\_TEMP) \*/ X\_CITY,X\_STATE,X\_PR\_YEAR\_QTY from  
siebel.S\_OPTY\_PROD p  
where replace(replace(replace(p.ROW\_ID,'ce','='),'-','='), ['@'][Link 1], '$') = r.row\_id)

GOTO a page to Bookmark Review | Bottom | Top  
Online updation of very big table March 06, 2004  
Reviewer: Praveen from Delhi

Hi Tom  
Iwant to update a table with one go on online system.A table has 20 lakh records  
and has 110 columns. when i give the update command it takes ~ one hrs.I don't  
know why it is taking so much time even index is created on that particular  
field.

select count(\*),app\_flg from test;  
app\_flag has only two values Approved or unapproved.By defualt is unapproved.  
approved =170000  
unapproved=30000  
update test set app\_flg='APPROVED' where app\_flg='UNAPPROVED'  
it took 1hrs to update the records and other application online users processing  
got slow down and locks started to occure on the table.  
  
Suggest the best solution  
pkr

Followup:  
It would be the INDEX that slowed it down.

You want to FULL SCAN this table.  
You want there to be NO index on the column you are updating.

GOTO a page to Bookmark Review | Bottom | Top  
it gives error March 07, 2004  
Reviewer: praveen from india

on dropping the index it gives error ora-00054

thanks

Followup:  
that'll happen whilst there are outstanding transactions, yes. You'll want to  
keep trying until you get a chance to drop it.

GOTO a page to Bookmark Review | Bottom | Top  
thanks March 07, 2004  
Reviewer: jasdeep,praveen from patiala india

i have solved that problem as an user has locked rows on the but at present not  
logged on i killed that session and index was dropped immediately.  
and updates were as fast as u can think.

thanks

GOTO a page to Bookmark Review | Bottom | Top  
Update taking hrs of time March 09, 2004  
Reviewer: Sachin from India

Hi Tom,  
I have a query as:  
I have two tables Table1(with around 10000 records max) and table2(actually the  
GL code combination master table with around 600,000 records). I need to update  
three fields in table1 (which is a temp processing table) with a unique value  
from table2. T1 has ccid fields which need to be updated, and s1-s4 fields  
corresponding to segment1-segment4 fields of table2  
Following is the query like:

UPDATE table1 t1  
SET t1.ccid1 = (SELECT t2.code\_combination\_id  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = t1.s4  
and t2.segment3 = 'XXXXX'  
and t2.segment2 = t1.s2  
and t2.segment1 = t1.s1  
and t2.coa\_id = gb.chart\_of\_accounts\_id  
and gb.set\_of\_books\_id = 1),  
t1.ccid2 = (SELECT t2.code\_combination\_id  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = t1.s4  
and t2.segment3 = 'zzzzzz'  
and t2.segment2 = t1.s2  
and t2.segment1 = t1.s1  
and t2.coa\_id = gb.coa\_id  
and gb.set\_of\_books\_id = 1),  
t1.ccid3 = (SELECT t2.ccid  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = t1.s4  
and t2.segment3 = 'wwwww'  
and t2.segment2 = t1.s2  
and t2.segment1 = t1.s1  
and t2.coa\_id = gb.coa\_id  
and gb.set\_of\_books\_id = 1

But this is taking more than 6 hrs to complete. When I check the table locks the  
table remains locked Row Xclusively. I am committing immediately after the  
update statement in the procedure. Cud u pls tell me why is this happening?

Followup:  
not that i don't believe you but -- about the locking issue -- I'm not sure i  
believe you.

when you commit -- locks are released. You'd have to show me the info from  
v$lock showing that t1 is still "locked" and locked by your session.

you could

set (c1,c2,c3) = ( select max(decode(segment3,'xxxxx',ccid)),  
max(decode(segment3,'zzzzz',ccid)),  
max(decode(segment3,'wwwww',ccid))  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = t1.s4  
and t2.segment3 in ('xxxxx','zzzzzz', 'wwwww')  
and t2.segment2 = t1.s2  
and t2.segment1 = t1.s1  
and t2.coa\_id = gb.coa\_id  
and gb.set\_of\_books\_id = 1 )

instead of running a query per column.

if you run the query

select max(decode(segment3,'xxxxx',ccid)),  
max(decode(segment3,'zzzzz',ccid)),  
max(decode(segment3,'wwwww',ccid))  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = some\_value\_value  
and t2.segment3 in ('xxxxx','zzzzzz', 'wwwww')  
and t2.segment2 = some\_value\_value  
and t2.segment1 = some\_value\_value  
and t2.coa\_id = gb.coa\_id  
and gb.set\_of\_books\_id = 1

how long does that take?

GOTO a page to Bookmark Review | Bottom | Top  
Updating millions of rows March 15, 2004  
Reviewer: A reader from NJ, USA

Tom,

In this discussion you mention that you would not do an UPDATE/DELETE of  
millions of rows, but rather would create a new table (in parallel/nologging)  
with the rows that needs to be retained and then create indexes in parallel,  
drop the old table and rename the new table to old table. If so, my  
understanding is :

1. Delete/UPDATE of tables with more than 1 million row would also have to  
maintain the index structure and also will generate considerable rollback and  
undo.  
2. Other consideration with respect to update/delete is that the blocks will be  
moved in and out of the freelists. Please confirm if this is true and if so,  
please explain what happens during update/delete of large number of rows with  
regard to freelists?  
3. Other consideration is that it will cause lot of table/segment fragmentation  
and lot of empty blocks below the HWM and all full table scans on the table will  
read lot of empty blocks.

Is my understanding correct and if I have missed any other things as to why  
DELETE/UPDATE of large tables is not recommended, please let me know.

4. If my table that needs to be updated/deleted is the primary/parent table with  
dependent child tables/constraints and triggers, is this approach correct  
Disable all the triggers/constraints  
Drop the table  
Rename the new table to the old table  
Reenable all constraints and triggers  
What to do in such cases?

Followup:  
I "might", "probably" if it was most of the records...

1) correct  
2) correct, deletes would put them on, updates might put them on, might take  
them off.

3) it could, yes.

the delete/update can take lots longer than CTAS + reindex if you are doing MOST  
all of the records in a LARGE table with lots of indexes.

4) triggers are not relevant here....

but you would have to drop the fkeys  
do the CTAS+drop+rename  
add the constraints back with enable/rely (don't need validation)

GOTO a page to Bookmark Review | Bottom | Top  
Deletes/Updates March 15, 2004  
Reviewer: A reader from NJ, USA

Tom,

In the above discussion you mention :  
1. The deletes will put them on because the used space in the block will drop  
below PCTUSED. Is it correct?  
2. The update will put them on if the updated column makes the used space in  
the block fall below PCTUSED or the updated column makes the free space  
available in the block less than PCTFREE. Is it correct.

If both of the above understanding is not correct, please explain. Also, please  
let me know how we could track the movement of the blocks on and off the  
freelist. Any SQL scripts/tools available that I could use?

Thanks

Followup:  
1) the deletes could, yes.

2) yes.

to track individual blocks -- not without dumping blocks (and I don't go there).  
dbms\_space and other tools (search this site for show\_space for the script I  
use) can show you how MANY things are on the freelist.

GOTO a page to Bookmark Review | Bottom | Top  
A small correction March 15, 2004  
Reviewer: A reader from NJ, USA

Tom,

In the above scenario for question 2, there is a small correction marked in  
CAPITAL letters :  
"The update will put them on if the updated column makes the used space in the  
block fall below PCTUSED AND WILL TAKE OFF of the freelist if the updated column  
makes the free space available in the block less than PCTFREE. "  
Is it correct?

Thanks

Followup:  
I'll state it simply, forget pctfree/pctused/etc -- in english:

if the update reduces the row size, it can put it back onto the freelist.

if the update increases the row size, it can take it off the freelist.

GOTO a page to Bookmark Review | Bottom | Top  
Parallel DML March 16, 2004  
Reviewer: A reader

1)On page 630 of your Oracle Expert One on One, you mention that parallel DML  
can be used only upon partitions -- in 8i. I'm using 9.2. Has this behavior  
changed in the later versions ?

2)I need to do a mass delete on a 12 GB table and it's taking to much time (far  
too much, really). How could I speed it up ?

Thanks

Followup:  
1) yes, that is a 9ir2 new feature.

[http://download-west.oracle.com/docs/cd/B10501\_01/server.920/a96531/ch2\_9ir2.htm\#74432][http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96531_ch2_9ir2.htm_74432]

2) did you read the original answer above?

GOTO a page to Bookmark Review | Bottom | Top  
enable PK with parallel clause March 20, 2004  
Reviewer: A reader

I am working in 8.1.7.4.

I want to delete 30 million rows of 60 million so I am doing this

1. create a copy of original table with good data  
2. disable original tables′s constraints, primary keys (and child FKs) and  
foreign keys  
3. truncate the original table  
4. make all original tables indexes unusable  
5. insert append back from copy to original, I have to do this way because I am  
not allowed to modify constraint names  
6. drop the copy  
7. enable constraints. here when I enable PK I cannot provide paralle clause  
right? I searched the doco but it seems that I can only specify using index  
tablespace clause or I am missing something?  
8. rebuild all unusable indexes

Followup:  
you can create an index in parallel (unique) and then enable/rely/novalidate the  
pk.

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table March 25, 2004  
Reviewer: A reader

Hi Tom,  
I read your response to Murali's question above and believe, there will be a  
downtime window for the application. This is because if I want to keep the  
index names same as before, I will have to create the new table, drop the old  
table, rename the new table to old table name and then create the required  
indexes on the same.

Wondering, how if we can create the indexes by some other name on the new table  
and rename the indexes after dropping the old table.

As always your valuable advice helps a lot!

Followup:  
you can rename the indexes.

alter index supports this.

GOTO a page to Bookmark Review | Bottom | Top  
how to make this restartble? March 25, 2004  
Reviewer: A reader

I want to delete 80% of rows from a couple of 50 million rows table. The process  
is divided in 10 steps

1. Create TEMP table, CTAS from original table  
2. Disable constraints  
3. Truncate original table  
4. Set indexes to UNUSABLE  
5. Insert into original table from TEMP table  
6. Drop TEMP table  
7. Create PK and UK  
8. Enable PK and UK  
9. Enable FKs  
10. Rebuild indexes

I want to make this process restartble, i.e if it fails in step 3 if I later  
rerun the procedure it will start from step 3 again. How can we achiveve this? I  
have been thinking using a table which stores the two table name and process  
status something like following table then update the status as the process is  
going

TABLE: PROCESS\_STATUS  
TABLE\_NAME STATUS  
\----------- ---------------  
EMP 2  
DEPT 0

Process\_status is the step, 0 means that I need to start from beginning so in  
each process I would query this table and see which step should be run. For  
example if the process failed at step 3 then STATUS would be 2

select status into l\_proc\_status from PROCESS\_STATUS  
where table\_name = 'EMP'  
if l\_proc\_status = 0  
then  
run process 1  
end if;

select status into l\_proc\_status from PROCESS\_STATUS  
where table\_name = 'EMP'  
if l\_proc\_status = 1  
then  
run process 2  
end if;

select status into l\_proc\_status from PROCESS\_STATUS  
where table\_name = 'EMP'  
if l\_proc\_status = 2  
then  
run process 3  
end if;

and so on.

Any suggestions :-? What I see is that I will hav quite a few redundant codes...

Followup:  
you'd have to keep a state table and have you code query it up, much like you  
are suggesting. That would do it yes.

another "inserting" idea might be to

a) insert the steps to process  
b) delete them as you complete them (and commit)  
c) to restart, just pick up at the step you wanted

you could insert the procedures to be called and just:

for x in ( select name from procedures order by seq )  
loop  
execute immediate 'begin ' || x.name || '; end;';  
delete from procedures....  
commit;  
end loop

(just a thought, not fully baked)

GOTO a page to Bookmark Review | Bottom | Top  
provide more information about that procedure drop indexes and rebuild with nologging April 07, 2004  
Reviewer: mohan from india

Hi Tom  
Could you provide more information about that procedure and  
how to drop indexes and rebuild with nologging .We are using informatica etl  
tool  
before loading bulk data into target drop the indexes ( pre-session) and after  
load the data rebuilt the indexes with nologging (post-session ), it takes less  
amount of time because its generates less undo  
Regards  
Mohan

Followup:  
it is just a drop and create? not sure what you are looking for -- if it is  
"syntax", we document that.

GOTO a page to Bookmark Review | Bottom | Top  
Problem with Update April 08, 2004  
Reviewer: Ram from Bangalore,India

Hi Tom,  
This update is not working properly.How to correct this?

SQL> declare  
2 x number;  
3 y number;  
4 begin  
5 execute immediate 'update e set sal = sal\*1.1 where ename in  
('SMITH','KING') returning sal into :x,:y' using x,y;  
6 dbms\_output.put\_line(x || chr(9)||y);  
7\* end;  
\*  
ERROR at line 5:  
ORA-06550: line 5, column 65:  
PLS-00103: Encountered the symbol "SMITH" when expecting one of the following:  
. ( \* @ % & = - + ; < / > at in is mod not rem return  
returning <an exponent (\*\*)> <> or != or ~= >= <= <> and or  
like between into using || bulk

Please do reply.  
Bye!

Followup:  
..... ( ''SMITH'', ''KING'' ) .....

you need two quotes for a quote in a string (until 10g when there is another  
way)

GOTO a page to Bookmark Review | Bottom | Top  
Updating million records April 08, 2004  
Reviewer: Himnish Narang from Chandigarh, India

Hi Tom,

i was just going through the discussion in this subject. In this you have  
described that you will create a table instead of going for updates of million  
records and this update will be peroformed as part of creation of new table.

Don't you think that the new table will also occupy the same amount of space and  
after dropping the table we will have to do the reorg for that tablespace.

please comment.

Thanx.

Followup:  
reorgs are so overrated.

no, i would not reorge a tablespace or anything simply cause I copied some data  
and dropped some old stuff. not a chance.

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table April 08, 2004  
Reviewer: Ganesh from Visakhapatnam

Hi Tom,  
Its very useful and I used this saved lots of time by creating new table.  
I got another issue similar to this. We are using 9i. there is requirement of  
needs to modify primary key datatype number to varchar which is having lot of  
dependents. Is there any option with out rebuilding table as data already exist.

Can you please suggest on this.  
Thanks in Advance.  
Ganesh

Followup:  
that'll be a rebuild pretty much.

GOTO a page to Bookmark Review | Bottom | Top  
Please see this April 08, 2004  
Reviewer: Ram from Bangalore,India

Hi Tom,  
Thanks for your reply but Still it's not working properly.  
SQL> declare  
2 x number;  
3 y number;  
4 begin  
5 execute immediate 'update e set sal = sal\*1.1 where ename in  
(''SMITH'',''ALLEN'')  
6 RETURNING sal into :x,:y' using x,y;  
7 dbms\_output.put\_line(x ||chr(9) ||y);  
8 end;  
9 /  
declare  
\*  
ERROR at line 1:  
ORA-00913: too many values  
ORA-06512: at line 5

How to correct this?You say about another way in Oracle 10G.How does that  
work?Could you please provide an example.  
Please do reply.  
Bye!

Followup:  
you are returning SAL (one thing) into two things (:x,:y)

different problem all together.

in 10g, the strings could be

q'|how's this for quoting|'

instead of  
'how''s this for quoting'

a character string literal that starts with q for quote will use the next two  
characters as the start quote and those two characters in reverse for the end  
quote.

GOTO a page to Bookmark Review | Bottom | Top  
Thanks April 09, 2004  
Reviewer: Ram from Bangalore,India

Hi Tom,  
Thanks for your reply.I found out the way of doing it as follows and Do you  
have any other option to do it in a better way?  
SQL> create or replace type sal\_t as table of number;  
2 /

Type created.

SQL> declare  
2 sals sal\_t := sal\_t();  
3 begin  
4 execute immediate 'update emp set sal = sal\*1.1 where ename in  
(''SMITH'',''KING'')  
5 returning sal into :sals' returning bulk collect into  
sals;  
6 for i in 1..sals.count loop  
7 dbms\_output.put\_line('Updated salary:'||sals(i));  
8 end loop;  
9 end;  
10 /  
Updated salary:968  
Updated salary:5500

PL/SQL procedure successfully completed.

Please do reply.  
Bye!

Followup:  
get rid of the dynamic sql, it isn't necessary (fixes your original issue with  
the quotes as well). also, would seem to make sense to pull back other columns  
so you would be able to see what rows were actually updated:

[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> declare  
2 type numArray is table of number;  
3 l\_sals numArray;  
4 l\_empno numArray;  
5 begin  
6 update emp  
7 set sal = sal\*1.1  
8 where ename in ('SMITH','KING')  
9 returning empno, sal bulk collect into l\_empno, l\_sals;  
10  
11 for i in 1..l\_sals.count loop  
12 dbms\_output.put\_line  
13 ('Updated salary for empno ' || l\_empno(i) ||  
14 ' to: '||l\_sals(i));  
15 end loop;  
16 end;  
17 /  
Updated salary for empno 7369 to: 880  
Updated salary for empno 7839 to: 5500  
  
PL/SQL procedure successfully completed.

GOTO a page to Bookmark Review | Bottom | Top  
To Mr. Ram April 09, 2004  
Reviewer: A reader

Hi Ram and All,  
May I request you all not to pester Tom with inane posts requesting Tom to debug  
and program on our behalf.  
This forum is not to learn coding.

GOTO a page to Bookmark Review | Bottom | Top  
update based on rowid April 16, 2004  
Reviewer: john

Tom,

we use non-intelligent primary key to update the table through a stored  
procedure.

i.e. when the table is queried, our select procedure returns the non-intelligent  
key along with the details like below

select emp\_key, other details from emp;

when updating the data, emp\_key is passed from the front end to the stored  
procedure and table is updated based on emp\_key.

is it a bad idea, using rowid instead of non-intelligent key(emp\_key)?

Followup:  
non-intelligent? interesting choice of terminology.

But in any case -- once upon a time ago, rowids were IMMUTABLE. Once assigned a  
row would have a rowid assigned to it and that rowid would live with that row  
until you deleted it.

Starting in 8i with support for updates to partition keys that would cause a row  
to move from partition A to partition B -- that is no longer true (and then  
there are IOT's...).

In 10g, there are even more options for 'row movement' -- an online segment  
shrink for example.

So, rowids can change and are changing in more circumstances as time goes on.

Sooooo, if you have lost update detection in place using 'FOR UPDATE' -- rowids  
are very safe (forms uses them). What I mean is -- you

a) select a.\*, rowid from T a where .... 'normally'  
b) you decide to update a row, so you

select \* from t where rowid = :that\_rowid AND  
((c1 = :that\_c1 or (c1 is null and :that\_c1 is null)) AND  
((c2 = :that\_c2 or (c2 is null and :that\_c2 is null)) AND ...  
((cN = :that\_cN or (cN is null and :that\_cN is null))  
for update NOWAIT;

you lock it -- this prevents anything that could change a rowid from doing so  
and verifies the data hasn't been changed. If that returns 0 rows, someone  
changed the data (or reorg'ed it and moved it). You need to requery to get the  
current values before you let the user even think about modifying it. If that  
returns a row -- you got it. If that returns ora-54, something has it locked,  
you have to decide what you want to do about that.

c) you can then safely update that row by rowid

If you do not have lost update detection in place using FOR UPDATE locks -- then  
you should stick with a primary key probably, just to protect yourself in the  
future.

GOTO a page to Bookmark Review | Bottom | Top  
update based on rowid April 19, 2004  
Reviewer: john

thanks a lot Tom.

to understand fully what you said, can you please tell

1.why do we have to where condition?

((c1 = :that\_c1 or (c1 is null and :that\_c1 is null)) AND  
((c2 = :that\_c2 or (c2 is null and :that\_c2 is null)) AND ...  
((cN = :that\_cN or (cN is null and :that\_cN is null))

2.another question is, is there a situation where a rowid of a row being  
assigned another row of the same table?(because this may cause more danger as we  
end up updating another row)?

thanks again.

Followup:  
1) you want to make sure the row hasn't changed.

c1 = :that\_c1 will only with if both the column and the bind are not null,  
hence the rest is needed to make it so null's compare "the same"

2) this is tied into 1 again. sure, we could delete from emp where empno =  
7788; commit; and then re-insert that row and get the same rowid (rare, but  
could happen).

But - if we inserted the same exact values and they got the same exact rowid --  
then \#1 would make this "safe". the values of the row are the same, for all  
intents and purposes it IS the same row.

If the newly inserted row doesn't match column by column the values we expect,  
well, then we'll reject it (won't lock it) and all will be well.

GOTO a page to Bookmark Review | Bottom | Top  
update large table April 22, 2004  
Reviewer: Prasad Chittori from US

I have a very large partitioned table with DATE column, I would like to take out  
the time portion from the date column  
I did the following and it is taking lot of time and failing with unable to  
extend the rollback segments.  
Any better ideas without increasing the rollback segments or changing the query

update t1  
set dt = trunc(dt)

Followup:  
parallel dml -- each pq slave can get its own RBS, letting you use them all at  
the same time, not just one of them.

GOTO a page to Bookmark Review | Bottom | Top  
convert delete to insert April 26, 2004  
Reviewer: marvin

We want to delete several tables of several million of rows. The problem is, the  
delete statement is quite simple but if we want to this faster we would insert  
to temp table, truncate original and insert back the good data. We want to  
convert delete into insert statements but I think it′s very hard

For example

We have these DELETE statements

DELETE  
FROM OM\_ANNEX t  
WHERE EXISTS  
(SELECT 1  
FROM LISTA\_ORDENES l,OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id=l.order\_id)  
/  
COMMIT  
/  
DELETE  
FROM OM\_ANNEX t  
WHERE EXISTS(SELECT 1 FROM OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id IS NULL)  
/  
COMMIT

to convert to that we did

INSERT /\*+ APPEND \*/ INTO OM\_ANNEX$TMP  
SELECT t.\*  
FROM OM\_ANNEX t  
WHERE NOT EXISTS (SELECT 1  
FROM LISTA\_ORDENES l,  
OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id=l.order\_id)  
UNION  
SELECT t.\*  
FROM OM\_ANNEX t  
WHERE NOT EXISTS (SELECT 1  
FROM OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id IS NULL);

Seems incorrect, if I run the queries inside insert seperately I get correct  
results but if we use UNIONs we get data which we dont want, if we do it in two  
inserts we get duplicate rows

One approach is this

create a temp table using the delete statements but converted to querys for  
example instead of

DELETE  
FROM OM\_ANNEX t  
WHERE EXISTS  
(SELECT 1  
FROM LISTA\_ORDENES l,OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id=l.order\_id)

we convert to

select t.\*  
FROM OM\_ANNEX t  
WHERE EXISTS (SELECT 1  
FROM LISTA\_ORDENES l,OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id=l.order\_id)

then simply says

select \* from om\_annex a  
where not exists (select null from temp\_table b  
where a.annexid = b.annexid)

annexid is PK

the other approach is this

select \* from om\_annex a  
where annexid not in (select annexid  
FROM OM\_ANNEX t  
WHERE EXISTS (SELECT 1  
FROM LISTA\_ORDENES l,OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id=l.order\_id)  
union  
select annexid  
FROM OM\_ANNEX t  
WHERE EXISTS(SELECT 1  
FROM OM\_CONTRACT\_INST c  
WHERE t.contract\_id=c.contract\_id  
AND c.order\_id IS NULL))

They are all very slow because involving tables have several million of rows!

Is there a better approach?

Followup:  
why not:

create table new as select rows to keep;  
drop table old;  
rename new to old;

do the first in parallel, with nologging.

GOTO a page to Bookmark Review | Bottom | Top  
err the problem is convert the DELETE to CTAS April 26, 2004  
Reviewer: A reader

create table new as select rows to keep;  
drop table old;  
rename new to old;  
do the first in parallel, with nologging.

That is exactly I want to do, the problem is until now we have always done the  
other way round, use plain DELETE (and it takes a week to delete everything!),  
the problem I am not sure how to convert DELETE to CTAS. If I want to do the  
reverse of DELETE statements (some table has 5 DELETE statements!) it is not as  
simple as write the DELETE the other way round? For example how would you change

delete tab1  
where exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

delete tab1  
where exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

Would you change it to

insert into tmp\_x  
select \*  
from tab1  
where not exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

insert into tmp\_x  
select \*  
from tab1  
where not exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

Is simple as this?

Followup:  
if i had this:

delete tab1  
where exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

delete tab1  
where exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

I would probably have this:

create table tab1\_new  
as  
select tab1.\*  
from tab1, tab2, tab3  
where tab1.id = tab2.fid(+)  
and tab2.id = tab3.id(+)  
and NOT ( tab2.fid is not null and tab3.id is not null )  
and NOT ( tab2.fid is not null and tab2.id is null )  
/

outer join the three tables. Negate the conditions for the where exists.

that is, after outer joining tab1 to tab2, tab3 -- remove the rows

where tab2.fid is not null and tab3.id is not null -- that is subquery one in  
your deletes above.

where tab2.fid is not null and tab2.id is null -- that is subquery two in your  
deletes above.

GOTO a page to Bookmark Review | Bottom | Top  
err the problem is convert the DELETE to CTAS April 26, 2004  
Reviewer: marvin

create table new as select rows to keep;  
drop table old;  
rename new to old;  
do the first in parallel, with nologging.

delete tab1  
where exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

delete tab1  
where exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

Would you change it to

insert into tmp\_x  
select \*  
from tab1  
where not exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

insert into tmp\_x  
select \*  
from tab1  
where not exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

Is simple as this?

GOTO a page to Bookmark Review | Bottom | Top  
thank you very much for the outer join tip April 27, 2004  
Reviewer: marvin

I am going to have a look how to apply the outer join in order to convert DELETE  
to CTAS.

I have a further question, I have another table which undergoes 4 DELETEs

DELETE  
FROM SW\_PERSON t  
WHERE EXISTS  
(SELECT 1  
FROM LISTA\_ORDENES o  
WHERE o.order\_id=t.swobjectid AND t.swtype='ORDER')  
/  
COMMIT  
/  
DELETE  
FROM SW\_PERSON t  
WHERE t.swtype='ORDER' AND t.swobjectid IS NULL AND COMP\_INST\_ID IS NULL  
/  
COMMIT  
/  
DELETE  
FROM SW\_PERSON t  
WHERE t.swtype IS NULL  
/  
COMMIT  
/  
DELETE  
FROM SW\_PERSON t  
WHERE t.swtype='ORDER'  
AND t.swobjectid IS NULL  
AND EXISTS  
(SELECT 1  
FROM OM\_COMPANY\_INST c, LISTA\_ORDENES l  
WHERE c.COMP\_INST\_ID=t.COMP\_INST\_ID  
AND l.order\_id=c.order\_id)  
/  
COMMIT  
/

I need to convert this to CTAS as well, however I am not sure if this can be  
done in a single statement. These DELETE for example cant be converted into one  
as follows right? (because of commit between them)

DELETE  
FROM SW\_PERSON t  
WHERE EXISTS (SELECT 1  
FROM LISTA\_ORDENES o  
WHERE o.order\_id=t.swobjectid  
AND t.swtype='ORDER')  
OR (t.swtype='ORDER'  
AND t.swobjectid IS NULL  
AND COMP\_INST\_ID IS NULL)  
OR t.swtype IS NULL  
OR (t.swtype='ORDER'  
AND t.swobjectid IS NULL  
AND EXISTS (SELECT 1  
FROM OM\_COMPANY\_INST c,  
LISTA\_ORDENES l  
WHERE c.COMP\_INST\_ID=t.COMP\_INST\_ID  
AND l.order\_id=c.order\_id));

Can this use the outer join tip as well?

TIA

Followup:  
why have any commits in between.

but of course -- any four deletes against a single table can (and if you ask me,  
should) be done as a single delete.

the outer join was used in the CTAS, not in a delete.

GOTO a page to Bookmark Review | Bottom | Top  
why do you use outer join April 27, 2004  
Reviewer: A reader

Why is outer join needed for tab1, tab2 and tab3 :-?

Followup:

because we wanted to keep all rows in tab1 -- if there is NO mate in tab2/tab3

since they deleted "where exists in tab2/tab3":

delete tab1  
where exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

delete tab1  
where exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

rows in tab2/tab3, we need to ALWAYS keep rows that are NOT in tab2/tab3 using  
CTAS. outer join is mandatory for that in this example.

GOTO a page to Bookmark Review | Bottom | Top  
to marvin April 27, 2004  
Reviewer: A reader

hi marvin, you can try this

select \* from tab1  
where PK not in (select PK  
from tab1  
where exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)  
union  
select \*  
from tab1  
where exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)))

GOTO a page to Bookmark Review | Bottom | Top  
regarding the conditions April 27, 2004  
Reviewer: A reader

Hi Tom

May you show some light why

NOT ( tab2.fid is not null and tab3.id is not null )  
is same as  
exists (select null  
from tab2, tab3  
where tab2.id = tab3.id  
and tab1.id = tab2.fid)

and  
NOT ( tab2.fid is not null and tab2.id is null )  
is same as  
exists (select null  
from tab2  
where tab2.fid = tab1.id  
and tab2.id is null)

Cant see why. Thank you

Followup:  
it isn't the same.

it is in fact the opposite.

if you outer join T1 to T2 to T3

and before you were looking for (where exists)

a) a match in T2 (tab1.id = tab2.fid)  
b) a match in T3 for that T2 (tabe2.id = tab3.id)

then you are saying "if I outer join T1 to T2 to T3, that row would be such  
that:

a) tab2.fid is NOT NULL (we found a mate)  
b) tab3.id is NOT NULL (we found a mate in t3 for t2)

with the where exists -- we would have deleted that row, hence with the CTAS  
(which is finding rows to keep) we simply NEGATE that (with NOT).

Therefore we would keep that row, IF that was "not" satisfied.

Same logic for the second part.

the second where exists says

delete the row if

a) there is a match in T2 (where tab2.fid = tab1.id)  
b) the id column in t2 for that match is NULL

that would be (in an outer join)

(tab2.fid is not null) -- we joined to a row  
(tab2.id is null) -- and that row is having id is null

negate it and keep it.

GOTO a page to Bookmark Review | Bottom | Top  
Updating Table having millions of records taking lot of Time.. April 28, 2004  
Reviewer: Anand Pandey from INDIA

Hi Tom,  
I hd a table having millions of record in which two of its cols are Null.  
i just tried to update the null colls with the data from other table, which is  
taking taking around 10-12 hrs for a single day record, and I've to update it  
for 31 days.  
pls help Me in getting the high perf. on updation.

Query used is:

UPDATE /\*+nologging parallel(4) \*/ MASTER\_tab A SET  
(null\_col1,null\_col2)=(SELECT /\*+PARALLEL(4) \*/ MIN(C1),MIN(C2)  
FROM Table2 B WHERE SUBSTR (A.col1\_T1, 1, LENGTH (B.C1)) = B.C1  
AND SUBSTR(A.col2\_T1,1,3)=B.C2)  
WHERE c\_date='01-Jan-04'

Thanks and Regards,  
Anand Pandey

GOTO a page to Bookmark Review | Bottom | Top  
Nologging - how does it impact recovery? April 30, 2004  
Reviewer: Naresh from Cyprus

Hi Tom,

This is a great chain of discussion. I especially liked the "outer join to  
replace the not exists". I am really looking forward to my copy of your first  
book that I oredered recently (on it's way from amazon).

One question regarding making the table nologging: Does it not have implications  
for recovery? What am I missing?

Followup:  
you need to schedule a hot backup if you use non-logged operations, yes.

GOTO a page to Bookmark Review | Bottom | Top  
db sequential waits on UPDATE May 14, 2004  
Reviewer: A reader

Dear Tom,

Due to some processing complexities I am unable to use a single SQL for  
Update/Insert.

I use:  
LOOP  
1. Bulk select (500 rows at a time) from Table A, C with rowids from C  
2. Bulk insert  
3. Bulk update table C  
END LOOP

I am getting a very high number of "db file sequential read" waits on the update  
part. Please see relevant portions of Trace File:  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
SELECT /\*+ FULL(v) FULL(c) PARALLEL(v,4) PARALLEL(c,4) HASH(v,c) \*/  
c.rowid,  
...  
FROM vp v,citi c  
WHERE v.idno= c.idno

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.03 0.07 10 234 0 0  
Execute 1 0.32 3.38 3023 156 3 0  
Fetch 155 1.96 82.82 0 0 0 77500  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 157 2.32 86.28 3033 390 3 77500

Misses in library cache during parse: 1  
Optimizer goal: CHOOSE  
Parsing user id: 95 (recursive depth: 1)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
db file sequential read 4 0.01 0.03  
rdbms ipc reply 26 0.00 0.00  
enqueue 2 0.02 0.02  
PX Deq: Join ACK 7 0.00 0.00  
PX Deq: Parse Reply 4 0.23 0.35  
PX Deq: Execute Reply 717 2.06 735.45  
PX qref latch 4 1.02 2.75  
PX Deq: Table Q Normal 5218 0.35 23.95  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

INSERT /\*+ APPEND PARALLEL(A,4) \*/ INTO t\_v A  
VALUES(:b1,  
:b2)

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.00 0.01 2 11 0 0  
Execute 155 0.32 8.00 552 1116 5567 77500  
Fetch 0 0.00 0.00 0 0 0 0  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 156 0.32 8.02 554 1127 5567 77500

Misses in library cache during parse: 1  
Optimizer goal: CHOOSE  
Parsing user id: 95 (recursive depth: 1)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
db file sequential read 552 0.36 6.43  
free buffer waits 6 1.02 1.23  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

UPDATE /\* PARALLEL(c,4) \*/ citi c  
SET ...  
WHERE c.rowid = :b6

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.00 0.00 0 1 0 0  
Execute 154 35.26 586.87 75251 77036 79595 77000  
Fetch 0 0.00 0.00 0 0 0 0  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 155 35.26 586.87 75251 77037 79595 77000

Misses in library cache during parse: 1  
Optimizer goal: CHOOSE  
Parsing user id: 95 (recursive depth: 1)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
db file sequential read 75578 1.04 461.09  
free buffer waits 242 1.03 96.11  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Is it not true that if the row has already been selected (and is there in the  
buffer cache) no disk IO should happen to update this row?

Please tell me a way to make this faster.

Regards

Followup:  
insert /\*+ append parallel... \*/ VALUES hmmmmmm

append = no go with values, only works with "as select"  
parallel -- parallel on what exactly?

update /\* parallel(c,4) \*/ hmmmmmm

in search of the fast = true switch. thankfully that hint is malformed and hence  
ignored as well -- you are updating by rowid.

parallel is \*not\* fast=true.  
append is not either really, especially with the values clause.

you can review the trace file itself (p1,p2,p3 will tell you file and block  
info, along with blocks read). you can use that to verify that it is the very  
act of reading the indexes that need to be updated and maintained that is  
causing this. If they are not in the case, well, we'll need to read them into  
there.

GOTO a page to Bookmark Review | Bottom | Top  
More Info May 16, 2004  
Reviewer: A reader

Thanks Tom,  
The hint in the update was there by a Developer, it has been rightly disabled.  
The insert is not a bottleneck so didn't look into it.  
This is a development machine, there are no other jobs running, asynchronous IO  
is enabled, the machine is on RAID 0 (no fault tolerance - being a development  
one). There are NO INDEXES on Tables being inserted and updated.

The segment on which the WAITS (db sequential read) are happening are that of  
the TABLE that is being UPDATED.

Please guide me next.

Followup:  
then you are seeing physical IO performed to read into the buffer cache the data  
needed to be updated. If it is not cached, we'll have to read it.

GOTO a page to Bookmark Review | Bottom | Top  
PARALLEL DML May 28, 2004  
Reviewer: Sar from NJ, USA

Tom,

I need to update a table that has 50 million rows but  
the number of rows affected are only 1 million rows.

I have a single update statement to do this.  
  
UPDATE acc\_proc  
SET acc\_seq\_num = 0  
WHERE acc\_seq\_num > 0  
/

The column acc\_seq\_num is not part of an index.

Can you please suggest me if there is anything better  
that I can do on this SQL to run it faster.

Thanks  
Sar

Followup:  
nope, that is perfect.

shouldn't take very long at all. unless you are getting blocked constantly by  
other sessions.

GOTO a page to Bookmark Review | Bottom | Top  
do i need to commit after execute immediate ? for dml or ddl ? June 02, 2004  
Reviewer: A reader

[http://download-east.oracle.com/docs/cd/A87861\_01/NT817EE/index.htm][http_download-east.oracle.com_docs_cd_A87861_01_NT817EE_index.htm]

do i need to commit after execute immediate ? for dml or ddl ? or it dose  
automatically ?

I tried to find the answer but not found in doc. from above site please help

Followup:  
DDL is processed \*conceptually as:

begin  
commit; -- commit anything you started, even if DDL fails, you are committed  
do the ddl;  
commit; -- commit the ddl  
exception  
when others  
then  
rollback; -- the ddl  
raise;  
end;  
/

You need not commit DDL, it'll commit 2 times for you.

You need to commit DML as DML does not do that.

This is regardless of the method used to execute SQL, native dynamic sql,  
dbms\_sql, static sql, whatever -- the rules are the same in all cases.

GOTO a page to Bookmark Review | Bottom | Top  
Thanks !! June 02, 2004  
Reviewer: A reader

ok,

so i did  
1.)

begin  
begin  
Execute immediate 'SET CONSTRAINTS ALL DEFERRED';  
end;  
  
begin  
execute immediate 'delete from table ' || t;  
end;

begin  
execute immediate 'delete from table ' || t1;  
end;

exception  
when others then  
rollback;  
raise;  
end;  
/

i got the message some constraint of t1 violated. (child record exists)  
if i did not issue the commit,post or rollback,  
and did not perform any DDL,constraints are DEFERRED  
what is the issue ? can u help ?

Followup:  
...  
begin  
execute immediate 'set constraints all deferred';  
execute immediate 'delete from ' || t;  
execute immediate 'delete from ' || t1;  
execute immediate 'set constraints all immediate';  
end;  
/

that is all the code you want -- the all immediate will validate the contraints

if they fail -- then the error (exception) will be propagated back to the client  
and the rollback of the statement (which is whatever the client sent over to the  
database -- a plsql block, whatever) will be undone.

GOTO a page to Bookmark Review | Bottom | Top  
June 02, 2004  
Reviewer: A reader

...  
begin  
execute immediate 'set constraints all deferred';  
execute immediate 'delete from ' || t; -- \*\*\*\*\* this one  
\-- execute immediate 'delete from ' || t1;  
\-- execute immediate 'set constraints all immediate';  
end;  
/

the question is  
as soon as it executes the  
'delete from ' || t;  
statement, it raises the referential constriant violation exception. why ?  
as  
1.) constraints all deferred  
2.) it NOT a ddl  
3.) I am not commiting(or ending the tx) manually

Followup:  
the constraint must not be deferrable.

you have to have deferrable constraints in order to defer them. the default is  
"not deferrable"

GOTO a page to Bookmark Review | Bottom | Top  
7.3.4 - extracting 5 million records from 100 million record table June 15, 2004  
Reviewer: A reader

Any suggestions on how to accomplish this on 7.3.4? No concept of nologging,  
partitioning, Export/query clause etc in 7.3.4..

How I can extract about 5 million records (based on a condition) into a separate  
database/server from a 100million record table? What's the most efficient way?  
I'm having to make do with very limited disk/space on both machines.

Thanks.

Followup:  
5 million is pretty small.

I'd just

create table as select ( concept of nologging did in fact exist, unrecoverable);  
export it  
import it

or use the sqlplus copy command if they are connected via a nice network.

GOTO a page to Bookmark Review | Bottom | Top  
June 16, 2004  
Reviewer: A reader

Thanks. Had some long columns in there. So 'Create table as select' wouldn't  
work. Am just ending up plsql looping and hitting smaller chunks of the mega  
table. Then creating smaller target tables..then exp/imp.

Am afraid I won't have sufficient temp space to do a sqlplus "Copy from". Also,  
documentation talks about Copy not being intended for Oracle to Oracle DBs. No  
idea why.

Followup:  
copy is ONLY for oracle to oracle db's....

GOTO a page to Bookmark Review | Bottom | Top  
June 16, 2004  
Reviewer: A reader

Per 7.3.4 documentation :

[http://download-west.oracle.com/docs/pdf/A42562\_1.pdf][http_download-west.oracle.com_docs_pdf_A42562_1.pdf]

Excerpt from it -

Note: In general, the COPY command was designed to be used  
for copying data between Oracle and non-Oracle databases. You should use SQL  
commands (CREATE TABLE AS and INSERT)  
to copy data between Oracle databases.

Followup:  
but if you think about it..... doesn't matter what the doc says, it takes two  
connections. connections are only to oracle databases. sure you could be using  
a gateway -- but even there, it would be true that create table as and insert  
would work.

GOTO a page to Bookmark Review | Bottom | Top  
Updating and Inserting 1 Million rows Daily with Bitmap indexes June 24, 2004  
Reviewer: Sidda from Santa Clara,USA

Hi Tom,  
Here we are facing very big problem. We have a Partitioned table with 180Million  
records(with 70 columns and 10 bitmaps,10 B-tree indexes).End users need Bitmap  
indexes 24/7 so we can't drop and recreate table and indexes.Daily we have to  
update and insert 1Million records. We tried with bulk updates but invain. What  
is the best method to follow up?

Thanks in advance  
Sidda

Followup:  
describe "in vain", what went wrong?

it would be best to do a SINGLE insert into and a SINGLE Update against this  
table (not even in bulk - just single statements)

GOTO a page to Bookmark Review | Bottom | Top  
Creating Table with aggregated data from another table July 26, 2004  
Reviewer: RB from NJ

Tom: Qn related to creating a table with data from another table -

I want to create a table with few fields and aggregated some of few columns from  
another table.

Here is a query:

create table SRC200401 as select ID, src, LOC, sum(BYTES) TOTAL, sum  
(billable\_bytes) TOTAL\_BILLABLE from SRC\_MONTHLY where ID = 1072915200 group by  
ID,LOC, SRC;

I have 128Million record in the SRC\_MONTHLY table and it is taking more than 1  
hr to get this table created. I have index on ID, src and LOC fields.

Any faster way of getting this table created?

Followup:  
so how many records have that id?

GOTO a page to Bookmark Review | Bottom | Top  
Great aproach, but is it as fast for an IOT table? July 26, 2004  
Reviewer: Peter Tran from Houston, TX USA

Hi Tom,

I'm trying the same approach with an IOT table. We have an IOT table  
partitioned daily. I want to recreate this table with a monthly partition. I  
do a CTAS parallel nologging using the new monthly partition, but it's SLOW.  
Then again, the table does have 200 million rows. Is the "Index Organization"  
part of table the slow part?

Thanks,  
\-Peter

Followup:  
what do the inputs/outputs look like (table structure) -- remember, you are  
sorting 200,000,000 rows as well!

did you give it a nice juicy sort area size?

GOTO a page to Bookmark Review | Bottom | Top  
Unfortunately no. July 26, 2004  
Reviewer: Peter Tran from Houston, TX USA

"...nice juicy sort area size" That would be a negative. :( Man, this is  
going to take forever isn't it? Ugh...

SQL> show parameter sort\_area

NAME TYPE VALUE  
\------------------------------------ ----------- --------  
sort\_area\_retained\_size integer 8388608  
sort\_area\_size integer 8388608

SQL> desc odfrc;  
Name Null? Type  
\----------------------------------------------------- -------- ------------

ODIFID NOT NULL NUMBER(10)  
TRPORGN NOT NULL VARCHAR2(5)  
TRPDSTN NOT NULL VARCHAR2(5)  
POSCOUNTRYCODE NOT NULL VARCHAR2(3)  
PAXTYPE NOT NULL VARCHAR2(1)  
DCP NOT NULL NUMBER(2)  
ODIFDATE NOT NULL DATE  
FRCDATE NOT NULL DATE  
BKGMEAN NUMBER  
BKGMEANINFLUENCED NUMBER  
BKGVARIANCE NUMBER  
XXLMEAN NUMBER  
XXLMEANINFLUENCED NUMBER  
XXLVARIANCE NUMBER

Here's my CTAS:

CREATE TABLE ODFRC\_MONTHLY (  
ODIFID,  
TRPORGN,  
TRPDSTN,  
POSCOUNTRYCODE,  
PAXTYPE,  
DCP,  
ODIFDATE,  
FRCDATE,  
BKGMEAN,  
BKGMEANINFLUENCED,  
BKGVARIANCE,  
XXLMEAN,  
XXLMEANINFLUENCED,  
XXLVARIANCE,  
CONSTRAINT ODFRC\_MONTHLY\_PK PRIMARY KEY  
(ODIFID, ODIFDATE, TRPORGN, TRPDSTN,POSCOUNTRYCODE, PAXTYPE, DCP, FRCDATE)  
) ORGANIZATION INDEX nologging parallel 8  
PARTITION BY RANGE (ODIFDATE)  
(PARTITION ODFRC\_20021130 VALUES LESS THAN (TO\_DATE('2002-12-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX01 ,  
PARTITION ODFRC\_20021231 VALUES LESS THAN (TO\_DATE('2003-01-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX02 ,  
PARTITION ODFRC\_20030131 VALUES LESS THAN (TO\_DATE('2003-02-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX03 ,  
PARTITION ODFRC\_20030228 VALUES LESS THAN (TO\_DATE('2003-03-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX04 ,  
PARTITION ODFRC\_20030331 VALUES LESS THAN (TO\_DATE('2003-04-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX05 ,  
PARTITION ODFRC\_20030430 VALUES LESS THAN (TO\_DATE('2003-05-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX06 ,  
PARTITION ODFRC\_20030531 VALUES LESS THAN (TO\_DATE('2003-06-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX07 ,  
PARTITION ODFRC\_20030630 VALUES LESS THAN (TO\_DATE('2003-07-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX08 ,  
PARTITION ODFRC\_20030731 VALUES LESS THAN (TO\_DATE('2003-08-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX09 ,  
PARTITION ODFRC\_20030831 VALUES LESS THAN (TO\_DATE('2003-09-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX10 ,  
PARTITION ODFRC\_20030930 VALUES LESS THAN (TO\_DATE('2003-10-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX11 ,  
PARTITION ODFRC\_20031031 VALUES LESS THAN (TO\_DATE('2003-11-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX12 ,  
PARTITION ODFRC\_20031131 VALUES LESS THAN (TO\_DATE('2003-12-01',  
'YYYY-MM-DD'))  
TABLESPACE ODFRC\_IDX13) AS SELECT  
ODIFID,  
TRPORGN,  
TRPDSTN,  
POSCOUNTRYCODE,  
PAXTYPE,  
DCP,  
ODIFDATE,  
FRCDATE,  
BKGMEAN,  
BKGMEANINFLUENCED,  
BKGVARIANCE,  
XXLMEAN,  
XXLMEANINFLUENCED,  
XXLVARIANCE  
FROM odfrc nologging;

The original ODFRC table has daily partition for one year. Anyway, I can  
estimate how long this will take?

\-Peter

Followup:  
the nologging is in the wrong place

from odfrc nologging;

is the same thing as:

from odfrc a;

would you consider

a) create the table (empty, alter it to be nologging)  
b) insert /\*+ append \*/ into the individual partitions (instead of all at once)  
in parallel sessions?

GOTO a page to Bookmark Review | Bottom | Top  
Create Table with data from an aggregated sum of few fields from another table July 26, 2004  
Reviewer: RB from NJ

Tom: Qn related to creating a table with data from another table -

I want to create a table with few fields and aggregated some of few columns from

another table.

Here is a query:

create table SRC200401 as select ID, src, LOC, sum(BYTES) TOTAL, sum  
(billable\_bytes) TOTAL\_BILLABLE from SRC\_MONTHLY where ID = 1072915200 group by  
ID,LOC, SRC;

I have 128Million record in the SRC\_MONTHLY table and it is taking more than 1  
hr to get this table created. I have index on ID, src and LOC fields.

Any faster way of getting this table created?

Followup:  
so how many records have that id?

RB: Tom - This number varies - we have so many IDs in the master table. If I  
pass one id then the query will have one equi join with that ID if more than one  
I was planning to use IN clause. So I do not know how many records per id I will  
have it in the table at any given point of time.

Followup:  
now I'm confused -- the predicate is variant? why create \*that\* table then?

what is the goal here -- to make queries of the form:

select ID, src, LOC, sum(BYTES) TOTAL, sum  
(billable\_bytes) TOTAL\_BILLABLE from SRC\_MONTHLY where ID = :x group by id, loc,  
src;

faster in general? (you don't have to duplicate lots of text, it is all right  
here)

GOTO a page to Bookmark Review | Bottom | Top  
Great suggestion! July 26, 2004  
Reviewer: Peter Tran from Houston, TX USA

Hi Tom,

Thanks for the useful suggestion.

1) Part one done (easy).  
2) Is this what you mean for part 2? When you say parallel sessions, do you  
mean kick off a bunch of them using execute immediate?

INSERT /\*+ append \*/ INTO odfrc\_monthly(  
ODIFID,TRPORGN,TRPDSTN,POSCOUNTRYCODE,  
PAXTYPE,DCP,ODIFDATE,FRCDATE,BKGMEAN,  
BKGMEANINFLUENCED,BKGVARIANCE,XXLMEAN,  
XXLMEANINFLUENCED,XXLVARIANCE)  
SELECT  
ODIFID,TRPORGN,TRPDSTN,POSCOUNTRYCODE,  
PAXTYPE,DCP,ODIFDATE,FRCDATE,BKGMEAN,  
BKGMEANINFLUENCED,BKGVARIANCE,XXLMEAN,  
XXLMEANINFLUENCED,XXLVARIANCE  
FROM odfrc partition(ODFRC\_20021114) nologging;

3) Should I still give it a nice juicy sort\_area\_size? :)

Thanks,  
\-Peter

Followup:  
2) fire up N sqlplus sessions and run the insert append in each

3) sure, let each session have a big SAS -- alter session can be used.

GOTO a page to Bookmark Review | Bottom | Top  
Create Table with data from an aggregated sum of few fields from another table July 26, 2004  
Reviewer: RB from NJ

Tom - If the user can select one or more ids. If I have more than one ID then I  
was planning to use an IN clause in the where clause. The temp table that I am  
creating will be used in a later phase of the app for other joins. What I am  
looking for a soln which will be must fater than my current approach. The Query  
that I have given with a 120M table is taking more than 1 hr to create the  
aggregated table.

Followup:  
what is the query plan in general then.

you would not create a temporary table in oracle -- that would be so sqlserver.  
just use that query in the "IN" statment in the first place!!!!!

GOTO a page to Bookmark Review | Bottom | Top  
Update... July 27, 2004  
Reviewer: Peter Tran from Houston, TX USA

Hi Tom,

I wanted to give you an update on progress. The nice thing about your approach  
is I can monitor the progress, but it's not as fast I as thought it would be.

I started 6 sessions and gave each session a sort\_area\_size = 200M. I then  
executed a month's worth of insert each session. It's taking about 30 minutes  
per partition/day to insert from the source daily table to the monthly table.  
Each partition holds around 700K to 800K rows.

Should I expect to run this long?

Thanks,  
\-Peter

Followup:  
sounds long -- can you run one of the sessions with a 10046 level 12 trace and  
see what they might be waiting on?

GOTO a page to Bookmark Review | Bottom | Top  
sort\_area\_size, pga\_aggregate\_target, insert /\*+ APPEND\*/ July 28, 2004  
Reviewer: Dusan Valasek from Czech republic

Hi Tom,

I have two questions about setting of big sort\_area\_size before direct path  
load.  
1) How does setting of sort\_area\_size (from session) affect direct path load? I  
thought that this is useful for sort operation and building of indexes.  
2) If I use pga\_aggregate\_target, does setting of sort\_area\_size (from session)  
override pga\_aggregate\_target?

Thanks,

Dushan

Followup:  
1) you are loading an IOT. big sort going on.

2) you would set the workarea policy to manual (in your session) to allow you to  
override the sort area size for this "batch operation"

GOTO a page to Bookmark Review | Bottom | Top  
July 28, 2004  
Reviewer: A reader

Would you suggest to re-create the table when other users want to update it  
online

Followup:  
huh?

GOTO a page to Bookmark Review | Bottom | Top  
How to change a DATATYPE of column. July 30, 2004  
Reviewer: peru

Hi Tom,  
How to change a datatype of a particular column.  
say a table with column VARCHAR2 for date.  
Now i want to change the datatype DATE.  
Assume that table has many records and referenced with  
tables , procedures,triggers.  
Thank you

Followup:  
not really going to happen.

you can add a new column that is a date, update this new column to be to\_date()  
of the varchar2 and set the varchar2 to null, drop the old column and rename the  
newly added column to the old name.

GOTO a page to Bookmark Review | Bottom | Top  
Update to July 27, 2004 August 22, 2004  
Reviewer: Peter Tran from Houston, TX USA

Hi Tom,

Sorry it took awhile to get back with you on this. You wanted to see a 10046  
level 12 trace. I wanted to do some research first and I had to rebuild the  
table to reproduce the step.

Here's the trace.

insert /\*+ append \*/ into odfrc\_monthly select \* from odfrc  
partition(ODFRC\_20040201)

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.01 0.01 0 0 0 0  
Execute 1 15.08 5776.28 11657 15874 111333 569939  
Fetch 0 0.00 0.00 0 0 0 0  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 2 15.09 5776.29 11657 15874 111333 569939

Misses in library cache during parse: 1  
Optimizer goal: CHOOSE  
Parsing user id: 34

Rows Row Source Operation  
\------- ---------------------------------------------------  
1 LOAD AS SELECT  
569939 INDEX FAST FULL SCAN ODFRC\_PK PARTITION: 185 185 (object id 12351)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
control file sequential read 8 0.00 0.00  
enqueue 1911 3.00 5726.43  
db file sequential read 38 0.03 0.20  
db file scattered read 380 0.63 9.50  
direct path write 19 0.00 0.00  
direct path read 10 0.00 0.00  
log buffer space 45 0.61 12.01  
log file switch completion 2 0.28 0.33  
log file sync 1 0.00 0.00  
SQL\*Net message to client 1 0.00 0.00  
SQL\*Net message from client 1 0.00 0.00

Jonathan Lewis wrote in his book that you cannot use direct-path insert using  
the APPEND hint into an IOT table. Can this be the reason for the large enqueue  
timed event? Are the 6 sessions waiting to lock the index to modify it?

Thanks,  
\-Peter

Followup:  
Umm, you were blocked by someone else for an excessively long period of time  
here. Enqueue waits:

enqueue 1911 3.00 5726.43

normal "blocker, blockee" problem here! doh, it was me doing it to you.

only one session at a time can append. only one session at a time can direct  
path insert into a table.

sorry -- use normal insert, I goofed.

GOTO a page to Bookmark Review | Bottom | Top  
delete 2M records without dropping table. August 23, 2004  
Reviewer: Sean from NJ, USA

Hi Tom,

I have to delete 2M records from 6M records and the table has about 400 columns  
(Oracle 920, Solaris 9).

I understand that your suggestion of creating temp table with the records I  
needed, then drop the original table and change the temp table name.

But since our table is not that big and the application is using this table all  
the time, we try to use traditional delete method to accomplish this.

(1)If we have enough redo tablespace, what is the good number of rows for each  
delete. I tried each commit for 10000 records or 1000 records. Both are quite  
slow.  
(2)Anything you can suggest to improve the speed of the delete in the following  
procedure.

Thanks so much for your help

\------------------------------------------------------------  
create or replace procedure sp\_del\_audit(p\_total\_loop\_time number)  
is

total\_loop\_time number := 1;

BEGIN  
WHILE total\_loop\_time <= p\_total\_loop\_time  
LOOP  
delete /\*+ parallel \*/ from audit a  
where rownum < 10000  
and exists ( select /\*+ parallel \*/ null from audit\_temp b  
where a.Pkey = b.Pkey and  
a.row\_type = b.row\_type and  
a.update\_time = b.update\_time);  
  
commit;  
  
total\_loop\_time := total\_loop\_time + 1;

END LOOP;  
END;  
/  
\------------------------------------------------------------

Followup:  
1) assuming you meant UNDO tablespace, there is no such thing as a REDO  
tablespace...

removing 2 million rows, with 400 columns (every row in this table is CHAINED,  
when you have more than 255 columns...) and probably many indexes -- it is not  
going to be what you might term "speedy".

2) do it as a SINGLE delete (deleting 10,000 records in "parallel" doesn't even  
begin to make sense here. PQ is for BIG BIG BIG things. this is tiny)

delete from audit  
where (pkey, rowi\_type, update\_time) in ( select pkey, row\_type, update\_time  
from audit\_temp);

nothing more, nothing less. but don't expect this to be super fast if this  
table is indexed. removing 33% of the index entries is going to take some  
amount of time.

GOTO a page to Bookmark Review | Bottom | Top  
Deleting 90% of rows October 13, 2004  
Reviewer: A reader

As part of some ETL processing, I need to delete 90% of the rows in a large  
(6million rows) table. This table has a PK index so traditional delete takes a  
long time. I see the following options

1. Mark the index unusable, delete and rebuild index nologging. This is  
significantly faster than plain delete  
2. create new table as remaining 10% of the rows, drop, rename, etc  
3. Precreate a regular or GTT and do insert /\*+ append \*/ in my batch program.

(2) is not very attractive as I dont want to be doing DDL in my code.

How would you compare (1) and (3) above? Both of them would generate similar  
amounts of undo/redo, take roughly same amount of time?

Thanks

Followup:  
ETL is 'special', it doesn't matter 5 times a minute.

I'd go with \#2 actually, CTAS a newone, drop oldone, rename newone to oldone.

\#3 is "dangerous" -- you could lose everything in the event of a failure.

GOTO a page to Bookmark Review | Bottom | Top  
October 14, 2004  
Reviewer: A reader

"\#3 is "dangerous" -- you could lose everything in the event of a failure"

Why would I lose everything? What kind of failure? Instance or media failure?  
Oracle guarantees recoverability of commited transactions, right, why do you  
bring that up here?

The only reason I dont want to do (2) is that I usually want to prevent DDL in  
my code. The only difference between (2) and (3) is that the table is already  
created in (3), right? Yes, (3) would generate much more redo/undo (lesser if I  
create the table as nologging), but I thought using a GTT would cut down on the  
redo/undo as well?)

Followup:  
i saw "use a gtt"

i could only assume you mean:

insert rows to keep into gtt  
truncate table  
insert rows from gtt into the table

that would be dangerous. if you fail after the truncate, before/during the  
insert -- gone, all gone. gtt would be dangerous here.

(truncate is ddl btw...)

GOTO a page to Bookmark Review | Bottom | Top  
How can you Display multiple rows in one record October 14, 2004  
Reviewer: Mack from USA

Hi Tom,

Let's suppose in deptno 10, there are 3 to 5 employees, I want to see the emp  
names like  
TOM, KIM, JOHN... and so on.

Is there an easy way to do it in SQL? Not through PL/SQL. The number of records  
are unknown, it could have 10, 20 or one hundred. Please advise.

Followup:  
collect() in 10g

stragg (search for it -- nasty plsql you install once and use over and over and  
over and over and over) in pre 10g

GOTO a page to Bookmark Review | Bottom | Top  
Query first and then update October 15, 2004  
Reviewer: A reader

We have two tables with approximately 28 million and 35 million records  
respectively. These tables are joined to produce data to be displayed to users  
using IE browsers. Based on this information, around 10000 records get added to  
these tables and around the same updated daily. Our SLA is to display each  
screenful (100 rows) in 2-4 seconds.

While partitioning is being reviewed to improve the performance for the queries,  
could you let us know if there are any issues regarding partitions? For instance  
someone has reported that using global indexes on a partitioned table has  
degraded the performance.

Followup:  
i seriously doubt partitioning is going to be used to increase the performance  
of these queries.

partitioning -- great for speeding up a full scan. are you suggesting to full  
scan and still return in 2-4 seconds?

"someone reported using global indexes ... degraded performance"

I just about fell out of my chair on that one. geez.

If you have my book "Effective Oracle by Design" -- I go into the "physics  
behind partitioning".

In order to return the first 100 rows to a web based application -- you are  
doing to be using indexes (or you are not going to be doing what you signed up  
to do -- funny, you have an SLA in place but no idea if you can live up to  
it...). whether the tables are partitioned or not probably won't have any  
bearing on making this faster. Partitioning could certainly make things slower  
if you do it wrong, but in a system like this, you would be looking at  
partitioning to make administration easier and hopefully not NEGATIVELY impact  
performance.

given two tables...  
index access  
get 100 rows  
  
I personally would be shooting for well under 1 second response times for  
everything -- regardless of whether there was 1 row or 1 billion.

don't get the tie into "query first and then update" though.

GOTO a page to Bookmark Review | Bottom | Top  
October 18, 2004  
Reviewer: A reader

"Our SLA is to display each \*screenful\* (100 rows) in 2-4 seconds."

In our case the queries, after joining 2 tables of 28 million and 35 million  
records, could return 10 screens or 600 screens or many more screens based on  
the query parameters. Each screenful (with 100 records in each screen) should  
appear in 2-4 seconds.

Followup:  
so, you've signed up for an SLA you have no idea if you can meet.

but hey -- using indexes to retrieve 100 rows from 1, 1000, 10000000000000  
should be about the same amount of time (and way under subsecond).

but -- getting to "screen 600" is not. Look to google as the gold standard for  
searching and web pagination

o totally estimate the number of returned rows -- don't even THINK about giving  
an accurate count

o don't give them the ability to go to page "600", pages 1-10 is more than  
sufficient

o even if there is a page 600 -- realize it doesn't make sense to go there (no  
human could know "what I need is on page 600 -- 6,000 rows into this result  
set). Google stops you at page 99

o understand that page 2 takes more time to retrieve than page 1, page 50 more  
than 2, and so on (as you page through google --each page takes longer)

But perhaps most importantly -- laugh at people that say things like:

"For instance  
someone has reported that using global indexes on a partitioned table has  
degraded the performance."

and say -- "yes, it has when improperly applied it can degrade performance,  
there have also been sightings of systems where it didn't affect performance at  
all, there have also been reported instances of them massively improving  
performance. Now what we have to do is understand how the feature works, what  
it does, and how it might apply to our problem"

The neat thing about that paragraph -- it is INFINITELY reusable. You can use  
it with regards to any feature!

(if you have effective Oracle by design -- i go into the "physics" of  
partitioning and who -- without the judicious use of global indexes, your system  
could fall apart and run really really slow as well)

GOTO a page to Bookmark Review | Bottom | Top  
Senior Statistical Analyst October 18, 2004  
Reviewer: Hank Freeman from Atlanta, GA

18 October 2004

Tom,

Thanks for the lob\_replace code !!!  
It worked wonders when trying to fix about 25,000 clobs with a known error.  
Here my detailed discussion in outline and then in detail.  
  
1. What type of data was is in the CLOB  
2. What went wrong to create the error  
3. What was done to correct it.  
a. Stored Proc  
b. Declare syntax  
c. VBA code in Excel

1. What type of data was is in the CLOB. The company has about 45,000  
internet/intranet web pages stored in an Oracle database table field which is a  
CLOB. Meaning instead of calling the website from a file server or physical  
location the site, the entire website-webpages/html source code is held in the  
table at this CLOB field.  
2. What went wrong to create the error. An error occurred when these records  
were being modified to remove a specific piece of information and a replacement  
null character was inserted. The null character for some unknown reason did not  
work and the web-page information in the CLOB field got garbage appended to the  
end of the web-page after the closing </html> tag.  
a. It was easy to spot the records but a conventional update within would not  
work because of all the size of the web-pages being so large and the special  
characters that were found within and the unique nature of working with clobs.  
b. I contacted a friend who pointed me to your site and URL:  
[http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950\_P8\_DISPLAYID:1533006062995][http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_1533006062995]  
c. Upon examination of the last changes you made for a previous person, the  
solution came into focus clear on how to update the records and, it had to be  
done with a program.  
3. What was done to correct the problem.  
a. The last stored procedure you listed on your website was used, because it  
was applicable to our situation. This procedure allowed the creation of a  
specific version of the Declare sql to select the bad part of the CLOB record  
and replace it with a “ ” (space character) and not a null.  
i. The Stored Proc syntax, is not listed since it is the Tom’s “lob\_replace”  
syntax verbatim.  
ii. The Declare statement is listed below would take the unique record id and  
the starting location of the bad information in the CLOB. The sql would then  
pass the CLOB, the data to find, and the replacing string (a space) to the  
stored procedure.

Declare Sql syntax

declare  
l\_lob clob;  
l\_substr varchar2(32767);  
begin  
select KS\_DESCRIPTION into l\_lob  
from ks\_longdescription  
WHERE KS\_LONGDESCRIPTIONID = 55012 for update;  
select SUBSTR(KS\_DESCRIPTION, 876, length(KS\_DESCRIPTION) - 876) into  
l\_substr  
from ks\_longdescription  
WHERE KS\_LONGDESCRIPTIONID = 55012;  
hf\_clob\_replace (l\_lob, l\_substr, ' ');  
end;

iii. It was decided to use Excel VBA to control the update process. It was  
contemplated to write a second procedure with for the declare statement and just  
call execute it with parameters. However, as you noticed the maximum value for  
the l\_substr Varchar2 field was declared as 32767. It was already known that  
some of the erroneous parts of the CLOB data were larger than 25,000 characters.  
Therefore, Excel was used to ascertain which records were going to be within  
this limit and those outside a 25,000 character limit would require a manual  
fix.  
iv. Approximately 6,000 records were loaded into an Excel worksheet and the  
following Macro code was written to process/update the records. This was the  
optimum method of performing the cleanup because the VBA code was written to  
decide:  
1. Length of clob  
2. Starting location of the junk in the clob  
3. The unique record id was already known and with the aforementioned  
starting location being determined by the VBA code, it was a simple task to just  
pass there parameters so the VBA Declare SQL string and create a specific run  
for each qualifying record.  
4. The VBA code then would execute the Declare SQL string, firing up the  
stored procedure hf\_lob\_replace, thus cleaning up the record.  
5. If a record could be processed it was, otherwise it was marked for manual  
cleanup.  
6. During the process it was determined that any l\_Substr value greater than  
20,000 would cause an Oracle error on the Buffer size. Therefore, upon see that  
the error would occur often any l\_Substr(p\_what) value greater then 20,000 was  
immediately marked for a “SQL Buffer Failure”, meaning it would have to be  
manually updated.. The important part here was that the process would not stop  
if did not like a specific record, it just kept going.

In Summary, it took less than ONE second to fix a record ,with an auto-commit  
turned on and we knew it was working for the process could be watched. Within  
four hours (6,000 per hour) using two systems, the 25,000 records were  
corrected with only 150 needing a manual fix, a highly acceptable result.

In closing, I would like to give credit to the following.  
? Mr. Andy Sword, who is/was a subscriber to Tom’s web page and submitted to  
me the first draft of the Declare statement we used. This was what we really  
needed to know !!! I had never worked with CLOB, so this was a real leason.  
? Tom’ Web page:  
[http://asktom.oracle.com/pls/ask/f?p=4950:1][http_asktom.oracle.com_pls_ask_f_p_4950_1]:  
? Tom Reed:  
[http://the-big-o.port5.com/index.shtml][http_the-big-o.port5.com_index.shtml]  
where some of the best Excel/VBA code for interfacing with  
Oracle can be found.  
? I have listed the VBA code below for those who are so inclined to read it.  
There is nothing there that is private and confidential. So anyone who can learn  
from it may feel free to do so.

Respectfully

Hank Freeman  
FCS Ltd  
[hfreeman@msn.com][hfreeman_msn.com]

GOTO a page to Bookmark Review | Bottom | Top  
updating millions of records October 26, 2004  
Reviewer: Josphat from KENYA

You approach is quite ok for one line update but how about procedure which  
updates the next row based on value of previous row.  
Kindly look at the following scripts and give hard suggestions on how 5 million  
rows could be processed in under 10 hours...capturing the business logic  
\----starting here  
\---3 Scripts to process over 5 million rows  
\---and constitute approx 40000 distinct identifiers (id)  
\---should exec as ordered

drop table temporary\_emphist;

create table temporary\_emphist  
(ID VARCHAR2(15),PAYDATE DATE, AMOUNT NUMBER(10,2),  
PPART NUMBER(13,2), IPART NUMBER(13,2), INTRATE NUMBER(5,2), OBAL  
NUMBER(13,2),  
REF NUMBER(7), NOOFMONTHS NUMBER(13,2), ACCRUED\_INT NUMBER(15,2))  
/

\-----values(ID ,PAYDATE,AMOUNT , PPART, IPART, INTRATE, OBAL, REF, NOOFMONTHS,  
ACCRUED\_INT)  
\-----data for different ids being inserted (one id might have as many as 300  
entries)  
\-----but i have put just a few for each id  
\----set for customer id 10979844

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE, OBAL)  
values('10979844','30-jun-2003',2000,2,112000);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-jul-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-aug-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-dec-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-jan-2004',1950,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-jun-2004',5000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-aug-2004',4000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10979844','30-oct-2004',3000,2);

\----set for customer id 10779844

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE, OBAL)  
values('10779844','30-jun-2003',2000,2,99380);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-jul-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-aug-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-dec-2003',2000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-jan-2004',1950,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-jun-2004',5000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-aug-2004',4000,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10779844','30-oct-2004',3000,2);

\----set for customer id 10679844  
insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE, OBAL)  
values('10679844','30-jun-2003',800,4,40390);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-jul-2003',1500,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-aug-2003',1600,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-dec-2003',2000,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-jan-2004',1950,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-mar-2004',1950,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-jul-2004',1000,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-aug-2004',1000,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-sep-2004',1992,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE)  
values('10679844','30-oct-2004',1200,4);

commit;  
set lines 1000  
select \* from temporary\_emphist  
order by paydate,id;  
create index  
temporary\_emphistid on temporary\_emphist(id);  
create index temporary\_emphistpd on temporary\_emphist(paydate);

\---script 1 (will number transactions for each customer from 1 to ..n then start  
from 1 for the next customer  
DECLARE -----Procedure 19  
V\_2 NUMBER(7) :=1;  
V\_1 VARCHAR2(18);  
v\_3 VARCHAR2(18);  
v\_4 VARCHAR2(18) :='T';  
v\_5 date;  
CURSOR DRECS IS  
SELECT ROWID,id,paydate  
FROM temporary\_emphist  
ORDER BY ID,paydate;  
BEGIN  
OPEN DRECS;  
LOOP  
FETCH DRECS INTO V\_1,v\_3,v\_5;  
EXIT WHEN DRECS%NOTFOUND;

if v\_3 <> v\_4 then  
v\_4 := v\_3;  
v\_2 := 1;  
end if;  
UPDATE temporary\_emphist  
SET REF = V\_2  
WHERE ROWID = V\_1;  
V\_2 := V\_2+1;  
END LOOP;  
commit;  
CLOSE DRECS;  
END;  
/  
select \* from temporary\_emphist  
order by id,paydate;

\-----script 2

\----gets the difference in months from one transaction to the next

DECLARE -----Procedure 20  
V\_1 NUMBER(7) :=0;  
V\_2 NUMBER(15,2):=0;  
V\_3 NUMBER(15,2) :=0;  
v\_4 NUMBER(15,2) :=0;  
V\_5 NUMBER(15,2) :=0;  
V\_6 NUMBER(15,2) :=0;  
V\_7 varchar2(15) :='0';  
V\_8 NUMBER(15,2) :=0;  
v\_9 number(15,2);  
v10 number(35,10) := ROUND(1/12,10);  
V11 number(15,2) ;  
V12 DATE;  
V14 DATE;  
V13 NUMBER(13,2);  
V15 number(5);

CURSOR UPLO IS  
SELECT REF,OBAL,amount,ID,INTRATE,last\_day(PAYDATE) FROM temporary\_emphist  
where nvl(ref,0)>1  
ORDER BY ID, REF;

BEGIN  
OPEN UPLO;  
LOOP  
FETCH UPLO INTO V\_1,V\_2,V\_3,V\_7,V11,V12;  
EXIT WHEN UPLO%NOTFOUND;  
\---V13 :=V12;  
uPDATE temporary\_emphist  
SET NOOFMONTHS=mONTHS\_BETWEEN(v12,last\_day(paydate))  
WHERE NVL(REF,0) = V\_1-1 and id = V\_7 ;

END LOOP;

commit;  
CLOSE UPLO;  
END;  
/  
select \* from temporary\_emphist  
order by id,paydate;

\---script 3  
\-------------------------------------------puts interest per month  
\-------------------------------------------CALCULATES INTEREST ON OUTSTANDING  
BALANCE  
\---as per the numbering above(script 1) value of rows for customer  
\----will be manipulated to get values for the subsequent row though some input  
from the  
\----current row will be integrated with derivative of the previous to populate  
values into the  
\----current row. This is applied for each customer untill all are cleared

SELECT 'ESTABLISHING BALANCES ON REDUCING BASIS-----Procedure 21 STARTS ' FROM  
DUAL;  
DECLARE -----Procedure 21  
V\_1 NUMBER(7) :=0;  
V\_2 NUMBER(15,2):=0;  
V\_3 NUMBER(15,2) :=0;  
v\_4 NUMBER(15,2) :=0;  
V\_5 NUMBER(15,2) :=0;  
V\_6 NUMBER(15,2) :=0;  
V\_7 varchar2(15) ;  
V\_8 NUMBER(15,2) :=0;  
v\_9 number(15,2);  
v10 number(35,10) := ROUND(1/12,10);  
V11 number(15,2) ;  
V12 NUMBER(15,2) :=0;  
V13 NUMBER(15,2);  
v14 number(15,2) :=0; ---interest hold  
v15 number(15,2) :=0; ---balance hold  
v16 number(15,2) :=0; ---ipart hold  
V20 VARCHAR2(18) :='T';  
CURSOR UPLO IS  
SELECT NVL(REF,0),NVL(OBAL,0),NVL(amount,0),ID,INTRATE,nvl(NOOFMONTHS,1) FROM  
temporary\_emphist

ORDER BY ID,REF;  
BEGIN  
OPEN UPLO;  
LOOP  
FETCH UPLO INTO V\_1,V\_2,V\_3,V\_7,V11,V12;  
EXIT WHEN UPLO%NOTFOUND;  
if v\_7<>v20 AND V\_1=1 then  
v20 :=v\_7;  
V\_4 :=V\_2-V\_3;  
v16 :=ROUND((((v\_4)\*POWER((1+V11/100),v10\*V12)-V\_4)/V12),2);  
v15 :=v\_4;  
ELSE  
  
select obal,INTRATE,nvl(NOOFMONTHS,1) into v\_4,V11,V12 from temporary\_emphist  
where NVL(ref,0) = V\_1 - 1 AND ID = V\_7 ;

select nvl(amount,0) into v\_3 from temporary\_emphist  
where NVL(ref,0) = V\_1 AND ID = V\_7 ;

v15 := ROUND((v\_4)\*POWER((1+V11/100),v10\*V12),2)-v\_3; -------obal  
v14 :=ROUND((v\_4)\*POWER((1+V11/100),v10\*V12),2)-V\_4; ---accrued interest  
v16 :=ROUND((((v\_4)\*POWER((1+V11/100),v10\*V12)-V\_4)/V12),2); --ipart  
END IF;  
UPDATE temporary\_emphist  
SET OBAL = v15,ACCRUED\_INT =v14,IPART=v16  
WHERE NVL(REF,0) =V\_1 AND ID = V\_7 ;  
\--END IF;  
END LOOP;  
commit;  
CLOSE UPLO;  
END;  
/  
select \* from temporary\_emphist  
order by id,paydate;

I dont know whether there is a faster way to employ the logic...I've seen your  
authority..give it a short

Followup:  
one word for you:

analytics

they rock, they roll. lag/lead -- i frankly did not really read all of your  
code (i'm not a compiler, v\_1, v\_2 etc make it really truly utterly hard to  
follow and comprehend) but if you need to update a row based on the prior row(s)  
after sorting -- analytics -- lag, lead, sum, whatever.

GOTO a page to Bookmark Review | Bottom | Top  
database October 26, 2004  
Reviewer: cp

Hi Tom,

I have to appear for interview and need your help.  
1. Hows to decide for datafile size?  
2. How to decide for redolog file size?  
3. Any other factors which you feel as interviewer to ask?

Please help me out as its really urgent  
Regards

Followup:  
how do you know they'll ask these questions -- not questions I would ask.

datafile size = pure function of amount of data you want to store. I mean,  
well, what else could you say?

redolog file size is a function of

o amount of redo generated by most of your transactions  
o the number of these transactions you do in a period of time

predominantly (but if you are an experienced dba -- why not just fall back on  
your knowledge of what you've done in the past?)

GOTO a page to Bookmark Review | Bottom | Top  
problem October 26, 2004  
Reviewer: cp

So untill or unless we get some opportunity we cant learn the skills to decide  
for initial size of database right?

Please provide words about or facotrs to think on this

Thanks!

Followup:  
experience.

you get an entry level job (you have to sort of start in the basement) and get  
experience.

GOTO a page to Bookmark Review | Bottom | Top  
update of millions of records/rows October 29, 2004  
Reviewer: Josphat from KENYA

Hi Tom,  
Thanks for decisive debate in this site.

Kindly go through my script cos now I have elaborated on  
the v\_1,v\_2..etc just variables as you will see...SORRY for MY FIRST SUBMISSION.  
I will be grateful if ya suggestion /code  
could reduce my processing time for 5 million records from  
15 hrs to 1, 2, 3? .

\---Script to process over 5 million rows  
\---and constitute approx 40000 distinct identifiers (id)

\-------------------------------------------puts interest per month  
\-------------------------------------------CALCULATES INTEREST ON OUTSTANDING  
BALANCE  
\-----EACH customer HAS OWN ROWS STARTING 1..N AND REPRESENTED BY REF COLUMN ON  
TABLE  
\-----MULTIPLIER FACTOR FOR EACH MONTH IS REPRESENTED BY NOOFMONTHS  
\-----will be manipulated to get values for the subsequent row though some input  
from the  
\-----current row will be integrated with derivative of the previous to populate  
values into the  
\-----current row. This is applied for each customer untill all are cleared  
\-----PROCESSING IS SEQUENCIAL (I.E FROM REF 1 ..N FOR EACH CUSTOMER)  
\-----REF =1 FOR EACH CUSTOMER HOLDS A VALUE FOR OBAL WHICH WILL BE USED TO FILL  
UP  
\-----THE SUBSEQUENT ROWS AFTER MANIPULATION WITH MULTIPLIER FACTOR

\----work starts next

drop table temporary\_emphist;

\----DATA set for customer id 10979844

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,  
OBAL,ref,NOOFMONTHS)  
values('10979844','30-jun-2003',2000,2,112000,1,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-jul-2003',2000,2,2,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-aug-2003',2000,2,3,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-dec-2003',2000,2,4,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-jan-2004',1950,2,5,5);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-jun-2004',5000,2,6,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-aug-2004',4000,2,7,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10979844','30-oct-2004',3000,2,8,0);

\----DATA set for customer id 10779844

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,  
OBAL,ref,NOOFMONTHS)  
values('10779844','30-jun-2003',2000,2,99380,1,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-jul-2003',2000,2,2,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-aug-2003',2000,2,3,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-dec-2003',2000,2,4,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-jan-2004',1950,2,5,5);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-jun-2004',5000,2,6,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-aug-2004',4000,2,7,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10779844','30-oct-2004',3000,2,8,0);

\----DATA set for customer id 10679844  
insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,  
OBAL,ref,NOOFMONTHS)  
values('10679844','30-jun-2003',800,4,40390,1,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-jul-2003',1500,4,2,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-aug-2003',1600,4,3,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-dec-2003',2000,4,4,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-jan-2004',1950,4,5,2);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-mar-2004',1950,4,6,4);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-jul-2004',1000,4,7,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-aug-2004',1000,4,8,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-sep-2004',1992,4,9,1);

insert into temporary\_emphist(ID ,PAYDATE,AMOUNT , INTRATE,ref,NOOFMONTHS)  
values('10679844','30-oct-2004',1200,4,10,0);

commit;  
set lines 1000  
select \* from temporary\_emphist  
order by paydate,id;

create index temporary\_emphistid on temporary\_emphist(id);  
create index temporary\_emphistpd on temporary\_emphist(paydate);

\---script STARTS

DECLARE  
V1\_REF temporary\_emphist.REF%TYPE;  
V2\_OBAL temporary\_emphist.OBAL%TYPE;  
V3\_AMOUNT temporary\_emphist.amount%TYPE;  
v\_4 NUMBER(15,2) :=0;  
V5 NUMBER(15,2) :=0;  
V6 NUMBER(15,2) :=0;  
V7\_ID Temporary\_emphist.ID%TYPE;  
V8 NUMBER(15,2) :=0;  
v9 number(15,2);  
v10 number(35,10) := ROUND(1/12,10); ---CONVERTS ONE MONTH INTO  
YEARS  
V11\_INTRATE temporary\_emphist.INTRATE%TYPE;  
V12\_NOOFMONTHS temporary\_emphist.NOOFMONTHS%TYPE;  
V13 NUMBER(15,2);  
v14 number(15,2) :=0; ---interest hold  
v15 number(15,2) :=0; ---balance hold AS CALCULATED  
v16 number(15,2) :=0; ---ipart holdING INTEREST AS CALCULATED  
V20 VARCHAR2(18) :='T'; ----ARBITRALY KEY TO ENABLE FOCUS ON  
DIFFERENT IDS  
CURSOR UPLO IS  
SELECT NVL(REF,0),NVL(OBAL,0),NVL(amount,0),ID,INTRATE,nvl(NOOFMONTHS,1) FROM  
temporary\_emphist

ORDER BY ID,REF;  
BEGIN  
OPEN UPLO;  
LOOP  
FETCH UPLO INTO V1\_REF,V2\_OBAL,V3\_AMOUNT,V7\_ID,V11\_INTRATE,V12\_NOOFMONTHS;  
EXIT WHEN UPLO%NOTFOUND;

\----V20 IS VARIABLE KEY FOR CHECKING WHETHER WORKING ON SAME ID

if v7\_ID<>v20 AND V1\_REF=1 then  
v20 :=v7\_ID;  
V\_4 :=V2\_OBAL-V3\_AMOUNT;  
v16  
:=ROUND((((v\_4)\*POWER((1+V11\_INTRATE/100),v10\*V12\_NOOFMONTHS)-V\_4)/V12\_NOOFMONTHS  
),2);  
v15 :=v\_4;  
ELSE  
  
select obal,INTRATE,nvl(NOOFMONTHS,1) into v\_4,V11\_INTRATE,V12\_NOOFMONTHS from  
temporary\_emphist  
where NVL(ref,0) = V1\_REF - 1 AND ID = V7\_ID ;  
select nvl(amount,0) into v3\_AMOUNT from temporary\_emphist  
where NVL(ref,0) = V1\_REF AND ID = V7\_ID ;  
\--------V10 IS ONE MONTH COVERTED INTO YEARS  
v15 :=  
ROUND((v\_4)\*POWER((1+V11\_INTRATE/100),v10\*V12\_NOOFMONTHS),2)-v3\_AMOUNT;  
\-------CALCULATED obal  
v14 :=ROUND((v\_4)\*POWER((1+V11\_INTRATE/100),v10\*V12\_NOOFMONTHS),2)-V\_4;  
\---CALCULATED accrued interest  
v16  
:=ROUND((((v\_4)\*POWER((1+V11\_INTRATE/100),v10\*V12\_NOOFMONTHS)-V\_4)/V12\_NOOFMONTHS  
),2); --ipart  
END IF;  
UPDATE temporary\_emphist  
SET OBAL = v15,ACCRUED\_INT =v14,IPART=v16  
WHERE NVL(REF,0) =V1\_REF AND ID = V7\_ID ;  
\--END IF;  
END LOOP;  
commit;  
CLOSE UPLO;  
END;  
/  
select \* from temporary\_emphist  
order by id,paydate;  
spool off

Followup:  
this isn't the right place for "recode my application".

show me your attempt at using analytics.

(UPDATE temporary\_emphist  
SET OBAL = v15,ACCRUED\_INT =v14,IPART=v16  
WHERE NVL(REF,0) =V1\_REF AND ID = V7\_ID ;

is still really obscure, comments didn't "do much" really)

GOTO a page to Bookmark Review | Bottom | Top  
Reader November 21, 2004  
Reviewer: dheeraj from UK

Excellent discussion as usual on this site.

GOTO a page to Bookmark Review | Bottom | Top  
Selecting data over a database link from 7.3.4 to 10g December 02, 2004  
Reviewer: Stewart W. Bryson from Atlanta, GA

Tom:

If I use the "INSERT /\*+ APPEND \*/ ..." statement on a 10g database, but the  
"SELECT..." part of the statement is over a database link to a 7.3.4 database,  
would the bulk-loader still be invoked, or would the hint be ignored?

Also, if I do a "MERGE... " statement with the "INSERT..." part of the statement  
being an "INSERT /\*+ APPEND \*/...", would the MERGE work correctly, and would  
the bulk-loader be used?

Thanks for your time.

Followup:  
trick question

it isn't going to work -- 9ir2 and later do not interoperate with 7.3 and  
earlier.

if it did work

insert /\*+ append \*/ into local\_table select \* from [remote\_table@dblink][remote_table_dblink];

could be direct pathed.

insert /\*+ append \*/ is supported only for insert as select -- not insert  
values.

[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> create table t ( x varchar2(25), y varchar2(25) );  
  
Table created.  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]>  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> merge into t  
2 using dual  
3 on ( t.x = dual.dummy )  
4 when matched then update set y = dummy  
5 when not matched then insert /\*+ append \*/ values ( dummy, null );  
  
1 row merged.  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]>  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> select \* from t;  
  
X Y  
\------------------------- -------------------------  
X  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> insert /\*+ append \*/ into t values ( 'y', null );  
  
1 row created.  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> select \* from t;  
  
X Y  
\------------------------- -------------------------  
X  
y  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> insert /\*+ append \*/ into t select 'z', null from dual;  
  
1 row created.  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> select \* from t;  
select \* from t  
\*  
ERROR at line 1:  
ORA-12838: cannot read/modify an object after modifying it in parallel  
  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]>

only the last insert append was a direct path...

GOTO a page to Bookmark Review | Bottom | Top  
A follow-up question December 02, 2004  
Reviewer: Stewart W. Bryson from Atlanta, GA

Thanks for the good information Tom.

I've looked and I've looked, but I still cannot find the information about 9iR2  
and 7.3.4 database links stated in the documentation. As a matter of fact, the  
9iR2 Migration guide lists that 7.3.4 Materialized View sites can interact with  
9iR2 master sites, and vice versa:

[http://download-west.oracle.com/docs/cd/B10501\_01/server.920/a96530/migrep.htm\#620714][http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96530_migrep.htm_620714]

As Advanced Replication utilizes database links, this seems a little  
contradictory. Of course, if you tell me once more (I'm a bit stuborn) that it  
won't work, I promise to believe you.

As I am currently architecting a solution to try and bring some of the data from  
our European sites into a central repository, and as I don't yet have access to  
those sites, I cannot simply test it as we have no 7.3.4 sites (thank God) in  
the US.

I appreciate your time and understanding.

Followup:  
contact support -- they will confirm

version 7.3 was so far out of any sort of support at the time of 9ir2 being  
released that they never even considered testing it. and it does not work.

I believe that documentation to be erroneously left over from 9ir1.

GOTO a page to Bookmark Review | Bottom | Top  
December 03, 2004  
Reviewer: mg from Germany

Hi Tom,

I have a table T in tablespace TS1.

Every day so much of insertions on table T.

So I needs to check the a perticular size of TS is exceeds, if then I needs to  
move the older data to another tablespace.

So could you please tell me the best way to do this?

meny thanks in advance.

Followup:  
you sound like you really want a rolling window partitioned table -- a table  
partitioned by date.

GOTO a page to Bookmark Review | Bottom | Top  
December 06, 2004  
Reviewer: A reader

Can we use trigger to check the % of filled tablespace size ?

Followup:  
yes, but -- why? just to make the code run really really slow?

GOTO a page to Bookmark Review | Bottom | Top  
What about the larger tables? December 15, 2004  
Reviewer: Steven from Austin, TX USA

I used to work at a place where the main table had 4 billion rows and growing.  
This tracked manufacturing details and rows for an item trickled in and then  
were "archived" (written to tape then deleted) as one. Finding a good  
partitioning scheme was difficult due to the large variable time that items  
could stay in the manufacturing process. That is, two rows inserted on the same  
day could be archived on vastly different timescales.

The system had around 40 million rows inserted and deleted on a daily basis  
(about 1%). Some of the items could have 1 million or more records; they needed  
to be archived at the same time. The table had a PK and a secondary index.

Obviously CTAS was not an option due to the 24x7 availability requirement and  
besides we usually didn't have enough space for a full copy of the main table.

Is this a case where you just kick off the delete and take your lumps?

(Hmm, given past growth curves, the table should by now be up to 20 billion rows  
and 200 million rows in/out daily.)

Followup:  
or, enable row movement....

partition by a date field, the date field represents "record done as of"

when you initially insert it, it has a "done as of" way into the future (in the  
active partition(s))

when you are done, you update this field and it moves from active into the "you  
can archive me later" partitions.

It would be like the delete but the delete would be happening continously as  
little transactions instead of a big delete all at once.

GOTO a page to Bookmark Review | Bottom | Top  
data warehouse daily load December 16, 2004  
Reviewer: shgoh from Malaysia

Dear Tom,

One of my dw developer is having performance problem with their daily data  
load from legacy system. Approximately 5-8 million records inserted into a table  
(append load table). The table has a lots of constraints and none of us are  
familiar with the data given (it could have a constraint voilation).

What is the method to speed up the data loading process beside parallel,  
direct load and nologging ?

Appreciated if you can share your view. Since we don't know well about the  
data and the constraints, is it advisable to disbale constraint in this case.  
Thanks

Rgds  
SHGoh

Followup:  
parallel  
direct  
nologging

they are them. how long is "long"

if you are adding 5/8 million records a day -- are you using partitioning? you  
can look at disabling portions of partitioned indexes and rebuilding them after  
the fact.

GOTO a page to Bookmark Review | Bottom | Top  
Not a partionioning table December 17, 2004  
Reviewer: shgoh from Malaysia

Dear Tom,

It is not a partioning table. Would it help if I go for parallel , nologging,  
direct and disable portion of the constraints? Thanks

Rgds  
Shgoh

Followup:  
you are already doing parallel, nologging, direct -- disabling the constraints  
only means "gotta enable them".

you asked for additional ideas. partitioning.

GOTO a page to Bookmark Review | Bottom | Top  
How about Bulk Update? December 22, 2004  
Reviewer: Tony from India

Tom,

I have two tables PO\_HDR and PO\_DTL having millions of records. I want to join  
the two tables, and update a flag in PO\_HDR table. Can I use Bulk Update based  
on rowid here?

Example:

DECLARE

TYPE tblrowid IS TABLE OF ROWID INDEX BY BINARY\_INTEGER;  
arr\_rowid tblrowid;

CURSOR po\_cur  
IS SELECT a.rowid FROM po\_hdr a, po\_dtl b  
WHERE a.poseq = b.poseq  
AND a.custid = b.custid  
AND b.po\_qty <> b.del\_qty;  
  
BEGIN

OPEN po\_cur;  
LOOP  
FETCH po\_cur BULK COLLECT INTO arr\_rowid LIMIT 100000;  
EXIT WHEN arr\_rowid.COUNT = 0;  
  
FORALL i IN arr\_rowid.FIRST..arr\_rowid.LAST  
UPDATE po\_hdr  
SET del\_flg = 'Partial'  
WHERE rowid = arr\_rowid(i);

COMMIT;  
END LOOP;  
CLOSE po\_cur;

END;  
/

Is rowid based bulk update is fast, and reliable? Is there any better, and  
faster approach?

Please advice me.

Followup:  
update po\_hdr  
set del\_flg = 'Partial'  
where (poseq,custid) in ( select poseq,custid  
from po\_dtl  
where po\_qty <> del\_qty );

period.

GOTO a page to Bookmark Review | Bottom | Top  
To update or insert by sql statement and not procedurally December 23, 2004  
Reviewer: DAV

Hi,Tom,  
I have 2 questions:  
The one is :  
By the update - sql statement that you wrote above  
say i want to execute on millions records, and i want to make commit every 1000  
records  
how can i do it?  
The second question is:  
If i have to use pl/sql table and i want to load  
alot of records to the table say 70,000- have i do it by:  
fetch bulk bind limit 1000  
or SELECT ... BULK BIND ....?  
Why is it a problem to load many records to pl/sql tables?  
Thank's  
DAV

Followup:  
you don't want to commit every 1000 records, that would take longer, consume  
more resources and break your transactional integrity.

if you have 70k rows to process, and want to do it 1k at a time, you'll be using  
fetch ... bulk collect ... limit 1000;

can you spell "memory"

GOTO a page to Bookmark Review | Bottom | Top  
Tom,sorry i didn't understand you. December 23, 2004  
Reviewer: DAV

Why fetch is better than sql statement at the case that written above. You told  
before that sql is better.  
Can you answer me please to the second question thar i wrote above?  
Tnank you very much.  
DAV

Followup:  
not understanding you.

you asked:

If i have to use pl/sql table and i want to load  
alot of records to the table say 70,000- have i do it by:  
fetch bulk bind limit 1000  
or SELECT ... BULK BIND ....?  
Why is it a problem to load many records to pl/sql tables?

and I answered??? the question wasn't "single sql vs procedural code"

you started with "if I HAVE TO USE..."

you would not select BULK COLLECT for 70k records, you would use fetch bulk  
collect with the limit clause.

GOTO a page to Bookmark Review | Bottom | Top  
I was unclear - let me explain December 23, 2004  
Reviewer: A reader

Sorry ,Tom,  
i didn't explain myself well.  
What i meant was:

First:

if i have to update million records in the plsql procedure  
is it better to do it in update sql statement or procedural kode like  
fetch.....? i want to commit every n records because of rollback segment so if i  
use sql update statement how can i do it?

Second:

General question - IF I USE PROCEDURAL CODE - why it is not good to load say 40k  
of records to plsql table by SELECT...BULK BIND... but by fetch bulk bind limit  
...  
As i know plsql table's records are kept in pga memory,so what happend if there  
will be 40k of records?  
Please,clarify the theory.  
Thank you very much.

Followup:  
you need to size your rollback segment large enough to do you job. seriously --  
really you do.

a million records is nothing.

[big\_table@ORA9IR2][big_table_ORA9IR2]> update big\_table set owner = lower(owner);  
  
1000000 rows updated.  
  
[big\_table@ORA9IR2][big_table_ORA9IR2]> select used\_ublk\*8/1024 from v$transaction;

USED\_UBLK\*8/1024  
\----------------  
92.4921875  
  
[big\_table@ORA9IR2][big_table_ORA9IR2]>

93meg, big deal.

If you commit every N records -- you do realize you have to make your code  
restartable somehow, so when you inflict the ora-1555 on YOURSELF by committing,  
or fail for whatever other reason -- you can restart where you left off. Now we  
are talking some code!

If you have to update tons of records and tons of columns AND they are indexed,  
disable indexes, consider recreating the table instead of a mass update (if you  
are updating millions of records -- this is going to be an offline thing anyway,  
you might be supporting queries but you are not going to have "high  
concurrency")

can you spell "memory"

and think about streaming -- keeping everyone busy. So you wait for a long time  
while oracle gets 40k records, jams them into ram, manages them, then you  
process them and do it all over. doing things in bite sized pieces is easier  
for everyone - couple hundred in , couple hundred out.

GOTO a page to Bookmark Review | Bottom | Top  
Update 1000000 in sql statement December 24, 2004  
Reviewer: A reader

Thank's, Tom!  
What is the message ora-1555?I haven't a guide.  
And i really can't findthe way to mmit n rows in update sql statement.May you  
give me some idea?  
About loading to PL/SQL table for example 80k, if i need procedural code of  
update at least 1000000 records and not update sql statement so why can'not i  
avoid pl/sql table and do it just by regular cursor?

Followup:  
search this site for 1555, you'll see lots and lots. check out Expert one on  
one Oracle -- i wrote about it lots.

YOU DO NOT WANT TO COMMIT THE ROWS INCREMENTALLY. that is my fundemental  
premise here.

If you have access to my book "Expert one on one Oracle" -- you can read all  
about it.

GOTO a page to Bookmark Review | Bottom | Top  
Another question December 24, 2004  
Reviewer: A reader

About loading to PL/SQL table for example 80k, if i need procedural code of  
update at least 1000000 records and not update sql statement so why can'not i  
avoid pl/sql table and do it just by regular cursor?  
  
Thank's

Followup:  
slow by slow is slow by slow (opps, keep doing that don't I, row by row is row  
by row)

GOTO a page to Bookmark Review | Bottom | Top  
Sorry,misunderstanding. December 25, 2004  
Reviewer: A reader

I didn't explain myself well.  
What i meant in my question is a generall question:  
If a need a cursor ( and not sql statement) for some process that acceses to a  
big table,why is it better to load data to pl/sql table and run on plsql table  
than just fetch records directly from the db table ?

Followup:  
I understood, you didn't understand me.

slow by slow processing (a row at a time)

is slower than

o a single sql statement  
o bulk collect and forall updates

doing things in BULK is

o faster  
o uses less resources

GOTO a page to Bookmark Review | Bottom | Top  
index DDL in the midst of transactions December 30, 2004  
Reviewer: Nik Malenovic from Chicago, IL

Tom,

this is a followup to your March 07, 2004 followup related to ddl operations on  
indecies whilst there are outstanding transactions in progress on the underlying  
table. You said

"that'll happen whilst there are outstanding transactions, yes. You'll want to  
keep trying until you get a chance to drop it."

We just ran into a similar production problem: the system was slow because we  
were missing an index on a column and there were updates running full table  
scans (don't ask!). Since there are users banging on the darn thing, there was  
no way to shutdown and add index so we had to do it on the fly. Here's what we  
did:

declare  
l\_attempt pls\_integer := 0;  
l\_limit pls\_integer := 2000\*10; -- 10 seconds  
l\_rindex binary\_integer;  
l\_slno binary\_integer;  
l\_target binary\_integer;  
begin  
l\_rindex := dbms\_application\_info.set\_session\_longops\_nohint;  
l\_target := 42;

dbms\_application\_info.set\_client\_info('Vincent The Builder');  
dbms\_application\_info.set\_module('rebuild\_index', 'between\_transactions');  
for l\_attempt in 1 .. l\_limit  
loop  
begin  
dbms\_application\_info.set\_session\_longops  
(  
rindex => l\_rindex,  
slno => l\_slno,  
op\_name => 'rebuilding the index',  
target => l\_target,  
context => 0,  
sofar => l\_attempt,  
totalwork => l\_limit,  
target\_desc => 'attempt',  
units => 'attempts'  
);

execute immediate 'create index RESPONSE\_QUESTIONID\_INDX on response  
(questionid) nologging parallel 5';  
dbms\_output.put\_line('SUCCESS - now rebuilding');  
exit;  
exception  
when others then  
null;  
end;  
if l\_attempt = l\_limit then  
dbms\_output.put\_line('FAILED - there was not a phat chance to succeed');  
end if;  
end loop;  
dbms\_application\_info.set\_module(null, null);  
dbms\_application\_info.set\_client\_info(null);  
end;  
/

and in another session we monitored the progress with

select sid, message from v$session\_longops where time\_remaining > 0;

that's all. This may be useful for somebody in a similar situation, hence the  
post.

thanks,

Nik

Followup:  
you could try (for maximum "uptime")

create index .. ONLINE;

that would

a) block and wait for outstanding transactions to complete but...  
b) block new transactions from starting and  
c) once it started creating -- lets the new transactions proceeed and  
d) at the very very end -- does a/b again to finish

reduces the amount of "downtime" by allowing people to still work in the middle  
(yes, they'll be full scanning, but working -- if that was something you  
wanted..)

GOTO a page to Bookmark Review | Bottom | Top  
CTAS doesnt have compute statistics? January 06, 2005  
Reviewer: A reader

create index .... compute statistics;

Why doesnt create table have a similar compute statistics clause?

I have a CTAS that takes 5 hours during which Oracle already knows everything  
about the data (similar to create index). Gathering stats on this table later  
takes another 5 hours!

Is there a way to gather stats during the CTAS itself?

Thanks

Followup:  
Oracle does not already know about everything.

Have you traced a dbms\_stats.gather\_table\_stats to see how many queries, in how  
many different ways it gathers stuff?

about the only thing Oracle knows after a CTAS is number of rows and number of  
blocks (and so do you, you can set that)

there is no way it was computing histograms and such

I don't believe it takes 5 hours after the fact -- if it does, have you  
considered "estimate"

GOTO a page to Bookmark Review | Bottom | Top  
January 18, 2005  
Reviewer: robin

I am trying to modify your code from above:

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
set (c1,c2,c3) = ( select max(decode(segment3,'xxxxx',ccid)),  
max(decode(segment3,'zzzzz',ccid)),  
max(decode(segment3,'wwwww',ccid))  
FROM gl\_code\_combinations t2,  
gl\_sets\_of\_books gb  
WHERE t2.segment5 = 'yyyyy'  
and t2.segment4 = t1.s4  
and t2.segment3 in ('xxxxx','zzzzzz', 'wwwww')  
and t2.segment2 = t1.s2  
and t2.segment1 = t1.s1  
and t2.coa\_id = gb.coa\_id  
and gb.set\_of\_books\_id = 1 )  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
to allow me to update one column based on several different where clauses and  
having no luck.

What I want to do is:

update table t1

set t1.c1 = 'X' where t1.c5 and t1.c6 are null

set t1.c1 = 'A' where t1.4 is not null and t1.c8 > sysdate

set t1.c1 = 'B' where t1.4 is null and t1.c8 < sysdate

etc....

I've tried a case statement and decode like above, but neither seems to  
compile....thanks

Followup:  
well, in answer to the "what I want to do is"

update table t1  
set t1.c1 = (case when c5 is null and c6 is null  
then 'X'  
when c4 is not null and c8 > sysdate  
then 'Y'  
when c4 is null and c8 < sysdate  
then 'B'  
end )  
where (c5 is null and c6 is null )  
or (c4 is not null anc c8 > sysdate)  
or (c4 is null and c8 < sysdate)

I cannot comment on the other one -- well, because there isn't anything to  
comment on :)

GOTO a page to Bookmark Review | Bottom | Top  
updating large table January 20, 2005  
Reviewer: Tracy from UK

I have table\_a with 50 million rows and table\_b with 10 million rows.

table\_a has a new column added called statid

Table\_b has statid and tabaid. tabaid has a unique constraint on it and it is  
also a foreign key to the primary key of table\_a (id)

I want to update all rows in table\_a to set the statid column to match the  
statid column from table\_b where the tabaid column in table\_b = the primary key  
in table\_a

which of the following two do you think is better, and is there some other  
altogether different way which would be better still?

UPDATE  
(SELECT a.statid newval  
,b.statid oldval  
FROM table\_a a  
,table\_b b  
WHERE a.id=b.tabaid)  
SET newval = oldval

UPDATE table\_a a SET a.statid =  
(SELECT b.statid  
FROM table\_b b  
WHERE a.id=b.tabaid)

I've tested the above on a subset of the data using rownum, and both statements  
seem to update at a rate of about 300,000 rows per hour, but I would like to get  
it going faster than this.

Followup:  
update the join.

expect to see a nice big juicy full scan and hash join. it'll be as good as it  
gets.

GOTO a page to Bookmark Review | Bottom | Top  
I have a similar situation where I must update millions of records fast February 09, 2005  
Reviewer: Ryan from Canada

First I want to say thank you very much for all your help this last week Tom, it  
has helped me out a lot. Unfortunately Oracle is not easy so I have another  
question.

I am updating millions of records using insert statements.  
I am updating the records using insert with values. I'm not updating one table  
using another table.

example:  
insert into dbname (FirstName, Number) values ('Ryan', 1);

called with different records millions of times.

When I call "commit" at the end of all the insert's it takes more than 17 hours  
to commit. Of course this was importing records from a 1.5 gigabyte text file  
with 83000 records. The time to call all insert statements was less than a half  
an hour. Can you think of any reasons why the commit would be so slow. There is  
another program at the office that can import the same data (in another database  
format) in less than 20 minutes and that program is calling insert and commit in  
a similar fashion so it can't be that oracle is just that slow. I'm also  
disabling all triggers at the beginning of the import, and enabling them at the  
end. I'm turning off logging and using /\*+ APPEND \*/ and parallel hints but they  
probably don't help as I'm not using direct load inserts.

Any help would be greatly appreciated.

Followup:  
<quote>  
I am updating millions of records using insert statements.  
</quote>

really?

sorry-- but i don't believe you on your commment about commit, you are mistaken.

committing is fast, regardless of the number of records. Your method of  
observation is somehow "flawed".

Unless of course you have an "on commit refresh" set of mv's or something.

So, turn on 10046 level 12 trace and trace it, use tkprof to report on it and  
see what you see.

GOTO a page to Bookmark Review | Bottom | Top  
query retrival slow it effects in the whole system February 10, 2005  
Reviewer: Praveen from US

hi Tom  
When developer using the escape sequence in the selection criteria the response  
time is slow upto 15sec. this delay time effect the whole system. Kindly suggest  
the appropriate way to resolve this.  
select count(\*)  
from pf\_document\_rv where  
((r\_folder\_path like '/Jobs/52021/Assembly/UNDER~\_BAR/%' escape '~')  
and (lower(r\_object\_id) <> lower('f004000000010471')))  
takes about 15 secs to run

select count(\*)  
from pf\_document\_rv where  
((r\_folder\_path like '/Jobs/52021/Assembly/NOBAR/%')  
and (lower(r\_object\_id) <> lower('f004000000010471')))  
takes less then one second.  
Perhaps this is helpful ... if I add the escape '~' specifier to this query it  
also runs slowly even though it doesn't use the escape character.  
regards  
PKR

Followup:  
so, why do you believe this has anything to do with escape?

(you do understand these two queries return totally different result sets and  
perhaps that -- that is the "difference")

have you tkprof'ed these queries and compared them?

GOTO a page to Bookmark Review | Bottom | Top  
RE : Row Migration February 17, 2005  
Reviewer: A reader from NJ, USA

Hi Tom,

You mentioned in one of the above discussion threads that while updating a  
null-column to a not null value we have to make sure that we have enough PCTFREE  
and no row migration happens.  
I have a table that gets 2 million rows on a daily basis and I want to update  
say a column for only 50000 rows.

1. What should be my approach  
A. if the column to update is indexed.  
B. if the column to update is not indexed  
C. How do I find out row migration that may happen  
during the update

Thanks

Followup:  
regardless of whether the column is indexed or not -- the approach would be "the  
same". the index will make the update take longer as it does lots more work,  
but for just 50,000 rows -- it won't be a very big deal.

2,000,000 and 50,000 are only big if it is US dollars and it was your bonus and  
paycheck. In a database, in 2005, they are very small numbers.

[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> set timing on  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> update big\_table set owner = owner||'x' where mod(id,40) = 0;  
  
50000 rows updated.  
  
Elapsed: 00:00:18.02  
  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> create index big\_table\_idx on big\_table(owner);  
  
Index created.  
  
Elapsed: 00:00:27.84  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> update big\_table set owner = owner||'y' where mod(id,40) = 0;  
  
50000 rows updated.  
  
Elapsed: 00:00:28.04  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> select count(\*) from big\_table;  
  
COUNT(\*)  
\----------  
2000000  
  
Elapsed: 00:00:03.02  
[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]>

to discover if rows migrated, you would analyze the table (perhaps using the  
list chained rows, if you just analyze it, you'll get chain\_cnt filled in the  
dictionary)

you cannot find out "before" the update what will happen.

GOTO a page to Bookmark Review | Bottom | Top  
RE : Update February 18, 2005  
Reviewer: A reader from NJ, USA

Tom,

I think I did not make the last statement regarding the UPDATE of 50000+ rows  
more clear. Here is my requirement :

1. I have a flat-file coming in to our system around 10.00 AM in the morning  
with over 2 million records and has the following fields :  
ACCOUNT\_NUMBER  
SECURITY\_ID  
COST\_BASIS  
....  
....  
2. I have another flat file (a delta file containing only data that has changed)  
coming in on a daily-basis around 2.00 AM in the morning with around 50000+  
records and has the following fields :  
ACCOUNT\_NUMBER  
SECURITY\_ID  
.....  
.....  
which we directly load into a table B with the following fields :  
ACCOUNT\_NUMBER  
SECURITY\_ID  
COST\_BASIS <== which is set to NULL during this load  
.....  
.....  
3. Once this file is loaded, we run a process which takes unmodified data (from  
prior run ie. which did not appear in file in step 2) so that the table B always  
has full-load which is the 2 million records I was talking about  
4. We need to run a process that will update the COST\_BASIS field only for  
modified data (ie. that appeared in today's delta load which will be the 50000+  
records or so that I need to update. For data is brought over from previous run,  
we use prior run's value itself)

In order to perform this, I thought of using the 2 million file as an external  
table (say table A) and join this table with B and update the COST\_BASIS from A  
to B for the modified records. We have the logic to identify the changed records  
however. But my concern is that the table A has to be full-scanned for 50000+  
records from B and no indexes can be created on A.  
I kindly need your suggestion as to what would be the best approach in tackling  
this problem.

Followup:  
why would A be full scanned more than once in a join?

GOTO a page to Bookmark Review | Bottom | Top  
RE : Full Scan February 18, 2005  
Reviewer: A reader from NJ, USA

Tom,

This is because I thought if we join two tables, then it will do a Nested Loop  
join. For each row got from table B, it would have to full scan A to get  
matching rows and also that B (delta table) would be my driving table (since it  
has fewer number of rows to start with)

If my understanding is not correct, please advise.

Followup:  
hash join it would do in light of no indexes.

hash join it would do even in the presence of indexes because the CBO is smart  
and knows indexes are evil when most or all of the rows are going to be  
processed.

it would be a nice big juicy fast hash join.

GOTO a page to Bookmark Review | Bottom | Top  
Use MERGE command to combine steps 3 and 4 February 18, 2005  
Reviewer: Logan Palanisamy from Santa Clara, CA USA

Combine steps 3 and 4 with the merge command. Your problem is solved. The  
External table with 2 million records will be scanned just once.

MERGE INTO B  
USING A  
ON (B.ACCOUNT\_NUMBER = A.ACCOUNT\_NUMBER)  
WHEN MATCHED THEN UPDATE  
SET B.COST\_BASIS = A.COST\_BASIS  
WHEN NOT MATCHED THEN INSERT (B.ACCOUNT\_NUMBER, B.SECURITY\_ID, B.COST\_BASIS, ..)  
VALUES (A.ACCOUNT\_NUMBER, A.SECURITY\_ID, A.COST\_BASIS, ..)

"A" is your external table with 2 million records.  
"B" is the one that initally contains 50,000+ records. After the merge, B will  
have 2 million records.

The INSERT clause is step number 3  
The UPDATE clause is step number 4

Hope that helps

GOTO a page to Bookmark Review | Bottom | Top  
Delete Record !!! February 28, 2005  
Reviewer: Anupam

What is the diffence between :

1. DELETE <table> TRUNCATE WHERE <clause>;  
2. DELETE <table> WHERE <clause>;

?? Both works!!!

Followup:  
just like

[ops$tkyte@ORA9IR2][ops_tkyte_ORA9IR2]> delete t this\_is\_a\_correlation\_name where 1=1;  
  
27794 rows deleted.

works -- truncate is just being used as a correlation name there (a table alias)

GOTO a page to Bookmark Review | Bottom | Top  
Delete record February 28, 2005  
Reviewer: Anupam from India

What is the diffence between :

1. DELETE <table> TRUNCATE WHERE <clause>;  
2. DELETE <table> WHERE <clause>;

?? Both works!!!

GOTO a page to Bookmark Review | Bottom | Top  
Deleted message March 03, 2005  
Reviewer: Franc Uwa from Nigeria

the deleted was of today and trying to see if you can help me to resend those  
deleted message of me back to my inbox

Followup:  
huh?

GOTO a page to Bookmark Review | Bottom | Top  
March 10, 2005  
Reviewer: Brad Doczkat from Pittsburgh, PA

What do you do when the resident DBAs do not approve of the use of this method?  
How do I convince them that this approach is much more streamlined and requires  
far less processing time than doing an update of millions of rows the old  
fashioned way? For my particular need that brought me to this thread, I only  
have to do a one time initialization of a one character column that I want to  
put a not null (with default) contraint on. There are 5.5 million rows in the  
table. I need to do this one-time as an implementation step, yet the DBAs do not  
seem to buy into this approach. They would rather update so many rows a night,  
over the course of several nights, doing frequent commits during the update  
process.

Followup:  
tell the dba's to do the work then?

Sorry -- I just don't know how to make your dba's do something they don't want  
to.

GOTO a page to Bookmark Review | Bottom | Top  
Help Please I don't Understand March 11, 2005  
Reviewer: A Reader from US

Tom:

I have a project due tomorrow that I am getting paid a boat load of money for  
and do not have a clue… Could you write it for me without being compensated? Oh  
and answer my interview questions for me as well.

Just joking ;)

As always you have the best info on your site (dare we say better than meta  
link?).

However I have made it through two threads in just over 9 hours. Have you ever  
thought of hiring a few people and extracting: best part of thread ; lessons  
learned; sample code (I still have not found the latest and greatest 10g  
show\_space); and a glossary for items like “CTAS”, “(sic)”, “SLA”, “FBI”?  
Perhaps make a book out of it and sell it but it would have to be a searchable  
electronic copy. (Hmm, WIKI enable a portion of asktom (naa)?)

In this thread alone I found: q’| quoted strings |’; CTAS for speed; I really  
need to start using hints; I need to look into Analytics; I have to check if  
my DW really needs partitions…

Thanks for everything.  
A Reader.

(Guess response: This is a feature request… Denied :)

Followup:  
I do that -- it is called "a book" :)

Effective Oracle by Design -- best of asktom, with scripts as of the time of the  
book.

Expert One on One Oracle -- what you need to know, with scripts -- second  
edition under construction (in fact I'm going to take April "off" -- from asktom  
\-- in order to just get it going)

Expert One on One Oracle second will be the 10g version of what you are looking  
for.

GOTO a page to Bookmark Review | Bottom | Top  
March 12, 2005  
Reviewer: Alberto Dell'Era from Milan, Italy

> best of asktom, with scripts  
> as of the time of the book.

Yes, but that doesn't mean that the books are just a collection of q&a from  
asktom (as i've found many ones believe); they are "regular" books - the q&a are  
mainly used as "Customer-driven Topic Selection", so to speak...

Followup:  
correct -- definitely not a collection of asktom articles, that would be  
disjointed at best. Rather, a flow -- a story -- with examples from the site to  
help it along. The things I pick to write about are driven very much by the  
questions and trends I see here.

GOTO a page to Bookmark Review | Bottom | Top  
March 13, 2005  
Reviewer: A reader

Hi Tom,  
Regarding...  
"in fact I'm going to take April "off" -- from asktom "

What will happen to desparate people, who are looking for answers supported by  
technical reasons? I am sure that others will be of the same opinion that this  
is one of greatest help ever one can get in Oracle in timely and accurate  
manner...

Followup:  
start here:

[http://www.jlcomp.demon.co.uk/faq/ind\_faq.html][http_www.jlcomp.demon.co.uk_faq_ind_faq.html]

and try:

groups.google.com -> comp.databases.oracle.\*

the place where I got hooked on discussing Oracle in the first place way back in  
October of 1994. You might get an answer from Jonathan Lewis, Connor McDonald,  
or a host of others. I still lurk there from time to time.

otn.oracle.com -> discussion forums

metalink.oracle.com -> discussion forums

the oracle-l list

[http://www.freelists.org/archives/oracle-l/][http_www.freelists.org_archives_oracle-l]  
Where Cary Millsap or Steve Adams or Tim Gorman to name but a very few might  
answer your question.

You might even find yourself answering some from time to time!

GOTO a page to Bookmark Review | Bottom | Top  
dblink March 23, 2005  
Reviewer: shiva from india

We are trying to access a stored procedure which is existing in another database  
thro dblink, in that stored procedure one update stat.. is there because of  
that screen gets hanged  
  
Regards,  
Shivachandramouli.R

Followup:  
so little data... 'one update stat' what is that. and what have you done to  
diagnose the issue?

GOTO a page to Bookmark Review | Bottom | Top  
questions in the dependent objects after March 27, 2005  
Reviewer: A reader

Hi Tom,

As u suggested us to create a new table ,then drop the original table and rename

sounds the great method update the table by CTAS, drop original table and then  
rename it to the original table.  
questions all the dependent objects such as packages, views, synonynums... all  
will because invalid. do these need to be recompiled? I know all the grants have  
to be regrants.

Thanks.

Followup:  
dependent objects will take care of themselves next time they are executed.

GOTO a page to Bookmark Review | Bottom | Top  
March 27, 2005  
Reviewer: A reader

Hi Tom,

Sounds the great method to update the table by CTAS, drop original table and  
then  
rename it to the original table.  
Question is all the dependent objects such as packages, views, synonynums... all

will become invalid. Do these need to be recompiled? I know all the grants have  
to be regrants.

Thanks.

GOTO a page to Bookmark Review | Bottom | Top  
March 27, 2005  
Reviewer: A reader

tom,  
you said:

"  
Now, just set job\_queue\_processes > 0 to set the "degree of threads" and in 8i  
and before set job\_queue\_interval to say 60 or so and there you go.  
"

what's the "degree of threads"?

Followup:  
"degree of threads" is number of concurrent job queue processes, how many  
dbms\_job jobs can be running simultaneously.

GOTO a page to Bookmark Review | Bottom | Top  
commits or not April 01, 2005  
Reviewer: A reader

want to insert 30 million records to a dummy table with nologging as you  
adivsed.

insert into /\*+ append parallel ( ) \*/  
select \* from ...

if i have the large rollback segs, it can be complete without doing the commits  
during the insert into (for example comment every one millions inserts), is it  
faster without the commits?

Thanks alot

Followup:  
to commit or not. Everytime you commit, you will wait, wait for log file sync.

Every time you commit, you say "i'm done". That's ok, if you are restartable!

However, with a big parallel direct path insert it'll be all or nothing (no  
point to do N rows, and do the query all over again). Each parallel process  
will utilize it's own rbs and if you have no indexes, a direct path insert isn't  
going to generate MUCH undo at all (it writes above the HWM and doesn't need to  
generate UNDO information)

GOTO a page to Bookmark Review | Bottom | Top  
April 05, 2005  
Reviewer: A reader

GOTO a page to Bookmark Review | Bottom | Top  
Let's Tell the CBO About Our Great Idea! April 11, 2005  
Reviewer: Doug Winage from New Orleans, LA

Given that it seems there are clear cases in which making and loading a new  
table is preferable to updating in place, I wonder if it would be just as well  
to make such an approach available to the CBO as a candidate execution plan.  
I'll grant that one wouldn't want to leave the choice entirely up to the  
optimizer; the safest thing would be to allow enabling and disabling of the plan  
option at the system level with a parameter called, say,  
"ctas\_for\_updates\_enabled." One could also provide a pair of hints, say, /\*+  
USE\_CTAS \*/ and /\*+ NO\_CTAS \*/, to allow one to override the CBO, if need be,  
and expressly demand or prohibit a plan involving recreation of the table.

Followup:  
all really good ideas need to go via support and get filed....

GOTO a page to Bookmark Review | Bottom | Top  
CTAS instead of Deletes April 11, 2005  
Reviewer: GG from Minneapolis, MN

Tom,  
Please, this is a relevant follow up to this thread.  
I have also included a test case.  
Instead of deleting rows from a table with 270 Million records, I have used  
CTAS. The delete criteria is based on a join of two fields from two tables. The  
delete occurs only on one table (ORDERS). The other table is INVOICE.  
If DOC\_NUMBER and ORD\_ITEM values from both tables match, then delete the  
matching records from ORDERS.

Test case:

drop table orders;  
create table orders (  
DOC\_NUMBER number(10) not null  
,S\_ORD\_ITEM number(10) not null  
,PROMOTION varchar2(10) not null);

drop table invoice;  
create table invoice(  
DOC\_NUMBER number(10) not null  
,S\_ORD\_ITEM number(10) not null  
,OTHER\_INFO varchar2(10) not null);

begin  
for i in 1..10 loop  
insert into orders values(i,i+1,'ORDERS');  
end loop;  
commit;  
end;  
/

select \* from orders;

DOC\_NUMBER S\_ORD\_ITEM PROMOTION  
\---------- ---------- ----------  
1 2 ORDERS  
2 3 ORDERS  
3 4 ORDERS  
4 5 ORDERS  
5 6 ORDERS  
6 7 ORDERS  
7 8 ORDERS  
8 9 ORDERS  
9 10 ORDERS  
10 11 ORDERS

begin  
for i in 1..5 loop  
if mod(i,2) = 0 then  
insert into invoice values(i,i+1,'INVOICE');  
end if;  
end loop;  
commit;  
end;  
/

select \* from invoice order by 1;

DOC\_NUMBER S\_ORD\_ITEM PROMOTION  
\---------- ---------- ----------  
2 3 INVOICE  
4 5 INVOICE

The above two rcords should be deleted from ORDERS:

drop TABLE ORDERS\_NEW;  
CREATE TABLE ORDERS\_NEW  
NOLOGGING  
AS  
SELECT /\*+ parallel(ORDERS,4) \*/ \*  
from orders ord  
where NOT EXISTS  
(select null from invoice inv  
where inv.DOC\_NUMBER = ord.DOC\_NUMBER and  
inv.S\_ORD\_ITEM = ord.S\_ORD\_ITEM);

select \* from orders\_new order by 1;

DOC\_NUMBER S\_ORD\_ITEM PROMOTION  
\---------- ---------- ----------  
1 2 ORDERS  
3 4 ORDERS  
5 6 ORDERS  
6 7 ORDERS  
7 8 ORDERS  
8 9 ORDERS  
9 10 ORDERS  
10 11 ORDERS

IS THIS THE CORRECT WAY OF DOING IT? ESPECIALLY THE SQL WITH "NOT EXISTS"?

Followup:  
hints need to use the correlation (alias) names....

I would just use NOT IN but not exists with the CBO will be considered as not in  
conceptually so it is just harder to code, but has the same net effect.

See the concepts guide for details on parallel CTAS.

GOTO a page to Bookmark Review | Bottom | Top  
Update millions of records April 12, 2005  
Reviewer: Marsboy from USA

Hi Tom,  
It helped me so much that my days work is now can be accomplished in  
minutes.

GOTO a page to Bookmark Review | Bottom | Top  
update partition April 14, 2005  
Reviewer: phil

I am sorry for posting aq similar question in another thread. The follow up  
should have been here !

I am getting a little confused with the technique of updated lots of rows in a  
partitioned table.

I have a table partioned by month. To do an update on a column you suggest doing  
a create table as select. This of course will create a new table with the  
updated colum and the data from the table to be updated.

What I do not understand is how to handle the each partition. My new table has  
none, just valid data. How do I now get the new table to the structure of the  
old with all the partitions ?

I am sure the answer is here, I just cannot see it !

regards

Followup:  
to do a huge bulk update of most of the rows, using a create table as select  
could be a way to go. I followed up on the other thread.

GOTO a page to Bookmark Review | Bottom | Top  
Partioned tables April 14, 2005  
Reviewer: Greg from Toronto

a previous poster was asking about partioned tables ..

\[quote\]  
I am getting a little confused with the technique of updated lots of rows in a  
partitioned table.  
\[/quote\]

Feel free to ignore this question if you answered it already in the other  
followup (I wasn't sure which thread you posted it in, and can't see find it ..  
\*sigh\*)

Anyway ... if the table is partitioned, can you not just deal with each  
partition seperately ?

That is: if you have 15 partitions (ie P01 through P15 ...), and you "update"  
only encompased P03 and P04 ... then could you not just "re-create" that  
partition in the same way you "re-create" the table .. then just "stuff" the  
partition(s) back in?

I can't remember all the syntax to pull this little "card-shuffle" off ... but  
I'm pretty sure it can be done? can't it?

Followup:  
sorry, the other thread is

[http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950\_P8\_DISPLAYID:7143624535091][http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_7143624535091]

Yes, if the table is partitioned, you can. (and that is what I was trying to  
describe on the other page, I just didn't have sufficient detail the first time  
around to make it clear)

GOTO a page to Bookmark Review | Bottom | Top  
April 21, 2005  
Reviewer: A reader

"  
declare  
l\_job number;  
begin  
dbms\_job.submit( l\_job, 'execute immediate ''alter index idx1 rebuild'';' );  
commit;  
dbms\_job.submit( l\_job, 'execute immediate ''alter index idx2 rebuild'';' );  
commit;  
end;

Now, just set job\_queue\_processes > 0 to set the "degree of threads" and in 8i  
and before set job\_queue\_interval to say 60 or so and there you go.  
  
"  
will the dbms job only be executed once?

TIA

Followup:  
yes, well, it will execute successfully ONCE.

It might fail over and over and over and over, then run successfully and go  
away.

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table? May 17, 2005  
Reviewer: S.Darshan from Singapore

I have to update one table which has 10 million records. The regular rollback  
segment won't enough to hold this data, In this case, how can we predict, how  
much rollback area will be used by this update.

Initially I thought of your approach "CREATE TABLE new\_table as select <do the  
update "here"> from old\_table;"

But I have to update about 30% of columns. Rest of the 70% columns should be not  
touched. Also this table have lot foreign key Constraints. Is any alternative  
method? Thanks in Advance.

With best regards  
S.Darshan

Followup:  
if I had to update 1/3 of a 10,000,000 row table -- that is the perfect  
candidate for create table as select. foreign keys or not (you can always  
disable, ctas and for the columns you have not updated -- enable novalidate,  
just put them back on)

but, if you cannot do that, disable indexes on columns to be updated and update  
and then rebuild the indexes. to get a grasp on "how much undo", create a copy  
of the first 100,000 rows of data for example, update it and see how much undo  
you generated.

GOTO a page to Bookmark Review | Bottom | Top  
Parallel DML May 18, 2005  
Reviewer: Tamilselvan from USA

I agree with what Tom said.

How ever, in many real life situation CTAS may not be executed as we think. For  
example, if the table has a LONG column, then CTAS will not work.

Consider Parallel DML. I have successfully implemented parallel dml in a large  
DB.

Here is the script:

alter table scott.table\_a parallel 8;  
alter index scott.table\_a\_p1 parallel 8;  
alter table scott.tamil\_temp\_table\_b parallel 8;  
alter index scott.tamil\_temp\_table\_b\_idx parallel 8;

commit;  
alter session enable parallel dml ;  
alter session enable parallel query ;

update /\*+ parallel(a,8) \*/ scott.table\_a a  
set a.pr\_addr\_id = ( select addr\_per\_id  
from scott.tamil\_temp\_table\_b c  
where c.accnt\_id = a.ID)  
where exists ( select null  
from scott.tamil\_temp\_table\_b c  
where c.accnt\_id = a.ID) ;  
commit;  
alter session disable parallel dml ;  
alter session disable parallel query ;  
alter table scott.table\_a noparallel ;  
alter index scott.table\_a\_p1 noparallel ;

Thanks  
Tamil

Followup:  
<quote>  
if the table has a LONG column, then CTAS will not work.  
</quote>

convert long to clob and be done with it.

GOTO a page to Bookmark Review | Bottom | Top  
Ecellent Explanation May 18, 2005  
Reviewer: A reader from Virgina

this is very helpful nice Toma i have question i am facing while using dbstart  
utility where can i ask that question

Followup:  
suggest you don't use dbstart but rather write your own rc.d script to  
startup/shutdown that which you want.

What I do is like this:

[http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950\_P8\_DISPLAYID:1302619965694\#12118047114111][http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_1302619965694_12118047114111]

GOTO a page to Bookmark Review | Bottom | Top  
How to Update millions or records in a table June 08, 2005  
Reviewer: Debasish from New Delhi, India

you told that create tble with update command. but I have a problem that. ( in  
8i)  
One of my Master table is contain following fields ( approx 500000 records)

1. ID varchar2  
2. Pincode Nuimber  
3. Tag char

Another three table is bankmaster1,bankmaster2,bankmaster3 fields are  
1. pincode number  
2. branch varchar2

I have to update tag column in master table according the pincode distribution  
in bankmaster table, like tag E for bank1, tag C form bank2, tag V for bank3.  
when I try it through update command then it works for a small table but not for  
this 500000 record table.

Plese let me know in advance.

Followup:  
I told you to create a table with the update command? Where and when -- I'll  
need to correct that if true, for you cannot create a table with the update  
command.

It works for me for a 50,000,000 row table or a 5 row table. You'll have to  
tell us why it isn't "working" (and hopefully it is not using a single index)

[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> create table t  
2 as  
3 select id, id pincode, 'x' tag  
4 from big\_table.big\_table  
5 where rownum <= 1000000;  
  
Table created.  
  
Elapsed: 00:00:01.36  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]>  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]>  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> create table t1 as select id pincode, 'xxxxxx' branch from t  
where mod(id,3) = 0;  
  
Table created.  
  
Elapsed: 00:00:01.60  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> create table t2 as select id pincode, 'xxxxxx' branch from t  
where mod(id,3) = 1;  
  
Table created.  
  
Elapsed: 00:00:01.54  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> create table t3 as select id pincode, 'xxxxxx' branch from t  
where mod(id,3) = 2;  
  
Table created.  
  
Elapsed: 00:00:01.49  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]>  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> exec dbms\_stats.gather\_table\_stats( user, 'T', method\_opt=>  
'for all indexed columns' );  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.06  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> exec dbms\_stats.gather\_table\_stats( user, 'T1', method\_opt=>  
'for all indexed columns' );  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.13  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> exec dbms\_stats.gather\_table\_stats( user, 'T2', method\_opt=>  
'for all indexed columns' );  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.07  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> exec dbms\_stats.gather\_table\_stats( user, 'T3', method\_opt=>  
'for all indexed columns' );  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.13  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]>  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> set timing on  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> update (select tag from t where pincode in (select pincode  
from t1)) set tag = 'E';  
  
333333 rows updated.  
  
Elapsed: 00:00:06.13  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> update (select tag from t where pincode in (select pincode  
from t2)) set tag = 'C';  
  
333334 rows updated.  
  
Elapsed: 00:00:06.48  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> update (select tag from t where pincode in (select pincode  
from t3)) set tag = 'V';  
  
333333 rows updated.  
  
Elapsed: 00:00:06.40  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]>

Only takes a couple of seconds.

GOTO a page to Bookmark Review | Bottom | Top  
June 10, 2005  
Reviewer: A reader from MI

Tom,  
I have this query in which I'm trying update the old POC\_ID with the new  
POC\_ID. However, it doesn't produce the results I would expect. The table does  
not get updated with the correct POC\_ID. What am I missing here?

begin for c1 in (select w.poc\_id new\_poc\_id, a.poc\_id old\_poc\_id  
from wsar\_poc w, ap32admin.adas\_poc a  
where w.poc\_responsible\_ind = a.poc\_responsible\_ind)  
loop  
update wsar\_archive\_cs  
set poc\_id = c1.new\_poc\_id  
where poc\_id = c1.old\_poc\_id;  
end loop;  
end;

Thanks,  
Randy

Followup:  
need example.

but that looks like it should be at most a SINGLE sql statement, no code  
whatsoever...

GOTO a page to Bookmark Review | Bottom | Top  
June 10, 2005  
Reviewer: A reader

I have a table WSAR\_ARCHIVE\_CS which contains a column POC\_ID. POC\_ID is FK to  
the ADAS\_POC POC\_ID PK. I want to update that column with the new POC\_ID which  
is stored in WSAR\_POC POC\_ID PK.

Thanks,  
Randy

Followup:  
create table .....  
create table ....  
create table ....  
insert some data...  
insert some more data...

followed by "and this is what I need to do..."

GOTO a page to Bookmark Review | Bottom | Top  
June 10, 2005  
Reviewer: A reader

create table wsar\_poc  
(POC\_ID NOT NULL NUMBER,  
POC\_RESPONSIBLE\_IND VARCHAR2(50),  
DIVISION\_ID NOT NULL NUMBER,  
OUSER VARCHAR2(30),  
ACCESS\_ROLE VARCHAR2(50),  
PHONE\_NUMBER VARCHAR2(20),  
EMAIL VARCHAR2(200))

Create table wsar\_archive\_cs  
(FILE\_ID NOT NULL NUMBER,  
ONELINER\_DATE DATE,  
PRODUCT VARCHAR2(50),  
COMPONENT VARCHAR2(400),  
PRIORITY VARCHAR2(50),  
POC\_ID NUMBER,  
SUBJECT VARCHAR2(300),  
ISSUE VARCHAR2(4000))

I have the table adas\_poc which contians the old poc\_id that is in the  
WSAR\_ARCHIVE\_CS table.

desc adas\_poc  
POC\_ID NOT NULL NUMBER,  
POC\_RESPONSIBLE\_IND VARCHAR2(50),  
DIVISION\_ID NOT NULL NUMBER,  
OUSER VARCHAR2(30),  
ACCESS\_ROLE VARCHAR2(50),  
PHONE\_NUMBER VARCHAR2(20),  
EMAIL VARCHAR2(200)

I tried to update using the cursor for loop, but no luck. The column does not  
get updated with the correct ID.

Thanks,  
Randy

Followup:  
oh, so close -- you almost had inserts there with data to show

a) what happened  
b) explain what you expected

That will definitely join wsap\_poc to adas\_poc by poc\_responsible\_ind

it will definitely then update wsar\_archive\_cs

you need to explain why this logic isn't working, it is rather straight forward.  
Demonstrate for us.

GOTO a page to Bookmark Review | Bottom | Top  
RE: Parallel Import June 20, 2005  
Reviewer: Bakunian

Tom,

I created range partitioned table with parallelization enabled, then I use  
import utility to load 5Gb of data into table however I don't see parallel  
processes spawned during load.  
Shouldn't import take advantage of this functionality?  
I am using 9.2 on Solaris 8.

These are instance parameters.

Thank you for your time.

NAME\_COL\_PLUS\_SHOW\_PARAM TYPE VALUE\_COL\_  
\--------------------------------------------- ----------- ----------  
fast\_start\_parallel\_rollback string LOW  
log\_parallelism integer 4  
parallel\_adaptive\_multi\_user boolean TRUE  
parallel\_automatic\_tuning boolean TRUE  
parallel\_execution\_message\_size integer 4096  
parallel\_instance\_group string  
parallel\_max\_servers integer 40  
parallel\_min\_percent integer 0  
parallel\_min\_servers integer 0  
parallel\_server boolean FALSE  
parallel\_server\_instances integer 1  
parallel\_threads\_per\_cpu integer 2  
recovery\_parallelism integer 0

Followup:  
imp is a serial process, always has been - it reads a single DMP file  
sequentially and loads it.

10g with impdb has a parallel capability.

GOTO a page to Bookmark Review | Bottom | Top  
Tome u r great June 30, 2005  
Reviewer: shyam

I have a quick question my statistics are as below i am loading data

datafile Blocks\_Read FS.Phyblkwrt Blocks\_Written Total\_IOs

/oracle/u03/oradata/dev/data\_01.dbf  
26507 5240 31747

/oracle/u03/oradata/dev/data\_09.dbf  
26823 2823 29646  
  
/oracle/u06/oradata/dev/data\_08.dbf  
24978 3704 28682  
/oracle/u04/oradata/dev/data\_10.dbf  
26288 1831 28119  
  
/oracle/u04/oradata/dev/data\_02.dbf  
24053 3634 27687  
  
/oracle/u05/oradata/dev/data\_11.dbf  
26155 1284 27439  
/oracle/u05/oradata/dev/data\_07.dbf  
23125 2865 25990  
  
/oracle/u04/oradata/dev/data\_06.dbf  
23558 1978 25536  
  
/oracle/u06/oradata/dev/data\_25.dbf  
21672 2234 23906  
/oracle/u05/oradata/dev/data\_03.dbf  
21299 2574 23873  
  
/oracle/u03/oradata/dev/data\_05.dbf  
20324 1819 22143  
  
/oracle/u06/oradata/dev/data\_04.dbf  
19673 1746 21419  
  
/oracle/u06/oradata/dev/data\_12.dbf  
19574 1306 20880  
  
/oracle/u04/oradata/dev/data\_14.dbf  
19309 755 20064  
  
/oracle/u03/oradata/dev/data\_13.dbf  
18807 848 19655  
/oracle/u05/oradata/dev/data\_24.dbf  
17656 1316 18972  
  
/oracle/u06/oradata/dev/data\_16.dbf  
16748 1730 18478  
  
/oracle/u05/oradata/dev/data\_15.dbf  
17006 810 17816  
/oracle/u03/oradata/dev/data\_17.dbf  
14810 1350 16160  
  
/oracle/u05/oradata/dev/data\_19.dbf  
13552 1157 14709  
  
/oracle/u03/oradata/dev/data\_21.dbf  
13622 911 14533  
  
/oracle/u05/oradata/dev/data\_23.dbf  
13366 878 14244  
  
/oracle/u04/oradata/dev/data\_18.dbf  
12644 908 13552  
  
/oracle/u04/oradata/dev/data\_22.dbf  
12521 741 13262  
/oracle/u06/oradata/dev/data\_20.dbf  
12137 546 12683

Tom u can observe that total reads is more than total writes as a result the  
performance is decreasing. let me know how should i increse the block written.  
as a result o this my data loading is slow

Followup:  
who is "u" and why is "u" observing things?

how do you know that reads are the cause of performance decreasing. All I see  
are a bunch of numbers.

GOTO a page to Bookmark Review | Bottom | Top  
loading data June 30, 2005  
Reviewer: shyam

this is the quesry i used to generate statis stics

select DF.Name File\_Name,  
FS.Phyblkrd Blocks\_Read,  
FS.Phyblkwrt Blocks\_Written,  
FS.Phyblkrd+FS.Phyblkwrt Total\_IOs  
from V$FILESTAT FS, V$DATAFILE DF  
where DF.File\#=FS.File\#  
order by FS.Phyblkrd+FS.Phyblkwrt desc;

Followup:  
so, you've just shown the number of reads done since the database was started.  
All they are are a bunch of numbers with no meaning.

how do you know read IO is your bottleneck? what made you decide that?

GOTO a page to Bookmark Review | Bottom | Top  
performance while loading data June 30, 2005  
Reviewer: shyam

FS.Phyblkwrt  
datafile = /oracle/u03/oradata/dev/data\_01.dbf  
  
Blocks\_Read = 26507  
Blocks\_Written =5240  
Total\_IOs= 31747

Followup:  
so what? (he says again)

you've shown me that file has been read 26,507 times SINCE STARTUP and written  
to 5,240 time SINCE STARTUP

big deal, so what?

where they all during your load.  
did they take A LONG TIME <<<===

GOTO a page to Bookmark Review | Bottom | Top  
PRECAUTIONS TO TAKE WHEN HUGE INSERTS July 03, 2005  
Reviewer: shyam

tom can u tell me the tips and how to improv the speed with which data is  
loading.

to to improve data load speed. for huge inserts at present i have 73 tables data  
is being loaded it takes for one project 60 mins

Followup:  
depends on your needs and circumstances. To describe them all would be a small  
book.

So, you'd need to be more "specific". How many new rows, how many old rows, is  
there lots of validation going on, is this a reload or a load, have lots of  
un-necessary indexes, etc etc etc

GOTO a page to Bookmark Review | Bottom | Top  
Delete & import 6M records - Data Warehouse July 06, 2005  
Reviewer: A reader

Tom,

We currently have v8.0.6 running data warehouse. We have 6M rows to delete  
everyday & same time import same amount. It currently take us 10hrs to do  
deletion part. Data are import every 5mins. Is there any way we can speed up the  
deletion & improve the import(using oracle imp/exp)

Thanx so much for your help

Followup:  
insufficient data. why no partitioning? maybe a create table as select rows to  
keep and drop old table.

but insufficient data to answer properly.

and really old software.  
and really archaic tools.

a lot has happened in the time between 8.0.6 and 10gR2 - you'd find many more  
options in 2005 than you did in 1997 when 8.0 was current.

GOTO a page to Bookmark Review | Bottom | Top  
slowly my data base August 06, 2005  
Reviewer: hameed

hi tom  
pleas help me  
i have program by oracle 8i& dev 6i  
the problem:  
data base is slowly

GOTO a page to Bookmark Review | Bottom | Top  
One large insert Vs multiple smaller inserts August 14, 2005  
Reviewer: Sami

Dear Tom,

We had this issue in production during huge data migration.

Environment:-  
Oracle 8.1.7.3 Sun 2.6 - 2 Node OPS. 12 CPU on each box.  
The table in question here involved in M-M Oracle replication.

There is no other activity on both "12Million\_Rec\_table" and "1Million\_Rec\_table  
" other than my process.

Process-1  
=========  
exec dbms\_reputil.replication\_off();  
commit;  
insert /\*+ append parallel(t,12) \*/ into 12Million\_Rec\_table t  
select /\*+ parallel \*/ \* from 1Million\_Rec\_table;

Process-2  
=========  
exec dbms\_reputil.replication\_off();  
commit;  
\--for loop with diff predicate value  
loop  
insert /\*+ append parallel(t,12) \*/ into 12Million\_Rec\_table  
select /\*+ parallel \*/ \* from 1Million\_Rec\_table  
where predicate=&1; -- there is an Index on Predicate column  
end loop

12Million\_Rec\_table has only one primary key index. There is no other  
constraints or LOB columns on this table. For some reason it didn't use  
direct-path insert, don't know why.

Process-2 executed N times in a sequence.Basically Process-1 is one large  
transaction compared to Process-2 (which has N number of transactions in a  
loop).

In terms of execution time the Process-1 took longer than Process-2. Process-1  
was running for more than  
2 hrs without completion so we stopped it. Process-2 with smaller chunk of  
records completed very fast.

When Process-1 started the USED\_UBLK value in v$rollstat increased rapidly then  
slowed down gradually. I have seen  
this behaviour many times while running Process-1.

My questions are

1)Why Process-1 is slower than Process-2. \[We didn't have any rollback segment  
issue while running both Process-1 and Process-2\]

2)Is there any performnce issue if the rollback segment goes beyond some size,  
say 200M or something..?

Followup:  
you did not enable parallel DML, to do a parallel direct path insert, you must.

I cannot answer these questions, insufficient data. You could have captured  
information while it was running.

There is a \*small\* chance that what happened was

a) the big insert, because of the primary key index, generated gobs of undo.  
b) this caused more undo to be generated than fit in the buffer cache  
c) so it had to be flushed by dbwr  
d) causing buffer waits by the insert (trying to get more buffers from the cache  
for undo)  
e) and causing IO contention on the disk

The smaller inserts \*MIGHT\* have

a) filled up part of the buffer cache with undo blocks  
b) committed, allowing the undo blocks in the cache to be reused  
c) goto A n times

avoiding the buffer waits and the flushing of the undo to disk.

However, I would not have taken your approach for a small set of one million  
rows. It likely would have been much faster to

a) get rid of the primary key index  
b) set big table nologging if not already  
c) insert /\*+ append \*/ into big\_table select \* from small\_table;  
d) recreate the primary key index in parallel (if even needed, just 12 million  
rows), nologging  
e) put logging attribute back on for the future  
f) backup affected datafiles

GOTO a page to Bookmark Review | Bottom | Top  
did not get rid of PK index because the table is in M-M replication August 14, 2005  
Reviewer: Sami

Dear Tom,

Thanks a lot for your immediate response. You are absolutely  
right about why the "smaller chunk transactions" defeated "one large  
transaction".

<asktom>  
a) get rid of the primary key index  
b) set big table nologging if not already  
c) insert /\*+ append \*/ into big\_table select \* from small\_table;  
d) recreate the primary key index in parallel (if even needed, just 12 million  
rows), nologging  
e) put logging attribute back on for the future  
f) backup affected datafiles  
</asktom>

I agree with you 100%. Even I prefer your approach \[get rid of the primary key  
index,delete,rebuild index\]. However the table in question is involved in  
Master-Master replication.  
We are not sure about getting rid of primary key index for the table which  
involved in replication. That is why we didn't take this approach.

Tom,thanks for your time & effort.

GOTO a page to Bookmark Review | Bottom | Top  
Additional Info August 14, 2005  
Reviewer: Sami

Our large pool is set to 0 size. I am just wondering whether this value had any  
impact on our parallel process poor performance.

NAME TYPE VALUE  
\------------------------------------ ------- ------------------------------  
large\_pool\_size string 0

Followup:  
are you sure it is really zero, if you are using automatic SGA memory  
management:

ops$xp10gr1/tkyte@ORA10GR1> show parameter large

NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
\_\_large\_pool\_size big integer 4M  
large\_pool\_size big integer 0  
ops$xp10gr1/tkyte@ORA10GR1>

I didn't set a large pool, but Oracle has set up a 4m one (that can grow/shrink  
as needed)

GOTO a page to Bookmark Review | Bottom | Top  
Large pool size August 15, 2005  
Reviewer: Sami

Tom, We are still in 8.1.7.3.

Yes, I am sure the large pool size is set to zero on both instances(2 node OPS)

Followup:  
it isn't "impacting it" performance wise, it is using memory from elsewhere if,  
but the large pool would be a better place since this memory for parallel  
execution isn't "LRU" memory like the other stuff in the shared pool -- the  
large pool is designed for memory that is allocated and deallocated, unlike  
shared pool memory which is designed to be allocated and aged out over time.

GOTO a page to Bookmark Review | Bottom | Top  
Undo - With and without index August 15, 2005  
Reviewer: Sami

Session \#1  
=================================================================================

/\*

create table t1 as select \* from dba\_objects;  
create index t1\_idx01 on t1(object\_id);

\*/  
SQL> select distinct sid from v$mystat;

SID  
\----------  
126

SQL> insert into t1 select \* from dba\_objects;

48601 rows created.

=================================================================================

Session \#2  
=================================================================================

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965B9F4 568

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r  
wheres.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965AFFC 2

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965B9F4 636

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965AFFC 2

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

no rows selected

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965B9F4 754

SQL> select taddr,used\_ublk from v$transaction t,v$session s,v$rollstat r where  
s.taddr=t.addr and t.xidusn=r.usn and s.sid=126;

TADDR USED\_UBLK  
\-------- ----------  
1965B9F4 1035

SQL>

=================================================================================

I monitored rollback activities for session\#1(while inserting rows) through  
session\#2.

While the USED\_UBLK is gradually going up for one transaction , I noticed some  
other  
transaction(TADDR=1965AFFC) with USED\_UBLK=2.  
Is it because of Index t1\_idx01?

I didn't see this behavior when I tested the same test case without Index.

Sometimes I noticed "no rows selected" in session\#2. I assume this is the time  
the transaction is  
switching between TADDR 1965AFFC and 1965B9F4?

Please correct me if I am wrong?

Followup:  
ddl commits, there were two transactions there

the create table and the create index.

GOTO a page to Bookmark Review | Bottom | Top  
no ddl here August 15, 2005  
Reviewer: Sami

Tom,

Sorry for the confusion. I shouldn't have put the create table and create index  
statement over there (though it was commented)

create table and create index is outside of this scope. The reason I put those  
because I just wanted to show you that  
1) the table is created from dba\_objects  
2) it has index on object\_id column

I have tested this behavior in both 8i and 10g. The following output and "no  
rows selected" appeared N times (more than 2) WHILE THE INSERT STATEMENT IS  
GOING ON, just for clarity I just truncated the output.

TADDR USED\_UBLK  
\-------- ----------  
1965AFFC 2

Followup:  
then I have no clue what we are in fact looking at there.

I cannot even remotely reproduce - nor is this the way it works.

the used\_ublks would in this case increase, until you commit, then they go away.

GOTO a page to Bookmark Review | Bottom | Top  
1 big INSERT with joins - OR - multiple UPDATES... August 16, 2005  
Reviewer: Sandeep from London, UK

Hi Tom,

I'm trying to archive data from some big tables (5 or 6 of them). Before doing  
that I need to "rollup" an "amount"  
column within each of those big tables (based on  
a "creation date" criteria). The summations of these amount will be grouped by  
date (monthly sum-up) and will  
need to be put into a reporting table.

The reporting table will have columns like  
"Month", "Sum\_Amt\_BigTable1","Sum\_Amt\_BigTable2",  
"Sum\_Amt\_BigTable3" and the like.

Initially there was only one BigTable (say BigTable1), so the archiving script  
was simple.  
Just one - INSERT INTO REPORT\_TABLE... SELECT.. FROM BIGTABLE1.. WHERE (the date  
criteria)  
GROUP BY (the date column).

Now since there are more "BigTables", I have two(?) options:

1. Modify the insert script such that there is JOIN  
between all BigTables, say BigTable1..BigTable6 (eeps!)  
on the date criteria, and just sum-up the required  
columns. For the sake of simplicity, assume that all  
the tables contain at least one record for each month  
(so no need to outer-join!).

2. Use the original INSERT script to create the initial  
records within the REPORT table... then run multiple  
UPDATEs on the REPORT table - one for  
each bigtable, like this (roughly!)-  
UPDATE REPORT\_TABLE  
SET Sum\_Amt\_BigTable2 = (SELECT sum(amountxx)  
FROM BigTable2 WHERE (the date criteria)  
GROUP BY (the date column))  
WHERE REPORT\_TABLE.month = LAST\_DAY(TO\_DATE(TO\_CHAR(  
BigTable2.date\_column,'DD-MON-YYYY'),'DD-MON-YYYY'))  
;

... and so on - for each remaining BigTable..

Which option do you thing will be better in terms of performance? - I suspect  
option2, as I'm a bit  
apprehensive about joining 6 big tables for one big insert. Will be testing this  
out, but wanted  
to have your opinion about the same.

Regards,  
Sandeep

Followup:  
\#1 of course, do as much as you can in a single sql statement.

big hash area size, nice juicy full scans -- ready to rock and roll and have it  
done fast.

GOTO a page to Bookmark Review | Bottom | Top  
big INSERT with joins - OR - multiple UPDATES August 17, 2005  
Reviewer: Sandeep from London, UK

Hi,  
Was thinking you might actually suggest \#2 !!  
Having a big join across 6 tables with multimillion  
records? hmm... not sure.. but will definitely try this  
out.  
Did the \#2 and it works well... will try to do the  
\#1 and get some stats to compare... somehow joins  
between 6 \*huge\* tables does not convince me... but...  
Thanks,  
Sandeep

Followup:  
6 tables, 60 tables -- I don't care, make SQL work for you -- NOT the other way  
around.

databases were BORN TO JOIN, it is what they do

GOTO a page to Bookmark Review | Bottom | Top  
Option \#3? August 17, 2005  
Reviewer: Bob B from Latham, NY

If there was the likelihood of an outer join in the above scenario, it might be  
worth using a union all approach. Something like:

SELECT  
'Big Table 1' NAME,  
SUM( WHATEVER ) WHATEVER\_SUM,  
SUM( WHATEVER2 ) WHATEVER2\_SUM,  
PARAM1,  
PARAM2  
FROM BIG\_TABLE1  
WHERE CONDITION1 = 'whatever'  
GROUP BY PARAM1, PARAM2  
UNION ALL  
SELECT  
'Big Table 2' NAME,  
SUM( WHATEVER ) WHATEVER\_SUM,  
SUM( WHATEVER2 ) WHATEVER\_SUM,  
PARAM1,  
PARAM2  
FROM BIG\_TABLE2  
WHERE CONDITION1 = 'whatever'  
GROUP BY PARAM1, PARAM2  
... etc

Essentially, write the report query for each table, union all them together and  
then summarize the union all query as necessary:

SELECT  
SUM( SUM\_WHATEVER ) SUM\_WHATEVER,  
SUM( SUM\_WHATEVER2 ) SUM\_WHATEVER2,  
PARAM1,  
PARAM2  
FROM (  
QUERY ABOVE  
)  
GROUP BY PARAM1, PARAM2

GOTO a page to Bookmark Review | Bottom | Top  
With more clear test case August 17, 2005  
Reviewer: sami

</asktom>  
then I have no clue what we are in fact looking at there.

I cannot even remotely reproduce - nor is this the way it works.

the used\_ublks would in this case increase, until you commit, then they go away.

</asktom>

Dear Tom,

While the session\#1 is doing DIRECT PATH INSERT on TABLE which has PRIMAR KEY,I  
Monitored SESSION\#2 as below.

While the USED\_UBLK is gradually going up, there are 2 occurances it reported  
different TADDR with USED\_UBLK=2.

I just wanted to know what is the new TADDR.

Session\#1  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

20:37:47 SQL> select distinct sid from v$mystat;

SID  
\----------  
162

20:37:50 SQL> insert /\*+ append \*/ into t1 select \* from dba\_objects;

48603 rows created.

20:38:23 SQL>  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

Session \#2

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
SQL> desc undo\_monitor  
Name Null? Type  
\----------------------------------------- -------- -------------------------

TADDR VARCHAR2(50)  
UNDO\_UBLK NUMBER  
TIME DATE

SQL> truncate table undo\_monitor;

Table truncated.

SQL> set time on  
SQL>  
SQL>  
SQL> declare  
2 i number :=0;  
3 begin  
4 loop  
5 for c0rec in (select taddr,used\_ublk from v$session s,v$transaction t  
6 where s.taddr=t.addr  
7 and s.sid=&session\_to\_be\_monitored)  
8 loop  
9 insert into undo\_monitor values(c0rec.taddr,c0rec.used\_ublk,sysdate);  
10 end loop;  
11 i := i+1;  
12 if i=100000 then  
13 exit;  
14 end if;  
15 end loop;  
16 end;  
17 /  
Enter value for session\_to\_be\_monitored: 162  
old 7: and s.sid=&session\_to\_be\_monitored)  
new 7: and s.sid=162)

PL/SQL procedure successfully completed.

20:39:49 SQL> select distinct time,taddr,undo\_ublk from undo\_monitor order by t  
ime;

TIME TADDR UNDO\_UBLK  
\--------- -------------------------------------------------- ----------  
17-AUG-05 4C64FADC 1  
17-AUG-05 4C64FADC 1  
17-AUG-05 4C64FADC 1  
17-AUG-05 4C64FADC 1  
17-AUG-05 4C6504D4 2  
17-AUG-05 4C64FADC 5  
17-AUG-05 4C64FADC 6  
17-AUG-05 4C64FADC 9  
17-AUG-05 4C64FADC 10  
17-AUG-05 4C64FADC 16  
17-AUG-05 4C6504D4 2  
17-AUG-05 4C64FADC 17  
17-AUG-05 4C64FADC 21  
17-AUG-05 4C64FADC 25  
17-AUG-05 4C64FADC 27  
17-AUG-05 4C6504D4 2  
17-AUG-05 4C64FADC 27  
17-AUG-05 4C64FADC 30  
17-AUG-05 4C64FADC 32  
17-AUG-05 4C64FADC 36  
17-AUG-05 4C64FADC 38  
17-AUG-05 4C64FADC 49  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52  
17-AUG-05 4C64FADC 52

80 rows selected.

20:39:56 SQL>

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

Followup:  
as I said, I cannot even REMOTELY reproduce this at all.

date is only valid down to the second, perhaps -- these rows are scrambled.  
put I in that last column, not sysdate and see.

(select to\_char(time, 'dd-mon-yyyy hh24:mi:ss' ) -- see if your times are not  
all in the same second there.

GOTO a page to Bookmark Review | Bottom | Top  
What if standby database were used? August 18, 2005  
Reviewer: Michel from France

Hi Tom,  
replacing update with CTAS and/or INSERT /\*+ append \*/ INTO ... is especially  
usefull because of NOLOGGING

Now, what to do if we are using stnadby databases and so it is not a good idea  
to use operations with NOLOGGING

Followup:  
CTAS and /\*+ append \*/ are done with logging enabled in ARCHIVELOG mode, unless  
you specify "alter <segment\_name> NOLOGGING" and even then the dba can "force  
logging" at the database level.

LOGGING happens with this stuff!!!

GOTO a page to Bookmark Review | Bottom | Top  
Excellent August 18, 2005  
Reviewer: Peter from Philadelphia, PA USA

Hi Tom,

You are the best of best. I visit your site everyday.

I need your expertise in this regard too.

I have a fact table T  
( order\_id number,  
customer\_id number,  
order\_date date,  
month\_id number,  
prd\_id number,  
prd\_name varchar2(30),  
quantity number,  
zip number,  
flag smallint,  
swiprd\_id number)  
with about 5 million records imported every month.  
NOTE: one product (prd\_name) may have one or more prd\_ids. There are about 200  
prd\_name and 2000 different prd\_id in this table)

Oracle SERVER: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

T was partitioned by month\_id.  
A local index is builded on T(customer\_id, order\_date, prd\_name).

table T was analyzed using  
dbms\_stats.GATHER\_TABLE\_STATS(  
OWNNAME=>'USER',  
TABNAME=>'T',  
ESTIMATE\_PERCENT=>dbms\_stats.auto\_sample\_size,  
METHOD\_OPT => 'for all indexed columns',  
cascade => true,  
degree => 4);

I need to know following statitics informations each month

1) how many continue customers for each prd\_id (not just prd\_name) in this  
month.  
the continue customer is defined as A customer who bought a product (with a  
prd\_id ) in this month  
and also bought same product (with same or different prd\_id) in past 365 days  
and ((previous order\_date+ 30\*previous quantity) >= current order\_date)  
SET flag =1

2) how many restart customers for each prd\_id (not just prd\_name) in this month.

the restart customer is defined as A customer who is not a continue customer  
and bought a product (with a prd\_id ) in this month  
and also bought same product (with same or different prd\_id) in past 365 days  
and ((previous order\_date+ 30\*previous quantity) < current order\_date)  
  
SET flag =2

3) how many switch customers for each prd\_id (not just prd\_name) in this month.  
the restart customer is defined as A customer who is not a continue nor  
restart customer and bought a product (with prd\_id) in this month  
and also bought different product (not just different prd\_id) in past 365  
days.  
and also record the what prd\_id switched from  
SET flag =3, swiprd\_id = previous prd\_id

4) how many new customers for each prd\_id (not prd\_name) in this month.  
the new customer is defined as A customer who bouht a product but never  
bought any product in past 365 days  
SET flag =4

Current solutions: use a procedure to process each order

CREATE OR REPLACE PROCEDURE get\_flag(v\_monthid IN NUMBER)  
AS  
cursor c1 IS  
SELECT distinct prd\_id  
from T  
WHERE month\_id=v\_monthid order by prd\_id;

cursor c2 is  
SELECT customer\_id, order\_date, prd\_name  
FROM T  
WHERE prd\_id=v\_prd\_id and month\_id = v\_monthid  
order by customer\_id, order\_date desc, quantity desc  
FOR UPDATE of flag NOWAITE ;

cursor c3 -- for all orders with same customer and same  
\-- product during lookback  
is SELECT order\_date, quantity  
FROM T  
WHERE customer\_id = v\_customer\_id  
and prd\_name = v\_prd\_name  
AND order\_date BETIEN (v\_order\_date-366)  
AND (v\_order\_date-1)  
order by order\_date desc, quantity desc;

cursor c4 -- for all orders with same customer and  
\-- different product during lookback  
is SELECT prd\_id  
FROM T  
WHERE customer\_id = v\_customer\_id  
and prd\_name <> v\_prd\_name  
AND order\_date BETIEN (v\_order\_date-366)  
AND (v\_order\_date-1)  
order by order\_date desc, quantity desc;

TYPE customer\_array is TABLE of T.customer\_id%type;  
TYPE order\_date\_array is TABLE of T.order\_date%type;  
TYPE prd\_name\_array is TABLE of T.prd\_name%type;  
lv\_customer\_arr customer\_array;  
lv\_order\_date\_arr order\_date\_array;  
lv\_prd\_name\_arr prd\_name\_array;

v\_prd\_id T.prd\_id%type;  
v\_customer\_id T.customer\_id%type;  
v\_order\_date T.order\_date%type;  
v\_order\_date\_tmp T.order\_date%type;  
v\_prd\_name T.prd\_name%type;  
v\_quantity T.quantity%type;  
v\_flag PLS\_INTEGER;  
v\_swiprd\_id T.swiprd\_id%type;

BEGIN  
FOR lv\_prd in c1  
LOOP  
v\_prd\_id :=lv\_prd.prd\_id;  
v\_customer\_id :=0 ;  
OPEN c2;  
LOOP  
FETCH BULK COLLECT INTO lv\_customer\_arr,  
lv\_order\_date\_arr, lv\_prd\_name\_arr  
LIMIT 100;  
FOR i in 1 .. lv\_customer\_arr.count  
LOOP  
IF v\_customer\_id <> lv\_customer\_arr(i)  
v\_customer\_id := lv\_customer\_arr(i);  
v\_order\_date := lv\_order\_date\_arr(i)  
v\_prd\_name := lv\_prd\_name\_arr(i);  
v\_flag := 0;  
v\_swiprd\_id := 0;  
  
OPEN c3;  
FETCH c3 INTO v\_order\_date\_tmp, v\_quantity ;  
IF c3%NOTFOUND  
THEN  
OEPN c4  
FETCH INTO v\_swiprd\_id ;  
IF c4%NOTFOUND  
THEN -- bought another product  
v\_flag :=4;  
ELSE  
v\_flag :=3;  
END IF;  
CLOSE c4;  
ELSE -- bought same product  
IF (v\_order\_date\_tmp + 30\*v\_quantity)  
>=v\_order\_date  
THEN  
v\_flag :=1 ;  
ELSE  
v\_flag :=2;  
END IF;  
END IF;  
CLOSE c3  
\-- UPDATE flag and swiprd\_id in table T  
IF v\_flag =3  
THEN  
UPDATE T  
SET flag = v\_flag,  
swiprd\_id = v\_swiprd\_id  
WHERE CURRENT OF c2;  
ELSE  
UPDATE T  
SET flag = v\_flag  
WHERE CURRENT OF c2;  
END IF;  
END IF;  
END LOOP;  
  
EXIT WHEN c2%notfound;  
END LOOP;  
CLOSE c2;  
END LOOP;

EXCEPTION  
WHEN ... THEN ....;  
WHEN OTHERS THEN  
dbms\_output.put\_line(SubStr('Error '||TO\_CHAR(SQLCODE)||': '||SQLERRM, 1,  
255));  
RAISE;  
END;

After processing I can run some query to get statitics information I need.

This solution is obviously very slow.  
(for each record 1~2 querys invoke, total about 5~7 million querys called)  
It will take about a week to finish the whole process.  
IF I divide the prd\_id into 6 different ranges and run 6 procedures with diffent  
prd\_id ranges at same time.  
It will take about 2 days. But it is still too long.

I did go thru. some threads in your site. you suggested that:

CREATE TABLE new\_table as select <do the update "here"> from old\_table;  
............

but it is very hard to achieve <do the update "here"> here.

My questions are:

1) Is there any better way to achieve this?

2) IF this is a way to be, how to improve the performance.

Thanks,

GOTO a page to Bookmark Review | Bottom | Top  
As you requested August 20, 2005  
Reviewer: sami

Dear Tom,

As you requested I put the sequence number(loop\#).  
I am able to reproduce consistenly in both 8i solaris and 10 windows.

Look at the TADDR difference between  
SEQUENCE TADDR UNDO\_UBLK TIME  
\---------- -------------------- ---------- ------------------  
18714 4C656458 27 20-AUG-05 09:26:51  
18715 4C65695C 2 20-AUG-05 09:26:51

Session\#1  
==========  
SQL> truncate table t1;

Table truncated.

SQL> select index\_name from dba\_indexes where table\_name='T1';

INDEX\_NAME  
\------------------------------  
T1\_PK

SQL> desc t1  
Name Null? Type  
\----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)  
OBJECT\_NAME VARCHAR2(128)  
SUBOBJECT\_NAME VARCHAR2(30)  
OBJECT\_ID NOT NULL NUMBER  
DATA\_OBJECT\_ID NUMBER  
OBJECT\_TYPE VARCHAR2(19)  
CREATED DATE  
LAST\_DDL\_TIME DATE  
TIMESTAMP VARCHAR2(19)  
STATUS VARCHAR2(7)  
TEMPORARY VARCHAR2(1)  
GENERATED VARCHAR2(1)  
SECONDARY VARCHAR2(1)

SQL> select sid from v$mystat where rownum=1;

SID  
\----------  
122

\----\*\*\* STARTED MONITONING THIS SESSION FROM HERE \*\*\*----

SQL> insert /\*+ APPEND \*/ into t1 select \* from dba\_objects;

48526 rows created.

SQL>  
\----\*\*\* STOPPED MONITONING THIS SESSION FROM HERE \*\*\*----  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

Session\#2  
===================  
SQL> drop table undo\_monitor;  
  
Table dropped.  
  
SQL> create global temporary table undo\_monitor(sequence number,taddr  
varchar2(2  
0),undo\_ublk number,time date)  
2 ;  
  
Table created.  
  
SQL> alter session set nls\_date\_format='DD-MON-YY HH24:MI:SS';  
  
Session altered.  
  
SQL> declare  
2 i number :=0;  
3 begin  
4 loop  
5 for c0rec in (select taddr,used\_ublk from v$session s,v$transaction t  
6 where s.taddr=t.addr  
7 and s.sid=&session\_to\_be\_monitored)  
8 loop  
9 insert into undo\_monitor values(i,c0rec.taddr,c0rec.used\_ublk,sysdate);  
10 end loop;  
11 i := i+1;  
12 if i=100000 then  
13 exit;  
14 end if;  
15 end loop;  
16 end;  
17 /  
Enter value for session\_to\_be\_monitored: 122  
old 7: and s.sid=&session\_to\_be\_monitored)  
new 7: and s.sid=122)  
  
PL/SQL procedure successfully completed.  
  
SQL> spool c:/temp/1.log  
SQL> set lines 120 pages 300  
SQL> select min(undo\_ublk),max(undo\_ublk) from undo\_monitor;

MIN(UNDO\_UBLK) MAX(UNDO\_UBLK)  
\-------------- --------------  
1 52

SQL>  
SQL> select \* from undo\_monitor where undo\_ublk >1 and undo\_ublk <52 order by se  
quence;  
SEQUENCE TADDR UNDO\_UBLK TIME  
\---------- -------------------- ---------- ------------------  
\[truncated for display purpose\]

18696 4C656458 27 20-AUG-05 09:26:51  
18697 4C656458 27 20-AUG-05 09:26:51  
18698 4C656458 27 20-AUG-05 09:26:51  
18699 4C656458 27 20-AUG-05 09:26:51  
18700 4C656458 27 20-AUG-05 09:26:51  
18701 4C656458 27 20-AUG-05 09:26:51  
18702 4C656458 27 20-AUG-05 09:26:51  
18703 4C656458 27 20-AUG-05 09:26:51  
18704 4C656458 27 20-AUG-05 09:26:51  
18705 4C656458 27 20-AUG-05 09:26:51  
18706 4C656458 27 20-AUG-05 09:26:51  
18707 4C656458 27 20-AUG-05 09:26:51  
18708 4C656458 27 20-AUG-05 09:26:51  
18709 4C656458 27 20-AUG-05 09:26:51  
18710 4C656458 27 20-AUG-05 09:26:51  
18711 4C656458 27 20-AUG-05 09:26:51  
18712 4C656458 27 20-AUG-05 09:26:51  
18713 4C656458 27 20-AUG-05 09:26:51  
18714 4C656458 27 20-AUG-05 09:26:51  
18715 4C65695C 2 20-AUG-05 09:26:51  
18716 4C65695C 2 20-AUG-05 09:26:51  
18717 4C65695C 2 20-AUG-05 09:26:51  
18718 4C65695C 2 20-AUG-05 09:26:51  
18719 4C65695C 2 20-AUG-05 09:26:51  
18720 4C65695C 2 20-AUG-05 09:26:51  
18721 4C65695C 2 20-AUG-05 09:26:51  
18722 4C65695C 2 20-AUG-05 09:26:51  
18723 4C65695C 2 20-AUG-05 09:26:51  
18724 4C65695C 2 20-AUG-05 09:26:51  
18725 4C65695C 2 20-AUG-05 09:26:51  
18726 4C65695C 2 20-AUG-05 09:26:51  
18727 4C65695C 2 20-AUG-05 09:26:51  
18728 4C65695C 2 20-AUG-05 09:26:51  
18729 4C65695C 2 20-AUG-05 09:26:51  
18730 4C65695C 2 20-AUG-05 09:26:51  
18731 4C65695C 2 20-AUG-05 09:26:51  
18732 4C65695C 2 20-AUG-05 09:26:51  
18733 4C65695C 2 20-AUG-05 09:26:51  
18734 4C65695C 2 20-AUG-05 09:26:51  
18735 4C65695C 2 20-AUG-05 09:26:51  
18736 4C65695C 2 20-AUG-05 09:26:51  
18737 4C65695C 2 20-AUG-05 09:26:51  
18738 4C65695C 2 20-AUG-05 09:26:51  
18739 4C65695C 2 20-AUG-05 09:26:51  
18740 4C65695C 2 20-AUG-05 09:26:51  
18741 4C65695C 2 20-AUG-05 09:26:51  
18742 4C65695C 2 20-AUG-05 09:26:51  
18743 4C65695C 2 20-AUG-05 09:26:51  
18744 4C65695C 2 20-AUG-05 09:26:51  
18745 4C65695C 2 20-AUG-05 09:26:51  
18746 4C656458 38 20-AUG-05 09:26:51  
18747 4C656458 38 20-AUG-05 09:26:51  
18748 4C656458 38 20-AUG-05 09:26:51  
18749 4C656458 38 20-AUG-05 09:26:51  
18750 4C656458 38 20-AUG-05 09:26:51  
18751 4C656458 38 20-AUG-05 09:26:51  
18752 4C656458 38 20-AUG-05 09:26:51  
18753 4C656458 38 20-AUG-05 09:26:51  
18754 4C656458 38 20-AUG-05 09:26:51  
18755 4C656458 38 20-AUG-05 09:26:51  
18756 4C656458 38 20-AUG-05 09:26:51  
18757 4C656458 38 20-AUG-05 09:26:51  
18758 4C656458 38 20-AUG-05 09:26:51  
18759 4C656458 38 20-AUG-05 09:26:51  
18760 4C656458 38 20-AUG-05 09:26:51  
18761 4C656458 38 20-AUG-05 09:26:51  
18762 4C656458 38 20-AUG-05 09:26:51  
18763 4C656458 38 20-AUG-05 09:26:51  
18764 4C656458 38 20-AUG-05 09:26:51  
18765 4C656458 38 20-AUG-05 09:26:51  
18766 4C656458 38 20-AUG-05 09:26:51  
18767 4C656458 38 20-AUG-05 09:26:51  
18768 4C656458 38 20-AUG-05 09:26:51  
18769 4C656458 38 20-AUG-05 09:26:51  
18770 4C656458 38 20-AUG-05 09:26:51  
18771 4C656458 38 20-AUG-05 09:26:51  
18772 4C656458 38 20-AUG-05 09:26:51  
18773 4C656458 38 20-AUG-05 09:26:51  
18774 4C656458 38 20-AUG-05 09:26:51  
18775 4C656458 38 20-AUG-05 09:26:51  
18776 4C656458 38 20-AUG-05 09:26:51  
18777 4C656458 38 20-AUG-05 09:26:51  
18778 4C656458 38 20-AUG-05 09:26:51  
18779 4C656458 38 20-AUG-05 09:26:51  
18780 4C656458 38 20-AUG-05 09:26:51  
18781 4C656458 38 20-AUG-05 09:26:51  
18782 4C656458 38 20-AUG-05 09:26:51  
18783 4C656458 38 20-AUG-05 09:26:51  
18784 4C656458 38 20-AUG-05 09:26:51  
18785 4C656458 38 20-AUG-05 09:26:51  
18786 4C656458 38 20-AUG-05 09:26:51  
18787 4C656458 38 20-AUG-05 09:26:51  
18788 4C656458 38 20-AUG-05 09:26:51  
18789 4C656458 38 20-AUG-05 09:26:51  
18790 4C656458 38 20-AUG-05 09:26:51  
18791 4C656458 38 20-AUG-05 09:26:51  
18792 4C656458 38 20-AUG-05 09:26:51  
18793 4C656458 38 20-AUG-05 09:26:51  
18794 4C656458 38 20-AUG-05 09:26:51  
18795 4C656458 38 20-AUG-05 09:26:51  
18796 4C656458 38 20-AUG-05 09:26:51  
18797 4C656458 38 20-AUG-05 09:26:51  
18798 4C656458 38 20-AUG-05 09:26:51  
18799 4C656458 38 20-AUG-05 09:26:51  
18800 4C656458 38 20-AUG-05 09:26:51  
18801 4C656458 38 20-AUG-05 09:26:51  
18802 4C656458 38 20-AUG-05 09:26:51  
18803 4C656458 38 20-AUG-05 09:26:51  
18804 4C656458 38 20-AUG-05 09:26:51  
18805 4C656458 38 20-AUG-05 09:26:51  
18806 4C656458 38 20-AUG-05 09:26:51  
18807 4C656458 38 20-AUG-05 09:26:51  
18808 4C656458 38 20-AUG-05 09:26:51  
18809 4C656458 38 20-AUG-05 09:26:51  
18810 4C656458 38 20-AUG-05 09:26:51  
18811 4C656458 38 20-AUG-05 09:26:51  
18812 4C656458 38 20-AUG-05 09:26:51  
18813 4C656458 38 20-AUG-05 09:26:51  
18814 4C656458 38 20-AUG-05 09:26:51  
18815 4C656458 38 20-AUG-05 09:26:51  
18816 4C656458 38 20-AUG-05 09:26:51  
18817 4C656458 38 20-AUG-05 09:26:51  
18818 4C656458 38 20-AUG-05 09:26:51  
18819 4C656458 38 20-AUG-05 09:26:51  
18820 4C656458 38 20-AUG-05 09:26:51  
18821 4C656458 38 20-AUG-05 09:26:51  
18822 4C656458 38 20-AUG-05 09:26:51  
18823 4C656458 38 20-AUG-05 09:26:51  
18824 4C656458 38 20-AUG-05 09:26:51  
18825 4C656458 38 20-AUG-05 09:26:51  
18826 4C656458 38 20-AUG-05 09:26:51  
18827 4C656458 38 20-AUG-05 09:26:51  
18828 4C656458 38 20-AUG-05 09:26:51  
18829 4C656458 38 20-AUG-05 09:26:51  
18830 4C656458 38 20-AUG-05 09:26:51  
18831 4C656458 38 20-AUG-05 09:26:51  
18832 4C656458 38 20-AUG-05 09:26:51  
18833 4C656458 38 20-AUG-05 09:26:51  
18834 4C656458 38 20-AUG-05 09:26:51  
18835 4C656458 38 20-AUG-05 09:26:51  
18836 4C656458 38 20-AUG-05 09:26:51  
18837 4C656458 38 20-AUG-05 09:26:51  
18838 4C656458 40 20-AUG-05 09:26:51  
18839 4C656458 40 20-AUG-05 09:26:51  
18840 4C656458 40 20-AUG-05 09:26:51  
18841 4C656458 40 20-AUG-05 09:26:51  
18842 4C656458 40 20-AUG-05 09:26:51  
18843 4C656458 40 20-AUG-05 09:26:51  
18844 4C656458 40 20-AUG-05 09:26:51  
18845 4C656458 40 20-AUG-05 09:26:51  
18846 4C656458 40 20-AUG-05 09:26:51  
18847 4C656458 40 20-AUG-05 09:26:51  
18848 4C656458 40 20-AUG-05 09:26:51  
18849 4C656458 40 20-AUG-05 09:26:51  
18850 4C656458 40 20-AUG-05 09:26:51  
18851 4C656458 40 20-AUG-05 09:26:51  
18852 4C656458 40 20-AUG-05 09:26:51  
18853 4C656458 40 20-AUG-05 09:26:51  
18854 4C656458 40 20-AUG-05 09:26:51  
18855 4C656458 40 20-AUG-05 09:26:51  
18856 4C656458 40 20-AUG-05 09:26:51  
18857 4C656458 40 20-AUG-05 09:26:51  
18858 4C656458 40 20-AUG-05 09:26:51  
18859 4C656458 40 20-AUG-05 09:26:51  
18860 4C656458 40 20-AUG-05 09:26:51  
18861 4C656458 40 20-AUG-05 09:26:51  
18862 4C656458 40 20-AUG-05 09:26:51  
18863 4C656458 40 20-AUG-05 09:26:51  
18864 4C656458 40 20-AUG-05 09:26:51  
18865 4C656458 40 20-AUG-05 09:26:51  
18866 4C656458 45 20-AUG-05 09:26:51  
18867 4C656458 45 20-AUG-05 09:26:51  
18868 4C656458 45 20-AUG-05 09:26:51  
18869 4C656458 45 20-AUG-05 09:26:51  
18870 4C656458 45 20-AUG-05 09:26:51  
18871 4C656458 45 20-AUG-05 09:26:51  
18872 4C656458 45 20-AUG-05 09:26:51  
18873 4C656458 45 20-AUG-05 09:26:51  
18874 4C656458 45 20-AUG-05 09:26:51  
18875 4C656458 45 20-AUG-05 09:26:51  
18876 4C656458 45 20-AUG-05 09:26:51  
18877 4C656458 45 20-AUG-05 09:26:51  
18878 4C656458 45 20-AUG-05 09:26:51  
18879 4C656458 46 20-AUG-05 09:26:51  
18880 4C656458 51 20-AUG-05 09:26:51  
18881 4C656458 51 20-AUG-05 09:26:51  
18882 4C656458 51 20-AUG-05 09:26:51  
18883 4C656458 51 20-AUG-05 09:26:51  
18884 4C656458 51 20-AUG-05 09:26:51  
18885 4C656458 51 20-AUG-05 09:26:51  
18886 4C656458 51 20-AUG-05 09:26:51  
18887 4C656458 51 20-AUG-05 09:26:51  
18888 4C656458 51 20-AUG-05 09:26:51  
18889 4C656458 51 20-AUG-05 09:26:51  
18890 4C656458 51 20-AUG-05 09:26:51

Followup:  
Recursive SQL.

turn on sql trace and see if you are not doing SQL behind your SQL (space  
management, quotas, etc)....

That was likely a recursive sql transaction to update the dictionary.

GOTO a page to Bookmark Review | Bottom | Top  
Which is the best way to update? August 22, 2005  
Reviewer: A Reader from India

Hi Tom,

We have a fact table with few dimension tables. Initially the records are  
inserted into the fact table. This way most of the columns in the table will be  
populated with values. For the remaining columns we have to update the table by  
joining with other tables.  
1) What will be the best way to update? We are currently using correlated  
queries to update, but there is no index on the column being joined in the fact  
table. The source table has index if its a key column.

2)Should we create index on the joining columns in the fact table? If there are  
many such columns, is it a good idea to create index on each of them before  
updating? Will this improve performance?

3)We are currently analyzing the fact table before update. Is this the right way  
though we are not creating index on all required columns before update?

4) Should index be created on table before update and the table be analyzed?

Your inputs will be highly appreciated and very helpful.

Thanks in advance.

Followup:  
I like to update joins

update ( select t1.a, t2.b from t1, t2 where t1.fk = t2.pk )  
set a = b;

t2 would need to have a primary key in place for this.

GOTO a page to Bookmark Review | Bottom | Top  
Which is the best way to update? August 24, 2005  
Reviewer: A Reviewer from India

Tom can you please answer the queries posted in the immediately previous post?

Followup:  
I did, update the join by primary key. That was my answer. Use the CBO.

GOTO a page to Bookmark Review | Bottom | Top  
October 02, 2005  
Reviewer: Allan Sun from IL, USA

I found your answer is very helpful and wonder how I can submit my question to  
you.  
Thanks

Followup:  
when taking them, there is a button on the home page.

GOTO a page to Bookmark Review | Bottom | Top  
Calculate redolog generation October 04, 2005  
Reviewer: A reader

Tom,  
Is there any way to calculate the amount of redolog that is going to be  
generated when some 2 million records are updated?  
I have to update 2 million records (I can't recreate a dummy table etc etc). I  
need to know how much redolog will be generated and OS space required for the  
undo datafile

Followup:  
not really, not without simulating at least a portion of it on some system and  
measuring the amount of redo.

Table/column widths, row chaining/migration, indexes involved - they'll all play  
a role.

I frequently do 1,000,000 to 10,000,000 records on my laptop - I would think you  
can test this out on a test system someplace?

GOTO a page to Bookmark Review | Bottom | Top  
it's very good December 07, 2005  
Reviewer: gopal

GOTO a page to Bookmark Review | Bottom | Top  
Delete, then Insert or Update? January 06, 2006  
Reviewer: A reader

Tom,  
Quick Question. while trying to modify a row in a table from a procedure, is  
it better\[Performance wise\] to delete existing one and then insert with new  
values or update the existing values with new values,WHY?

Thanks,

Followup:  
update is less work.

you typically update a subset of the columns.

only the columns that change values and have indexes on them will affect the  
indexes.

doing a delete+insert would take more resources, hit all indexes.

GOTO a page to Bookmark Review | Bottom | Top  
Large update due to consolidation January 25, 2006  
Reviewer: David Piazza from San Mateo, CA

We are consolidating five databases where the schemas are the same into one  
database. Currently a test system was created with five schemas that hold the  
previous data from the five databases. It isn't a clean update as all primary  
keys are in the same range, so some number x will have to be added to the  
primary(Psuedo key) and child keys in each schema to avoid dups. After reading  
through this topic, I can see two ways of doing the merge and updating the first  
schema: CTAS incrementing each primary and foreign key by some x value. Then  
either insert /\*+ append or use the merge command to insert/merge the data into  
the first schema. The second way would be to directly insert /\*+ append into  
the first schema from tables in each of the five schemas adding the appropriate  
value to the primary/foreign keys. How would you suggest implementing this?

Followup:  
I would try to load the "fixed" data.

Eg: if the keys that are in need of fixing are all numbers - I might multiply  
each key by 10 and add 1, 2, 3, 4, or 5 to each during the load.

GOTO a page to Bookmark Review | Bottom | Top  
mass insert code package February 13, 2006  
Reviewer: Brett Clark from Des Moines, IA USA

For what its worth, here is a package that can be used as a generic foundation  
for mass loads. It will disable triggers and indexes and (optionally) truncate  
the main table before running the insert statement that you provide. Hope this  
helps someone.

create or replace package table\_support is

TRUNCATE\_NO constant boolean := FALSE;  
TRUNCATE\_YES constant boolean := TRUE;  
  
procedure load\_table(G\_TABLE\_NAME Varchar2, G\_INSERT\_STMT Varchar2,  
TRUNC\_ACTION Boolean DEFAULT TRUNCATE\_NO );

end table\_support;  
/  
create or replace package body table\_support is

ACTION\_ENABLE constant boolean := TRUE;  
ACTION\_DISABLE constant boolean := FALSE;

procedure exec( inStmt Varchar2 ) is  
begin  
EXECUTE IMMEDIATE inStmt;  
end exec;

function exec( inStmt Varchar2 ) return Number is  
begin  
exec( inStmt );  
return( SQL%ROWCOUNT );  
end exec;

procedure tbl\_triggers(G\_TABLE\_NAME Varchar2, action Boolean) is  
begin  
if( action = ACTION\_ENABLE ) then  
exec( 'ALTER TABLE ' || G\_TABLE\_NAME || ' ENABLE ALL TRIGGERS' );  
elsif ( action = ACTION\_DISABLE ) then  
exec( 'ALTER TABLE ' || G\_TABLE\_NAME || ' DISABLE ALL TRIGGERS' );  
end if;  
end tbl\_triggers;

procedure tbl\_indexes( G\_TABLE\_NAME Varchar2, action Boolean ) is  
begin  
FOR C IN (SELECT INDEX\_NAME  
FROM USER\_INDEXES  
WHERE TABLE\_NAME = G\_TABLE\_NAME) LOOP  
if( action = ACTION\_DISABLE ) then  
exec( 'alter index ' || C.INDEX\_NAME || ' unusable' );  
elsif( action = ACTION\_ENABLE ) then  
exec( 'alter index ' || C.INDEX\_NAME || ' rebuild parallel nologging'  
);  
end if;  
END LOOP;

if( action = ACTION\_ENABLE ) then  
exec( 'alter session set skip\_unusable\_indexes=false' );  
elsif ( action = ACTION\_DISABLE ) then  
exec( 'alter session set skip\_unusable\_indexes=true' );  
end if;  
end tbl\_indexes;

procedure load\_table(G\_TABLE\_NAME Varchar2, G\_INSERT\_STMT Varchar2, TRUNC\_ACTION  
Boolean DEFAULT TRUNCATE\_NO ) is  
rowCount Number;  
begin  
\-- DISABLE ALL TRIGGERS  
tbl\_triggers( G\_TABLE\_NAME, ACTION\_DISABLE );  
  
\-- TRUNCATE TABLE  
if( TRUNC\_ACTION = TRUNCATE\_YES ) then  
exec( 'truncate table ' || G\_TABLE\_NAME );  
end if;  
  
\-- DISABLE INDEXES  
tbl\_indexes( G\_TABLE\_NAME, ACTION\_DISABLE );

\-- ENABLE PARALLEL DML  
exec( 'alter session enable parallel dml' );

\-- EXECUTE INSERT STATEMENT  
rowCount := exec( G\_INSERT\_STMT );  
  
DBMS\_OUTPUT.PUT\_LINE('Inserted ' || rowCount || ' records into ' ||  
G\_TABLE\_NAME);

\-- ENABLE/REBUILD INDEXES  
tbl\_indexes( G\_TABLE\_NAME, ACTION\_ENABLE );

\-- ENABLE TRIGGERS  
tbl\_triggers( G\_TABLE\_NAME, ACTION\_ENABLE );

\-- TURN OFF PARALLEL DML  
exec( 'alter session disable parallel dml' );

end load\_table;

begin  
\-- Initialization  
NULL;  
end table\_support;  
/

GOTO a page to Bookmark Review | Bottom | Top  
mass update February 17, 2006  
Reviewer: Hans from Canada

I have a table with 50 columns and 10 million records and I have a file with 10  
million rows and I have to update 5 columns in the table from the file( the  
update is performed by getting the common ID value).  
Steps I am performing  
1.open the file using utl\_file  
2. read the record  
3. perform the update in the table  
The process is very slow.  
I will appreciate better ideas from the group

Followup:  
external table (so you can query the flat file), or sqlldr it into a temporary  
table.

and then merge.

If you are in 9i, you'll have to:

merge into db\_table  
using (select \* from external\_table where id in (select id from db\_table))  
on (db\_table.id = external\_table.id)  
when matched then update ...  
when not matched then insert (id) values (null); <<<=== never happens

in 10g, you can just

merge into db\_table  
using external\_table  
on (db\_table.id = external\_table.id)  
when matched then update ...;

GOTO a page to Bookmark Review | Bottom | Top  
February 18, 2006  
Reviewer: A reader

GOTO a page to Bookmark Review | Bottom | Top  
Morning TOM. February 19, 2006  
Reviewer: sudip from BGALORE, india

What is archieve log mode? why we prefer recovery in Archieve log mode? Please,  
will u brief it?

Followup:  
"U" is not here  
I have never met "U"

If you know how to contact them - I'd really appreciate a pointer on doing so.  
They get a lot of requests.

If you don't mind, I'll give you a pointer to what archivelog mode is (until we  
can find this "U" person)

[http://download-east.oracle.com/docs/cd/B19306\_01/backup.102/b14192/strategy003.htm\#sthref127][http_download-east.oracle.com_docs_cd_B19306_01_backup.102_b14192_strategy003.htm_sthref127]

GOTO a page to Bookmark Review | Bottom | Top  
February 19, 2006  
Reviewer: A reader

To sudip from BGALORE, india

Looks like you are new to this site just put one thing in your mind Tom really  
ticks off for the "U" so dont use it next time :-)

Followup:  
Hey, the page he used to post that actually said "if you do this, I will do what  
I just did..."

Which is to poke fun at at :)

It amazes more than ticks me off.

Just like in some parts of the world, an "English Accent" adds 10 IQ points  
(when I hear a British accent, for whatever reason - I add default IQ points,  
maybe it was all of the James Bond movies when I was a kid) - using this instant  
message speak takes them away.

GOTO a page to Bookmark Review | Bottom | Top  
Large Update Due To Consolidation February 21, 2006  
Reviewer: David Piazza from San Mateo, CA

Tom,

Regarding my previous question. They've decided to do merge one of the five  
clusters at a time. I've got scripts to implement the first cluster as such:

CTAS NOLOGGING setting the keys to old\_key\*10+1  
insert /\*+ append \*/  
DROP old table  
RENAME new table  
GRANTs  
ALTER TABLE add Primary Key  
ALTER TABLE add Foreign Key  
CREATE INDEX NOLOGGING parallel(degree 4)  
ALTER TABLE ADD CONSTRAINT UNIQUE ENABLE  
ALTER TABLE Logging

This seems pretty fast. Anything you see that will make it faster?

When I merge in the next clusters, should I:  
\- Do the same steps as above(\*10+2,\*10+3 etc.)  
\- Disable primary keys, make indexes unusable and  
Insert /\*+ append, and then enable PK's, and indexes  
\- Drop primary keys, indexes, and constraints, and  
INSERT /\*+ append, create PK's, Indexes, and constraints

The number of rows in the tables(Approximately the same in each cluster) are:  
72338677,32278464,25347185,23834984,7595305,4519904,4371347,1229877,1168373,66697  
52,6382030,97250,4694,934,166

\-Thanks

Followup:  
why not all of them at once to avoid having to do the disable/enable over and  
over.

GOTO a page to Bookmark Review | Bottom | Top  
Large Update Due To Consolidation February 22, 2006  
Reviewer: David Piazza from San Mateo, CA

That was my suggestion, but the customer wants to do one cluster at a time,  
especially the first one, ensuring that it works as before and all the users are  
happy before merging another cluster in...

Followup:  
then you got it.

You don't really need to disable all contraints and if you could get it done in  
two bits - that would be the least work (load cluster 1, then 2, 3, 4 and 5  
after testing 1)

GOTO a page to Bookmark Review | Bottom | Top  
Large Update Due To Consolidation February 23, 2006  
Reviewer: David Piazza from San Mateo, CA

Ok. Which of the three methods would you suggest to merge in the remaining  
clusters, or would you recommend something else?

1) Do the same steps as above, CTAS/DROP old table/Rename  
new table to old table/Insert /\*+ append(\*10+3,\*10+4  
etc.)/Create constraints,indexes.  
OR  
2) Disable primary keys, make indexes unusable and  
Insert /\*+ append, and then enable PK's, and indexes  
OR  
3) Drop primary keys, indexes, and constraints, and  
INSERT /\*+ append, create PK's, Indexes, and constraints  
OR  
4) Something else

I'm leaning towards 2) or 3)

\-Thanks

Followup:  
I would just be disabling indexes (if I wanted to - insert append does a rather  
efficient "merge" of data) and insert appending the other data. I would not  
redo the CTAS over.

GOTO a page to Bookmark Review | Bottom | Top  
Bulk Insert / Update in sigle SQL February 24, 2006  
Reviewer: Narendra from India

Tom,

I have a target table that stores current as well as history records. Current  
records are identified by a field CURRENT\_REC having value of 'Y'. History  
records have value of CURRENT\_REC as 'N'. At any stage, for a PART\_NO, there can  
be only SINGLE record with CURRENT\_REC as 'Y' in target table.  
My problem is I want to transfer data (in bulk) from source table to target  
table with CURRENT\_REC flag as 'Y' and simultaneously update existing records  
having CURRENT\_REC as 'Y'. Following is table definition:  
Source table:  
CREATE TABLE PART\_SRC  
(  
PART\_NO NUMBER,  
PART\_NAME VARCHAR2(100),  
BALANCE\_QTY NUMBER(5),  
CREATED\_DATE DATE,  
CURRENT\_REC VARCHAR2(1)  
)  
target Table:  
CREATE TABLE PART\_TABLE  
(  
PART\_NO NUMBER,  
PART\_NAME VARCHAR2(100),  
BALANCE\_QTY NUMBER(5),  
CREATED\_DATE DATE,  
CURRENT\_REC VARCHAR2(1)  
)

Is this possible in a single SQL ?

Followup:  
I don't get it.

I want to transfer data (in bulk) from source table to target table with  
current\_rec flag as 'Y'

I understand that.

the "simultaneously update existing records have a current-rec as 'Y'"

which existing records, in which table, and update what exactly (since we are  
already looking only at 'Y' records - what exactly are we updating??!?!)

GOTO a page to Bookmark Review | Bottom | Top  
Bulk Insert / Update in sigle SQL February 24, 2006  
Reviewer: Michel Cadot from France

If i clearly understand you, you want something like (inspired by "merge delete"  
or "insert all" statements):

merge into part\_table t  
using part\_src s  
on (t.part\_no = s.part\_no and t.current\_rec='Y')  
when matched then  
update t.current\_rec = 'N'  
insert (t.part\_no, ...) values (s.part\_no, ...)  
when not matched then  
insert (t.part\_no, ...) values (s.part\_no, ...)  
/

merge ALL into part\_table t  
using part\_src s  
on (t.part\_no = s.part\_no and t.current\_rec='Y')  
when matched then update t.current\_rec = 'N'  
when TRUE then  
insert (t.part\_no, ...) values (s.part\_no, ...)  
/

But this does not exist. ;)

Michel

GOTO a page to Bookmark Review | Bottom | Top  
More details February 24, 2006  
Reviewer: Narendra from India

Tom,

One correction to table definitions.  
Source table does not contain CURRENT\_REC field.  
What I am trying to achieve is transfer data from source to target. Each time  
data is appended to target table. I may transfer updated details of PART\_NO  
multiple times from source to target. However, I do not want data in target  
table to be overwritten. In order to identify the "last loaded record", I am  
maintaining CURRENT\_REC field in target table. If a PART\_NO (from source table)  
does not exist in target table, it should simply add that PART\_NO to target  
table with value of CURRENT\_REC = 'Y'. However, if PART\_NO already exists i.e.  
data has been transferred earlier from source to target for that PART\_NO, target  
table should already have a single record with CURRENT\_REC as 'Y' and may have  
multiple records with CURRENT\_REC as 'N' for same PART\_NO. Now, when I transfer  
data for same PART\_NO again from source to target, it should be added with  
CURRENT\_REC as 'Y'. However, at the same time, existing record for same PART\_NO,  
having CURRENT\_REC as 'Y' should be updated with 'N'.  
Hope I am clear.

Michel,  
MERGE will not help since CURRENT\_REC will not be there in my source table.

Thanks

GOTO a page to Bookmark Review | Bottom | Top  
More details February 25, 2006  
Reviewer: Michel Cadot from France

Narendra,

I understood this and the pseudo-merges don't use the current\_rec of source (s)  
but only of target (t). It would be clearer if I wrote "insert (t.part\_no, ...,  
t.current\_rec) values (s.part\_no, ..., 'YES')".

There is no way to do it in a single SQL statement but it is easy to do it in an  
efficient PL/SQL block.

Regards  
Michel

GOTO a page to Bookmark Review | Bottom | Top  
Merging ten large tables March 17, 2006  
Reviewer: Jesse Buzzard from VA - USA

Good Evening,

This is my first foray into very large databases and I need some advice.

We have ten tables that contain blob data. The total size of the tables is  
approximately 1.8TB. They are stored in the same table space. How this came to  
be is a long story for another discussion thread. Our task is merge the tables  
into one table. After selecting the table with the largest number of rows as  
the target we came up with the below code to insert the data from another table  
into it.  
\-------- start of code -------  
\#: Announce start insert

echo "-------------------------------------------------------------"  
echo "\`date '+ %a %e-%b-%Y %X'\` - $0 - started" >> $1  
echo "-------------------------------------------------------------"

$ORACLE\_HOME/bin/sqlplus /nolog << EOF  
connect odirms/odirms

insert /\* +APPEND PARALLEL (nto, 8) \*/ into odirms.t\_ompf4 nto  
select /\* +APPEND PARALLEL (tompf1, 8) \*/ \* from t\_ompf1 tompf1;  
commit;  
exit

EOF

\#: End of program  
echo "\`date '+ %a %e-%b-%Y %X'\` - $0 - completed" >> $1  
echo "-------------------------------------------------------------"  
echo  
\------------ end of code ----------

At present we are trying to merge 4 of the tables. All logging and indexes are  
off. Multi threading is on. From all I have read we should have completed this  
in a relatively short amount of time. This job has been running for over 28  
hours. I can see the space used is growing (about 300MB /minute), but my  
supervisors would like to know when we will have all the tables merged.  
Where have gone astray?

Thank You,  
Jesse

Followup:  
well, you said "all indexes are off" and "we have blobs" - the two concepts do  
not go together. every blob has an index associated with it that cannot  
possibly be disabled for any reason (short of dropping the blob column!)

So, you do having logging going on, you do have index maintenance going on.

but you don't give any information as to the size of the tables being read (and  
hence written into the new table).

I don't think anything has "gone astray" here, I can say that your "hint" isn't  
a hint, it is just text you typed in:

[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]> create table t as select \* from dual;

Table created.

[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]>  
[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]> insert /\* +APPEND \*/ into t select \* from dual;

1 row created.

[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]> select \* from t;

D  
\-  
X  
X

[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]> insert /\*+ APPEND \*/ into t select \* from dual;

1 row created.

[ops$tkyte@ORA10GR2][ops_tkyte_ORA10GR2]> select \* from t;  
select \* from t  
\*  
ERROR at line 1:  
ORA-12838: cannot read/modify an object after modifying it in parallel

See how the /\* + didn't really do anything, but /\*+ does. If that is your real  
script, you likely did not achieve your goal of either append nor parallel  
(parallel implies append by the way, if it was in fact using parallel)

GOTO a page to Bookmark Review | Bottom | Top  
Merging ten large tables March 20, 2006  
Reviewer: Jesse Buzzard from VA - USA

Thank you for your response.  
If I may I would like to impose on your wisdom again. We are averaging 1800MB  
per hour. Yes, the sample I provied is one of our real scripts, and yes the goal  
was work in parallel. At present five of the tables (56% of the rows) have been  
merged. The total size of the data, is 1.8 TB in 32,518,931 rows. As you can  
see from the description below the amount of data in addition to the blob data  
is rather small. If we have gotten the hint correct would we have achieve a  
better insert/append rate?

Name Null? Type  
\----------------------------------------- -------- -----------

OMPF\_ID NOT NULL NUMBER  
OMPF\_UID NUMBER(10)  
OMPF\_IMG\_TYPE\_ID CHAR(1)  
OMPF\_IMG\_STYPE\_ID CHAR(1)  
OMPF\_PAGE\_NUM NUMBER(5)  
OMPF\_ACTIVE\_FLAG CHAR(1)  
OMPF\_ADDED\_DT DATE  
OMPF\_INACTIVE\_DT DATE  
OMPF\_IMAGE BLOB  
OMPF\_LOCK\_FLAG NUMBER(5)  
OMPF\_USER\_ID CHAR(8)

Followup:  
you might have, you might not have. you sort of have to figure out where you  
are spending your time (tracing - a level 12 10046 trace on a bit of this would  
go a long way)

GOTO a page to Bookmark Review | Bottom | Top  
Alternative Suggestion March 21, 2006  
Reviewer: VK from Atlanta

Here is an approach based on exchange partitions I used successfully in the  
past for combining tables (This will work only if the data in the individual  
tables is distinct and there is no overlap - A scenario in which this will work  
well is when people in their infinite wisdom create horizontally "partitioned"  
tables. An example may be where all customers with ID 1 to one million are kept  
in CUST1 table and one million to 5 million in CUST2 table and so on..):

4 tables to be combined:

create table test(a number,b blob);  
create table test1(a number,b blob);  
create table test2(a number,b blob);  
create table test3(a number,b blob);

Now, let's say table test has values 1 to 10,000 in column a and test1 has  
10,001 to 20,000, test2 has 20,001 to 30,000 and test3 has 30,001 to 40,000.

Create a partitioned table that looks exactly like the tables being combined -  
pick a partition key where the values do not overlap between tables.

create table testp(a number,b blob)  
partition by range(a)  
(  
partition p1 values less than (10001),  
partition p2 values less than (20001),  
partition p3 values less than (30001),  
partition p4 values less than (maxvalue)  
);

You can combine the 4 tables into the partitioned table using exchange  
partition:

alter table testpart exchange partition p1 with table test;  
alter table testpart exchange partition p1 with table test1;  
alter table testpart exchange partition p3 with table test2;  
alter table testpart exchange partition p4 with table test3;

At this point, you can use the partitioned table or if you don't want the table  
to be partitioned, you can merge all 4 partitions into a single partition and  
exchange with a non-partitioned table.

alter table testpart merge partitions p1,p2 into partition p5;  
alter table testpart merge partitions p5,p3 into partition p6;  
alter table testpart merge partitions p6,p4 into partition p7;

Now we have just one partition (P7) in the table and you can exchange this with  
any of the original tables (we'll use test).

alter table testpart exchange partition p7 with table test;

Now we have all 40,000 rows in table test. As you can see, it is a lot of work  
to combine 4 small tables but if you have tens/hundreds of millions of rows,  
this will be much faster.

GOTO a page to Bookmark Review | Bottom | Top  
Merging ten large tables March 21, 2006  
Reviewer: Jesse Buzzard from VA - USA

Tom,  
VK,

Thank you for your suggestions. They are educational and helpful. The merges  
have nearly completed. Now I am off to read about reorganizing the tablespace  
to reclaim some of the space formerluy used by the smaller tables.

GOTO a page to Bookmark Review | Bottom | Top  
Merging ten large tables March 21, 2006  
Reviewer: Jesse Buzzard from VA - USA

Tom,  
VK,

GOTO a page to Bookmark Review | Bottom | Top  
Very slow insert into table April 17, 2006  
Reviewer: bayaraat from Mongolia

Hi Tom

I need your expertise in this regard.  
My table is  
create table CDR00  
(  
CALLDATETIME DATE,  
TELNO VARCHAR2(20),  
CALLEDNO VARCHAR2(20),  
SECONDS NUMBER,  
PULSE NUMBER,  
PREFIXCODEA CHAR(14),  
PREFIXCODEB CHAR(14),  
RATECODE CHAR(28),  
TOG\_AMOUNT NUMBER(20,2),  
USD\_AMOUNT NUMBER(20,8),  
NO\_TOG\_AMOUNT NUMBER(20,8),  
NO\_USD\_AMOUNT NUMBER(20,2),  
TOG\_TAX NUMBER(20,8),  
USD\_TAX NUMBER(20,8),  
FNAME VARCHAR2(12),  
OPERATOR VARCHAR2(10),  
RESERVED VARCHAR2(10),  
PRINTED NUMBER(1)  
)  
tablespace CDR01  
pctfree 20  
pctused 80  
initrans 1  
maxtrans 255  
storage  
(  
initial 1M  
minextents 1  
maxextents unlimited  
);  
Every day insert into this table 2 million records and truncate table. This  
contains million or records insert into partition table.  
Insert process is very slow, respond times 12:00:00.  
I want to quickly insert many records. I using RAID 5.  
Not use constraint and trigger. Non stop archive log process. Because Oracle  
database server is Standby database server. Any suggestions please...

Followup:  
if done correctly, it would only take minutes or less to bulk load 2 million  
records.

So, you need to describe HOW you are loading this.

and if you are loading and truncating, doubtful that you are updating - so  
pctfree=0 would be more appropriate -and perhaps even "compress", but it depends  
on how you load after all - which we don't know.

[ops_tkyte_ORA817DEV.US.ORACLE.COM]: mailto:ops$tkyte@ORA817DEV.US.ORACLE.COM
[ops_tkyte_ORA920]: mailto:ops$tkyte@ORA920
[hrdmadm.ext_xpayd_pay_details_hip2]: mailto:hrdmadm.ext_xpayd_pay_details@hip2
[http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96524_c21dlins.htm_10629]: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm#10629
[big_table_ORA920]: mailto:big_table@ORA920
[ops_tkyte_ORA920LAP]: mailto:ops$tkyte@ORA920LAP
[Link 1]: mailto:'%20rel=
[http_asktom.oracle.com_tkyte_article1_index.html]: http://asktom.oracle.com/~tkyte/article1/index.html
[http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96531_ch2_9ir2.htm_74432]: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96531/ch2_9ir2.htm#74432
[ops_tkyte_ORA9IR2]: mailto:ops$tkyte@ORA9IR2
[http_download-east.oracle.com_docs_cd_A87861_01_NT817EE_index.htm]: http://download-east.oracle.com/docs/cd/A87861_01/NT817EE/index.htm
[http_download-west.oracle.com_docs_pdf_A42562_1.pdf]: http://download-west.oracle.com/docs/pdf/A42562_1.pdf
[http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_1533006062995]: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1533006062995
[http_asktom.oracle.com_pls_ask_f_p_4950_1]: http://asktom.oracle.com/pls/ask/f?p=4950:1
[http_the-big-o.port5.com_index.shtml]: http://the-big-o.port5.com/index.shtml
[hfreeman_msn.com]: mailto:hfreeman@msn.com
[remote_table_dblink]: mailto:remote_table@dblink
[http_download-west.oracle.com_docs_cd_B10501_01_server.920_a96530_migrep.htm_620714]: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96530/migrep.htm#620714
[big_table_ORA9IR2]: mailto:big_table@ORA9IR2
[http_www.jlcomp.demon.co.uk_faq_ind_faq.html]: http://www.jlcomp.demon.co.uk/faq/ind_faq.html
[http_www.freelists.org_archives_oracle-l]: http://www.freelists.org/archives/oracle-l/
[http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_7143624535091]: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7143624535091
[http_asktom.oracle.com_pls_ask_f_p_4950_8_F4950_P8_DISPLAYID_1302619965694_12118047114111]: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1302619965694#12118047114111
[ops_tkyte_ORA10G]: mailto:ops$tkyte@ORA10G
[http_download-east.oracle.com_docs_cd_B19306_01_backup.102_b14192_strategy003.htm_sthref127]: http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/strategy003.htm#sthref127
[ops_tkyte_ORA10GR2]: mailto:ops$tkyte@ORA10GR2