Materialized views as a means of controlling data integrity.
Data integrity control is one of the most important functions of a DBMS. The more carefully this control is organized, the easier it is to implement the applied logic, because the more restrictions are controlled by the database, the fewer “what if” variations should be foreseen when implementing the logic. At the same time, integrity monitoring is quite convenient to use and to verify the correctness of the application layer. Something like unit tests. “Extra” verification, sometimes can serve a very good service.
The traditional set of restrictions - the restriction of primary, foreign keys, uniqueness when using normalization allows to satisfy the vast majority of cases of control needs. However, in the case where the restriction is dependent on the values ​​in several tables and rows, these tools are not enough. Such constraints have to be implemented by trigger logic. And implementation is not always simple. The developer has to keep in mind that data modification can be carried out in a competitive environment, so you need to take care of locking resources yourself, while also trying to avoid mutual locks. Implementing a row constraint may require access to other rows of the same table, which in turn is a platform constraint — Oracle does not allow access to the currently mutable data set.
But there is another way. In some cases, it is possible to use restrictions imposed on materialized views that are updated upon the completion of transactions (fast refresh on commit). Such restrictions will work as deferred and will not allow the transaction to be committed if the integrity of the data is suddenly violated. In the context of a modifying transaction, restrictions may be violated. On the one hand, this simplifies data modification, on the other hand, it makes it difficult to identify the source of the error. In this article I would like to give a couple of simple examples of the implementation of such restrictions.
I would like to show the implementation of the approach on a simplified fictional example. It turned out to be quite difficult to find such an example so that it was simple enough for perception, but at the same time, so that the application of the approach was justified, do not blame me if something suddenly happened wrong.
')
Suppose we have the need to account for the goods in the context of the placement zone. The location in this case is the store (S) or warehouse (W).
Zone - physical or logical territory of each specific location. For example, a trading hall, or even shelves of a trading hall, a material room, a refrigerator, a zone of lost goods. Each location can have more than one zone of each type, but one zone of each type must necessarily be marked as primary. It will be used by default if the zone of operation is not explicitly defined. The main zone must be one and only one for each type of zone. This will be the first kind of restriction that we will try to implement.
The second type of restriction is the composition of the zones. In our example, we limit ourselves to setting the rules for the three types of zones:
Each posting record can be in three states - draft (W), active (A), not active (I). When the record is in the “Draft” state, we give the user the greatest freedom of action and allow violating this restriction.
To implement this restriction, we will create a materialized representation that will calculate the main zones for each type of placement zone, and from above we will impose a restriction that controls strict equality to the unit of the calculated value. For queries, on the basis of which the materialized views are built, a number of restrictions are defined , which, in addition, is strongly tightened by imposing the requirements for updating the fast method . In our case, we have an aggregate materialized view, and therefore we need to create a materialized view log for the zone table, including rowid and new values, the list of fields of which should include all values ​​that can affect the query result
We are also obliged to include in the result returned by the query the value of "count (*)"
It should be noted here: in order to evaluate whether a materialized view built on demand can be used to update the fast method, there is a dbms_mivew.explain_mview procedure. It is highly desirable to use it to control whether the fast update method is available for presentation. For example, if we had forgotten to specify count (*) in the query, the materialized view would have been successfully created and worked correctly when performing an insert operation. However, if modified, deleted, the value of primary_count would not be recalculated, which would violate the logic of our restriction. However, if we use explain_mview, oracle will helpfully tell us our miscalculation.
So, the materialized view is created, it remains only to add the restriction
Please note that the restriction is created by deferred. The fact is that in the process of updating the view of the Oracle, at some intermediate stage, it may turn out that the restriction will be temporarily violated. To avoid such false positives, it is better to set such restrictions deliberately delayed.
Check the work of this restriction.
Let's try to create a zone with a type that does not have a “primary” mark.
We will try to identify two main areas for placement with the same type.
This limitation differs from the previous one in that it relies on the values ​​of not one table, but two. Those. At the same time, it is necessary to satisfy the requirements of the fast update method for views with connections and aggregate views . But this is impossible. We cannot simultaneously output the rowid of the attached row and count (*) into the result. For this reason, it is necessary to build a cascade of materialized views. In one, the data sets will be joined, in the other - aggregation.
First you need to create a materialized veiw log for the placement table. For the zone table, the previously created log will be used.
Next, create join mivew. Unfortunately, the ANSI syntax here does not perceive oracle, use the old-style join.
Create a materialized veiw log for the join view.
Create an aggregated materialized view
Well, the limitations themselves
Check for restrictions:
Placement successfully created in the status of "draft". Let's try to activate it:
Not. You cannot activate a location if the necessary zones for its type are not defined for it.
It is very convenient to have such restrictions at the stage of testing, starting, stabilizing projects, when there is not enough confidence in the correctness of the logic, and the data can be modified by the application link.
Obviously, this approach should not be used in cases where it gives a noticeable performance drop.
In case of modification, refinement of the data scheme, the contents of the views may not be reliable and will require a complete update. If the time for a complete update of the materialized view can jeopardize the implementation of technical work regulations, you probably should not use this approach either.
The traditional set of restrictions - the restriction of primary, foreign keys, uniqueness when using normalization allows to satisfy the vast majority of cases of control needs. However, in the case where the restriction is dependent on the values ​​in several tables and rows, these tools are not enough. Such constraints have to be implemented by trigger logic. And implementation is not always simple. The developer has to keep in mind that data modification can be carried out in a competitive environment, so you need to take care of locking resources yourself, while also trying to avoid mutual locks. Implementing a row constraint may require access to other rows of the same table, which in turn is a platform constraint — Oracle does not allow access to the currently mutable data set.
But there is another way. In some cases, it is possible to use restrictions imposed on materialized views that are updated upon the completion of transactions (fast refresh on commit). Such restrictions will work as deferred and will not allow the transaction to be committed if the integrity of the data is suddenly violated. In the context of a modifying transaction, restrictions may be violated. On the one hand, this simplifies data modification, on the other hand, it makes it difficult to identify the source of the error. In this article I would like to give a couple of simple examples of the implementation of such restrictions.
Formulation of the problem
I would like to show the implementation of the approach on a simplified fictional example. It turned out to be quite difficult to find such an example so that it was simple enough for perception, but at the same time, so that the application of the approach was justified, do not blame me if something suddenly happened wrong.
')
Suppose we have the need to account for the goods in the context of the placement zone. The location in this case is the store (S) or warehouse (W).
Zone - physical or logical territory of each specific location. For example, a trading hall, or even shelves of a trading hall, a material room, a refrigerator, a zone of lost goods. Each location can have more than one zone of each type, but one zone of each type must necessarily be marked as primary. It will be used by default if the zone of operation is not explicitly defined. The main zone must be one and only one for each type of zone. This will be the first kind of restriction that we will try to implement.
The second type of restriction is the composition of the zones. In our example, we limit ourselves to setting the rules for the three types of zones:
- Storage zone (K) - the zone in which the goods are stored; this zone is mandatory for the warehouse, but can also be defined for the store.
- The area of ​​the sales area (S) is mandatory for the store and cannot be determined for the warehouse
- The zone of the lost commodity (L) - the logical zone to which the commodity will move, the status of which is not clear, needs clarification. Mandatory for both warehouse and store
Each posting record can be in three states - draft (W), active (A), not active (I). When the record is in the “Draft” state, we give the user the greatest freedom of action and allow violating this restriction.
Implementation
tables
create table location ( loc number primary key ,loc_type varchar2(1 char) check (loc_type in ('S','W')) ,status varchar2(1 char) not null check (status in ('W','A','I')) ,loc_desc varchar2(200 char) ); create table zone( zone number primary key ,loc number references location(loc) ,is_pirmary varchar(1 char) not null check (is_pirmary in ('Y','N')) ,zone_type varchar2(1 char) not null ,zone_desc varchar2(200 char) ); insert into location values (1,'S','W',' 1 '); insert into zone values (1,1,'Y','K',' 1'); insert into zone values (2,1,'Y','S',' 1'); insert into zone values (3,1,'Y','L',' 1'); commit; Limiting the number of main zones
To implement this restriction, we will create a materialized representation that will calculate the main zones for each type of placement zone, and from above we will impose a restriction that controls strict equality to the unit of the calculated value. For queries, on the basis of which the materialized views are built, a number of restrictions are defined , which, in addition, is strongly tightened by imposing the requirements for updating the fast method . In our case, we have an aggregate materialized view, and therefore we need to create a materialized view log for the zone table, including rowid and new values, the list of fields of which should include all values ​​that can affect the query result
create materialized view log on zone with rowid ,sequence (zone,loc,zone_type,is_primary) including new values We are also obliged to include in the result returned by the query the value of "count (*)"
create materialized view mv$zoneloc_pimary$chk refresh fast on commit as select loc ,zone_type ,count(decode(is_primary,'Y',1)) primary_count ,count(*) cnt from zone group by loc,zone_type; It should be noted here: in order to evaluate whether a materialized view built on demand can be used to update the fast method, there is a dbms_mivew.explain_mview procedure. It is highly desirable to use it to control whether the fast update method is available for presentation. For example, if we had forgotten to specify count (*) in the query, the materialized view would have been successfully created and worked correctly when performing an insert operation. However, if modified, deleted, the value of primary_count would not be recalculated, which would violate the logic of our restriction. However, if we use explain_mview, oracle will helpfully tell us our miscalculation.
SQL> set serveroutput on SQL> declare 2 result SYS.ExplainMVArrayType; 3 begin 4 dbms_mview.explain_mview(mv => 'select loc 5 ,zone_type 6 ,count(decode(is_primary,''Y'',1)) primary_counnt 7 --,count(*) cnt 8 from zone 9 group by loc,zone_type' 10 ,msg_array => result 11 ); 12 for i in 1..result.count 13 loop 14 dbms_output.put(rpad(result(i).capability_name,30,' ')); 15 dbms_output.put(' '||result(i).POSSIBLE); 16 dbms_output.put(' '||result(i).MSGTXT); 17 dbms_output.put_line(null); 18 end loop; 19 end; 20 / PCT F REFRESH_COMPLETE T REFRESH_FAST T REWRITE T PCT_TABLE F relation is not a partitioned table REFRESH_FAST_AFTER_INSERT T REFRESH_FAST_AFTER_ONETAB_DML F COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ANY_DML F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_PCT F PCT is not possible on any of the detail tables in the materialized view REWRITE_FULL_TEXT_MATCH T REWRITE_PARTIAL_TEXT_MATCH T REWRITE_GENERAL T REWRITE_PCT F general rewrite is not possible or PCT is not possible on any of the detail tables PCT_TABLE_REWRITE F relation is not a partitioned table PL/SQL procedure successfully completed So, the materialized view is created, it remains only to add the restriction
alter table mv$zoneloc_pimary$chk add constraint zone_loc_primary$chk check (primary_count=1) deferrable initially deferred; Please note that the restriction is created by deferred. The fact is that in the process of updating the view of the Oracle, at some intermediate stage, it may turn out that the restriction will be temporarily violated. To avoid such false positives, it is better to set such restrictions deliberately delayed.
Check the work of this restriction.
SQL> insert into location values (2,'S','W',' 2 '); 1 row inserted SQL> commit; Commit complete Let's try to create a zone with a type that does not have a “primary” mark.
SQL> insert into zone values (4,2,'N','S',' '); 1 row inserted SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated We will try to identify two main areas for placement with the same type.
SQL> insert into zone values (5,2,'Y','L',' '); 1 row inserted SQL> commit; Commit complete SQL> insert into zone values (6,2,'Y','L',' '); 1 row inserted SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated Restriction of the composition of the zones of placement
This limitation differs from the previous one in that it relies on the values ​​of not one table, but two. Those. At the same time, it is necessary to satisfy the requirements of the fast update method for views with connections and aggregate views . But this is impossible. We cannot simultaneously output the rowid of the attached row and count (*) into the result. For this reason, it is necessary to build a cascade of materialized views. In one, the data sets will be joined, in the other - aggregation.
First you need to create a materialized veiw log for the placement table. For the zone table, the previously created log will be used.
create materialized view log on location with rowid ,sequence (loc,loc_type,status) including new values; Next, create join mivew. Unfortunately, the ANSI syntax here does not perceive oracle, use the old-style join.
create materialized view mv$location$zone$join refresh fast on commit as select l.loc ,l.loc_type ,z.zone ,z.zone_type ,l.rowid l_rowid ,z.rowid z_rowid from location l ,zone z where z.loc(+) = l.loc and l.status in ('A','I') Create a materialized veiw log for the join view.
create materialized view log on mv$location$zone$join with rowid ,sequence (loc,loc_type,zone_type) including new values; Create an aggregated materialized view
create materialized view mv$location$zone$agg refresh fast on commit as select loc ,loc_type ,count(decode(zone_type,'K',1)) K_cnt ,count(decode(zone_type,'S',1)) S_cnt ,count(decode(zone_type,'L',1)) L_cnt ,count(*) cnt from mv$location$zone$join group by loc,loc_type; Well, the limitations themselves
alter table mv$location$zone$agg add constraint wh_zones_chk check(loc_type != 'W' or K_cnt > 0 and S_cnt = 0 and L_cnt > 0) deferrable initially deferred; alter table mv$location$zone$agg add constraint store_zones_chk check(loc_type != 'S' or K_cnt >= 0 and S_cnt > 0 and L_cnt > 0) deferrable initially deferred; Check for restrictions:
SQL> insert into location (loc,loc_type,status,loc_desc) 2 values (3,'S','W',' 3 '); 1 row inserted SQL> commit; Commit complete Placement successfully created in the status of "draft". Let's try to activate it:
SQL> update location set status = 'A' where loc = 3; 1 row updated SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.STORE_ZONES_CHK) violated Not. You cannot activate a location if the necessary zones for its type are not defined for it.
SQL> insert into zone(zone,loc,is_primary,zone_type) values (7,3,'Y','S'); 1 row inserted SQL> insert into zone(zone,loc,is_primary,zone_type) values (8,3,'Y','L'); 1 row inserted SQL> update location set status = 'A' where loc = 3; 1 row updated Commit complete Conclusion
When to use it
First of all, when the connection is so complicated that there is a high risk that the application developer will not be able to take everything into account, but the mismatch is critical. I used this approach for the first time when I designed a structure that was looped to itself by foreign keys through seven tables. Moreover, these tables are maintained by different business units. This restriction stands to this day. To this day, users find loopholes, send screenshots when this restriction works, and it’s impossible to reproduce to close the loophole in the application module, it requires a combination of circumstances from several users.It is very convenient to have such restrictions at the stage of testing, starting, stabilizing projects, when there is not enough confidence in the correctness of the logic, and the data can be modified by the application link.
When not to use it
Obviously, this approach should not be used in cases where it gives a noticeable performance drop.
In case of modification, refinement of the data scheme, the contents of the views may not be reliable and will require a complete update. If the time for a complete update of the materialized view can jeopardize the implementation of technical work regulations, you probably should not use this approach either.
Source: https://habr.com/ru/post/132727/
All Articles