Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Bruno.

Asked: November 03, 2016 - 1:06 am UTC

Last updated: November 17, 2016 - 2:27 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

I need to perform the following operation:

- add field to existing table (7b rows and 1Tb size);
- update that field by joining with another table that contains the necessary data.

I see a number of options, and would like your advice on which one is more efficient:
- do an update (not very efficient given that it needs to be done across the board)
- do an merge (more efficient, but still not very efficient for this particular case)
- do a CTAS (even more efficient, but not ideal in my opinion, because dependent objects still need to be copied manually, like indexes and grants for instance. This leads to human error)
- use dbms_redefinition (I tried, but I don't think it's possible to use it for this purpose. I saw that you created a blog post where you explain how to do an update using this package, but only within the same table and without a join).

Ideally, it would be good to do a CTAS and use the dbms_redefinition.copy_table_dependents functionality as standalone. This is not possible though.

Could you advise?

Thanks in advance

and Connor said...

The main risks with an large update are:

a) the redo/undo overhead
b) you might end up with a row migration disaster, which would ultimately require a reorganisation effort anyway.

If you can mitigate against those, then perhaps look at DBMS_PARALLEL_EXECUTE to getthe job done quickly.

If not, then CTAS is the way go, and has the often forgotten benefit that if you need to abandon the activity, or it fails, you still have the original table.

Rating

  (7 ratings)

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

Comments

More details

Gh, November 03, 2016 - 5:26 am UTC

What distinct values eg selectivity-cardinality are for this new column?
Is the table partitioned ?

Provide tabke description and also the joined one.


thank you

Bruno Luis, November 03, 2016 - 9:29 am UTC

Thanks for the useful answer. One remark: if CTAS is the best way, then it would be useful to have a seamless way of copying over all dependent objects (same way as with dbms_redefinition.COPY_TABLE_DEPENDENTS).

Best regards and keep up the good work!
Chris Saxon
November 03, 2016 - 8:58 pm UTC

dbms_redefinition.COPY_TABLE_DEPENDENTS is the way to do this. Why do you need another one?

I can't use COPY_TABLE_DEPENDENTS in this case

Bruno Luis, November 04, 2016 - 4:23 am UTC

Hi,

I'm sorry, I don't understand.

So, I want to update an empty (new) field on a huge table via a join. Like you said, the most efficient is a CTAS.

However, I can't use dbms_redefinition.copy_table_dependents as a standalone. Only if I do a dbms_redefinition.start_redef_table first. And if I do a dbms_redefinition.start_redef_table, Oracle will insert update into my target table, and I will lose the update I added via CTAS.

Or can I?

Thanks

this blog post does not apply

Bruno Luis, November 06, 2016 - 11:38 am UTC

Hi, thank you for answering.

That blog post does not apply to this particular situation. In that post, you take three columns— (TEMPORARY, GENERATED, and SECONDARY—) and concatenate them into a new FLAGS column.

What would be more interesting (and it's a scenario commonly found when updating huge fact tables) would be to add a new field to a table and UPDATE that field based on a join with another table.

This is something that can be achieved with, for instance:

update(
select src.field src_field, tgt.field tgt_field
from src, tgt
where
src.xx = tgt.xx
)
set tgt_field = src_field;

but this is much less efficient than a direct path INSERT SELECT or a CTAS. That's why I would like to either know a way of using dbms_redefinition package for the same purpose, and if not possible, at least use the dbms_redefinition.copy_
table_dependents to avoid the manual work of a CTAS.


Chris Saxon
November 11, 2016 - 4:02 pm UTC

Unfortunately not. Dbms_redefinition.start_redef_table needs an empty target table. And you need to be using this to copy everything over.

I'm not aware of other procedures that can transfer indexes in a similar way.

ok thank you

Bruno Luis, November 11, 2016 - 4:40 pm UTC

maybe Oracle will decide one day to have this procedure (copy_table_dependents) as a standalone... would be great :-)

thanks
Bruno
Chris Saxon
November 14, 2016 - 10:23 am UTC

If you want it, submit an enhancement request! Much more likely to get done that way ;) Particularly if you can attach a real business case to it.

but how?

Bruno Luis, November 14, 2016 - 10:53 am UTC

Hi,

thanks, that sounds like a good idea, I think this functionality could benefit the entire community. I'll be honest, I never did that before... could you explain what I need to do to create that enhancement?

Thanks inadvance
Bruno
Connor McDonald
November 16, 2016 - 4:42 am UTC

So how about something like this ?


SQL>
SQL> create table t1  as
  2  select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  3       OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE,
  4       CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
  5       TEMPORARY, GENERATED, SECONDARY
  6    from all_objects
  7    where object_id is not null;

Table created.

SQL>
SQL> alter table t1
  2    add constraint t1_pk
  3    primary key(object_id);

Table altered.

SQL>
SQL> create table t2 as select object_id, owner||'.'||object_name fname
  2  from all_objects
  3    where object_id is not null;

Table created.

SQL>
SQL> alter table t2
  2    add constraint t2_pk
  3    primary key(object_id);

Table altered.

SQL>
SQL> alter table t1 add full_name varchar2(200);

Table altered.

SQL>
SQL> select object_id, owner, full_name from t1
  2  where rownum < 10;

 OBJECT_ID OWNER                          FULL_NAME
---------- ------------------------------ ------------------------------
        30 SYS
        47 SYS
        32 SYS
        49 SYS
        17 SYS
         2 SYS
        29 SYS
        45 SYS
        10 SYS

9 rows selected.



So at this point I have table T1 with a new null column, and that value needs to come from table T2. I can simulate the join using a function

SQL> create or replace
  2  function get_full(p_id int )  return varchar2 deterministic is
  3    f varchar2(100);
  4  begin
  5    select  fname
  6    into f
  7    from t2
  8    where object_id = p_id;
  9
 10    return f;
 11  end;
 12  /

Function created.

SQL> create table t_interim (
  2   owner                         varchar2(128),
  3   object_name                   varchar2(128),
  4   subobject_name                varchar2(128),
  5   object_id                     number,
  6   data_object_id                number,
  7   object_type                   varchar2(23),
  8   created                       date,
  9   last_ddl_time                 date,
 10   timestamp                     varchar2(19),
 11   status                        varchar2(7),
 12   temporary                     varchar2(1),
 13   generated                     varchar2(1),
 14   secondary                     varchar2(1),
 15   full_name                     varchar2(200)
 16  );

Table created.

SQL>
SQL>
SQL> declare
  2      l_colmap varchar(512);
  3    begin
  4      l_colmap :=
  5            'OWNER
  6            ,OBJECT_NAME
  7            ,SUBOBJECT_NAME
  8            ,OBJECT_ID
  9            ,DATA_OBJECT_ID
 10            ,OBJECT_TYPE
 11            ,CREATED
 12            ,LAST_DDL_TIME
 13            ,TIMESTAMP
 14            ,STATUS
 15            ,TEMPORARY
 16            ,GENERATED
 17            ,SECONDARY
 18            ,get_full(OBJECT_ID) FULL_NAME';  -- <<=====
 19
 20      dbms_redefinition.start_redef_table
 21      (  uname           => user,
 22         orig_table      => 'T1',
 23         int_table       => 'T_INTERIM',
 24         col_mapping   => l_colmap );
 25   end;
 26   /

PL/SQL procedure successfully completed.

SQL>
SQL> variable nerrors number
SQL>
SQL> begin
  2     dbms_redefinition.copy_table_dependents
  3        ( user, 'T1', 'T_INTERIM',
  4          copy_indexes => dbms_redefinition.cons_orig_params,
  5          num_errors => :nerrors );
  6      end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> print nerrors

   NERRORS
----------
         0

SQL>
SQL> begin
  2         dbms_redefinition.finish_redef_table
  3           ( user, 'T1', 'T_INTERIM' );
  4        end;
  5        /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select object_id, owner, full_name from t1
  2  where rownum < 10;

 OBJECT_ID OWNER                          FULL_NAME
---------- ------------------------------ ------------------------------
        30 SYS                            SYS.I_COBJ#
        47 SYS                            SYS.I_USER2
        32 SYS                            SYS.CCOL$
        49 SYS                            SYS.I_COL2
        17 SYS                            SYS.FILE$
         2 SYS                            SYS.C_OBJ#
        29 SYS                            SYS.C_COBJ#
        45 SYS                            SYS.I_TS1
        10 SYS                            SYS.C_USER#

9 rows selected.


it will work, but for huge tables?...

Bruno Luis, November 16, 2016 - 10:42 am UTC

Hi,

thanks for your reply. This is indeed a very good idea, it should work fine. Do you think, however, that it applies to very big tables? This function will run once per row, right? I can run a few tests, but I have the feeling it will be much slower than a CTAS for big tables.
Connor McDonald
November 17, 2016 - 2:27 am UTC

I blogged about this

https://connormcdonald.wordpress.com/2016/11/16/performing-a-large-correlated-update/

Quote: You might be thinking “But we’ll be slow because we’re calling that PL/SQL millions of times”. Yes, this will be slower than a pure CTAS operation, but by using DBMS_REDEFINITION the process is online with nearly zero service disruption. Hence the overall execution time is not nearly so critical anymore as a CTAS where applications are unavailable during the activity.

Please note the comments as well, in terms of the lookup table being static

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library