Update Script

> This question relates to updating a very large table using IDS94. > > I am trying to update a single column in one table (A) with the value > of a column in another table (B). > > Both columns have the same name and size (phy_pract_cd - char1). A > join on the phy_billing_num and fiscal_start_yr is required in order > to obtain the correct column value. There should be a match found for > every row based on the join above. > > Table A contains 1.1 billion rows > Table B contains 310,000 rows > > I have created a query (see below) that worked successfully on a test > table of 15 million rows but it needed 25 minutes to complete. > Running this query on the main table (A) would take a very long time. > > Both tables have a composite index of > (fiscal_start_yr+phy_billing_num) but according to the explain output > (see below), the query must still perform a sequential scan of the > physician table for each update. (hence the slowness) > > Is there an easier/faster method? > > Thank you, > Tony > > ========================= > update a_medical_serv_mth > set a_medical_serv_mth.phy_pract_cd = > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > where exists > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > ; > ========================= > > ========================= > Estimated Cost: 269763 > Estimated # of Rows Returned: 106149 > Maximum Threads: 0 > > 1) informix.physician: SEQUENTIAL SCAN (Parallel, fragments: ALL) > > 2) informix.a_medical_serv_mth: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: > (informix.a_medical_serv_mth.phy_billing_num = > informix.physician.phy_billing_num AND informix.a_ > medical_serv_mth.fiscal_start_yr = informix.physician.fiscal_start_yr > ) > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 6 > > 1) informix.physician: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: (informix.physician.phy_billing_num = > informix.a_medical_serv_mth.phy_billing_num AND informi > x.physician.fiscal_start_yr = > informix.a_medical_serv_mth.fiscal_start_yr ) > ========================= >

The optimizer has elected to scan the smaller table on the assumption that it will likely have to visit almost every page in that table anyway, and then for each row in the physicians table find all of the matching rows in the larger table using the index you mentioned. Based on the estimated number of rows and the query plan, I'd guess that the tables' stats were out of date on both tables or perhaps only LOW stats have been generated. Art S. Kagel ----- Original Message ----- From: Tony Demeis <ids@iiug.org> At: 7/30 15:54:49 > This question relates to updating a very large table using IDS94. > > I am trying to update a single column in one table (A) with the value > of a column in another table (B). > > Both columns have the same name and size (phy_pract_cd - char1). A > join on the phy_billing_num and fiscal_start_yr is required in order > to obtain the correct column value. There should be a match found for > every row based on the join above. > > Table A contains 1.1 billion rows > Table B contains 310,000 rows > > I have created a query (see below) that worked successfully on a test > table of 15 million rows but it needed 25 minutes to complete. > Running this query on the main table (A) would take a very long time. > > Both tables have a composite index of > (fiscal_start_yr+phy_billing_num) but according to the explain output > (see below), the query must still perform a sequential scan of the > physician table for each update. (hence the slowness) > > Is there an easier/faster method? > > Thank you, > Tony > > ========================= > update a_medical_serv_mth > set a_medical_serv_mth.phy_pract_cd = > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > where exists > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > ; > ========================= > > ========================= > Estimated Cost: 269763 > Estimated # of Rows Returned: 106149 > Maximum Threads: 0 > > 1) informix.physician: SEQUENTIAL SCAN (Parallel, fragments: ALL) > > 2) informix.a_medical_serv_mth: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: > (informix.a_medical_serv_mth.phy_billing_num = > informix.physician.phy_billing_num AND informix.a_ > medical_serv_mth.fiscal_start_yr = informix.physician.fiscal_start_yr > ) > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 6 > > 1) informix.physician: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: (informix.physician.phy_billing_num = > informix.a_medical_serv_mth.phy_billing_num AND informi > x.physician.fiscal_start_yr = > informix.a_medical_serv_mth.fiscal_start_yr ) > ========================= > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Using an index join this will indeed take hours if not days to complete. I would suggest starting with the following article which addresses this sort of situation. http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/parker/ 0502parker.html Do you have enough disk space to write a new copy of the table? Is the table_to_be_updated active 24x7? How much memory do you have (how much of it is DS memory)? what are the sizes of the two join columns (fisyr and billing num)? j. >From: "Demeis, Tony (MOH)" <Tony.Demeis@ontario.ca> >Date: 2007/07/30 Mon PM 02:54:28 CDT >To: ids@iiug.org >Subject: Update Script [9649] >> This question relates to updating a very large table using IDS94. >> >> I am trying to update a single column in one table (A) with the value >> of a column in another table (B). >> >> Both columns have the same name and size (phy_pract_cd - char1). A >> join on the phy_billing_num and fiscal_start_yr is required in order >> to obtain the correct column value. There should be a match found for >> every row based on the join above. >> >> Table A contains 1.1 billion rows >> Table B contains 310,000 rows >> >> I have created a query (see below) that worked successfully on a test >> table of 15 million rows but it needed 25 minutes to complete. >> Running this query on the main table (A) would take a very long time. >> >> Both tables have a composite index of >> (fiscal_start_yr+phy_billing_num) but according to the explain output >> (see below), the query must still perform a sequential scan of the >> physician table for each update. (hence the slowness) >> >> Is there an easier/faster method? >> >> Thank you, >> Tony >> >> ========================= >> update a_medical_serv_mth >> set a_medical_serv_mth.phy_pract_cd = >> (select physician.phy_pract_cd from physician >> where >> a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr >> and >> a_medical_serv_mth.phy_billing_num = physician.phy_billing_num >> ) >> where exists >> (select physician.phy_pract_cd from physician >> where >> a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr >> and >> a_medical_serv_mth.phy_billing_num = physician.phy_billing_num >> ) >> ; >> ========================= >> >> ========================= >> Estimated Cost: 269763 >> Estimated # of Rows Returned: 106149 >> Maximum Threads: 0 >> >> 1) informix.physician: SEQUENTIAL SCAN (Parallel, fragments: ALL) >> >> 2) informix.a_medical_serv_mth: INDEX PATH >> >> (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, >> fragments: ALL) >> Lower Index Filter: >> (informix.a_medical_serv_mth.phy_billing_num = >> informix.physician.phy_billing_num AND informix.a_ >> medical_serv_mth.fiscal_start_yr = informix.physician.fiscal_start_yr >> ) >> NESTED LOOP JOIN >> >> Subquery: >> --------- >> Estimated Cost: 1 >> Estimated # of Rows Returned: 1 >> Maximum Threads: 6 >> >> 1) informix.physician: INDEX PATH >> >> (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, >> fragments: ALL) >> Lower Index Filter: (informix.physician.phy_billing_num = >> informix.a_medical_serv_mth.phy_billing_num AND informi >> x.physician.fiscal_start_yr = >> informix.a_medical_serv_mth.fiscal_start_yr ) >> ========================= >> > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Do you have an index on the physician.phy_pract_cd column? That will be the reason for the sequential scan. Cheers -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Tuesday, July 31, 2007 8:17 AM To: ids@iiug.org Subject: Re:Update Script [9652] The optimizer has elected to scan the smaller table on the assumption that it will likely have to visit almost every page in that table anyway, and then for each row in the physicians table find all of the matching rows in the larger table using the index you mentioned. Based on the estimated number of rows and the query plan, I'd guess that the tables' stats were out of date on both tables or perhaps only LOW stats have been generated. Art S. Kagel ----- Original Message ----- From: Tony Demeis <ids@iiug.org> At: 7/30 15:54:49 > This question relates to updating a very large table using IDS94. > > I am trying to update a single column in one table (A) with the value > of a column in another table (B). > > Both columns have the same name and size (phy_pract_cd - char1). A > join on the phy_billing_num and fiscal_start_yr is required in order > to obtain the correct column value. There should be a match found for > every row based on the join above. > > Table A contains 1.1 billion rows > Table B contains 310,000 rows > > I have created a query (see below) that worked successfully on a test > table of 15 million rows but it needed 25 minutes to complete. > Running this query on the main table (A) would take a very long time. > > Both tables have a composite index of > (fiscal_start_yr+phy_billing_num) but according to the explain output > (see below), the query must still perform a sequential scan of the > physician table for each update. (hence the slowness) > > Is there an easier/faster method? > > Thank you, > Tony > > ========================= > update a_medical_serv_mth > set a_medical_serv_mth.phy_pract_cd = > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > where exists > (select physician.phy_pract_cd from physician > where > a_medical_serv_mth.fiscal_start_yr = physician.fiscal_start_yr > and > a_medical_serv_mth.phy_billing_num = physician.phy_billing_num > ) > ; > ========================= > > ========================= > Estimated Cost: 269763 > Estimated # of Rows Returned: 106149 > Maximum Threads: 0 > > 1) informix.physician: SEQUENTIAL SCAN (Parallel, fragments: ALL) > > 2) informix.a_medical_serv_mth: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: > (informix.a_medical_serv_mth.phy_billing_num = > informix.physician.phy_billing_num AND informix.a_ > medical_serv_mth.fiscal_start_yr = informix.physician.fiscal_start_yr > ) > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 6 > > 1) informix.physician: INDEX PATH > > (1) Index Keys: fiscal_start_yr phy_billing_num (Parallel, > fragments: ALL) > Lower Index Filter: (informix.physician.phy_billing_num = > informix.a_medical_serv_mth.phy_billing_num AND informi > x.physician.fiscal_start_yr = > informix.a_medical_serv_mth.fiscal_start_yr ) > ========================= > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.