On Rollups, Merges, and Moves

Our technologist materializes, merges, and moves.

March/April 2005

I have a parent table and a child table. The child table has a column 'qty'. I want to SUM 'qty' and keep it in the parent table column 'tot_qty'. Any changes (UPDATE, DELETE, INSERT) to the child column 'qty' should be updated in the parent column 'tot_qty'. I know it can be done using a trigger, but I'm not sure how to go about it.

Summing up QTY columns for a parent record is pretty darn fast. Before you actually do this, just make sure that you are solving a real problem. Databases were born to join and aggregate data; it's what they do best. Also, remember, with this summing approach, you will serialize at the level of the parent record. Every modification to a child record will serialize across transactions at the parent level.

There are two approaches. You can either take a do-it-yourself (DIY) approach via triggers, or you can use an ON COMMIT REFRESH materialized view. With the second option, the parent table won't have the summary, but you'll have a summary table with the parent key and the summary count.

First, for the DIY approach, I'll assume a table structure as follows:

create table p
( x int primary key, qty number );
insert into p (x) values (1);
create table c
( a int primary key, x references p,
  qty number not null );

That is the parent/child table, and I want to maintain the sum of C.QTY in P.QTY . The trigger I would use would look like the following:

create or replace trigger c_trigger
after insert or delete or update
of qty, x on C
for each row
begin
  if ( inserting or updating )
  then
    update p
    set qty = nvl(qty,0)+:new.qty
    where x = :new.x;
  end if;
  if ( updating or deleting )
  then
    update p
    set qty = nvl(qty,0)-:old.qty
    where x = :old.x;
  end if;
end;
/

This trigger will fire any time I modify the QTY column in C or when I modify the foreign key value in C , hence moving the quantity from one parent record to another. When I insert, I just add to the parent table's QTY record (using NVL to set any NULL values to zero first). When I delete, I just subtract. When I update, I add the new value to the new foreign key record and subtract the old value from the old foreign key.

Now with that trigger in place, I can perform insert and update operations such as:

SQL> insert into c(a,x,qty)
  2  values ( 100, 1, 55 );
1 row created.
SQL> update c
  2  set qty = qty+100;
1 row updated.

And I can review the modifications to the parent record:

SQL> select * from p;
         X        QTY
  --------      --------
         1        155 

I said there was another way—without using DIY triggers—and that is a materialized view. (See the Oracle Data Warehousing Guide, for all of the details on materialized views.) An equivalent solution (assuming that you just created P and C and there are no triggers here) to this "precompute the summed-up QTY " could be:

SQL> create materialized view log
  2  on c with rowid(x,qty)
  3  including new values;
Materialized view log created.
SQL> create materialized view mv
  2   refresh fast on commit
  3   as
  4   select c.x,
  5         sum(qty) sum_qty,
  6         count(*) cnt,
  7         count(qty) cnt_qty
  8    from c
  9   group by c.x
 10  /
Materialized view created.

And that's it. The table MV (the materialized view creates and maintains a physical table) contains the summed up QTY value, as well as the COUNT(*) and COUNT(QTY) values. The counts are a requirement of a "single table aggregate fast refresh materialized view"—without them, it could not refresh incrementally as this one can.

I am confused about the MERGE statement and the firing of database triggers. What triggers are supposed to fire and when in a MERGE?

A MERGE is a funny statement—it is both an INSERT and an UPDATE , and in Oracle Database 10g, it could also be a DELETE . Hence, when you issue a MERGE , it will immediately fire the BEFORE UPDATE and BEFORE INSERT triggers in Oracle9i Database (since the WHEN MATCHED THEN UPDATE AND WHEN NOT MATCHED THEN INSERT clauses are mandatory). In Oracle Database 10g, it'll fire the BEFORE UPDATE, INSERT , and/or DELETE triggers—depending on which ones apply.

Likewise, after the MERGE statement executes, the AFTER triggers will fire. In Oracle9i Database, any AFTER UPDATE or AFTER INSERT triggers will fire, and in Oracle Database 10g, any of the AFTER

The row triggers for INSERT and UPDATE (and in Oracle Database 10g, DELETE ) will fire as the MERGE progresses and performs each action.

The table I'll use to demonstrate this is very simple:

create table t
(x varchar2(1),
 y varchar2(1)
);

I'll place six triggers on this table, using the following template:

create or replace trigger t_bi
before insert on t
begin
    dbms_output.put_line
    ('before insert');
end;
/

I'll do the same thing for BEFORE UPDATE, BEFORE DELETE , and AFTER UPDATE/INSERT/DELETE for a total of six triggers. Now I issue:

SQL> merge into t
  2    using dual
  3    on (dummy=x)
  4    when matched
  5    then update
  6         set y = nvl(y,0)+1
  7    when not matched
  8    then insert (x,y)
  9    values (dummy,null);
before insert
before update
after update
after insert
1 row merged.

And that is what you'd see in both Oracle9i Database and Oracle Database 10g—the BEFORE INSERT/UPDATE triggers and the AFTER UPDATE/INSERT triggers all fired, even though in this case the only action that occurred was the INSERT (because table T was empty, there were no matches). This is to be expected, since an update of an empty table would fire the BEFORE and AFTER triggers as well. The insertion of zero rows would fire them also. Since the MERGE might INSERT and it might UPDATE , it has to fire both triggers.

In Oracle Database 10g, each part of the MERGE is optional—we don't have to INSERT , we don't have to UPDATE , and we have the option of deleting data. I can have a MERGE statement such as the following:

SQL> merge into t
  2    using dual
  3    on (dummy=x)
  4    when matched
  5    then update
  6        set y = nvl(y,0)+1
  7        delete where y = 2
  8    when not matched
  9    then insert (x,y)
 10       values (dummy,null);
before insert
before update
before delete
after update
after insert
after delete
1 row merged.

Notice that now all six triggers fired, because any of the INSERT, UPDATE , or DELETE operations could take place. If I take out the optional (in Oracle Database 10g) INSERT clause:

SQL> merge into t
  2    using dual
  3    on (dummy=x)
  4    when matched
  5    then update
  6        set y = nvl(y,0)+1
  7        delete where y = 2;
before update
before delete
after update
after delete
1 row merged.

Now the INSERT triggers do not fire. Only the triggers that could possibly fire will fire in Oracle Database 10g. See asktom.oracle.com/~tkyte/merge_trigger.html for the original Q&A on this; this online discussion has more examples and goes into the nuances of row-level triggering with MERGE as well.

I was going through the Oracle9i Performance Tuning Guide and Reference. I read about view merging and predicate pushing. Can you explain these?

View merging simply means the query is rewritten without the view. It is as if instead of:

select * from VIEW;

you actually typed in the VIEW text.

Predicate pushing is when a predicate is pushed down as far into the plan as possible (and evaluated as soon as it can be).

I'll use three views to show

• A nonmergeable, nonpushable view

• A mergeable view

• A nonmergeable view that does predicate pushing

The tables I'll use are very simple:

create table t1 as
select * from all_users;
create table t2 as
select * from all_users;
        I create the first view as follows:
create or replace view v1
as
select t1.user_id, t2.username,
       rownum r
  from t1 inner join t2
    on ( t1.user_id =
         t2.user_id );

I will not be able to merge this view, and I won't be able to push predicates. That is because of ROWNUM in this case (analytics would typically have the same effect). The reason: R would change values if I applied predicates before assigning the ROWNUM value to R . The result of SELECT * FROM (SELECT ..., ROWNUM R FROM T) WHERE <predicate> is very different than the result of SELECT ..., ROWNUM R FROM T WHERE <predicate>.

I create the second view as follows:

create or replace view v2
as
select t1.user_id, t2.username
  from t1 inner join t2
    on ( t1.user_id =
         t2.user_id );

This view can be merged. That is, Oracle Database will be able to rewrite SELECT * FROM V2 with the text of V2 replaced in the query itself. The optimizer has the best chance of developing a good overall plan in this fashion.

I create the last view, V3 , as follows:

create or replace view v3
as
select t1.user_id, t2.username
  from t1 inner join t2
    on ( t1.user_id =
         t2.user_id )
 order by t1.user_id,
          t2.username;

The ORDER BY in this view will prevent total merging but not predicate pushing, in contrast to the ROWNUM example in V1 . So how can we actually see these facts? The answer is EXPLAIN PLAN . Let's take a look at a query against each one of these views and analyze the results. Listing 1 is a query against V1.

Code Listing 1: Query against V1

SQL> explain plan for
  2    select * from v1 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
| Id  | Operation               |  Name     | Rows  | Bytes | Cost       |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |            |
|*  1 |  VIEW                   | V1        |       |       |            |
|   2 |   COUNT                 |           |       |       |            |
|   3 |    MERGE JOIN           |           |       |       |            |
|   4 |     SORT JOIN           |           |       |       |            |
|   5 |      TABLE ACCESS FULL  | T2        |       |       |            |
|*  6 |     SORT JOIN           |           |       |       |            |
|   7 |      TABLE ACCESS FULL  | T1        |       |       |            |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------
   1 - filter("V1"."USERNAME"='FRED')
   6 - access("T1"."USER_ID"="T2"."USER_ID")
       filter("T1"."USER_ID"="T2"."USER_ID")

The appearance of the VIEW step ( Id=1 ) in Listing 1 shows that the view was not merged. The fact that the filter WHERE USERNAME='FRED' is way up there ( Id=1 ) shows that the predicate was not pushed into the view. Oracle Database will materialize the view and then apply the predicate. This is an example of a view that cannot be merged and does not support predicate pushing.

The second example, a query against V2 , is in Listing 2.

Code Listing 2: Query against V2

SQL> create or replace trigger no_more_than_10
SQL> explain plan for
  2  select * from v2 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| Id    | Operation             |  Name         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0   | SELECT STATEMENT      |               |       |       |       |
|   1   |  MERGE JOIN           |               |       |       |       |
|   2   |   SORT JOIN           |               |       |       |       |
|*  3   |    TABLE ACCESS FULL  | T2            |       |       |       |
|*  4   |   SORT JOIN           |               |       |       |       |
|   5   |    TABLE ACCESS FULL  | T1            |       |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   3 - filter("T2"."USERNAME"='FRED')
   4 - access("T1"."USER_ID"="T2"."USER_ID")
       filter("T1"."USER_ID"="T2"."USER_ID")

The lack of the VIEW step in Listing 2 shows that the view was merged. It is as if I queried:

select t1.user_id, t2.username
  from t1 inner join t2
    on ( t1.user_id =
         t2.user_id )
 where t2.username = 'FRED';
instead of querying:
select *
  from v2
 where username = 'FRED';

Oracle Database just rolled our text directly into the query itself, optimizing it as if the view didn't even exist.

The last query, against V3, is shown in Listing 3.

Code Listing 3: Query against V3

SQL> explain plan for
  2  select * from v3 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id    | Operation                     |  Name | Rows  | Bytes | Cost    |
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT              |       |       |       |         |
|   1   |  VIEW                         | V3    |       |       |         |
|   2   |   SORT ORDER BY               |       |       |       |         |
|   3   |    MERGE JOIN                 |       |       |       |         |
|   4   |     SORT JOIN                 |       |       |       |         |
|*  5   |      TABLE ACCESS FULL        | T2    |       |       |         |
|*  6   |     SORT JOIN                 |       |       |       |         |
|   7   |      TABLE ACCESS FULL        | T1    |       |       |         |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------
   5 - filter("T2"."USERNAME"='FRED')
   6 - access("T1"."USER_ID"="T2"."USER_ID")
       filter("T1"."USER_ID"="T2"."USER_ID")

In Listing 3, I see the VIEW step, indicating that the view was not merged. Because I requested the rows to be ordered, I could not merge that step. The predicates applied to the view, however, were pushed down as far into the query as possible. Step 5 ( Id=5 ) in the plan in Listing 3 shows that the predicate WHERE USERNAME = 'FRED' is applied as early as it can be—since it would not modify the answer returned by the query—in contrast to the first view query (in Listing 1) I explored.

Because of the reorganization of one of my data files, I have to move one table to another data file. I was thinking about two possibilities. One is to create another tablespace, export the table, drop the table, precreate it in another tablespace, and import it. The other is to use the ALTER TABLE MOVE operation across data files.

Which variant is better in terms of downtime of the table, space consumption, and whether the process is restartable? I have no test system. The table is a heap table of about 3.5GB with one primary key.

There are only two approaches I would even consider:

First, if I can have downtime, the easiest would be ALTER TABLE T MOVE TABLESPACE NEW_TABLESPACE . This can be done in parallel with the NOLOGGING operation if you like (but make sure to schedule a backup immediately if you use NOLOGGING in archive log mode). It will preclude modifications from taking place for the duration of the move and subsequent index rebuilds. Queries can take place during the move, but right after the move completes, the indexes will all be unusable so queries will start failing at that point until you rebuild them. This approach is totally transactional—as opposed to EXP and IMP (any time you take the data out of the database, I get nervous, because you might lose that data). The ALTER TABLE MOVE either succeeds or leaves the data as it was.

The second approach would be to use the DBMS_REDEFINITION package available in Oracle9i Database and above; search for DBMS_REDEFINITION on asktom.oracle.com for quick examples. This package's advantages are that it supports continuous query and modifications. However, in Oracle9i Database you are responsible for making sure the new object meets your needs (that is, indexes, constraints, triggers, and so on are all in place). Oracle Database 10g takes this responsibility from you—you can have the database do all of the bookkeeping, making sure the redefined table has all of the needed grants, indexes, constraints, triggers, and so on—to make it a perfect copy of the original. This, too, is transactional; no data can occur.

For 3.5GB, either approach is going to be pretty fast regardless; 3.5GB is not very large these days. If you can have downtime, the ALTER TABLE MOVE is probably the easiest approach (remember to rebuild the indexes!). But in the event downtime is not permitted, DBMS_REDEFINITION would be the way to go.