Oracle Study Notes-views and Indexes

1. VIEW)
1.1 concepts
View ----- is a "logical table" defined by a SELECT query statement (which can be a single table or multi-Table query). It is a "virtual table" with only definitions but no data ". when creating a view, you only save the definition information of the view in the data dictionary. instead of copying the actual data to any place, you do not need to allocate storage space for the view in the tablespace. A view is a way to view and manipulate data in a base table. You can use a view like a table.
Tips: there are no restrictions on query views. Operations on insert, update, or delete views are limited. All operations on views affect the base table of the view; to Prevent Users From indirectly modifying the data of the base table through a view, you can create a view as a read-only view (with the with read only option ).
The data in the view is automatically updated as the base table is updated.
A view is like a "window" of a base table. Many management operations can be implemented through this "window". In a view, the maximum number of columns can be defined as 1000, which is the same as that of a table.
View constraints: constraints can be generated on the view, such as "primary key constraints, unique key constraints, foreign key constraints, and check constraints. however, the constraints on the view are not mandatory, but declarative. the syntax of the view constraint is the same as that of the table. when creating a view, you can use the with check option to define a check constraint for the view so that it can only query and operate record rows that meet the check constraint.
1.2 role
1) provides a variety of data representations. You can use different methods to present the data in the base table to the user, so as to meet the user's usage habits (main means: use aliases ).
2) Hide the logic complexity of data and simplify query statements. Multi-Table query statements are generally complex, and you need to understand the relationship between tables. Otherwise, errors may occur; if you create a view based on such a query statement, you can directly perform a "simple query" on the view to obtain the result. in this way, the data complexity is hidden and the query statements are simplified. this is one of the reasons why oracle provides various "data dictionary views". all_constraints is a view that contains two subqueries and connects nine tables (in catalog. defined in SQL ).
3) execute some queries that must use views. some queries can be completed only with the help of views. for example, some queries need to connect one table after grouping statistics to another table. In this case, you can create a view based on grouping statistics, connect the view to another table in the query.
4) provides some security assurance. A view provides a controllable way for different users to see different columns without allowing access to sensitive columns, this ensures that sensitive data is not visible to users.
5) simplify the management of user permissions. You can grant view permissions to users without granting permissions to certain columns in the base table. This simplifies the definition of user permissions.
1.3 create a view
Permission: to create a view in the current solution, you must have the create view system permission. To create a view in other schemes, you must have the create any view system permission. the view function depends on the view owner's permissions.
Syntax: create [or replace] [force] view [schema.] view_name
[(Column1, column2,...)]
As
Select...
[With check option] [constraint constraint_name]
[With read only];
Tips:
Or replace: If a view with the same name exists, use the new view to replace the existing view.
Force: "force" creates a view, regardless of whether the base table exists or whether it has the permission to use the base table.
Column1, column2 ,...: View column name. The number of column names must be the same as the number of columns in the select query. If the select query contains a function or expression, the column name must be defined for it. in this case, you can use column1 and column2 to specify the column name, or you can specify the column name in the select query.
With check option: specify that the dml operation on the view must meet the "view subquery" condition, that is, "check" the addition, deletion, and modification operations on The View ", the data that requires the addition, deletion, and modification operation must be the data that can be queried by the select query. Otherwise, the operation is not allowed and an error message is returned. by default, "the system does not check" whether these rows can be retrieved by the select query before addition, deletion, and modification.
With read only: the created view can only be used to query data, but cannot be used to change data.
1.3.1 create a simple view
A view created based on a single table that does not contain any functions, expressions, or grouped data.
Example 1: Create a vw_emp view based on the emp table
Create view vw_emp
As
Select empno, ename, job, hiredate, deptno from emp; -------- create a simple view desc vw_emp; -------- use it like a table
Select * from vw_emp where deptno = 10; -------- Query
Insert into vw_emp values (1234, 'jack', 'cler', '29-January 1, April-1963 ', 10); -------- added
Update vw_emp set ename = 'andy Lau 'where ename = 'jack'; -------- update
Delete vw_emp where ename = 'andy Lau '; -------- delete

Create view vw_emp_readonly
As
Select empno, ename, job, hiredate, deptno from emp
With read only; -------- creating a read-only view can only be used to execute the select statement delete from vw_emp_readonly where empno = 1234; -------- failed to delete create view vw_emp_check
As
Select empno, ename, job, hiredate, deptno
From emp where deptno = 10
With check option constraint vw_emp_chk;
-------- Create check view: Check the addition, deletion, and modification operations through the view,
The data required for the add, delete, and modify operations must be the data that can be queried by the select query.
Insert into vw_emp_check
Values (1235, 'jack', 'cler', '29-January 1, April-1963 ', 20 );
-------- Department 20 is out of the query scope and violates inspection restrictions
Delete from vw_emp_check where empno = 1234;
-------- The deleted data is within the query range and does not violate inspection Constraints 1.3.2 create a connection View
A view created based on multiple tables. That is, a query for a defined view is a join query. The main purpose is to simplify connection Query
Example 1: Query department and employee information numbered 10 and 30
Create view vw_dept_emp
As
Select a. deptno, a. dname, a. loc, B. empno, B. ename, B. sal
From dept a, emp B
Where a. deptno = B. deptno
And a. deptno in (10, 30); select * from vw_dept_emp;

1.3.3 create a complex view
A view that contains functions, expressions, or grouped data. The main purpose is to simplify the query. It is mainly used to perform query operations, not DML operations.
Note: When a select query in a view contains a function or expression, you must define a column alias for it.
Example 1: query the average salary, total salary, maximum salary, and minimum wage of each position.
Create view vw_emp_job_sal
(Job, avgsal, sumsal, maxsal, minsal)
As
Select job, avg (sal), sum (sal), max (sal), min (sal)
From emp
Group by job; select * from vw_emp_job_sal; 1.3.4 force create View
Normally, creating a view fails if the base table does not exist. However, you can use the force option to forcibly create a view (premise: the statement for creating a view has no syntax error !), The view is invalid.
Example 1:
Create force view vw_test_tab
As
Select c1, c2 from test_tab; -------- "Warning: The created view has a compilation error ." Select object_name, status from user_objects
Where object_name = 'vw _ TEST_TAB '; -------- view status: INVALID
Select * from vw_test_tab; -------- error: create table test_tab -------- create table first
(C1 number (9) primary key, c2 varchar2 (20), c3 varchar2 (30 ));
Select * from vw_test_tab; -------- view with invalid automatic compilation
Select object_name, status from user_objects
Where object_name = 'vw _ TEST_TAB '; -------- view status: VALID 1.4 change view
Before changing (or redefining) a view, consider the following:
One-because the view is only a virtual table with no data, changing the view only changes
Define information. All basic objects in the view will not be affected.
2 -- after the view is changed, all views and PL/SQL programs dependent on the view will change to INVALID (INVALID) state.
3 -- if the previous view has the with check option, but this option is not used for redefinition,
This option is deleted automatically. 1.4.1 modify view definition
Method -- execute the create or replace view statement. This method replaces the first deletion ("permission will also be deleted ")
The created method retains the permissions on the view, but the stored procedures and views related to the view become invalid.
Example 1: create or replace view v_test_tab
As
Select c1, c2 | '+' | c3 c23 from test_tab;

1.4.2 view recompilation
Syntax: alter view name compile;
Purpose: when the base table on which the view depends changes, the view becomes invalid ". To ensure that this change does not affect the view and
For other objects, you should use the alter view statement to "explicitly recompile" the view, so that you can find the view again before running it.
Compilation error. After the view is re-compiled, if an error is found, the object dependent on the view will also become invalid. If no error is found,
The view changes to "valid ".
Permission: to recompile views in other modes, you must have the alter any table system permission.
Note: when accessing the changed views of the base table, oracle will "automatically recompile" these views.
Example 1: select last_ddl_time, object_name, status
From user_objects
Where object_name = 'v _ TEST_TAB '; -- View status: alter table test_tab modify (c2 varchar2 (30); -- modify the length of column c2 select last_ddl_time, object_name, status
From user_objects
Where object_name = 'v _ TEST_TAB '; -- view status: alter view v_test_tab compile is invalid; -- explicitly recompile select last_ddl_time, object_name, status
From user_objects
Where object_name = 'v _ TEST_TAB '; -- View status: VALID
Think: What if the above Code modifies a table name instead of the column length?
<Warning: the changed view has a compilation error. View status: Invalid>

1.5 delete a view
You can delete any view in the current mode;
To delete a VIEW in other modes, you must have the permission to drop any view;
After a view is deleted, its definition is deleted from the dictionary and the "Permissions" granted to the view are also deleted.
After a view is deleted, other views and stored procedures that reference the view will become invalid.
Example 1: drop view vw_test_tab; 1.6 View
Use Data Dictionary View
Dba_views -- DBA view describes all views in the database
All_views -- ALL views describe the "accessible" View
User_views -- USER View description "USER-owned" view dba_tab_columns -- DBA view description column (or table column) of all views in the database)
All_tab_columns -- ALL view describes the columns (or table columns) in the "accessible" view)
User_tab_columns -- Example 1: Query Information of all views in the current solution
Desc user_views;
Set long 400;
Select view_name, text from user_views;
Example 1: query the column name information of the specified view (or table) in the current scheme.
Select * from user_tab_columns where table_name = 'vw _ DEPT '; 1.7 perform DML operations on the connection View
All DML operations performed on the view will be completed on the base table;
There are no restrictions on the select view, but there are some restrictions on insert/delete/update. 1.7.1 steps and principles for performing DML operations on the View
Step 1: Merge the SQL statements for the view and the definition statements for the view (stored in the data dictionary) into one SQL statement.
Step 2: "parse" (and optimize) the merged SQL statement in the shared SQL area of the Memory Structure
Step 3: "execute" the SQL statement
Example:
Suppose the Definition Statement of v_emp is as follows:
Create view v_emp
As
Select empno, ename, loc
From employees emp, orders ments dept
Where emp. deptno = dept. deptno and dept. deptno = 10; when the user executes the following query statement:
Select ename from v_emp
Where empno = 9876; oracle combines the SQL statement with the view definition statement into the following query statement:
Select ename
From employees emp, orders ments dept
Where emp. deptno = dept. deptno and dept. deptno = 10
And empno = 9876; then, parse (and optimize) the merged query statement and execute the query statement 1.7.2 query "updatable" (including "add, delete, modify") columns in the view
Use Data Dictionary View
Dba_updatable_columns -- display the updatable status of all columns in all views of the database
All_updatable_columns -- display the updatable status of all columns in a user-accessible View
User_updatable_columns -- display the updatable status of all columns in the user-owned View
Example 1: query which columns in v_stu_dept are updatable.
Select table_name, column_name, insertable, updatable, deletable
From user_updatable_columns
Where table_name = 'v _ STU_DEPT '; 1.7.3 updatable connection View
If the select query for creating a connection view does not contain the following structure,
And comply with the "update rules" of the connection view ",
The connection view is "updatable:
One: Set operators (union, intersect, minus)
2: DISTINCT keyword
3: group by, order by, connect by or start with clause
4: subquery
5. Grouping Functions
6. columns to be updated are not defined by the "column expression ".
7. All not null columns in the base table belong to this view. 1.7.4 key-value saving table
If a "base table key" (primary key and unique key) in the connection view still exists in its view,
The "base table key" is still the "key in the connection View" (primary key and unique key );
That is, a column is a primary key in the base table | unique key, still a primary key in the View | unique key
The base table is called "key-value saving table ".

Generally, a connection view consists of two tables with a primary-foreign key relationship. A foreign key table is a key-value storage table, but a primary key table is not. 1.7.5 connection view update rules
One: General Principle -- (Lecture)
Any DML operation can only update the table that stores the key values in the view,
That is, "You cannot modify multiple base tables through the connection View ";
In DML operations, "Only columns defined in the connection view can be used ";
All columns in the "self-join View" are updatable (add, delete, and modify ).
Ii. insert Criterion
In the insert statement, columns (including "Join columns") in the "Save table with non-key values" cannot be used ");
In the view where the insert operation is executed, at least the "include" key value should be saved to all columns in the table with constraints set;
If the with check option is used when defining the connection view,
Then, "no" executes the insert operation on the Connected View.
3: update criteria
Columns in the key-value storage table can be updated;
If the with check option is used when defining the connection view,
The connection columns in the connection view (generally "common columns") and the "other common columns" in the base table are "non-updatable,
The connection column and other columns except the common columns can be updated.
Iv. delete Criterion
If the with check option is used when defining the connection view,
You can still perform the delete operation on the connection view. 2 Index
The goal is to improve the query speed. When users are not satisfied with the query speed and need to adjust the database performance, the indexing should be prioritized.
The index concept in the database is very similar to that in the book index,
The difference is that database indexes are used to find specific rows in a table.
Index disadvantages:
When you add or delete a row to a table, it takes additional time to update the index of the row.
Index creation time:
When you need to retrieve a few rows from a large table, you should create an index for the column.
Basic Principles:
Indexes are very useful when the number of rows to be retrieved in a single query <= 10% of the total number of rows in the table.
Candidate index columns:
It should be a column used to store a large range of values
Automatic index creation:
The table's primary key and unique key will automatically create an index
2.1 syntax
Create [unique] index name ------- unique specifies that the value in the index column is unique. We recommend that you use idx as the index name.
On Table Name (column 1, column 2...) -------- you can create an index for multiple columns. This index is called a "Composite Index"
[Tablespace name]; -------- after saving, the index will be stored in the user's default tablespace
Tip:
For performance reasons, indexes and tables should be stored in different tablespaces.
Example:
Create index idx_customers_lastname on MERs (last_name );
2.2 query the index and index column information
Select * from user_indexes where table_name in ('customer', 'ployees ');
Select * from user_ind_columns where table_name in ('customer', 'ployees ');
2.3 modify an index
Alter index name rename to new index name
2.4 delete an index
Drop index name