OraFAQ Forum: Performance Tuning » using sequences in dml

Home » RDBMS Server » Performance Tuning » using sequences in dml

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using sequences in dml [message #322712]

Mon, 26 May 2008 00:50

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

Report message to a moderator

Re: using sequences in dml [message #322725 is a reply to message #322712]

Mon, 26 May 2008 01:45

Littlefoot
Messages: 21811
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.

Report message to a moderator

Re: using sequences in dml [message #323169 is a reply to message #322712]

Tue, 27 May 2008 09:25

Kevin Meade
Messages: 2103
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

Report message to a moderator

Previous Topic:	Network Load
Next Topic:	SQL STATEMENT TUNING

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:02:31 CDT 2024