View and index of Oracle Practice--PL/SQL Foundation

A view and index of the start of PL/SQL basics

PL/sql: Process language (Procedure Language) and structured language (structured Query Language)The combination of the programming language, is theSQLextensions that support multiple data types, such as large objects and collection types, can use control statements such as conditions and loops to create stored procedures, packages, triggers, and so on, toSQLthe execution of the statement adds program logic, andOracleservers, andOracletools are tightly integrated, with portability, flexibility, and security.

-----------------------------------------------------------------------------------------------------

View:

/*

View: Displays data from one or more tables in a way that is developed

can be considered a " virtual table " or " stored query "

The table on which the view is created is the base table

Advantages:1. provides an additional level of security ;

2. the complexity of hiding data ;

3. simplify the user's SQL statement ;

4. changes in the isolation base table

5. provide access to the data from another angle by renaming the column.

*/

--piercing view, keyword view as

Create or Replace view Emp_view  --to have the view permission as select  ename,sal,dname from emp,dept where Emp.deptno = dept.de Ptno;

-- querying from the view

SELECT * from Emp_view;

-- Delete a view

Drop View Emp_view;

/*

A connection view query is similar to a multi-table join query because views can be treated as virtual tables with only a few limitations:

1. only one underlying base table can be modified

2. You cannot update a view if you modify a constraint that violates the base table

3. If the view contains a join operator,DISTINCT keyword, set operator, aggregate function, or GROUP by clause, the view cannot be updated

4. If the view contains pseudo-columns or expressions, you will not be able to update the view

*/

--------------------------------------------------------------------------------------------------------------- --

Index:

/*

Index: Index is an optional structure associated with a table

advantages of Creating an index: 1. reduce the disk I/O

2. can improve the execution ability of query statement

Note: Creating unnecessary indexes can result in performance overhead for writing data and wasted disk space

*/

--1. Create a standard index

CREATE INDEX Emp_index on EMP (ename);--note that you cannot use or Replacealter index Emp_index rebuild;--to Refactor indexes Drop INDEX emp_index;--Delete indexes

-- Create a unique index

Create unique index Dept_uniqueindex on dept (dname);d ROP unique index dept_uniqueindex;

Article Source: http://blog.csdn.net/ysjian_pingcx/article/details/25646251