RE: better sql code
- From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>
- To: <raja4list@xxxxxxxxx>, "oracle list" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Oct 2005 13:20:40 -0400
What version of Oracle is this? Perhaps you can declare the file as an
external table then just do a select/ insert from there without previously
loading it.
Don Freeman
Database Administrator 1
Bureau of Information Technology
Pennsylvania Department of Health
717-783-8095 Ext 337
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of raja rao
Sent: Tuesday, October 25, 2005 1:16 PM
To: oracle list
Subject: better sql code
Hi All,
can someone give me a better code for the below:
We use the sqlloder to load somedata into oracle.
that will be loaded into a stage table called adtstage.
Later this should be updated into the main table HSP.
For this purpose, we have develped the code like this.
we will collect the acct# numbers into a flat file (filename is list) and
for each line will fire theupdate statement like below.
cat list|while read line
do
sqlplus -s <<EOF
${connect_string}
@update.sql ${line}
EOF
done
# list is a file which contains the acct# to be updated into main table.
update.sql contains teh below code:
define hsval=&1
update HSP set
mr#= ( select mr# from adtstage where acct#= &hsval ),
facility_code=( select facility_code from adtstage where acct#=
&hsval ),
Patient_LName= ( select Patient_LName from adtstage where
acct#= &hsval ),
.....
adt_flag='Y'
where acct#= '&hsval' and hsp_id='XXX'
;
commit;
In teh abvoe both tables, acct#, mr# columns are primary key.
There are around 100 cols to be updated from stage to main tables. If there are
a 1000 records it is taking
long time to update the main table. Becoz, it should run thru the whole 1000
recods and run the above update
which is taking lot of time.
can someone help me in tuning this.
Thanks,
Raj
NOTE: Both the tables HSP and ADTSTAGE are same structures and they have
acct#, mr# as the primary key.
_____
Yahoo!
<http://us.lrd.yahoo.com/_ylc=X3oDMTFqODRtdXQ4BF9TAzMyOTc1MDIEX3MDOTY2ODgxNjkEcG9zAzEEc2VjA21haWwtZm9vdGVyBHNsawNmYw--/SIG=110oav78o/**http%3a//farechase.yahoo.com/>
FareChase - Search multiple travel sites in one click.
Other related posts:
