1.1. 事务
事务就是业务上的一个逻辑单元，它能够保证其中对数据所有的操作会全部成功，要么全部失败。
事务开始于一条可执行的SQ语句，继续执行事务主题，然后结束于以下的任意一种情况。
显示提交(COMMIT)：当事务遇到COMMIT指令时，将结束事务并永久保存所有所有更改的数据。
显示回滚(ROLLBACK):当事务遇到ROLLBACK指令时，也将结束事务的执行，但是此时它回滚所有更改的数据到事务开始时的原始值，即取消更改。
DDL语句:一旦用户执行了DDL(数据定义语言，如CREATE、DROP)语句，则之前的所有DML(数据操作语言)操作作为一个事务提交，这种提交称为“瘾式提交”。
正常结束程序：如果oracle数据库应用程序正常结束，如使用SQL*PLUS工具更改了数据，而正常退出该程序(输入“exit”)，则oracle自动提交事务。
非正常地结束程序：当程序崩溃或意外终止时，所有数据更改都被回滚，这种回滚称为隐士回滚。
1.1.1. 事务的特点
在前面的数据库学习中，已经知道事务的四个特性，简写为ACID特性
原则性：以转账操作为例，转出账户余额减少和转入账户余额增加是两个DML语句，但是必须作为一个不可分割的完整操作，要么同时成功，要么同时失败，只转出而没有转入显然是不可接受的。
一致性：无论是在事务前、事务中、事务后，数据库始终处于一致的状态。
隔离性：在某个时间段，肯定有很多人都在转账，每个人额转账都是在自己的事务中，所以在一个数据库中，会有很多事务同时存在。虽然同时存在很多事务，但是事务之间不会相互影响。
持久性：如果事务提交成功，则数据修改永远生效；如果是回滚，则数据完全没有被修改，就相当于没有这件事情发生。
1.1.2. 事务控制
使用COMMIT和ROLLBACK实现事务控制
COMMIT：提交事务，即把事务中对数据库的修改进行永久保存
ROLLBAC：回滚事务，即取消对数据库所做的任何修改。
使用COMMIT和ROLLBACK实现事务控制
使用AUTOCOMMIT实现事务的自动提交
oracle提供了一种自动提交DML操作的方式，这样一旦用户执行了DML操作，如update、delete等，数据就会自动提交。
使用AUTOCOMMIT实现事务的自动提交，设置了AUTOCOMMIT为NO
从案例中可以看出即便做了回滚操作，查询结果仍然包含插入数据。关闭自动提交只需将AUTOCOMMIT设置为OFF，即SET AUTOCOMMIT OFF
程序异常退出对事务的影响
在事务执行过程中，程序会发生异常，如实例崩溃、断开连接等。此时事务结束并回滚所有的数据更改。
1.2. 索引
1.2.1. oracle索引思想
oracle索引通过存储特定列的排序数据快速的快速的访问表行，使用这些排序值很容易查找到相应的表行，此方法类似于使用一本书的索引快速找到你所感兴趣的条目。索引可以查找有某个列值的行，而不必查看表中的其他行。因此适当使用索引将会最大程度的减少使用稀缺的磁盘I/O。索引是一种可选的数据库结构，并且完全由oracle维护。
使用索引涉及快速的检索查询结果与低速的更新和插入之间的折中问题。折中的第一部分，即快速执行查询是显然的：如果你查找一个排序索引而不执行全表扫描，那么查询会更快。但是在更新、插入、删除有索引的表行时，索引也必须随之更新，插入或删除。则就使得这些处理过程在有索引的表上更耗时。另外不要忘记，大表有大索引，大磁盘除了存储表数据外还要用来容纳这些索引。
通常一个表大部分时间用来读数据(例如数据仓库)，那么最好多用索引。如果数据库为OLTP型，有大量的插入、更新、删除操作，则最好少用索引。
一个oracle表所使用的索引数目无限定，但是索引会涉及性能方面的问题。
1.2.2. 索引类型
oracle索引有几种类型，以下是最重要的几种:
唯一和非唯一索引：唯一索引是基于唯一列的索引，通常类似于员工编号。尽管你可以明确创建唯一索引，但是oracle建议不要这样做，它建议使用唯一约束。当在表的列中放置唯一约束时，oracle将自动创建那些列的唯一索引。
主索引和次索引：主索引是表的唯一索引，它始终拥有一个值且不能为空。次索引是同一个表中的其他索引，它不是唯一的。
组合索引：组合索引是包含在同一个表中的两个列或更多列的索引。该索引还称为拼接索引。如果一个表中，没有一个单独的可唯一标识一行的列的话，更应该使用组合索引。
关于索引和键
一般我们会看到术语“索引”，“键”交换使用，但是这两个术语实际上是不同的。索引是存储在数据库中的一个物理结构。你可以创建、更改和删除一个索引，索引主要用来加速表数据的访问。然而，键纯粹是一个逻辑的概念。键代表创建用来实施业务规则的完整性约束，索引和键的混淆是用于数据库使用索引来实施完整性约束。我们只需知道两者是不同的即可。
1.2.3. 创建索引的准则
尽管大家都知道索引会增强数据库的性能，但是也需要了解如何使其更好的工作。在表中设置不必要或不相关的索引将降低性能。
以下是关于创建oracle表的有效索引的准则
如果需要访问的数据不超过表中的4%或5%，则可以使用索引。访问表中的行数据的另一种方法是按从头至尾的顺序读取整个表，这种方法称为“全表扫描”。全表扫描适用于请求的数据占全表数据百分比较高的查询。请记住，用索引来检索行需要两个读操作：即读索引和读表。
相对较小的表应避免使用索引。全表扫面很适合小表，对于小表不需要同时存储表的数据和索引的数据。
为所有表创建主键。在指定一列作为主键时，oracle自动创建该列的索引
对包含在多表联结操作中使用列建立索引
对在where子句中频繁使用的列建立索引
对包括在ORDER BY和GROUP BY操作的列或设计排序的UNION和DISTINCT等其他操作中的列建立索引。由于索引已经被拍讯，因此执行前述操作的排序要求将显著减少。
由长字符串组成的列通常不是索引的候选列。
被频繁更改的列由于涉及开销问题，理论上不索引。
只对有高选择性的表建立索引。即选择对几乎没有相同值的表建立索引
使用索引数目较少
当唯一列值可能不唯一时需要使用组合索引。在组合索引中，驱动列或第一列应该为选择性最强的列。
1.2.4. 索引的模式
1.2.5. 创建B-树索引
以SCOTT用户为例：默认是被禁用的，使用alter user 命令解锁SCOTT用户，修改已经过期的密码
SQL> conn / as sysdba
Connected.
SQL> alter user scott account unlock;
User altered.
[oracle@oraclesrv ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 19 04:36:51 2015
……
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password:
Retype new password:
Password changed
Connected to:
……..
SQL>
从EMP表中查找员工‘KING’,此时是不会使用索引的。因为ename上是没有索引的。此时查找时会把表中的所有的行读一遍，最后取出“KING”，如果EMP表中有10000行，就要扫描一万行，在取出其中的一行，这样显然是不合理的
SQL> select * from emp where ename=’KING’;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000
10
在ename上创建索引
如何查看列上有没有索引
SQL> desc user_ind_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_POSITION NUMBER
COLUMN_LENGTH NUMBER
CHAR_LENGTH NUMBER
DESCEND VARCHAR2(4)
查看EMP目前有哪些索引，只有EMPNO列是有索引的。
SQL> col column_name for a20
SQL> select index_name,column_name from user_ind_columns where table_name=’EMP’;
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
PK_EMP EMPNO
创建索引，此时sql语句就会使用索引了
SQL> create index emp_ename_i on emp(ename);
Index created.
使用索引查询，索引代价为1
SQL> set autot trace exp
SQL> select * from emp where ename=’KING’;
Execution Plan
Plan hash value: 549418132
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 | //使用了索引
Predicate Information (identified by operation id):
2 – access(“ENAME”=’KING’)
Note
- dynamic sampling used for this statement (level=2)
SQL> set autot off
删除索引，再次查询使用全表扫描，查询代价为3
SQL> drop index emp_ename_i;
drop index emp_ename_i;
Index dropped.
SQL> set autot trace exp
SQL> select * from emp where ename=’KING’;
Execution Plan
Plan hash value: 3956160932
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 – filter(“ENAME”=’KING’)
Note
- dynamic sampling used for this statement (level=2)
SQL> set autot off
以上是B树索引，即oracle 默认索引
1.2.6. 如何在建立索引时进行排序
建立索引时，对语句需要进行排序，如何创建索引呢？
SQL> select * from emp where sal>2600 order by sal asc;
创建索引
SQL> set autot trae exp
SQL> create index emp_sal_i on emp(sal asc);
Index created.
SQL> set autot trace exp
SQL> select * from emp where sal>2600 order by sal asc;
select * from emp where sal>2600 order by sal asc;
Execution Plan
----------------------------------------------------------
Plan hash value: 490730320
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 5 | 435 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 435 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL_I | 5 | | 1 (0)| 0
0:00:01 | //用到了索引范围扫描
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL">2600)
Note
-----
- dynamic sampling used for this statement (level=2)
如果是降序
SQL> select * from emp where sal>2600 order by sal desc;
select * from emp where sal>2600 order by sal desc;
Execution Plan
Plan hash value: 3581623745
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 5 | 435 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 435 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| EMP_SAL_I | 5 | | 1 (0)|
00:00:01 |
Predicate Information (identified by operation id):
2 - access("SAL">2600)
Note
- dynamic sampling used for this statement (level=2)
以上是对索引扫描后的结果进行排序
如果要让索引仅仅是范围扫描，不做降序
SQL> drop index emp_sal_i;
drop index emp_sal_i;
Index dropped.
SQL> create index emp_sal_i on emp(sal desc);
create index emp_sal_i on emp(sal desc);
Index created
SQL> select * from emp where sal>2600 order by sal desc;
select * from emp where sal>2600 order by sal desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 490730320
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 5 | 435 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 435 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL_I | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("SAL")
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SAL"))>2600)
Note
-----
- dynamic sampling used for this statement (level=2)
其实在建立索引时，也可以建立不排序的索引，这种情况一般适合于表里面的数据本身就是按照升序排序。
例如
SQL> create table emp1 as select * from emp;
Table created.
SQL> select empno from emp1; 从小到大排序的
select empno from emp1;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
1.2.7. 建立索引时不排序
SQL> create index emp1_empno_i on emp1(empno) nosort;
create index emp1_empno_i on emp1(empno) nosort;
Index created.
使用nosort的前提是建立的索引列值必须是升序排序
SQL> create index emp1_ename_i on emp1(ename) nosort;
create index emp1_ename_i on emp1(ename) nosort;
create index emp1_ename_i on emp1(ename) nosort
*
ERROR at line 1:
ORA-01409: NOSORT option may not be used; rows are not in ascending order
组合索引
SQL> select * from emp where deptno=20 and job='CLERK';
select * from emp where deptno=20 and job='CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
1.2.8. 如何建立组合索引
SQL> create index emp_deptno_job_i on emp(deptno,job);
create index emp_deptno_job_i on emp(deptno,job);
Index created.
SQL> set autot trace exp
set autot trace exp
SQL> select * from emp where deptno=20 and job='CLERK';
select * from emp where deptno=20 and job='CLERK';
Execution Plan
Plan hash value: 1342397128
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
| 0 | SELECT STATEMENT | | 2 | 78 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 78 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_JOB_I | 2 | | 1
(0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("DEPTNO"=20 AND "JOB"='CLERK')
如果是分开建立索引，在查看会发现只使用其中的一个索引 emp_job_i
SQL> drop index emp_deptno_job_i;
drop index emp_deptno_job_i;
Index dropped.
SQL> create index emp_deptno_i on emp(deptno);
create index emp_deptno_i on emp(deptno);
Index created.
SQL> create index emp_job_i on emp(job);
create index emp_job_i on emp(job);
Index created.
SQL> set autot trace exp
set autot trace exp
SQL> select * from emp where deptno=20 and job='CLERK';
select * from emp where deptno=20 and job='CLERK';
Execution Plan
Plan hash value: 2386020627
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 0
0:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_I | 3 | | 1 (0)| 0
0:00:01 |
Predicate Information (identified by operation id):
1 - filter("DEPTNO"=20)
2 - access("JOB"='CLERK')
建立组合索引时把重复值比较少的放在前面。
SQL> set autot off
set autot off
SQL> select count( distinct(deptno) )from emp; //除掉重复行，在统计个数
select count( distinct(deptno) )from emp;
COUNT(DISTINCT(DEPTNO))
3
SQL> select count (distinct(job))from emp;
select count (distinct(job))from emp;
COUNT(DISTINCT(JOB))
5
建立组合索引时将deptno放在前面。create index emp_deptno_job_i on emp(deptno,job);
SQL> drop index emp_job_i;
drop index emp_job_i;
Index dropped.
SQL> drop index emp_deptno_i;
drop index emp_deptno_i;
Index dropped.
1.2.9. 如何重建索引
在数据库使用当中，索引必定会带来一些性能上的开销，此时当我们建立索引时，可以先将索引设置为不可用状态，然后将表的数据插入到表中之后再去重建索引，让索引变成可用的索引，这样可以提高insert等操作的速度，也可以避免insert时还要对索引进行insert操作。
SQL> create index emp_deptno_i on emp(deptno) unusable;
create index emp_deptno_i on emp(deptno) unusable;
Index created.
SQL> select index_name,status from user_indexes;
select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
EMP1_EMPNO_I VALID
PK_EMP VALID
EMP_SAL_I VALID
EMP_DEPTNO_I UNUSABLE //索引不可用、无效
PK_DEPT VALID
此时查询时不会使用索引
SQL> set autot trace exp
set autot trace exp
SQL> select * from emp where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 195 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
可以使用ALTER命令重建索引
SQL> alter index emp_deptno_i rebuild;
alter index emp_deptno_i rebuild;
Index altered.
SQL> select * from emp where deptno=10;
select * from emp where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3845781929
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 5 | 195 | 2 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 195 | 2 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_I | 5 | | 1 (0)
| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
1.2.10. 如何建立反向索引
SQL> select ename,sal from emp where ename like'%AM%';
select ename,sal from emp where ename like'%AM%';
ENAME SAL
---------- ----------
ADAMS 1100
JAMES 950
SQL> insert into emp(empno,ename,sal) values (10,'A10',2100);
SQL> select empno,ename,sal from emp where ename like 'A%';
EMPNO ENAME SAL
---------- ---------- ----------
10 A10 2100
11 A11 2200
12 A12 2000
13 A13 2400
14 A14 2500
15 A15 2200
16 A16 2200
17 A17 2200
18 A18 2200
7499 ALLEN 1600
7876 ADAMS 1100
11 rows selected.
SQL> set autot trace exp
SQL> select empno,ename,sal from emp where ename like 'A%';
select empno,ename,sal from emp where ename like 'A%';
Execution Plan
Plan hash value: 549418132
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 2 | | 1 (0)|
00:00:01 |
Predicate Information (identified by operation id):
2 - access("ENAME" LIKE 'A%')
filter("ENAME" LIKE 'A%'
如果是查找以A结尾的
SQL> select empno,ename,sal from emp where ename like '%A';
select empno,ename,sal from emp where ename like '%A';
Execution Plan
Plan hash value: 1237049707
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 2 (0)|
00:00:01 |
|* 2 | INDEX FULL SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 | //此时做全表扫描
Predicate Information (identified by operation id):
2 - filter("ENAME" LIKE '%A' AND "ENAME" IS NOT NULL)
可以建立反向索引
SQL> drop index emp_ename_i;
drop index emp_ename_i;
Index dropped.
SQL> create index emp_ename_i on emp(ename) reverse; 建立反向索引
create index emp_ename_i on emp(ename) reverse;
Index created.
SQL> select empno,ename,sal from emp where ename like 'A%';
select empno,ename,sal from emp where ename like 'A%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME" LIKE 'A%')
以上没有使用索引。
SQL> conn scott/nihao123!
conn scott/nihao123!
Connected.
SQL> create index emp_ename_i on emp(ename);
create index emp_ename_i on emp(ename);
Index created.
SQL> create index emp_ename_i_r on emp(reverse(ename));
create index emp_ename_i_r on emp(reverse(ename));
Index created.
SQL> set autot trace exp
set autot trace exp
SQL> select * from emp where ename like 'A%';
select * from emp where ename like 'A%';
Execution Plan
----------------------------------------------------------
Plan hash value: 549418132
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME" LIKE 'A%')
filter("ENAME" LIKE 'A%')
SQL> select * from emp where ename like '%G';
Execution Plan
Plan hash value: 1237049707
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX FULL SCAN | EMP_ENAME_I | 1 | | 1 (0)|
00:00:01 | //使用全表扫描
Predicate Information (identified by operation id):
2 - filter("ENAME" LIKE '%G' AND "ENAME" IS NOT NULL)
SQL> select * from emp where reverse(ename) like 'G%';
select * from emp where reverse(ename) like 'G%';
Execution Plan
Plan hash value: 2402509802
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0
)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0
)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I_R | 1 | | 1 (0
)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access(REVERSE("ENAME") LIKE 'G%')
filter(REVERSE("ENAME") LIKE 'G%')
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
以上反向索引应用场景使用于优化like查询。
一般情况下，查询“A%”开头的，使用B-树索引就可以。如果是“%G”结尾的查询使用reverse索引。如果是%ABC%，使用文本查询。
另外反向键索引是用来优化数据库中热点块的。如果发现某一个索引的块出现热点块的现象时，可以使用反向键索引。
数据库的热点块，从简单了讲，就是极短的时间内对少量数据块进行了过于频繁的访问。实际上最有效的办法，是从优化sql入手，不良的 sql往往带来大量的不必要的访问，这是造成热点块的根源。
如果索引所在的块里面包含的索引键值是按照连续的值，而且是从小到大的方式存储，如1，2，3….或者是按照序列的方式存储10,20,30,40,这些都属于连续的值，这些连续的值会存在一个块中，如果经常去访问这些值，就会导致block很忙。解决的办法就是可以将这些值分散存储在不同的块中，此时只需将索引重建即可，alter index emp_empno_i rebuild reverse。这样表当中存储的数据就会分散到不同的块当中，这样就可以避免热点块的现象，
需要注意的是这个方式并不是改变查询的语句，语句不变化，只是把索引的键值进行了反向存储了，假如之前是用0001 0002 0003方式存储，经过反向键索引后就用1000 2000 3000的方式存储，此时就变成了不连续的值，就存放在了不同的块中了。
1.2.11. 如何创建函数索引
函数索引就是就是预先计算给定列的函数并在索引中存储结果。
SQL> select * from emp where sal+nvl(comm,0)>3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
创建函数索引
SQL> create index emp_sal_nvl on emp(sal+nvl(comm,0));
Index created.
SQL> select * from emp where sal+nvl(comm,0)>3000;
select * from emp where sal+nvl(comm,0)>3000;
Execution Plan
Plan hash value: 3807620672
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL_NVL | 1 | | 1 (0)|
00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL"+NVL("COMM",0)>3000)
函数索引必须在以下条件下才有效
SQL> conn / as sysdba
conn / as sysdba
Connected.
SQL> show parameter que
show parameter que
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
job_queue_processes integer 1000
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SQL>
1.2.12. 如何创建唯一索引
唯一索引：保证定义索引的列中没有任何两行有重复值。唯一索引中的索引关键字只能指向表中的一行。
非唯一索引：定义索引的列中可以有重复值。
举例：在薪水级别(salgrade)表中，为级别编号grade列创建唯一索引。
SQL> create UNIQUE INDEX emp_grade_unique_idx ON salgrade(grade);
create UNIQUE INDEX emp_grade_unique_idx ON salgrade(grade);
Index created.
SQL> select grade from salgrade;
GRADE
----------
1
2
3
4
5
SQL> set autot trace exp
set autot trace exp
SQL> select * from salgrade where grade=3;
select * from salgrade where grade=3;
Execution Plan
Plan hash value: 3034377781
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 10 |
1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALGRADE | 1 | 10 |
1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_GRADE_UNIQUE_IDX | 1 | |
0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("GRADE"=3)
1.2.13. 如何创建位图索引
位图索引使用位图标识被索引的列值。对于低基数且表尺寸大的列来说，这是一个理想的索引。该索引通常不适合于有大量更新任务的表，当很适合与数据仓库的应用。
B-树索引与位图索引B-树索引 位图索引 B-树索引 位图索引
适用于高基数的数据 适用于低基数的数据
适用于OLTP数据库 适用于数据仓库应用 使用大量的空间 使用的空间相对少
易于更新 难于更新
举例
SQL> create bitmap index emp_job_i on emp(job);
Index created.
SQL> set autot trace exp
set autot trace exp
SQL> select job from emp where job='CLERK';
select job from emp where job='CLERK';
Execution Plan
Plan hash value: 3606498495
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 3 | 24 | 1 (0)|
00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 3 | 24 | 1 (0)|
00:00:01 |
|* 2 | BITMAP INDEX FAST FULL SCAN| EMP_JOB_I | | | |
| 使用位图索引的cost 是几乎没有开销的，性能会更好。适用于低基数的列，并且不能对此列进行DML操作，如果进行DML操作，需要将索引禁止，在oracle 11g也可以将索引隐藏。
Predicate Information (identified by operation id):
2 - filter("JOB"='CLERK')
如果换成普通索引，就会使用索引范围扫描。
SQL> drop index EMP_JOB_I;
drop index EMP_JOB_I;
Index dropped.
SQL> create index EMP_JOB_I on emp(job);
create index EMP_JOB_I on emp(job);
Index created.
SQL> select job from emp where job='CLERK';
select job from emp where job='CLERK';
Execution Plan
----------------------------------------------------------
Plan hash value: 714484323
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 24 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_JOB_I | 3 | 24 | 1 (0)| 00:00:01 |
使用的是索引范围扫描。
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("JOB"='CLERK')
还有一种情况，当建立位图索引后，查询多个列是，可能不会使用索引，此时可以强制使用索引
SQL> create bitmap index EMP_JOB_I on emp(job);
create bitmap index EMP_JOB_I on emp(job);
Index created.
SQL> set autot trace exp
set autot trace exp
SQL> select * from emp where job='CLERK';
select * from emp where job='CLERK';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='CLERK')
SQL> select /*+ index(emp,emp_job_i)*/* from emp where job='CLERK'; //强制使用位图索引
select /*+ index(emp,emp_job_i)*/* from emp where job='CLERK';
Execution Plan
----------------------------------------------------------
Plan hash value: 3256789423
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 3 | 114 | 4 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 114 | 4 (0)|
00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | |
|
|* 3 | BITMAP INDEX SINGLE VALUE | EMP_JOB_I | | | |
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB"='CLERK')
当在某些非常大的表中把普通的B树索引换成位图索引时，可以看到查询性能有明显的改进。但是每个位图索引项都覆盖了表中大量的数据行，因此，如果表中数据被更新、插入或删除的话，那么位图索引的更改需求就会特别大，而且索引的尺寸也将明显的增加。解决位图索引尺寸增加以及性能下降问题的唯一办法是，通过定期重建索引来维护位图索引。对于有大量插入、删除、更新任务的表，位图索引不是最好的选择。
1.3. 视图
视图是一个虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典里。视图中的数据是从一个或多个实际表中获得的。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
物化视图(materialized view)也称实体化视图、快照，含有数据、占用存储空间，在数据仓库中很有实用价值。
视图的作用：
限制对数据的访问：只读视图或对某个视图不能做DML操作等
提供数据的独立性：不同的视图展现了不同的数据访问结构
复杂查询简单化：可将复杂的语句简单化
相同的数据展现不同的视图
视图的类型功能 简单的视图 复杂的视图
表的数量 1个 一个或多个
是否包含函数 不包含 包含
是否包含分组数据 不包含 包含
是否可以通过视图执行DML 可以 不可以
创建视图
CREATE [OR REPLACE] [FORCE] VIEW
view_name [(alias[, alias]...)]
AS select_statement
[WITH CHECK OPTION]
[WITH READ ONLY];
可以使用复杂的子查询
子查询不能使用order by
查看用户的视图信息
SELECT * FROM user_views;
修改视图：使用CREATE OR REPLACE修改视图
创建复杂的视图
创建复杂的视图
CREATE VIEW dept_sum_vu
(name,minsal,maxsal,avgsal)
AS SELECT d.department_name,MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
在视图上进行DML操作
可以对简单视图做DML操作。
如果视图包含以下之一，就不能删除行
__分组函数
__GROUP BY子句
__DISTINCT关键字
__伪列ROWNUM关键字
如果视图包含以下之一，就不能修改行：
__分组函数
__GROUP BY子句
__DISTINCT关键字
__伪列ROWNUM关键字
__表达式定义的列
如果视图包含以下之一，就不能添加行
__分组函数
__GROUP BY子句
__DISTINCT关键字
__伪列ROWNUM关键字
__表达式定义的列
__视图中没有选取基表中的NOT NULL列
使用WITH CHECK OPTION子句
使用WITH CHECK OPTION子句可以确保视图中不包含子查询范围以外的记录
举例
SQL> create or replace view emp10 as select e.* from emp e where deptno=10 with check option constraint aa;
//视图子查询的范围是部门编号为10的记录。如果尝试向视图中添加部门编号不是10的记录，或修改原有的记录的部门编号，就会出错。
使用WITH READ ONLY子句
使用WITH READ ONLY子句，可以确保视图中无法进行DML操作。
只要在视图上执行DML操作，就返回出错信息。
SQL> create or replace view emp10 as select e.* from emp e where deptno=10 with read only;
丢弃视图
丢弃视图也不会影响基表。
DROP VIEW view;
举例
DROP VIEW emp10;
关于视图的举例。
SQL> create view emp10 as select * from emp where deptno=10;
create view emp10 as select * from emp where deptno=10;
create view emp10 as select * from emp where deptno=10
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
conn / as sysdba
Connected.
SQL> grant create view to scott;
grant create view to scott;
Grant succeeded.
SQL> conn scott/nihao123!
conn scott/nihao123!
Connected.
SQL> create view emp10 as select * from emp where deptno=10;
create view emp10 as select * from emp where deptno=10;
View created.
查询视图
SQL> select * from emp10;
select * from emp10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
查询视图等同于这个查询语句
SQL> select * from emp where deptno=10;
现在可以对这个视图进行任何的更改
SQL> update emp10 set sal=sal+1 where sal=1300;
update emp10 set sal=sal+1 where sal=1300;
1 row updated.
更改后视图中的数据就会发生变化
SQL> select * from emp10 where deptno=10; 当然emp表也会有相应的更改。
select * from emp10 where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1301
10
SQL> create or replace view emp10 as select rownum r,e.* from emp e where deptno=10;
create or replace view emp10 as select rownum r,e.* from emp e where deptno=10;
View created.
SQL> select * from emp10 where deptno=10; 此时emp10视图中增加了一个rownumber列。
select * from emp10 where deptno=10;
R EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- ---------- --------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
1 7782 CLARK MANAGER 7839 09-JUN-81 2450
10
2 7839 KING PRESIDENT 17-NOV-81 5000
10
3 7934 MILLER CLERK 7782 23-JAN-82 1301
10
此时对视图进行更改操作
SQL> update emp10 set sal=sal+1 where sal=1300;
update emp10 set sal=sal+1 where sal=1300;
update emp10 set sal=sal+1 where sal=1300
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> create or replace view emp10 as select e.* from emp e where deptno=10;
create or replace view emp10 as select e.* from emp e where deptno=10;
View created.
SQL> update emp10 set deptno=20 where sal=1301;
update emp10 set deptno=20 where sal=1301;
1 row updated.
SQL> select * from emp10 where deptno=10; //发现1301的记录不存在了，因为把部门编号变成了“20”
select * from emp10 where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
如何保证视图当中部门编号为10的是数据不被修改
先把被修改的数据改过来
SQL> update emp set deptno=10 where sal=1301;
1 row updated.
SQL> select * from emp10 where deptno=10;
select * from emp10 where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1301
10
SQL> create or replace view emp10 as select e.* from emp e where deptno=10 with check option constraint aa;
create or replace view emp10 as select e.* from emp e where deptno=10 with check option constraint aa;
//视图子查询的范围是部门编号为10的记录。如果尝试向视图中添加部门编号不是10的记录，或修改原有的记录的部门编号，就会出错。
View created.
SQL> update emp10 set deptno=20 where sal=1301;
update emp10 set deptno=20 where sal=1301;
update emp10 set deptno=20 where sal=1301
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
创建只读视图
这样创建的视图都可以进行更改操作。
SQL> create or replace view emp10 as select e.* from emp e where deptno=10;
create or replace view emp10 as select e.* from emp e where deptno=10;
View created.
如果不允许对视图进行DML操作，可以创建只读视图 with readonly；
SQL> create or replace view emp10 as select e.* from emp e where deptno=10 with read only;
View created.
SQL> update emp10 set sal=sal+10 where sal=1301;
update emp10 set sal=sal+10 where sal=1301;
update emp10 set sal=sal+10 where sal=1301
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
如果基表不存在，我们可以强制创建视图
SQL> create force view va as select * from aaaa;
create force view va as select * from aaaa;
Warning: View created with compilation errors.
SQL> create table aaaa as select * from dept;
create table aaaa as select * from dept;
Table created.
查看视图的状态。
SQL> select object_name,status from user_objects where object_type='VIEW';
select object_name,status from user_objects where object_type='VIEW';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
VA
INVALID //此时视图VA是无效视图
EMP10
VALID
SQL> select * from va;
查看视图
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
再去查看视图状态就变为可用状态了。
SQL> select object_name,status from user_objects where object_type='VIEW';
select object_name,status from user_objects where object_type='VIEW';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
EMP10
VALID
VA
VALID
创建视图时使用order by子句
SQL> create or replace view emp10 as select e.* from emp e where deptno=10 order by sal;
create or replace view emp10 as select e.* from emp e where deptno=10 order by sal;
View created.
SQL> select * from emp10;
select * from emp10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1301
10
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
SQL> update emp10 set sal=sal+10 where sal=1301;
update emp10 set sal=sal+10 where sal=1301;
1 row updated.
1.4. 物化视图
物化视图是和普通视图相对应的。在oracle使用普通视图时，它会重复执行创建视图的所有SQL语句，如果这样的SQL语句含有多张表的连接或者ORDER BY子句，而且表数据量很大，则会非常耗时，效率非常低。为了解决这个问题，oracle提出了物化视图的概念。
简单的讲，物化视图就是具有物理存储的特殊视图，占据物理空间，就象表一样。物化视图是基于表、物化视图等创建的。它需要和源表进行同步，不断的刷新物化视图中的数据。
物化视图中有两个重要概念：查询重写和物化视图的同步
查询重写：
对SQL语句进行重写。当用户使用SQL语句对基表进行查询时，如果已经建立了基于这些基表的物化视图，oracle将自动计算和使用物化视图来完成查询。在某些情况下可以节约查询时间，减少系统I/O 。oracle的这种查询优化技术成为查询重写。参数QUERY_REWRITE_ENABLED决定是否使用重写查询，该参数为布尔类型。在创建物化视图时需要使用ENABLE QUERY_REWRITE来启动查询重写功能。通过show 命令可以查看该参数的值。
SQL>show parameter query_rewrite_enabled;
物化视图的同步：
物化视图是基于表创建的，所以当基表变化时，需要同步数据以更新物化视图中的数据，这样保持物化视图中的数据和基表的数据一致性。oracle提供了两种物化视图的刷新方式，决定何时进行刷新，即ON COMMIT方式和ON DEMAND方式。
ON COMMIT: 指物化视图在对基表的DML操作事务提交的同时进行刷新
ON DEMAND：指物化视图在用户需要的时候进行刷新。可以手工通过DBMS_MVIEW.refresh等方法来进行刷新，也可以通过JOB定时进行刷新。
选择刷新方式后，还需要选择一种刷新类型，刷新类型指刷新时基表与物化视图如何实现数据的同步，oracle提供了以下4种刷新类型：
COMPLETE:对整个物化视图进行完全刷新
FAST:采用增量刷新，只刷新自上次刷新以后进行的修改，须有物化视图日志，才能使用FAST
FORCE：oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE方式。
NEVER:物化视图不进行任何刷新。
默认值是FORCE刷新类型。
1.4.1. 创建物化视图
创建物化视图的前提条件
具备创建物化视图的权限、QUERY REWRITE的权限、以及对创建物化视图所涉及的表的访问权限和创建表的权限。
通过SCOTT用户来演示上述权限的赋予情况
物化视图举例
用户SCOTT
用户MV1
实验案例：将SCOTT用户下EMP表的变化同步到MV1用户下的EMP表中。
[oracle@oraclesrv ~]$ sqlplus / as sysdba
SQL> create user mv1 identified by oracle;
SQL> grant connect,resource to mv1;
SQL> grant select on scott.emp to mv1;
SQL> grant create materialized view to mv1;
SQL> grant execute on dbms_mview to mv1;
SQL> conn mv1/oracle
Connected.
完全刷新举例
SQL> select * from scott.emp; //可以访问scott用户下的EMP表
SQL> create materialized view emp as select * from scott.emp;
Materialized view created.
SQL> select sal from emp where deptno=10;
SAL
----------
2450
5000
1311
物化视图中的数据与SCOTT用户下的EMP表中的 数据时一样的。
打开新的终端，
对scott.emp表进行更新。
此时scott.emp表中sal已经更新，但是物化视图中的数据并没有更新
物化视图中的数据并没有更新，因为还没有定义同步的方式，
SQL> select sal from emp where deptno=10;
SAL
----------
2450
5000
1311
此时我们可以人工同步
SQL> exec dbms_mview.refresh('emp','c'); // ‘c’表示采用COMPLETE完全刷新。
PL/SQL procedure successfully completed.
再次查看物化视图中的数据就刷新过来了
SQL> select sal from emp where deptno=10;
SAL
----------
2448
4998
1309
此种方式的同步相当于把表中的数据删掉，重新插入是一样的，所以这种方式对于较大表操作不太现实。所以我们可以使用快速刷新，但是现在还不能使用快速刷新.
SQL> exec dbms_mview.refresh('emp','f');
BEGIN dbms_mview.refresh('emp','f'); END;
*
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
提示并没有创建物化视图日志
物化视图日志是用户选择了FAST刷新类型时要使用的，以增量同步基表的变化。
因为EMP表是有主键的，所以不用with rowed;
使用mv用户连接，使用快速刷新
SQL> create materialized view empf refresh fast on commit as select * from scott.emp;
Materialized view created.
SQL> select sal from empf where deptno=10;
SAL
----------
2448
4998
1309
更新SCOTT.emp
物化视图更新成功。这是对基表的DML操作事务COMMIT的同时对物化视图进行刷新，即使用ON COMMIT方式进行fast刷新。如果DML操作后不进行COMMIT提交，是不会更新的，在这里就不截图了。自行测试。
SQL> show user;
USER is "MV1"
SQL> select sal from empf where deptno=10;
SAL
----------
2458
5008
1319
快速刷新时可以定义时间，每次刷新按照系统当前时间为准，每隔一分钟刷新
SQL> create materialized view empf1 refresh fast start with sysdate next sysdate+1/1440 as select * from scott.emp;
Materialized view created.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 获取时间
TO_CHAR(SYSDATE,'YY
-------------------
2015-04-23 09:19:13
当然也可以使用手工刷新的方式
SQL> exec dbms_mview.refresh('empf','f');
PL/SQL procedure successfully completed.
创建一个可更新的物化视图
SQL> create materialized view empu1 refresh fast for update as select * from scott.emp;
Materialized view created.
SQL> update empu1 set sal=sal+1;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select sal from empu1 where deptno=10;
SAL
----------
2559
5109
1420
SQL> exec dbms_mview.refresh('empu1','f');
PL/SQL procedure successfully completed.
SQL> select sal from empu1 where deptno=10;
SAL
----------
2558
5108
1419
创建一个可更新的物化视图，并且每隔一天更新。
SQL> create materialized view empu3 refresh fast start with sysdate next sysdate+1 for update as select * from scott.emp;
Materialized view created.
1.5. 同义词
同义词是表、索引和视图等模式对象的一个别名。与视图等一样，同义词并不占用任何实际的存储空间，只在oracle的数据字典中保存其定义描述。在使用同义词时，oracle会将其翻译为对应对象的名称。
oracle中的同义词主要分为如下两类：
公有同义词：在数据库中的所有用户都可以使用
私有同义词：由创建它的用户所拥有。不过，用户可以控制其他用户是否有权使用自己的同义词。
同义词的用途
简化SQL语句
如果用户创建的表的名字很长，可以为这个表创建一个oracle同义词来简化SQL语句。
隐藏对象的名称和所有者
多用户协同开发中，可以屏蔽对象的名称及持有者。如果没有同义词，当操作其他用户的表时，必须通过“用户名.表名”的形式操作，采用了oracle同义词之后，就可以隐藏掉用户名。例如用户user1要访问用户SCOTT的emp表，必须使用SCOTT.emp来引用，如果为用户创建一个名为“emp”的同义词代表SCOTT.emp，那么user1就可以用同义词。就像访问自己的表一样引用scott.emp。
为分布式数据库的远程对象提供位置透明性
要完成远程对象的访问，先要了解数据库链接的概念。数据库链接是一个命名的对象。说明一个数据库到另一个数据库的路径，通过其可以实现不同数据库之间的通信。同义词在数据库连接中作用就是提供位置的透明性。后面会给出具体示例。
创建数据库链接的语法如下：
提供对数据库对象的公共访问。
公有同义词只是为数据库对象定义了一个公共的别名，即其他用户都可以通过这个别名访问，但能否通过该别名访问成功。还要看是否已经具有数据库对象的访问权限。
1.5.1. 私有同义词
私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，用户必须拥有CREATE SYNONYM系统权限。要在其他用户模式创建私有同义词，用户必须拥有CREATE ANY SYNONYM系统权限。
创建私有同义词的语法如下：
CREATE [OR REPLACE] SYNONYM [schema.] synonym_name FOR [schema.]object_name;
在语法中
OR REPLACE:在同义词存在的情况下替换该同义词。
synonym_name ：要创建同义词的名称
object_name：指定要为之创建私有同义词的对象名称。
在system模式下创建私有同义词访问SCOTT模式下的emp表。
举例：
在system模式下创建私有同义词访问SCOTT模式下的EMP表
SQL> conn system/123.com@orcl
conn system/123.com@orcl
Connected.
SQL> show user;
show user;
USER is "SYSTEM"
SQL> select * from scott.emp;
使用模式确实实现了查询，但却暴漏了emp表的模式信息，使用私有同义词可以避免这个问题
以system用户身份登录数据库
创建同义词
SQL> create synonym sy_emp for scott.emp;
create synonym sy_emp for scott.emp;
Synonym created.
访问同义词sy_emp，实际访问的就是SCOTT的emp表
SQL> select * from sy_emp;
1.5.2. 公有同义词
公有同义词被所有的数据库用户访问。公有同义词可以隐藏基表的身份，并降低SQL语句的复杂性，要创建公有同义词，用户必须拥有CREATE PUBLIC SYNONYM系统权限
创建公有同义词的语法如下：
CREATE [OR REPLACE] PUBLIC SYNONYM synonym_name FOR [schema.]object_name；
举例：
在SCOTT模式下对部门表dept创建公有同义词public_sy_dept，目的是使其他用户可以直接访问public_sy_dept。
SQL> conn system/123.com@orcl
conn system/123.com@orcl
Connected.
将创建公有同义词权限给scott用户；
SQL> grant create public synonym to scott;
grant create public synonym to scott;
Grant succeeded.
以scott用户身份登录数据库
SQL> conn scott/nihao123!@orcl;
conn scott/nihao123!@orcl;
Connected.
将查新emp权限给linda用户
SQL> grant select on dept to linda;
grant select on dept to linda;
Grant succeeded.
创建公有同义词public_sy_dept作为SCOTT用户dept表的别名
SQL> create public synonym public_sy_dept for dept;
create public synonym public_sy_dept for dept;
Synonym created.
以linda用户身份登录数据库
SQL> conn linda/linda@orcl;
conn linda/linda@orcl;
Connected.
访问同义词public_sy_dept;
SQL> select * from public_sy_dept;
select * from public_sy_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
使用同义词时，要保证具有与同义词对应的数据库对象的操作权限。
1.5.3. 删除同义词
删除同义词的语法如下：
DROP [PUBLIC] SYNONYM [schema.]synonym_name;
要删除同义词sy_emp和public_sy_dept，可以执行以下语句
删除私有同义词
SQL> conn system/123.com@orcl
conn system/123.com@orcl
Connected.
SQL> drop synonym sy_emp;
drop synonym sy_emp;
Synonym dropped.
删除公有同义词
SQL> drop public synonym public_sy_dept;
drop public synonym public_sy_dept;
Synonym dropped.
此命令只删除同义词，不会删除对应的表
1.6. 分区表
oracle允许用户把一个表中的所有行分成几部分，并将它们存储在不同的表空间。分成的每一部分称为一个分区，被分区的表称为分区表。
对于包含大量数据的表来说，分区很有用，表分区有以下优点
n 改善表的查询性能：在对表进行分区之后，用户执行SQL查询时可以只访问表中的特定分区而非整个表。
n 表更容易管理：因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。
n 便于备份和恢复：可以独立的备份和恢复每个分区。
n 提高数据安全性：将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性。
符合以下条件的表可以创建分区表
n 数据量大于2GB
n 已有的数据和新添加的数据有明显的界限划分
表分区对用户是透明的，即应用程序可以不知道表已被分区，在更新和查询分区表时当做普通表来操作，当oracle优化程序知道表已被分区。
要分区的表不能具有LONG和LONG RAW数据类型的列
分区表的分类
oracle提供的分区方法有范围分区、列表分区、散列分区、复合分区、间隔分区和虚拟列分区，其中间隔分区和虚拟列分区是oracle11g的性特性。
范围分区是应用范围比较广的表分区方式，它是以列的值的范围来作为分区的划分条件，将记录存放到列值所值的范围分区中。
1.6.1. 范围分区
将emp表中员工入职时间以每年为一个分区
SQL> conn scott/nihao123!@orcl;
conn scott/nihao123!@orcl;
Connected.
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from emp;
select to_char(hiredate,'yyyy-mm-dd') hiredate from emp;
HIREDATE
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
1981-06-09
1987-04-19
1981-11-17
1981-09-08
1987-05-23
HIREDATE
----------
1981-12-03
1981-12-03
1982-01-23
14 rows selected.
将入职时间为80,81,82,87年的放在不同分区中。
将80年入职的放到P1，81年入职的放到P2…..
建立表空间P1，P2，P3，P4，P5
SQL> conn / as sysdba;
conn / as sysdba;
Connected.
创建表空间 p1,p2,p3,p4,p5
SQL> create tablespace p1 datafile '/opt/oracle/oradata/orcl/p1.dbf' size 100m;
SQL> create tablespace p2 datafile '/opt/oracle/oradata/orcl/p2.dbf' size 100m;
SQL> create tablespace p3 datafile '/opt/oracle/oradata/orcl/p3.dbf' size 100m;
SQL> create tablespace p4 datafile '/opt/oracle/oradata/orcl/p4.dbf' size 100m;
SQL> create tablespace p5 datafile '/opt/oracle/oradata/orcl/p5.dbf' size 100m;
SQL> conn scott/nihao123!@orcl;
conn scott/nihao123!@orcl;
Connected.
SQL> desc emp;
desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
创建范围分区表
SQL>
create table empr1(empno number(4) primary key,ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2))
partition by range(hiredate)
(
partition empr1_p1 values less than(to_date('1981-01-01','yyyy-mm-dd')) tablespace p1,
partition empr1_p2 values less than(to_date('1982-01-01','yyyy-mm-dd')) tablespace p2,
partition empr1_p3 values less than(to_date('1983-01-01','yyyy-mm-dd')) tablespace p3,
partition empr1_p4 values less than(to_date('1988-01-01','yyyy-mm-dd')) tablespace p4,
partition empr1_p5 values less than(maxvalue) tablespace p5
);
Table created.
SQL> desc empr1
desc empr1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> insert into empr1 select * from emp;
14 rows created.
SQL> select table_name,partition_name from user_tab_partitions where table_name='EMPR1';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMPR1 EMPR1_P1
EMPR1 EMPR1_P2
EMPR1 EMPR1_P3
EMPR1 EMPR1_P4
EMPR1 EMPR1_P5
查看分区表信息
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p1);
HIREDATE
----------
1980-12-17
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p2);
HIREDATE
----------
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
1981-06-09
1981-11-17
1981-09-08
1981-12-03
1981-12-03
10 rows selected.
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p3);
HIREDATE
----------
1982-01-23
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p4);
HIREDATE
----------
1987-04-19
1987-05-23
分区表5中没有相应的记录
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p5);
no rows selected
为分区表p4插入1984-03-01的记录
SQL> insert into empr1(empno,hiredate) values(10,to_date('1984-03-01','yyyy-mm-dd'));
SQL> select to_char(hiredate,'yyyy-mm-dd') hiredate from empr1 partition(empr1_p4);
HIREDATE
----------
1987-04-19
1987-05-23
1984-03-01
查看分区表存放在哪个表空间；
SQL> set linesize 200
set linesize 200
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMPR1';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMPR1 EMPR1_P1 P1
EMPR1 EMPR1_P2 P2
EMPR1 EMPR1_P3 P3
EMPR1 EMPR1_P4 P4
EMPR1 EMPR1_P5 P5
在范围分区基础上如何添加或减少分区
SQL> select count(*) from empr1 partition(empr1_p5);
COUNT(*)
----------
0
empr1_p5中没有数据，删除分区empr1_p5，将88年入职的记录存放在empr1_p5分区中
SQL> alter table empr1 drop partition empr1_p5; //empr1表中的记录也不存在了。
Table altered.
SQL> alter table empr1 add partition empr1_p5 values less than(to_date('1989-01-01','yyyy-mm-dd' )) tablespace p5;
Table altered.
最后一个分区创建在默认表空间中。
SQL> alter table empr1 add partition empr1_p6 values less than(maxvalue) tablespace users;
Table altered.
1.7. 序列
序列是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键和唯一键的值。序列可以按升序排序，也可以按降序排列。例如，销售流水表中的流水号可以使用序列自动生成。
创建序列的语法
CREATE SEQUENCE sequence_name
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE];
在语法中：
START WITH:指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值；对于降序序列，其默认值为序列的最大值。
INCREMENT BY:用于指定序列号之间的间隔，其默认值为1。如果n为正值，则生成的序列将按升序排序，如果n为负值，则生成的序列将按降序排序。
MAXVALUE：指定序列可以生成的最大值。
NOMAXVALUE：如果指定了NOMAXVALUE，oracle将升序序列的最大值设为1027，将降序序列的最大值设为-1，这是默认选项
MINVALUE:指定序列的最小值，MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE.
NOMINVALUE:如果指定了NOMINVALUE，oracle将升序序列的最小值设为1，或将降序序列的最小值设为-1026。这是默认选项。
CYCLE:指定序列在达到最大值或最小值后，将继续从头开始生产值。
NOCYCLE:指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认选项。
CACHE:使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。当用完缓存中所有序列号时，oracle将生成另一组数值，并将其保留在缓存中。
NO CACHE: 使用NOCACHE选项，则不会为加快访问速度而预先分配序列号，如果在创建序列时忽略了CACHE和NO CACHE选项，oracle将默认缓存20个序列号。
举例:
创建序列，从序号10开始，每次增加1，最大为2000，不循环，在增加会报错。
SQL> create sequence toy_seq
start with 10
increment by 1
maxvalue 2000
nocycle
cache 30;
访问序列：
创建了序列之后，可以通过NEXTVAL和CURRVAL伪列来访问该序列的值。可以从伪列中选择值，但是不能操作他们的值，下面分别说明NEXTVAL和CURRVAL.
NEXTVAL:创建序列后第一次使用NEXTVLAL时，将返回该序列的初始值，以后再引用NEXTVAL时，将使用INCREMENT BY子句的值来增加序列值，并返回这个新值。
CURRVAL:返回序列的当前值，即最后一次引用NEXTVAL时返回的值。
1.7.1. 创建序列
举例：
在玩具表中，需要标识列toyid作为标识，不需要有任何含义，可以作为主键。
SQL> create table toys(toyid number not null,toyname varchar2(20),toyprice number);
Table created.
SQL> desc toys
desc toys
Name Null? Type
----------------------------------------- -------- ----------------------------
TOYID NOT NULL NUMBER
TOYNAME VARCHAR2(20)
TOYPRICE NUMBER
为toys表插入数据
SQL> insert into toys(toyid,toyname,toyprice) values(toy_seq.nextval,'twenty',25);
SQL> insert into toys(toyid,toyname,toyprice) values(toy_seq.nextval,'magic pencile',75);
SQL> select * from toys;
TOYID TOYNAME TOYPRICE
---------- -------------------- ----------
10 twenty 25
11 magic pencile 75
上述语句从序列toy-seq中选择值插入toys表中的toyid列，如果语句执行成功，将在该表的toyid列插入值’10’, ’11’。
查看序列的当前值
SQL> select toy_seq.currval from toys; //序列中CURRVAL值为11
CURRVAL
----------
11
11
1.7.2. 更改序列
ALTER SEQUENCE命令用于修改序列的定义。如果要进行下列操作，则会修改序列。
设置或删除MINVALUE或MAXVALUE
修改增量值。修改缓存中的序列号的数目。
更改序列的语法如下;
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT B Y integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE];
不能修改序列的START WITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。
SQL> alter sequence toy_seq //为toy_seq序列设置新的maxvalue,打开了CYCLE
maxvalue 5000
cycle;
1.7.3. 查看序列
可以通过查询名为“USER_SEQUENCES“的数据字典视图，来获取用户所创建的序列的详细信息。
SQL> select sequence_name,increment_by,cache_size from user_sequences where sequence_name='TOY_SEQ';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE
------------------------------ ------------ ----------
TOY_SEQ 1 30
1.7.4. 删除序列
DROP SEQUENCE 命令用于删除序列
删除序列的语法如下：
DROP SEQUENCE [schema.]sequence_name;
下列命令从数据库中删除了toy_seq序列
SQL> drop sequence toy_seq;
Sequence dropped.
105
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
