视图

视图

创建视图 emp_view，连接system用户，将创建视图的权限授予用户

GRANT CREATE VIEW to scott;
conn scottiger;
CREATE VIEW emp_view
AS SELECT ename,job,sal FROM emp;

创建带检查约束的视图。 建立一个部门员工薪水介于1000~3000元的员工信息SAL_MID视图。

create view SAL_MID as select ename,job,sal from emp where sal between 1000 and 3000 with check option;

创建只读视图。建立一个部门号为10的10员工的只读视图DEP_10。

create view dep_10 as select ename,job,sal from emp where deptno=10 with read only;

创建一个包含各部门的部门号、部门人数和部门平均工资的视图。

create view  sal_avg as select deptno ,avg(sal) avgsal,count(*) total from emp group by deptno;

创建一个部门员工信息视图，此视图中包含员工名、部门名、工作职位、薪水等信息。

create view emp_dept as select empno,ename,dname,job,sal from emp ,dept where emp.deptno =dept.deptno;

更改视图emp_view

create or replace view emp_view as select empno,ename,job,sal from emp;

给emp_view重新命名为emp_view1.

rename emp_view to emp_view1;

删除视图emp_view

drop view emp_view;

查询SCOTT用户下的视图及视图定义信息。

col view_name format a10;
select view_name ,text from user_views;

索引

从emp表上创建不同的索引。

create index emp_idx on emp(ename);

create index emp_idx_j on emp(ename,job);

创建基于函数的索引。

create index emp_fun_idx on emp(UPPER(ename));

建立一个大表，对表中数据进行查询操作，比较建立索引前后的系统开销。

①建立单独的表空间wb和单独的临时表空间wbtemp,创建用户wb并为其指定表空间wb和临时表空间wbtemp，用于存放大量数据。

conn system/password
create tablespace wb datafile 'D:\app\EShyee\oradata\orcl\wb01.DBF' size 50M autoextEND on;

create temporary tablespace wbtemp tempfile 'D:\app\EShyee\oradata\orcl\wbtemp.dbf' size 300M;
CREATE user wb identified by wb default tablespace wb temporary tablespace wbtemp;

②建立表emp1,并用pl/sql程序为其生成一百万行数据。

grant connect,resource to wb;
conn wb/wb;
 create table emp1(id number(8,0),name varchar2(20),sex number(1,0),birth date ,phone varchar2(15));
 DECLARE 
vtoday date;
vcnt number(8,0):=1000000;
--PL
begin
SELECT sysdate into vtoday from dual;
for i in 1..vcnt LOOP
	INSERT into EMP1(id,name,sex,birth,PHONE) VALUES(i,'name'||i,mod(i,2),vtoday-i,'phone'||i);

	IF mod(i,100)=0 THEN
		commit;
	END IF; 
END LOOP;
end;

③通过计算表上的统计数据得到查询语句的开销。由于表中有大量的数据，为确保SQL*Plus显示查询的开销，而不显示查询结果。进行如下设置:

analyze table emp1 compute statistics;
set autotrace trace explain;

④对empl表进行查询，从中获取一行数据，

 select id,name,phone from emp1 where id=849765;

执行计划
----------------------------------------------------------
Plan hash value: 2226897347

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |  1654   (2)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| EMP1 |     1 |    25 |  1654   (2)| 00:00:20 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=849765)

⑤在empl表的id列上创建索引，重新执行查询，

create index idx_id on emp1(id);

 select id,name,phone from emp1 where id=849765;

执行计划
----------------------------------------------------------
Plan hash value: 3940360760

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

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

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

|   0 | SELECT STATEMENT        |        |     1 |    25 |     4   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1   |     1 |    25 |     4   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN      | IDX_ID |     1 |       |     3   (0)| 00:0
0:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=849765)

检查索引是否已经创建。

select index_name from user_indexes where table_name='emp1';

使用SELECT查看表emp1的索引信息。

select index_name,table_name,uniqueness,status from user_indexes where table_name='emp1';

修改idx_ id 索引。

alter index idx_id rename to emp1_idx_id;

删除idx_sex 索引。

DROP index idx_sex;