RE: better sql code

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 25 Oct 2005 14:06:34 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKGEGPHCAA.mwf@rsiz.com>

Okay, my first response was a little terse.

First, I hope you realize that you are actually starting sqlplus and running through the security and session establishment code for each row. (If this was a practical joke question, I didn't recognize your name and you've bagged me.)

Second, I'm wondering whether "list" contains an account for each row of adtstage. If it does, then you don't need "list" at all. If it doesn't, then you should either filter the input to sqlloader or also load "list" into a table. Whichever is easier for you.

Option A: (All of adtstage to be processed once it is in Oracle)

update hsp a

   set adt_flag='Y',
(mr#, facility_code, patient_lname, ..., ....) =
(select

    mr#, facility_code, patient_lname, ..., ....     from adtstage b
    where a.acct# = b.acct#
   )
   where hsp_id='XXX'

      and acct# in (select acct# from adtstage);

Option B: (Adtstage contains some rows not to be updated, and list controls which rows to use)
Just change "adtstage" to "list" in the last line.

I just typed this in quickly and didn't test it. The main point is to log in once. The secondary point is to let Oracle use sets. Whether this would be faster via external tables seems unlikely to me since the correlated subquery will want the index, and someone giving this problem more thought can probably do it better.

Third, it also seems a little odd to me that you're updating mr# if it is indeed part of the pk. I'm guessing you're a bit new at this, unless, as I said, this is a practical joke.

Perhaps you don't really want it in the set list and the "and" predicate should be:

and (acct#,mr#) in (select acct#,mr# from adtstage)

Regards,

                        www.rsiz.com Oracle Technology and Business

Consulting

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




            Want a signature like this?

  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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.



--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 25 2005 - 13:56:34 CDT