Shulou(Shulou.com)06/01 Report--

The view is a virtual table that does not take up physical space because the definition statements of the view itself are stored in the data dictionary. The data in the view is obtained from one or more actual tables.

Materialized views: also materialized views, which contain actual data, take up storage space, and are often used in data warehouses.

Syntax for creating views

CREATE [OR REPLACE] [FORCE | NO FORCE] VIEWview_name [(alias [, alias]..)] As select_statement [WITH CHECK_ OPTION[CONSTRAINT constraint]] [WITH READ ONLY]

In grammar

OR REPLACE: if the view already exists, this option recreates the view.

FORC: if you use this keyword, a view will be created regardless of whether the base table exists or not

NO FORCE: this is the default value, and if you use this keyword, the view is created only if the base table exists

VIEW_NAME: the name of the view to create

ALIAS: specifies the alias of the expression or column selected by the view's query. The number of aliases must match the data of the expression selected by the view.

Select_statement:SELECT statement

WITH CHECK_OPTION: this option specifies that only rows that are accessible to the view can be inserted or updated, and constraint identifies the name specified by the CHECK OPTION constraint

WITH READ ONLY: this option ensures that no modification can be performed on the view.

Create a view with errors

If you use the FORCE option in the CREATE VIEW syntax, a view is created even if

The query defined by the view references a table that does not exist

The query defined by the view references invalid columns in the existing table.

The owner of the view does not have the required permissions.

In these cases, oracle only checks for syntax errors in the CREATE VIEW statement and, if the syntax is correct, creates a view and stores the definition of the view in the data dictionary, but the view cannot be used. This view is thought to have been created with errors. You can view errors with the name of the SHOW ERRORS VIEW view

View operation on a single table

SQL > create table order_master (ordernonumber (5) CONSTRAINT p_ord PRIMARY KEY

2 odate DATE,vencode number (5)

3 o_status char (1))

Insert data

SQL > insert into order_master values ('2010-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)

SQL > insert into order_master values ('2011-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)

Create a view with an order status of "p", indicating that you do not have permission to create a view

Grant SCOTT users permission to create views

Create a view

SQL > create view pen_view as select * from order_master where o_status ='p'

Query view

Modify the data through the view to change the order with a status of "p" to "d"

SQL > update pen_view SET o_status='d'where ostatic status

If the modification is successful, no records will be queried in the query view because the column in which the view is created as a condition has been modified

To avoid failing to query records after modifying the view, use the with check option statement to create a check constraint to prevent the above from happening, and you can use CONSTRAINT to specify the constraint name

SQL > create or replace view pen_view asselect * from order_master where oasis status

2 with check option constraintpenv

Update View

SQL > update pen_view set o_status='d'where ostatic status

Prompt with check option violates the where clause

Create a read-only view

SQL > create or replace view pen_view asselect * from order_master with read only

View view

Insert a record for a view

Create a view with errors

Because there is no venmast table

Create table venmast

SQL > create table venmast (id int)

Manually compile the error view you just created

View view

Create a view with an ORDER BY clause

SQL > create or replace view pen_view asselect * from order_master order by orderno

Complex view

DML statements are INSERT,DELETE,UPDATE statements that are used to modify data. Because the view is a virtual table, these statements can also be used with the view. In general, the data is not modified through the view, but the basic table is modified directly, because the conditioning is clearer. Using the DML statement on a view has the following restrictions: (relative to the table)

The DML statement can only modify one base table in the view

If the changes to the record violate the constraints of the base table, the view cannot be updated

If you create a view that contains join operators, DISTINCT operators, collection operators, aggregate functions, and group BY clauses, you will not be able to update the view.

If you create a view that contains pseudo columns or expressions, you cannot update the view.

The simple view is based on a single base table, excluding functions and grouping functions, and INSERT,UPDATE,DELETE operations can be performed in this view. These operations are actually inserting, updating, and deleting rows in the base table.

Complex views extract data from multiple functions, including functions and grouping functions, and complex views may not be able to perform DML operations.

You can delete a view using the

Materialized view

Materialized views correspond to normal views. When oracle uses normal views, it repeats all the sql statements that create views. If such SQL statements contain joins or ORDER BY clauses of multiple tables, and the table has a large amount of data, it will be very time-consuming and inefficient. In order to solve this problem, oracle proposed the concept of materialized view.

Materialized views are special views with physical storage that occupy physical space. Just like tables, materialized views are created based on tables, materialized views, and so on. It needs to synchronize with the source table and constantly refresh the data in the materialized view. There are two important concepts of materialized view: query rewriting and materialized view synchronization.

Query rewriting:

Rewrite the SQL statement. When users use SQL statements to query the base table, if the materialized views based on these base tables have been established, oracle will automatically calculate and use the materialized views to complete the query. In some cases, it can save query time and reduce the system Icano. This query optimization technique is called query rewriting. The parameter QUERY_REWRITE_ENABLED determines whether to use rewrite queries. When creating materialized views, you need to use ENABLE QUERY REWRITE to start the query rewriting function

You can view the value of this parameter through the SHOW command

Synchronization of materialized views:

Oracle provides two ways to refresh materialized views.

ON COMMIT: means that the materialized view refreshes the traffic submitted by the DML operation of the base table

ON DEMAND: it means that the materialized view is refreshed when the user needs it. It can be refreshed manually by DBMS_IVIEW.refresh or regularly by JOB.

After selecting the refresh method, you also need to select a refresh type. The refresh type values how to synchronize data between the base table and the materialized view when refreshing. Oracle provides the following four refresh types:

COMPLETE: completely refreshes the entire materialized view.

FAST: use incremental refresh to refresh only the changes made since the last refresh

When refreshing, FORCE:oracle will determine whether it can be refreshed quickly, and if so, use FAST refresh method, otherwise use COMPLETE method.

NEVER: materialized views are not refreshed at all

Create a materialized view

Prerequisites for creating materialized views:

Permission to create materialized views, permissions for QUERY REWRITE, access to tables involved in creating materialized views and permissions to create tables

Use SCOTT users to illustrate

1. Grant the appropriate permissions

SQL > show user

USER is "SYS"

SQL > grant create materialized view toscott

SQL > grant query rewrite to scott

SQL > grant create any table to scott

SQL > grant select any table to scott

two。 Create materialized view log

The materialized view log is needed when the user selects the FAST refresh type to incrementally synchronize the changes in the base table.

Create materialized views for SCOTT users' EMP table and DEPT table, so create materialized view logs for these two base tables

SQL > create materialized view log ondept with rowid

SQL > create materialized view log on empwith rowid

Create a materialized view

Create materialized views through CREATE MATERIALIEZED VIEW statements

SQL > create materialized viewmtrlview_test

2 build immediate

3 refresh fast

4 on commit

5 enable query rewrite as

6 selectd.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowide_rowid

7 from dept d,emp e whered.deptno=e.deptno

Where:

BUILD IMMEDIATE: this parameter means to create a materialized view immediately; you can also select BUILD DEFFERED, which means that it will not be executed immediately after the materialized view is defined, but will be delayed.

REFRESH FAST: type of refresh data Select FAST type

ON COMMIT: update the materialized view immediately after an update is submitted to the base table

ENABLE QUERY REWRITE: starts the query rewriting function, which is clearly stated to enable the query rewriting function when creating a materialized view.

AS: define the query statement that follows

Query body: the query content of the materialized view. The query result set of the SQL statement is output to the materialized view and saved in a table created automatically by oracle.

Delete materialized view

If you are interested, please scan the QR code below for more details for free

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.