Thanks for the question, B.

Asked: August 23, 2016 - 12:13 pm UTC

Last updated: August 23, 2016 - 4:25 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom

I have a table with 18 million rows. I need to do an once-off update to fix a data issue.

CREATE TABLE TESTLARGE(CODE number, STATE varchar2(5), SDATE date, flag char(1));

This table does not have any primary key enforced.

I have to update the FLAG='Y' based on state and date

UPDATE TESTLARGE
  SET
     FLAG='Y' WHERE CODE IN (SELECT CODE FROM TESTLARGE where STATE in ('PRE', 'POST', 'START', 'END') 
                and SDATE between :a1 and :b1 GROUP BY CODE)
WHERE
    STATE in ('PRE', 'POST', 'START', 'END') 
                and SDATE between :a1 and :b1 GROUP BY CODE
;

The problem here is, it has been running for 7 hours.

Can this update be written any efficiently, just touching the table once rather than twice?

Something like
UPDATE (select ....... from testlarge where ...group by...) set flag='Y';

Many thanks
BV

and Chris said...

I'm baffled. The subquery doesn't seem to serve any purpose!

It looks to me like you could just do this:

update testlarge
set    flag    ='Y'
where  state in ( 'PRE', 'POST', 'START', 'END' )
and    sdate between :a1 and :b1

So the question is:

What are you trying to do?

If the update above is correct and still takes a long time, you could investigate:

- Creating an index on (state, sdate)
- Doing the update as a "create table as select". For example:

create table testlarge_tmp as 
select state, sdate, 'Y' flag, othercols
from   testlarge
where  state in ( 'PRE', 'POST', 'START', 'END' )
and    sdate between :a1 and :b1
union all
select state, sdate, flag, othercols
from   testlarge
where  not (state in ( 'PRE', 'POST', 'START', 'END' )
     and    sdate between :a1 and :b1
);

Then switching the tables over.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Typo

B V, August 23, 2016 - 12:41 pm UTC

Hi Chris

Thanks for the quick response. I am sorry, I missed HAVING clause in group by.

It should have been

UPDATE TESTLARGE
  SET
     FLAG='Y' WHERE CODE IN (SELECT CODE FROM TESTLARGE where STATE in ('PRE', 'POST', 'START', 'END') 
                and SDATE between :a1 and :b1 GROUP BY CODE
HAVING COUNT(*) > 1
)
WHERE
    STATE in ('PRE', 'POST', 'START', 'END') 
                and SDATE between :a1 and :b1 GROUP BY CODE
;

Thanks

August 23, 2016 - 1:17 pm UTC

That makes more sense :)

You need to have a subquery in this case. Although you can "update a query", it's not possible if you have aggregates or analytics:

create table t (
  x int, y int
);

insert into t values (1, 1);
insert into t values (2, 1);
insert into t values (2, 2);

update (select * from (
    select x, y, count(*) over (partition by x) c from t
  )
where c > 1
)
set  y = 0;  

ORA-01733: virtual column not allowed here

Thanks

B V, August 23, 2016 - 1:32 pm UTC

Thanks Chris. Yes, that's right.

OK, as this is once-off update, I'll just let the update run to completion as long as it takes.

Cheers
BV

Juicy Hash joins

Rajeshwaran Jeyabal, August 23, 2016 - 1:51 pm UTC

18M, is really small these days, could be done in few mins, not hours.
please check if this helps.

demo@ORA11G> create table testlarge as
  2  select owner code,
  3      object_type as state,
  4      created as sdate,
  5      cast(null as varchar2(1)) as flag
  6  from big_table;

Table created.

demo@ORA11G>
demo@ORA11G> exec dbms_stats.gather_table_stats(user,'testlarge',degree=>2);

PL/SQL procedure successfully completed.

demo@ORA11G> select count(*) from testlarge;

  COUNT(*)
----------
  20000000

1 row selected.

demo@ORA11G>
demo@ORA11G> variable a1 varchar2(20)
demo@ORA11G> variable b1 varchar2(20)
demo@ORA11G> exec :a1 := '01/01/1990';

PL/SQL procedure successfully completed.

demo@ORA11G> exec :b1 := '08/23/2016';

PL/SQL procedure successfully completed.

demo@ORA11G> explain plan for
  2  merge into testlarge t1 using (
  3     select code ,count(*)
  4     from testlarge
  5     where STATE in ('TABLE SUBPARTITION','SYNONYM','JAVA CLASS','TABLE PARTITION')
  6     and SDATE between to_date(:a1,'mm/dd/yyyy')
  7                     and to_date(:b1,'mm/dd/yyyy')
  8     group by code
  9     having count(*) > 1 ) t2
 10  on (t1.code = t2.code)
 11  when matched then
 12     update set t1.flag ='Y'
 13     where t1.STATE in ('TABLE SUBPARTITION','SYNONYM','JAVA CLASS','TABLE PARTITION')
 14     and t1.SDATE between to_date(:a1,'mm/dd/yyyy')  and
 15             to_date(:b1,'mm/dd/yyyy');

Explained.

demo@ORA11G> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3388205627

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |           |  1428K|    73M| 60901   (2)| 00:12:11 |
|   1 |  MERGE                   | TESTLARGE |       |       |            |          |
|   2 |   VIEW                   |           |       |       |            |          |
|*  3 |    HASH JOIN             |           |  1428K|    87M| 60901   (2)| 00:12:11 |
|   4 |     VIEW                 |           |     3 |    90 | 30527   (2)| 00:06:07 |
|*  5 |      FILTER              |           |       |       |            |          |
|   6 |       SORT GROUP BY      |           |     3 |   102 | 30527   (2)| 00:06:07 |
|*  7 |        FILTER            |           |       |       |            |          |
|*  8 |         TABLE ACCESS FULL| TESTLARGE |  5556 |   184K| 30526   (2)| 00:06:07 |
|   9 |     TABLE ACCESS FULL    | TESTLARGE |    20M|   648M| 30257   (1)| 00:06:04 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."CODE"="T2"."CODE")
   5 - filter(COUNT(*)>1)
   7 - filter(TO_DATE(:B1,'mm/dd/yyyy')>=TO_DATE(:A1,'mm/dd/yyyy'))
   8 - filter(("STATE"='JAVA CLASS' OR "STATE"='SYNONYM' OR "STATE"='TABLE
              PARTITION' OR "STATE"='TABLE SUBPARTITION') AND
              "SDATE">=TO_DATE(:A1,'mm/dd/yyyy') AND "SDATE"<=TO_DATE(:B1,'mm/dd/yyyy'))

26 rows selected.

demo@ORA11G> set timing on
demo@ORA11G> merge into testlarge t1 using (
  2     select code ,count(*)
  3     from testlarge
  4     where STATE in ('TABLE SUBPARTITION','SYNONYM','JAVA CLASS','TABLE PARTITION')
  5     and SDATE between to_date(:a1,'mm/dd/yyyy')
  6                     and to_date(:b1,'mm/dd/yyyy')
  7     group by code
  8     having count(*) > 1 ) t2
  9  on (t1.code = t2.code)
 10  when matched then
 11     update set t1.flag ='Y'
 12     where t1.STATE in ('TABLE SUBPARTITION','SYNONYM','JAVA CLASS','TABLE PARTITION')
 13     and t1.SDATE between to_date(:a1,'mm/dd/yyyy')  and
 14             to_date(:b1,'mm/dd/yyyy');

17123270 rows merged.

Elapsed: 00:03:41.19
demo@ORA11G> commit;

Commit complete.

Elapsed: 00:00:01.80
demo@ORA11G>

August 23, 2016 - 4:24 pm UTC

Yep, several hours does sound wrong. But we can only speculate the reason here.

Don't update when FLAG is already 'Y'

Stew Ashton, August 23, 2016 - 1:51 pm UTC

UPDATE TESTLARGE SET FLAG='Y'
WHERE CODE IN (
  SELECT CODE FROM TESTLARGE
  where STATE in ('PRE', 'POST', 'START', 'END') 
    and SDATE between :a1 and :a2
  GROUP BY CODE
  HAVING COUNT(*) > 1
)
  and STATE in ('PRE', 'POST', 'START', 'END') 
  and SDATE between :a1 and :a2
  AND DECODE(FLAG,'Y',0,1) = 1;

The last line ensures that the update will not occur when FLAG = 'Y', but will occur if FLAG is NULL or equal to something other than 'Y'. This might reduce greatly the UNDO and REDO produced.

August 23, 2016 - 4:25 pm UTC

Yep, that may help. And you could include the decode in an index, possibly reducing the work further...

Rajeshwaran Jeyabal: missing condition

Stew Ashton, August 23, 2016 - 1:55 pm UTC

If you run your MERGE statement twice, it will update all those FLAG columns again!

How about setting FLAG = 'Y' only when it is not 'Y'?

To Stew | "missing condition"

Rajeshwaran Jeyabal, August 23, 2016 - 2:31 pm UTC

How about setting FLAG = 'Y' only when it is not 'Y'?

Thanks Stew, that helps.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Typo

Thanks

Juicy Hash joins

Don't update when FLAG is already 'Y'

Rajeshwaran Jeyabal: missing condition

To Stew | "missing condition"

More to Explore

Live SQL

Dev Gym

Update using subquery with group by

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Typo

Thanks

Juicy Hash joins

Don't update when FLAG is already 'Y'

Rajeshwaran Jeyabal: missing condition

To Stew | "missing condition"

More to Explore

Live SQL

Dev Gym