RE: better sql code

Er, how about using a comma list so you're only running a single subquery?

                        www.rsiz.com Oracle Technology and Business
Consulting

                        Mark W. Farnham
                        President  Rightsizing, Inc.
                        36 West Street
                        Lebanon, NH 03766-1239
                        mwf@xxxxxxxx  tel:  (603) 448-1803




            Want a signature like this?


  -----Original Message-----
  From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman, Donald
  Sent: Tuesday, October 25, 2005 1:21 PM
  To: raja4list@xxxxxxxxx; oracle list
  Subject: RE: better sql code


  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! FareChase - Search multiple travel sites in one click.

Other related posts: