Need help to speed up updates to a table

[rogers42]

Hi Folks,

tab_1 has 60K records
tab_2 has 90 records
Both tables are indexed
The code to update one of tab_1 columns is as follows

DECLARE
v_flag CHAR(1);

CURSOR c_provider IS
select p.PROV, p.FREE, p.SUB, p.PAID
from tab_1 p,
tab_2 vab
where p.PROV = vab.PROV
and vab.PAID is NULL;

BEGIN
FOR r_provider IN c_provider
LOOP
IF r_provider.FREE = 'Y' THEN
v_flag := 'N';

ELSIF r_provider.SUB = 'Y' THEN
v_flag := 'N';

ELSIF r_provider.PAID = 'Y' THEN
v_flag := 'Y';
END IF;

UPDATE tab_1
set PAID_FLAG = v_flag
where PROV = r_provider.PROV;

END LOOP;
commit;
END;
/

It is taking hour and a half to update a column (from NULL value to "Y" or "N") ?

Can anybody please suggest something to make the update go faster? or point out anything that might be wrong with my code?

Thanks in advance

rogers42

 

[SantaMufasa]

Rogers,

Before we work on fast, let's ensure that we first get the logic correct.

One issue is, if r_provider.FREE, .SUB, and .PAID are each 'N', then when you

 UPDATE tab_1
     set PAID_FLAG = v_flag
    where PROV = r_provider.PROV;

...the value of PAID_FLAG will be whatever was set on some previous record when r_provider.FREE, .SUB, or .PAID was 'Y'...That is not what you want, I believe.

As to performance, you said you have indexes -- The most important index is on "tab_1.prov", so I'm assuming that you have an index on that column, correct?

Is there a situation in your environment that is compelling you to use PL/SQL to perform this UPDATE? If the UPDATE is truly all you are doing in your PL/SQL block, then you can actually perform your UPDATE using SQL exclusively. Have you composed an all-SQL UPDATE statement and compared the speed?

Let us know regarding the above issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[rogers42]

Hi Mufasa,,

Thanks for the reply. Let me start out by answering some of your questions.

1, Yes the logic looks flawed because I had omitted the fact that all records have a"Y" or "N" value for only one of the fields (i.e. FREE, SUB, or PAID). While the other two fields will always be null.

e.g.

Table: tab_1
Sample Records:

prov, free, sub, paid
======================
aa, null, null, N
bb, Y, null, null
cc, null, null, N

Hence, we will fall into the if-else structure for every iteration of the loop.

2, Yes, I have an index on the prov columns of tab_1 and tab_2

3, Yes, my very first solution was 100% sql. The free, sub, and paid columns were examined using case statements. Since, the "prov" names are not unique, I had run into the "Can't return multiple values" error. Thus I had decided to switch to pl/sql. Unfortunately, I have scratched the solution. But, let me know, and I should be able to rewrite the code from memory and recreate the error message.

Additional Info:

A little google search had revealed "row chaining" as a potential cause of slowness. In order to eliminate the row chaining problem, I had recreated my table by doubling the PCT value. The table DDL is as follows

CREATE TABLE "DV"."TAB_1"
( "PROV" VARCHAR2(50 BYTE),
"PAID_FLAG" CHAR(1 BYTE),
"LAST_UPDATED" DATE
) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "NQ" ;


Please let me know if additional info is required.

Thanks in advance

rogers42

 

[SantaMufasa]

If you suspect row chaining, then the quickest way to eliminate that problem is to issue this command:

alter table <table_name> move parallel;

This command rebuilds the entire table at lightning speed.

Now, let's consider your logic (since you do not re-set the value of v_flag to a specific value each time through the loop):
[tt]
Case 1), record "aa": None of the IFs are true, therefore v_flag is NULL and thus, PAID_FLAG becomes NULL;

Case 2), record "bb": FREE = 'Y' THEN v_flag := 'N' and PAID_FLAG becomes 'N';

Case 3), record "cc": None of the IFs are true (as in Case 1, above), yet v_flag is still 'N' (from Case 2), thus PAID_FLAG becomes 'N';[/tt]

Please correct me if my analysis is flawed. If it is not flawed, I can't believe that the above is what you want to occur.

Please advise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

I think I'd do something like this:

UPDATE tab_1 p
SET paid_flag = 
   CASE
      WHEN p.free = 'Y' THEN 'N'
      WHEN p.sub  = 'Y' THEN 'N'
      WHEN p.paid = 'Y' THEN 'Y'
      ELSE p.paid_flag
   END
WHERE p.prov IN (SELECT vab.prov
                 FROM   tab_2 vab
                 WHERE  vab.paid IS NULL)

With an index on p.prov it should run pretty quickly.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[rogers42]

Hi,

Yes, your analysis is correct, however, my data set is not. The corrected sample data is as follows

prov, free, sub, paid
======================
aa, null, null, Y
bb, Y, null, null
cc, null, Y, null

Bottom line, one of the three fields will always be a "Y" while the other two fields will always be null. I agree, I should reset the variable regardless.

On the performance front, I did try the alter table command, however, the table update is still taking close to two hours. I guess, row chaining is not the problem ?

I shall appreciate any other suggestions or hints.

Thanks in advance.

rogers42

 

[SantaMufasa]

Regarding performance ideas, have you compared the speed of ChrisHunt's excellent SQL re-write to the PL/SQL version? Please let us know your findings...I'm curious about the speed difference(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

If your description of your data is accurate, with regard to the possible values of the flags, then the [tt]paid_flag[/tt] value that you're updating seems superfluous - it's essentially the same as [tt]NVL(paid,'N')[/tt].

It appears that tab_a can be in one of three states - free, sub or paid. I think you'd be better off storing this in one column, with a value of F, S or P, than in a cluster of Y/N fields that have to be kept in step with eachother.

You can always build a view that will make the single column look like multiple flags, if you need that for output purposes:

CREATE TABLE my_tab_a
(pay_type VARCHAR2(1))
/
CREATE VIEW display_tab_a AS
SELECT DECODE(pay_type,'F','Y',NULL) free,
       DECODE(pay_type,'C','Y',NULL) sub,
       DECODE(pay_type,'P','Y',NULL) paid,
       DECODE(pay_type,'P','Y','N')  paid_flag
FROM my_tab_a
/

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd