Home » RDBMS Server » Performance Tuning » using sequences in dml
using sequences in dml [message #322712] Mon, 26 May 2008 00:50 Go to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
Hi all,

i have a (staging)table say st_prod_tab having 3 columns testid prod_number price


i am using a sequence to autoincrement the "testid" in the sql loader control file and prod_number,price are loaded into the st_prod_tab from a flat file.

I am inturn doing some comparison and iserting records from the staging table to the operation table(Op_prod_tab).

Say i load 100 records from flat file into the staging table (this 100 records are later inserted into the operational table) today.

Now tomorrow i am loading say 300 records into the staging table and 500records day after and so on...

now how do i insert into the main table comparing data in the staging table starting from the last +1 sequence id?

insert into Op_prod_tab (
select prod_number,price
from st_pro_table s
where not exists (select 1
from op_prod_table o
where o.prod_number=s.prod_number
and (here i need to use the last+1 sequence id number so that the comparison is faster)

Regards,
Gyan
Re: using sequences in dml [message #322725 is a reply to message #322712] Mon, 26 May 2008 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A "sequence" you are mentioning is ... what? Oracle object named "sequence"? You know, the one you create in SQL*Plus using the CREATE SEQUENCE command? If so, why don't you just use it "as is" - tomorrow it will continue from where it stopped today (however, you may expect gaps, but that shouldn't be a problem).

If you insist on recreating the sequence, find the last sequence number from a table and create a new sequence using the START WITH option.
Re: using sequences in dml [message #323169 is a reply to message #322712] Tue, 27 May 2008 09:25 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try modifying your insert to include a scalar subquery to go get the max id off your final table add add it to the id value from your loader table. If this is confusing, do some googling for:

Quote:
SCALAR SUBQUERY ORACLE
SUBQUERY FACTORING ORACLE
MAX/MIN OF INDEX ORACLE


These are the special concepts you will want to know about with crafting your solution in order to make sure it goes fast.

Kevin
Previous Topic: Network Load
Next Topic: SQL STATEMENT TUNING
Goto Forum:
  


Current Time: Mon Dec 05 21:36:48 CST 2016

Total time taken to generate the page: 0.11733 seconds