Skip to Main Content
  • Questions
  • How to limit the numer of rows to a given number

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Todor.

Asked: June 08, 2005 - 9:45 am UTC

Last updated: November 02, 2008 - 4:51 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

Could you propose a solution for the following constraint:
in a given table T with columns A,B,C are up to 5 entries allowed for a given combination of (A,B).

The only solution I can think of is having a "counter" columnt in a separate table which to be inc- and decremented.

Example:

drop table t;

create table t (a varchar2(10), b number, c number,
constraint t_pk primary key (a,b,c));

insert into t(a,b,c) values ('A', 1, 1);
insert into t(a,b,c) values ('A', 1, 4);
insert into t(a,b,c) values ('A', 1, 99);
insert into t(a,b,c) values ('A', 1, 1000);
insert into t(a,b,c) values ('A', 1, 3);

-- should raise an error, because
-- it is the 6th row with a='A' and b=1
insert into t(a,b,c) values ('A', 1, 15);

and Tom said...

Yes, we'll need to maintain a rollup of this somewhere. You can do this via triggers, or you can use a materialized view.

I'll demonstrate the materialized view here, but you can read:
</code> https://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25asktom-089621.html <code>
which demonstrates both approaches (trigger/materialized view to maintain a rollup)

ops$tkyte@ORA9IR2> create table t (a varchar2(10), b number, c number, constraint t_pk primary key (a,b,c));

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view log on t with rowid(a,b) including new values;

Materialized view log created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv
2 refresh fast
3 on commit
4 as
5 select a, b, count(*) cnt
6 from t
7 group by a,b
8 /

Materialized view created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table mv add constraint check_lt_5 check(cnt<=5);

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 1);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 4);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 99);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 1000);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 3);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 15);

1 row created.

ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.CHECK_LT_5) violated


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 1);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 4);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 99);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 1000);

1 row created.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 3);

1 row created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> insert into t(a,b,c) values ('A', 1, 15);

1 row created.

ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.CHECK_LT_5) violated


Rating

  (25 ratings)

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

Comments

does not work for me

Matthias Rogel, June 09, 2005 - 3:15 am UTC

I have a table and want to assure it contains at most 2 rows.

so I used this technique, however it does not work

setup

create table t(
   t  integer);
   

create materialized view log on t with rowid including new values;

create materialized view t_limit
refresh fast on commit
as
   select count(*) co
   from t;   

alter table t_limit add constraint check_max_rows_in_t check(co<=2);




Session 1.

SQL> insert into t values(1);

1 Zeile wurde erstellt.

SQL> insert into t values(2);

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from t_limit;

        CO
----------
         2


REM ok

Session 2.
SQL>  insert into t values(4);

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
SQL> commit;
commit
*
FEHLER in Zeile 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (DP.CHECK_MAX_ROWS_IN_T) violated


REM ok.



Session 1. 

SQL> delete t;

2 Zeilen wurden gel÷scht.

SQL> select * from t_limit;

        CO
----------
         2

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from t_limit;

Es wurden keine Zeilen ausgewõhlt


REM ups - expected "0"

SQL> insert into t values(1);

1 Zeile wurde erstellt.

SQL> insert into t values(2);

1 Zeile wurde erstellt.


Session 2.
SQL> insert into t values(4);

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from t_limit;

Es wurden keine Zeilen ausgewõhlt

REM even more ups why that ????/

Session 1.
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from t;

         T
----------
         1
         2
         4
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


seems to me I hit a bug

question: how easiest to workaround ?
maintain a shadow table via trigger like in
https://asktom.oracle.com/Misc/oramag/on-rollups-merges-and-moves.html
? 

Tom Kyte
June 09, 2005 - 7:12 am UTC

tkyte@ORA9IR2W> create materialized view mv
2 refresh fast on commit
3 as
4 select '1', count(*) cnt
5 from t
6 group by '1'
7 /

Materialized view created.

tkyte@ORA9IR2W> alter table mv add constraint check_max_rows check (cnt<=2);

Table altered.

it is wanting a group by, a "key" to refresh by. (for some reason). Please contact support with your test case -- but that MV refreshes.

(but for something so small, the trigger would just work)



thx

Matthias Rogel, June 09, 2005 - 7:26 am UTC

i've filed it to support

FK's on materialized views...

Connor, June 09, 2005 - 8:12 am UTC

(Just a FYI really...)   We also tried a materialised view to maintain a list of distinct ID's from a table as per:

SQL> create table T ( p number, q number, r number);

Table created

SQL> alter table T add primary key ( p, q );

Table altered

SQL> insert into T values (1,2,3);

1 row created

SQL> insert into T values (4,5,6);

1 row created

SQL> insert into T values (7,8,9);

1 row created

rem
rem now we create a materialized view which is a summary of T
rem

SQL> CREATE MATERIALIZED VIEW LOG on T WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES;

Materialized view log created

SQL> CREATE MATERIALIZED VIEW MV
  2  build immediate
  3  REFRESH FAST ON COMMIT
  4  disable QUERY REWRITE AS
  5  SELECT p, count(*) cnt
  6  from t
  7  group by p;

Materialized view created

rem
rem now because we did a 'group by p' in the MV definition, we can make 'p'
rem a primary key for the MV
rem

SQL> alter table MV add constraint MV_PK primary key ( p ) initially deferred enable validate;

Table altered

So far so good, but it doesn't appear that this primary key then be used in any kind of FK constraint successfully...

rem
rem And now we create a table CHILD that will attempt to refer
rem back to that primary key via a ref integrity constraint
rem

SQL> create table CHILD ( c number, p number );

Table created

SQL> alter table CHILD add constraint FK foreign key ( p ) references MV ( p ) initially deferred enable validate;

Table altered

rem
rem So as this point, the db has happily let us assign the foreign
rem key But look what happens when I attempt a transaction
rem

SQL> insert into T values (10,11,12);

1 row created

SQL> insert into child values (0,10);

1 row created

rem
rem So far so good, because the constraints are deferred
rem

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (FK) violated - parent key not found

Bummer that one.... (its also with support)

Cheers
Connor 

seems that "delete t" triggers the bug

Todor Botev, June 09, 2005 - 11:26 am UTC

Plying out with the example of Matthias Rogel, I found that once the rows are deleted from the table ("delete t") the materialized view is not kept intact any longer. You can insert as many rows as you want - the view returns no rows.

So the bug seems to be triggered by the "delete t" statement.

Tom Kyte
June 09, 2005 - 5:58 pm UTC

against the single table aggregate MV without a group by.

adding index on snaptime$$

Alberto Dell'Era, June 09, 2005 - 11:41 am UTC

You'll want (in 9.2.0.6) an index on the mv log's SNAPTIME$$ column, such as

create index t_mlog_snap_idx on MLOG$_T (SNAPTIME$$);

since (almost) all the statements issued to refresh-fast after commit contains a query such as

SELECT /*+ CARDINALITY(MAS$ 1) */ ....
FROM MLOG$_T WHERE SNAPTIME$$ > :1

and since the mv log has no index, at every commit the log will be full scanned - if the hwm is high since a mass-insert/update/delete on t happened in the past, the index will be very helpful.

Example:

-- mass insert

insert into t (a,b,c)
select trunc (rownum/5), trunc (rownum/5), mod (rownum,5)
from (select null from all_objects where rownum <= 1000),
(select null from all_objects where rownum <= 100);
commit;

exec dbms_stats.gather_schema_stats (user, cascade=>true);

-- if you trace this:

insert into t(a,b,c) values (-1,-1,0);
commit;

======================================================================
/* MV_REFRESH (MRG) */ MERGE INTO "DELLERA4"."MV" "SNA$" USING (SELECT /*+ MAX_CARDINALITY(2000) */ "DLT$0"."A" "GB0", "DLT$0".
"B" "GB1", SUM(1) "D0" FROM (SELECT /*+ CARDINALITY(MAS$ 1) */ CHA
RTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."A", "MAS$"."B" FROM "DELLERA4"."MLOG$_T" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 ) "DLT$
0" GROUP BY "DLT$0"."A","DLT$0"."B")"AV$" ON (SYS_OP_MAP_NONNULL("S
NA$"."A")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND SYS_OP_MAP_NONNULL("SNA$"."B")=SYS_OP_MAP_NONNULL("AV$"."GB1")) WHEN MATCHED THEN UPDA
TE SET "SNA$"."CNT"="SNA$"."CNT"+"AV$"."D0" WHEN NOT MATCHED THEN I
NSERT ("SNA$"."A", "SNA$"."B", "SNA$"."CNT") VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0")

executions=1 buffer_gets=1611 (1611) rows_processed=1 (1)
elapsed_time=21232 (21232) cpu_time=20029 (20029)

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 156 |
| 1 | MERGE | | | | |
| 2 | VIEW | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 54 | 156 |
| 4 | VIEW | | 1 | 33 | 155 |
| 5 | SORT GROUP BY | | 1 | 29 | 155 |
|* 6 | TABLE ACCESS FULL | MLOG$_T | 1 | 29 | 146 |
| 7 | TABLE ACCESS BY INDEX ROWID| MV | 1 | 21 | 1 |
|* 8 | INDEX UNIQUE SCAN | I_SNAP$_MV | 1 | | |
------------------------------------------------------------------------------

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

6 - filter("SNAPTIME$$">:1)
8 - access(SYS_OP_MAP_NONNULL("A"(+))=SYS_OP_MAP_NONNULL("GB0") AND
SYS_OP_MAP_NONNULL("B"(+))=SYS_OP_MAP_NONNULL("GB1"))

create index t_mlog_snap_idx on MLOG$_T (SNAPTIME$$);

exec dbms_stats.gather_table_stats (user, 'MLOG$_T', cascade=>true);

insert into t(a,b,c) values (-2,-2,0);
commit;

======================================================================
(same statement as above : /* MV_REFRESH (MRG) */ MERGE INTO "DELLERA4"."MV" "SNA$" ...)
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 11 |
| 1 | MERGE | | | | |
| 2 | VIEW | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 54 | 11 |
| 4 | VIEW | | 1 | 33 | 10 |
| 5 | SORT GROUP BY | | 1 | 29 | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID| MLOG$_T | 1 | 29 | 1 |
|* 7 | INDEX RANGE SCAN | T_MLOG_SNAP_IDX | 1 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | MV | 1 | 21 | 1 |
|* 9 | INDEX UNIQUE SCAN | I_SNAP$_MV | 1 | | |
-------------------------------------------------------------------------------------

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

7 - access("SNAPTIME$$">:1)
9 - access(SYS_OP_MAP_NONNULL("A"(+))=SYS_OP_MAP_NONNULL("GB0") AND
SYS_OP_MAP_NONNULL("B"(+))=SYS_OP_MAP_NONNULL("GB1"))

There are other indexes that may be useful, i will investigate and share if someone is interested (on Monday, i've to join George Clooney near the Lake of Como in a couple of hours ;).

To me, it's very fascinating anyway. But, cross-table and cross-row consistency with mv ... requires a lot of tests and investigations.

materialized view compromises the transactions

Todor Botev, June 09, 2005 - 11:59 am UTC

The "materialized view" solution behaves strange when it comes to transactions:

Taking the setup of Matthias Rogel as basis, I created an additional table t1:

SQL> drop materialized view t_limit;
Materialisierte View wurde gelöscht.

SQL> drop table t;
Tabelle wurde gelöscht.

SQL> drop table t1;
Tabelle wurde gelöscht.

SQL> create table t(t  integer);
Tabelle wurde angelegt.

SQL> create materialized view log on t with rowid including new values;
Log von materialisierter View wurde erstellt.

SQL> create materialized view t_limit
  2  refresh fast on commit
  3  as
  4     select count(*) co
  5     from t;
Materialisierte View wurde erstellt.

SQL> alter table t_limit add constraint check_max_rows_in_t check(co<=2);
Tabelle wurde geändert.

SQL> create table t1 (n integer);
Tabelle wurde angelegt.

SQL> insert into t values(1);
1 Zeile wurde erstellt.

SQL> insert into t values(2);
1 Zeile wurde erstellt.

SQL> commit;
Transaktion mit COMMIT abgeschlossen.

SQL> insert into t values(3);
1 Zeile wurde erstellt.

SQL> insert into t1 values(3);
1 Zeile wurde erstellt.

SQL> select * from t;

         T
----------
         1
         2
         3

SQL> select * from t1;

         N
----------
         3

SQL> commit;
commit
*
FEHLER in Zeile 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (ICIS.CHECK_MAX_ROWS_IN_T) violated

REM So far so good. But now:

SQL> select * from t;

         T
----------
         1
         2

SQL> select * from t1;

Es wurden keine Zeilen ausgewählt

REM The latter is in German and says "no rows returned". The newly inserted rows in T and T1 just dissapeared without rollback - after commit failed.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
 

Tom Kyte
June 09, 2005 - 6:14 pm UTC

a commit that fails, is a rollback by definition.

Just like a commit with deferred constraints that fail.

Tom's workaround

A reader, June 09, 2005 - 7:02 pm UTC

Doesn't appear to work:

SQL> select * from t_limit;

         0         CO
---------- ----------
         0          2

SQL> delete t;

2 rows deleted.

SQL> select * from t_limit;

         0         CO
---------- ----------
         0          2

SQL> commit;

Commit complete.

SQL> 
SQL> select * from t_limit;

no rows selected 

Tom Kyte
June 09, 2005 - 7:29 pm UTC

umm, why does that make you think it does not work?

when T is empty, T_LIMIT is empty.

When T is not emtpy, T_LIMIT is not empty.

That was the goal -- do you see this not working to support the stated goal above?

thx everybody

Matthias Rogel, June 10, 2005 - 2:29 am UTC

thx for feedback

Tom's workaround works for me

Don't want to tell you which workaround support suggests ...
(Seldom laughed so loud in the last time !)

I know that I know nothing about Oracle
but
sometimes I think I have more Oracle experience
on my pinky toe than support has in their whole bodies put together

Mayank, June 10, 2005 - 7:56 am UTC

Hi Tom,

How do i ask questions.
Quote
"Sorry I have a large backlog right now, please ask a question later"
Unquote
Still i see threads 1 day old.

Thanks

Tom Kyte
June 10, 2005 - 8:39 am UTC

because I took some questions - I have a queue. I am a sole individual (not a team). When queue is filled, it shuts down all by itself.

a commit that fails, is a rollback by definition

Todor Botev, June 10, 2005 - 8:44 am UTC

OK, I agree. Just want to point out: this affects the transaction flow seriously. One should be aware of it when thinking about going the MV way to implement constraints on a table. You could probably mention it in the "caveats" section. :)

Group by empty set of columns

Mikito Harakiri, June 10, 2005 - 12:40 pm UTC

Isn't

group by 0

grouping by empty set of columns? Pseudocolumn expressions include table columns they are deprnding upon. Expression 0 doesn't depend on any column, hence we group by empty set of columns -- same as agregate with no grouping at all.

Tom Kyte
June 10, 2005 - 3:44 pm UTC

No, it is not, it is grouping by the literal 0, very very different and changes the outcome dramatically.

 
ops$tkyte@ORA10G> select count(*) from dual where 1=0;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA10G> select count(*) from dual where 1=0 group by 0;
 
no rows selected
 

 

How group by 0 should be defined in ANSI SQL

Mikito Harakiri, June 10, 2005 - 4:54 pm UTC

select count(*) from table

and

select count(*) from table group by 0

return identical results in all the cases, except the empty table. Wouldn't it be simpler just to define them equivalent in all the cases? What such (more consistent) definition might break?

Tom Kyte
June 10, 2005 - 5:05 pm UTC

um, so how is that different from my example above?

when given the empty table, they behave differently.
when given the non-empty table, they behave the same.

(a query is in fact a table)

the moment you add "group by", you need some rows to group by -- else there are NO ROWs by definition.

Ok .. so I tried this ... but ...

Greg, April 19, 2006 - 8:47 am UTC

Hey Tom .. if you could help me out here, I'm a bit confused ...

From the original question and your response, I expanded on it a bit as follows:

gregs-DEV10 > drop sequence s;

Sequence dropped.

gregs-DEV10 > drop materialized view mv;

Materialized view dropped.

gregs-DEV10 > drop table t;

Table dropped.

gregs-DEV10 > create sequence s;

Sequence created.

gregs-DEV10 > create table t(
2 t_id number,
3 group_id number,
4 pct number);

Table created.

gregs-DEV10 > alter table t add constraint cp_t primary key ( t_id );

Table altered.

gregs-DEV10 > create materialized view log on t with rowid (t_id, group_id, pct) including new values;

Materialized view log created.

gregs-DEV10 > create materialized view mv
2 refresh fast
3 on commit
4 as
5 select group_id,
6 sum ( pct ) pct
7 from t
8 group by group_id;

Materialized view created.

gregs-DEV10 > alter table mv add constraint cc_mv check ( pct = 100 );

Table altered.

gregs-DEV10 > insert into t values (s.nextval, 1, 10);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 1, 20);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 1, 30);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 1, 40);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 2, 10);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 2, 20);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 2, 30);

1 row created.

gregs-DEV10 > insert into t values (s.nextval, 2, 40);

1 row created.

gregs-DEV10 > commit;

Commit complete.

-- works fine ... but ..

gregs-DEV10 > insert into t values (s.nextval, 3, 99);

1 row created.

gregs-DEV10 > commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (GREGS.CC_MV) violated

-- fails as it should ...
-- we now have:

gregs-DEV10 > select * from t;
------more------

T_ID GROUP_ID PCT
---------- ---------- ----------
1 1 10
2 1 20
3 1 30
4 1 40
5 2 10
6 2 20
7 2 30
8 2 40

8 rows selected.

-- but watch what happens when I update:

gregs-DEV10 > update t
2 set pct = 71
3 where t_id = 1;

1 row updated.

gregs-DEV10 > commit;

Commit complete.

gregs-DEV10 > select * from t;
------more------

T_ID GROUP_ID PCT
---------- ---------- ----------
1 1 71
2 1 20
3 1 30
4 1 40
5 2 10
6 2 20
7 2 30
8 2 40

8 rows selected.

-- what's more interesting, it seems I "broke" the materialized view:

gregs-DEV10 > insert into t values (s.nextval, 3, 99);

1 row created.

gregs-DEV10 > commit;

Commit complete.

gregs-DEV10 > select * from t;
------more------

T_ID GROUP_ID PCT
---------- ---------- ----------
1 1 71
2 1 20
3 1 30
4 1 40
5 2 10
6 2 20
7 2 30
8 2 40
10 3 99

9 rows selected.

How do I apply the constraint to the update (and delete)? What did I do wrong? What am I missing? Why are gas prices so high?
;)

(oh and btw:
VERSION
-----------------
10.2.0.1.0

1 row selected.
)


.. don't mean to be rude .. but ..

Greg, April 28, 2006 - 7:59 am UTC

Hi Tom,

Not sure if you missed my post above, or didn't want to respond for any reason .. and I do know you are busy ... but I did try to find an answer a few other places (ie Metalink forums) .. but doesn't seem that anyone can explain what's going on ..

I'd appreciate if you had a minute to explain what's happening in the above scenario ...
Thanks!


Tom Kyte
April 28, 2006 - 12:37 pm UTC

I just don't see them all - every review/followup.

Using dbms_mview.explain_mview - it told me what you were missing (the errors about the refresh are in your alert log)

@?/rdbms/admin/utlxmv
exec DBMS_MVIEW.EXPLAIN_MVIEW ( mv => 'MV', stmt_id => 'TEST' );
select * from mv_capabilities_table;


It told me you were missing, sequence - count(expression) - count(*):

create materialized view log on t with sequence, rowid (t_id, group_id, pct) including new values;
create materialized view mv
refresh fast
on commit
as
select group_id,
sum ( pct ) pct,
count(pct) cnt_pct,
count(*) cnt_star
from t
group by group_id;


then it all works.

Awesome!! Thanks!

Greg, April 28, 2006 - 1:59 pm UTC

Didn't know that ... we'll look this over and try to understand it better ..

Thank-you so much!!!


problem with on commit for materialized view

William Moffatt, July 13, 2006 - 12:37 pm UTC

Hi Tom,

I've been trying to do something in a similar vein to this thread, but am having problems creating the materialized view.

The problem is that I want to enforce a constraint based on several rows in the table; I can do this via a trigger, however I want to make the constraint deferrable, and I can't reference a function in the constraint. (I'll show you what I'm doing later when I've established context).

I thus fell back on the materialized view technique, but am having problems.

17:01:50 > create table t(a number, b number, c number, d number, e number, primary key (b, c, d, e));

Table created.

17:01:54 > create unique index t_idx on t(a);

Index created.

17:02:02 > create materialized view log on t with primary key including new values
17:02:11 2 /

Materialized view log created.

17:03:37 > ed
Wrote file afiedt.buf

1 create or replace function f(i number) return number
2 as
3 s number;
4 ib number;
5 ic number;
6 id number;
7 begin
8 select b, c, d into ib, ic, id from t where t.a=i;
9 select sum(e) into s from t where t.b=ib and t.c=ic and t.d=id;
10 if (s = i)
11 then
12 return 0;
13 end if;
14 return s;
15* end;
17:04:02 > /

Function created.

17:04:03 > create materialized view t_mv
17:04:12 2 build immediate
17:04:18 3 refresh fast
17:04:20 4 on commit
17:04:22 5 as
17:04:23 6 select a, f(a) fa
17:04:26 7 from t
17:04:27 8 /
from t
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

The function is clearly a bit silly, but it hopefully demonstrates the idea of what I'm trying to do.

My intent was to put a constraint on the view, to check that the function returns the correct value (the real thing would return 0, so I'd add a constraint that f(a)=0)

Trying to add the constraint directly:

17:17:58> alter table t add constraint t_chk check(f(a)=0);
alter table t add constraint t_chk check(f(a)=0)
*
ERROR at line 1:
ORA-00904: invalid column name
)

won't let me refer to the function (which from the docs seems to be an invalid thing to do anyway).

I've tried various combinations of the log and the view, but have been unable to create one that is refresh fast/on commit; I managed to create one that was "refresh complete" and "on demand", but that isn't really useful to me I think - the data warehouse sections of the manuals were useful, but ultimately I must have misunderstood something.

I'd be grateful if you could point out my, no doubt stupid, mistake, or correct my intended technique.

I'm using 8.1.7.4

regards,

William


Tom Kyte
July 13, 2006 - 1:42 pm UTC

....
The problem is that I want to enforce a constraint based on several rows in the
table; I can do this via a trigger,
.....

Oh no you cannot, not without a LOCK TABLE command anyway!

that approach will never work, this thing called "read consistency" and "non-blocking reads" and "multiversioning" won't permit that logic to work as soon as more than one user logs in!!!

Thank goodness it didn't work syntactically - because the logic doesn't work..


state in english your requirements. state the rule here, not as code that won't work, but rather as a requirement.

William Moffatt, July 14, 2006 - 6:38 am UTC

The requirement is that all the rows in the table obey particular ordering.

Each row in the table has a name and a time and a "sequence". The sequence must be ordered correctly (based on time) for each row with the same name. The sequences start at "A" and increase, but can restart to "A" again, provided that they increase up to the previous maximum again (and no futher) - as an example:

name time interleave
x 9am A
x 10am B
x 11am C
x 12am A
x 1pm B
x 2pm C

Thus these are fine:
ABAB
ABCABCABC
AAA
but these are invalid:
BABA (out of order)
BC (doesn't start at A)
ABABC (missing C sequence)
ABACBC (out of order)

The tables I'm using can be created with the following; note that I've included the daystart table in case it's relevant - the time ordering of each sequence starts at the "start of the day" and then wraps around midnight; thus typcailly 23:30 is before 00:30.

tables used:

create table daystart(startofday date);

insert into daystart(startofday) values (to_date('200607142100', 'yyyymmddhh24mi'));

create table st(name varchar2(10), seq_time date, seq varchar2(1), st_id number);

create unique index st_idx on st(st_id);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101012330', 'yyyymmddhh24mi'), 'A', 1);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101012345', 'yyyymmddhh24mi'), 'B', 2);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101010015', 'yyyymmddhh24mi'), 'C', 3);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101010030', 'yyyymmddhh24mi'), 'A', 4);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101010100', 'yyyymmddhh24mi'), 'B', 5);

insert into st(name, seq_time, seq, st_id)
values ('seq1', to_date('000101010130', 'yyyymmddhh24mi'), 'C', 6);

insert into st(name, seq_time, seq, st_id)
values ('seq2', to_date('000101010900', 'yyyymmddhh24mi'), 'A', 7);

insert into st(name, seq_time, seq, st_id)
values ('seq2', to_date('000101011000', 'yyyymmddhh24mi'), 'A', 8);

insert into st(name, seq_time, seq, st_id)
values ('seq2', to_date('000101011300', 'yyyymmddhh24mi'), 'A', 9);

insert into st(name, seq_time, seq, st_id)
values ('seq3', to_date('000101011500', 'yyyymmddhh24mi'), 'B', 10);

insert into st(name, seq_time, seq, st_id)
values ('seq3', to_date('000101011530', 'yyyymmddhh24mi'), 'A', 11);

insert into st(name, seq_time, seq, st_id)
values ('seq3', to_date('000101011630', 'yyyymmddhh24mi'), 'B', 12);

insert into st(name, seq_time, seq, st_id)
values ('seq3', to_date('000101011700', 'yyyymmddhh24mi'), 'A', 13);

commit;

select * from st;

NAME SEQ_TIME S ST_ID
---------- ------------------- - ----------
seq1 0001/01/01 23:30:00 A 1
seq1 0001/01/01 23:45:00 B 2
seq1 0001/01/01 00:15:00 C 3
seq1 0001/01/01 00:30:00 A 4
seq1 0001/01/01 01:00:00 B 5
seq1 0001/01/01 01:30:00 C 6
seq2 0001/01/01 09:00:00 A 7
seq2 0001/01/01 10:00:00 A 8
seq2 0001/01/01 13:00:00 A 9
seq3 0001/01/01 15:00:00 B 10
seq3 0001/01/01 15:30:00 A 11
seq3 0001/01/01 16:30:00 B 12
seq3 0001/01/01 17:00:00 A 13

13 rows selected.

If the table is valid then the following equery will return zero rows:

select
st_id, seq_time, seq, name
from
(
select
rank() over (partition by name order by to_char(seq_time + (select 1 - (startofday - trunc(startofday)) from daystart), 'hh24mi')) - 1 r,
ascii(max(seq) over (partition by name)) - ascii('A') + 1 max_seq,
ascii(seq) - ascii('A') seq_val,
st_id, seq_time, seq, name
from st
)
where mod(r, max_seq)<>seq_val;


ST_ID SEQ_TIME S NAME
---------- ------------------- - ----------
10 0001/01/01 15:00:00 B seq3
11 0001/01/01 15:30:00 A seq3
12 0001/01/01 16:30:00 B seq3
13 0001/01/01 17:00:00 A seq3

4 rows selected.

(I know that this query will allow ABCAB through, which is technically invalid, however I'm not worried about this case at the moment)

Note that it is only important that the table be valid after commit; invalid rows can be inserted/updated/deleted, provided the errors are corrected prior to committing.

thanks for your time and help,

regards,

William


Tom Kyte
July 14, 2006 - 8:34 am UTC

....
The requirement is that all the rows in the table obey particular ordering.
.......

you have chosen the wrong type of database then - you appear to desire a flat file :)


I very much did not follow the example, did not get it?

William Moffatt, July 14, 2006 - 9:05 am UTC

Using the data from above:

1 select * from st where name='seq1'
2* order by to_char(seq_time + (select 1 - (startofday - trunc(startofday)) from daystart), 'hh24mi')
13:44:03 > /

NAME SEQ_TIME S ST_ID
---------- ------------------- - ----------
seq1 0001/01/01 23:30:00 A 1
seq1 0001/01/01 23:45:00 B 2
seq1 0001/01/01 00:15:00 C 3
seq1 0001/01/01 00:30:00 A 4
seq1 0001/01/01 01:00:00 B 5
seq1 0001/01/01 01:30:00 C 6

here the entries for "seq1", ordered by time, follow the sequence "ABCABC".

Similarly

1 select * from st where name='seq2'
2* order by to_char(seq_time + (select 1 - (startofday - trunc(startofday)) from daystart), 'hh24mi')
13:43:51 > /

NAME SEQ_TIME S ST_ID
---------- ------------------- - ----------
seq2 0001/01/01 09:00:00 A 7
seq2 0001/01/01 10:00:00 A 8
seq2 0001/01/01 13:00:00 A 9

seq2, ordered by time has the sequence "AAA".

However,

1 select * from st where name='seq3'
2* order by to_char(seq_time + (select 1 - (startofday - trunc(startofday)) from daystart), 'hh24mi')
13:46:49 > /

NAME SEQ_TIME S ST_ID
---------- ------------------- - ----------
seq3 0001/01/01 15:00:00 B 10
seq3 0001/01/01 15:30:00 A 11
seq3 0001/01/01 16:30:00 B 12
seq3 0001/01/01 17:00:00 A 13

seq3 has the sequence "BABA", which is out of order; it should be ABAB (or whatever - it's wrong, anyway).

I want to:

1. check that no entries in the table are out of order (which I can do with the query in the last post)

2. make sure that no one can modify the data in the table to create an invalid sequence (which I haven't yet figured out how to do)

To correct the error case here, either the rows with sequence "B" could be deleted, or they could both be moved forward an hour.

regards,

William


Tom Kyte
July 14, 2006 - 9:19 am UTC

we'll need to serialize at the level of "name" at the very least. Is there a parent table to this that has NAME as the primary key we can use?

William Moffatt, July 14, 2006 - 9:58 am UTC

There are two tables:
(both these tables have non-key data too)

create table names (name varchar2(10) primary key);

insert into names values ('seq1');

insert into names values ('seq2');

insert into names values ('seq3');

commit;

create table name_seq
(
name varchar2(10) references names(name),
seq varchar2(1),
primary key (name, seq)
);

insert into name_seq values ('seq1', 'A');

insert into name_seq values ('seq1', 'B');

insert into name_seq values ('seq1', 'C');

insert into name_seq values ('seq2', 'A');

insert into name_seq values ('seq3', 'A');

insert into name_seq values ('seq3', 'B');

commit;

alter table st add (
constraint st_fk
foreign key (name, seq)
references name_seq (name, seq));

BTW in case it's relevant, name is actually a composite key of two columns (I only used one, for simplicity).

regards,

William




Tom Kyte
July 14, 2006 - 12:32 pm UTC

My suggestion is going to be rather simple I'm afraid...

You want a transactional API, you don't want triggers at all here.  There is obviously no declaritive constraint that is appropriate - triggers would get nasty ugly really really fast.

In thinking about this, the only thing that makes sense is a package that accepts the information to be placed into the table - validates it's correctness and does the right thing.  No inserts/deletes by the application - a well formed transaction.

To do this "automagically" via triggers would take triggers, an on commit refresh materialized view (to fire the final validation).  

automagic would look something like this (caveats at the bottom)


ops$tkyte@ORA10GR2> create table st
  2  (name varchar2(10), seq_time date, seq varchar2(1), st_id number,
  3   primary key(name,seq_time,seq) );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter table st add (
  2    constraint st_fk
  3   foreign key (name, seq)
  4   references name_seq (name, seq));

Table altered.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace package state_pkg
  2  as
  3          type array is table of number index by names.name%type;
  4          g_data array;
  5  end;
  6  /

Package created.

<b>in a row trigger, we'll remember the "names" we've modified...</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace trigger st_bid_fer
  2  before insert or delete on st for each row
  3  declare
  4          l_name  names.name%type;
  5          l_rec   names%rowtype;
  6  begin
  7          if (inserting)
  8          then
  9                  l_name := :new.name;
 10          else
 11                  l_name := :old.name;
 12          end if;
 13
 14          select * into l_rec from names where name = l_name for update;
 15          state_pkg.g_data(l_name) := 1;
 16  end;
 17  /

Trigger created.

<b>here we remember the names AND serialize modifications by these names (only way to do this correctly - normally concurrent sessions cannot see others modifications - so we cannot have concurrent sessions touch the same "name")</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create materialized view log on st with rowid including new values;

Materialized view log created.

<b>we need an "on commit trigger"</b>

ops$tkyte@ORA10GR2> create materialized view st_mv
  2  refresh fast on commit
  3  as
  4  select count(*) cnt
  5    from st
  6  /

Materialized view created.

ops$tkyte@ORA10GR2> create trigger st_mv_trigger
  2  before insert or update or delete on st_mv
  3  declare
  4          l_idx   names.name%type;
  5  begin
  6          l_idx := state_pkg.g_data.first;
  7          while (l_idx is not null)
  8          loop
  9                  dbms_output.put_line( 'Verify data for name = ' || l_idx );
 10                  state_pkg.g_data.delete(l_idx);
 11                  l_idx := state_pkg.g_data.next(l_idx);
 12          end loop;
 13  end;
 14  /

Trigger created.
<b>and that is it - if you cannot validate the data, you will raise an exception.  You can just validate it procedurally (read out the data and validate it)</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into st(name, seq_time, seq, st_id) values ('seq1', to_date('000101011700', 'yyyymmddhh24mi'), 'A', 13);

1 row created.

ops$tkyte@ORA10GR2> insert into st(name, seq_time, seq, st_id) values ('seq2', to_date('000101011700', 'yyyymmddhh24mi'), 'A', 13);

1 row created.

ops$tkyte@ORA10GR2> insert into st(name, seq_time, seq, st_id) values ('seq3', to_date('000101011700', 'yyyymmddhh24mi'), 'A', 13);

1 row created.

ops$tkyte@ORA10GR2> commit;
Verify data for name = seq1
Verify data for name = seq2
Verify data for name = seq3

Commit complete.

ops$tkyte@ORA10GR2> delete from st where name = 'seq2';

1 row deleted.

ops$tkyte@ORA10GR2> commit;
Verify data for name = seq2

Commit complete.


<b>caveat; the array will possibly have values in it left over from other failed transactions!  There is no "before transaction begins trigger" to clean it out.  If the end user issues "rollback" after you did some inserts/deletes - those values stay and you'll validate them needlessly on the next commit that affects this ST table.</b>

 

Willam Moffatt, July 17, 2006 - 4:36 am UTC

Many thanks Tom, very helpful as always.

I can't lock down the table to allow only procedural access, so I will have to work with your alternative suggestion.

thanks again,

William


check constraint

kiran, September 10, 2008 - 8:22 am UTC

Hi Tom,

I have seen the example of restricting the rows in a table using check constraint. But i am confused why you have shown the materialized view example for the same. Anyways oracle is showing error using the check constraint. Pls help to understand
Tom Kyte
September 11, 2008 - 10:56 am UTC

a materialized view is just a summary table maintained by the database - it is just a table.

Not sure what part confused here - we have a table, we have a constraint on the table...


pragma SERIALLY_REUSABLE

Joaquin Gonzalez, September 22, 2008 - 11:31 am UTC

Hello,

About:

"caveat; the array will possibly have values in it left over from other failed transactions!
There is no "before transaction begins trigger" to clean it out. If the end user issues "rollback"
after you did some inserts/deletes - those values stay and you'll validate them needlessly on the
next commit that affects this ST table."

What if you use the pragma SERIALLY_REUSABLE in the package definition?

Thanks!
Tom Kyte
September 23, 2008 - 12:36 pm UTC

avoid triggers - what about when you have a recursive trigger firing, the before trigger trick fails.

avoid triggers
stay away from them

At most 1 row, but only value that exists in some other table...

Nicosa, October 31, 2008 - 11:10 am UTC

Hi Tom,

Always a pleasure to learn something new.
Using the MV trick described above, I can achieve to make sure there is at most one row in table B.

What I would like to achieve now, is to allow in B only a value that exists in A.
My problem is that the referenced column in table A is not unique (and can only be part of the primary key on A), which means I cannot enforce FK constraint (ORA-02270).

My test case :
create table a (
c1 varchar2(1 char) not null,
c2 varchar2(1 char)
);
create index a_idx on a(c1);

-- populating A with sample rows :
insert into a
select (mod(level,3)+1),chr(65+mod(level,26)) from dual connect by level <= 5;


create table b (c1 varchar2(1 char) not null);

create materialized view log on b with rowid including new values;

create materialized view b_mv refresh fast on commit
as select 'x' x, count(*) cnt from b group by 'x';
alter table b_mv add constraint b_mv_1rowmax check (cnt<=1);


-- this is throwing ORA-02270: no matching unique or primary key for this column-list :
alter table b add constraint b_fk foreign key (c1) references a(c1);

I've also tried to achieve it by using a more complex query for the MV but it seems I can't use subquery in MV's declaration to make sure there's at least 1 row in A with value from B.

I trying to get an efficient solution as the table A is supposed to grow real big.

Can this be achieved ?
(I'm on 10gR2)
Tom Kyte
November 02, 2008 - 4:51 pm UTC

...

What I would like to achieve now, is to allow in B only a value that exists in A.
My problem is that the referenced column in table A is not unique (and can only be part of the primary key on A), which means I cannot enforce FK constraint (ORA-02270).

...

that always implies a missing entity. Always - look back at your model, it doesn't make logical sense to have a foreign key pointing to something non-unique. I believe you are missing a piece of the puzzle.

more infos

Nicosa, November 03, 2008 - 4:00 am UTC

Hi Tom,

Thanks for taking time to answer.

You said :
"that always implies a missing entity. Always - look back at your model, it doesn't make logical sense to have a foreign key pointing to something non-unique. I believe you are missing a piece of the puzzle."

My model is the following :
The data in table A contains different sets of data grouped by their id (col C1 of table A in my testcase).

The table B is used to record which set of data from table A is the current one. This set of data is chosen by an "admin user", and I wanna make sure table B won't point to non-existing id from table A.
(What I mean by "admin user" is the owner of the schema. End-users will only be allowed to execute some package owned by that "admin user". The package will use table B to choose current set of data from table A (if there is one))

Maybe, I'm doing it the wrong way. How would you fill such a requierement ?

Guess what : You were right !

Nicosa, November 06, 2008 - 11:01 am UTC

Continuing however to implement my tool... I suddenly faced that something was actually missing in my data model...

Once again, thank you for this site, for sharing your knowledge, and for everything I learn from you.

Nico.

PS : As soon as you database knowledge is available to download as a .knowledge file, I'll be one of the first to get it.
(^_^)

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