Views, sequences, indexes, synonyms

First, the View
Grammar:
CREATE [OR REPLACE] [force| Noforce] View View
[(alias[, alias] ...)]
As subquery
[With CHECK OPTION [CONSTRAINT CONSTRAINT]]
[with READ only];
1. Simple view:

CREATE VIEW: SQL> Create ViewTest_view as Select *  fromEmpwhereDeptno=Ten; Query view: SQL> Select *  fromTest_view; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ----------7782CLARK MANAGER7839  the-June-Bayi 2450 Ten7839KING President --November-Bayi  the Ten7934MILLER Clerk7782  at-January- the 1300 TenTo View the view structure:

DESC Test_view;

2. Create a complex view

Sql> CREATE VIEWAvg_sal_comm as SELECTDname department Name, D.deptno department number,COUNT(ename) Total number of departments,ROUND(AVG(NVL (SAL,0)),2) Department average salary,ROUND(AVG(NVL (COMM,0)),1) departmental average funding fromEMP E Right JOINDEPT D onE.deptno=D.deptnoGROUP  byDname,d.deptnoORDER  byD.deptno; SQL> SELECT *  fromAVG_SAL_COMM Department Name Department number department total number of departments average salary department fund-------------- ---------- ---------- ------------ ------------ACCOUNTINGTen 3 2916.67 0 the - 5 2175 0SALES - 6 1566.67 366.7OPERATIONS + 0 0 0Testseq94 0 0 0

3. In the view definition, you can use the with READ only option to ensure that DML operations cannot be performed on the view.

4. Delete View
DROP VIEW view_name;

Two, sequence
1, create sequence
-Creates a sequence value named Dept_deptno for use with DEPT tables. Do not set the CYCLE option.

 create   SEQUENCE Dept_deptnoincrement  by  1  start  with  91  maxvalue  100  nocache  --  nocycle;  /*  nextval returns the next available sequence value, each time it is accessed, a new value is generated: Currval returns the current sequence value. The Currval pseudo-column can contain a value only if nextval is accessed. So just create a good sequence, the first time to access the currval times wrong. You must first access nextval and then access Currval.  */ 

2, the use of the sequence and query

Sql> SELECTDept_deptno. Currval fromDUAL;SELECTDept_deptno. Currval fromDUAL*Section1line error: ORA-08002: Sequence Dept_deptno. Currval has not defined SQL in this session> SELECTDept_deptno. Nextval fromDUAL; Nextval---------- theSQL> SELECTDept_deptno. Currval fromDUAL; Currval---------- theSQL>--using a sequence to insert data into a table deptSql> Insert  intoDeptValues(Dept_deptno.nextval,'Testseq','Testloc'); created1line. --If a sequence is established with the NoCache option, you can view the next available sequence value by querying the User_sequences table without increasing the current value of the sequence.Sql> SELECT *  fromuser_sequences; Sequence_name min_value max_value increment_by C O cache_size last_number------------------------------ ---------- ---------- ------------ - - ---------- -----------Dept_deptno1  - 1n N0  theSQL>=========SELECT *  fromall_sequences;SELECT *  fromDba_sequences;

3. Modify the sequence
You can change the increment, maximum, minimum, loop, or cache options for a sequence. Cannot modify the initial value of the sequence, otherwise it will error: ORA-02283: Unable to change the startup serial number
ALTER SEQUENCE Dept_deptno INCREMENT by 2;
ALTER SEQUENCE Dept_deptno MAXVALUE 200;

4. Deleting a sequence
DROP SEQUENCE Sequence_name;
Third, index
1. Create an index
Auto Create: Indexes are created automatically when a table is created, if a PRIMARY key or a unique constraint is specified.
Create manually: A user can establish a non-unique index on a column to speed up queries based on that row.
CREATE INDEX index_name
On table (column[, column] ...);
--Create an index to increase the speed of access to the ENAME column of the table EMP.
CREATE INDEX emp2_ename_idx on EMP2 (ename);
--When to create an index
The column to create the index is used frequently in the WHERE clause or join condition.
This column contains a number of different values.
The column contains a large number of null values.
The number of data rows in the table is very large, and only 2–4% data rows are queried.
--When do I need to create an index
The table is empty.
Columns are not used frequently in query conditions.
Most queries based on this table have a much larger amount of data than 2–4% rows.
Tables are frequently modified.

2. View Index
The User_indexes Data dictionary view contains the name of the user-created index and its uniqueness.
The User_ind_columns view contains the name, table name, and column name of the index.

Selectic.index_name, Ic.column_name,
Ic.column_position col_pos,ix.uniqueness
Fromuser_indexes IX, User_ind_columns IC
Whereic.index_name = Ix.index_name
Andic.table_name = ' EMP2 ';

3. Function-based indexing
A function-based index is an expression-based index.
An index expression consists of a table's columns, constants, SQL functions, or user-defined functions.
sql> CREATE TABLE Test (col1 number);
Sql> CREATE INDEX test_index on test (COL1,COL1+10);
Sql> SELECT col1+10 from test;

4. Delete Index
To delete an index, it must be the owner of the index, or have the permission to drop any index.
Removes the EMP_ENAME_IDX index from the data dictionary.
sql> DROP INDEX Emp2_ename_idx;
The index has been deleted.

Iv. synonyms
Simplifies access to objects in the database by creating a synonym (another name for the object). Shortened the name length of the object.
CREATE [public] synonym synonym
for object;
Example: Create a short name for the view Query_tabspace Q_space;

To Create a view:CREATE VIEWQuery_tabspace as(SELECT /*+no_merge (A) no_merge (B)*/B.tablespace_name table space Name,ROUND((b.bytes/1024x768)/1024x768,2) Total space size mb,nvl2 (a.bytes,ROUND((b.bytes-NVL (A.bytes,0))/1024x768/1024x768,2), b.bytes) already used size MB,NVL2 (a.bytes,ROUND(NVL (A.bytes,0)/1024x768/1024x768,2),0) is not using size Mb,nvl2 (A.bytes,to_char (ROUND(((B.bytes-NVL (A.bytes,0))/B.bytes)* -,2),'990.0'),' -')||'%'used rate from(SELECTTablespace_name,SUM(BYTES) BYTES fromDba_free_spaceGROUP  bytablespace_name) A, (SELECTTablespace_name,SUM(BYTES) BYTES fromDba_data_filesGROUP  bytablespace_name) BWHEREB.tablespace_name=A.tablespace_name (+) ) query view:SELECT *  fromquery_tabspace; Create synonyms:CREATESynonym Q_space forquery_tabspace; query synonyms:SELECT *  fromq_space; Remove synonymsDROPSynonym Q_space;

Views, sequences, indexes, synonyms