一、方案
方案是数据库用户拥有的数据库对象的集合，方案对象是直接引用数据库的逻辑结构，对象包括表、索引、序列、视图、同义词等结构。
这一章大部分内容，我们在SQL的第九章创建和管理表及约束和第十章其它常用对象都已讲过，做一些补充。
二、表
1、表的类型
（1）堆组织表
堆表就是普通表，也叫堆组织表。之所以这样叫，是因为他使用数据结构中堆的算法来组织表。堆表的特点就是插入的行没有顺序。
（2）索引组织表
索引组织表（index organizedtable）就是存储在一个索引结构中的表。一定要有索引，其实也就是一定要有一个主键，表中行的顺序将按照主键的顺序，依次排列。索引组织表的ROWID被称为UROWID，也被称为逻辑ROWID，它的主要成份是主键，是可变的。
（3）聚簇表
聚簇(CLUSTER)是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。
A、索引聚簇表：利用聚簇索引来查找数据的聚簇类型称为索引聚簇。
B、散列聚簇表：利用哈希函数来查找数据的聚簇类型称为哈希聚簇（也称为散列聚簇）
（4）临时表
临时表可以提高数据库处理性能，在没有必要存储时，就存储临时中，用户只能够操作各自的数据中。
分有两类临时表：会话级的临时表和事务级的临时表。
（5）外部表
外部表，是指存储在Oracle数据库之外的表，数据库中只有表结构的定义，真正的数据并未存储在数据库中。外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。
2、创建表
（1）创建组织表
gyj@OCM> Create table t15(id int, name varchar2(10));
Table created.
（2）创建一个简单的索引组织表
gyj@OCM> create table iot10(id int primary key,namevarchar2(20),job varchar2(40)) organization index tablespace tp1;
Table created.
（3）索引聚簇表
我来建两个表一个是班级代码表，一个是学生基本信息表，现在利用聚簇表把jsj01（计算01）班的学生都存储在同一个块上，如果一个块放不下，则存储在尽可能少的几个块中。
| Student(sname,classno) Joe jsj01 Rose jsj01 Jack jsj01 Tom jsj02 Jeny jsj02 Tony jsj02 Lewis jsj02 |
| Class(classno, cname) jsj01 计算机01 jsj02 计算机02 jsj03 计算机03 jsj04 计算机04 | student_class_cluster
|
A）创建一个索引聚簇，聚簇列是classno列。定义SIZE1024,表示每个聚簇键值关联大约1024字节的数据。那么Oracle就会用这个值算出每个块最多能放下多少个聚簇建，若db_block_size＝8K，那第每个块最多放7个聚簇键。也就是说，对应的班级可以有jsj01/jsj02/jsj03/jsj04/jsj05/jsj06/jsj07的数据会放在一个块上。一旦插入班级jsjs08，就会使用一个新块。如下操作：
gyj@OCM> create cluster student_class_cluster
2 (classno number(2)) size1024;
Cluster created.
B）向聚簇中放数据之前，需要先对聚簇建立索引。聚簇索引的任务是拿到一个聚簇值，然后返回这个键的块的块地址，其中每个聚簇键值指向了聚簇本身中的一个块。我们去jsj01班的数据时，Oracle会读取簇键，确定相应的块地址，然后读数据。好我们马上创建一个聚簇索引，如下操作：
gyj@OCM> create indexidx_student_class_cluster
2 oncluster student_class_cluster;
Index created.
C）好，接下来我们在聚集中创建班级表和学生表
gyj@OCM>create table class
2 (classno number(2) primarykey,
3 cname varchar2(20),
4 loc varchar2(10))
5 clusterstudent_class_cluster(classno);
Table created.
gyj@OCM> create table student
2 (studentno number primarykey,sname varchar2(10),job varchar2(9),
3 mgr number,hiredatedate,sal number,comm number,classno number(2)
4 references class(classno))
5 clusterstudent_class_cluster(classno);
Table created.
（4）散列聚簇表
A）创建散列聚簇
gyj@OCM> create cluster hash_cluster1 (hash_keynumber)
2 hashkeys 1000 size 8192
3 tablespace tp1;
Cluster created.
B）在聚簇中创建表
gyj@OCM> create tablehashed_table(x number,data1 varchar2(4000),data2 varchar2(4000))
2 cluster hash_cluster1(x);
Table created.
（5）、创建临时表
A）当提交时，将自动清除表中所有数据。
create global temporary table temp_t1(id int,name varchar2(10)) on commit delete rows;
B）只有当会话退出后，临时表中的行才会被清除。
create global temporary tabletemp_t2(id int,name varchar2(10)) on commit preserve rows;
（５）创建外部表
这个在数据迁移那块知识点再做详细介绍。（
3、表的操作
（1）手动分配区语法如下：
ALTER TABLE 表名 ALLOCATE EXTENT [ ([SIZE integer [K|M]] [ DATAFILE ‘filename’ ]) ]
A）对t10 表扩展区的大小为64K,操作如下：
gyj@OCM> alter table t10allocate extent (size 64K);
Table altered.
B ）再查t10表区分配的情况：
select BLOCKS,EXTENTS from dba_segments where segment_name='T10’;
（2）表的重组:ALTERTABLE 表名 MOVE [TABLESPACE 表空间名];
gyj@OCM> alter table t10 move tablespace users;
Table altered.
（3）更改创建表时的参数pctfree
gyj@OCM>alter table t10 pctfree 30;
Table altered.
（4）删除表Drop
gyj@OCM> drop table t15;
Table dropped.
（5）删除表数据 Truncate
gyj@OCM> truncate table t10;
Table truncated.
三、有关约束
1、 延迟或立即约束
立即约束就是在每个DML语句执行时，立即判断用户所作的修改是否违返了约束，如果违返了，立即回当前正在执行的DML语句。我们以前我遇到的约束，都是这样的立即约束。
延迟约束在DML语句结束时，并不判断是否违反约束，而是事务提交时再判断。如果约束违反，整个事务被回退。
在创建约束时，有两个选项可以设置约束的检查方式：
Initially immediate ：最初为立即约束。
Initially deferred ：最初为延迟约束。
如果将约束定为Initiallyimmediate，那么约束仍是立即的，但我们可以使用命令设为延迟的。而约束如果被设为了Initially deferred，约束建立好了，就是延迟的。
我们来试下：
gyj@OCM> drop table t8;
Table dropped.
gyj@OCM> create table t8(id number(10) constraint t8_id_c check(id>=5)
2 initially deferred,namevarchar2(20));
gyj@OCM> insert into t8 values(1,'gyj');
1 row created.
可以正常插入。约束的检查被推迟了
gyj@OCM> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (GYJ.T8_ID_C)violated
可以看到，提交时检查出来此事务违反了约束，整个事务被回滚，事务也结束了。注意立即约束中，只会回滚违反约束的当前语句，而不会回滚整个事务。
如果我把约束建立成Initially immediate，最初为立即。那么约束此时还是立即。需要使用如下命令，才能把约束设置为延迟的。而且，最初为延迟的约束，也可以使用此命令，设成立即的。Initially immediate和Initially deferred都是可延迟的约束，下面的这条命令，可以将可延迟的约束，设置为延迟或立即。命令语法如下：
set constraints 约束名|all Immediate|Deferred
比如我上面创建的，是最初为延迟，我们也验证过了，现在已经是延迟检查了，下面我把它改为立即的
gyj@OCM>set constraint t8_id_c immediate;
Constraint set.
t8_id_c已经被改为立即的了，再插入违反约束的行，马上就会报出错误：
gyj@OCM> insert into t8 values(1,'gyj');
insert into t8 values(1,'gyj')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.T8_ID_C)violated
这个命令只影响当前会话，并且只在COMMIT和ROLLBACK命令执行前有效。下面我来试一下，仍旧是在刚才的会话中，执行一个COMMIT或ROLLBACK：
gyj@OCM> rollback;
Rollback complete.
执行COMMIT或ROLLBACK那个命令都行，这两个命令都代表一个事务的结束，SET CONSTRAIN命令的作用，也就是维持到一个事务的结束。现在t8_id_c约束又回到了最初的状态，它最初是延迟检查约束。下面我再插入一个违反约束的行：
gyj@OCM> insert into t8 values(1,'gyj');
1 row created.
已经可以插入了，直到提交才报错：
gyj@OCM> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (GYJ.T8_ID_C)violated
Initiallyimmediate我就不再试验了，除了它最初是立即外，它和Initially deferred的控制操作是一样的。如果在创建约束时，没有加这两个选项中的任一个，约束就是不可延迟的，对于不可延迟的约束，使用SET CONSTRAINT命令会报出错误。
我先把t8表上的约束创建为不可延迟的：
gyj@OCM> drop table t8;
Table dropped.
gyj@OCM> create table t8(id number(10)constraint t8_id_c check (id>=5),name varchar2(20));
Table created.
现在我把t8_id_c约束设为可延迟：
gyj@OCM>set constraint t8_id_c deferred;
setconstraint t8_id_c deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint thatis not deferrable
报出了错误，因为AA3_ID_C是不可延迟约束。
SETCONSTRAINT命令的作用，只对当前会话的当前事务有效，另有一条命令，它的作用可以对当前会话中的所有事务有效：
Alter session set constraint=immediate|Deferred|Default;
immediate是立即，Deferred是延迟，Default是恢复创建约束时所设置的值.
操作如下：
gyj@OCM> drop table t8;
Table dropped.
gyj@OCM> create table t8(id number(10)constraint t8_id_c check (id>=5),name varchar2(20));
Table created.
gyj@OCM>Alter session set constraint=deferred;
Session altered.
2、约束的状态 :
约束有如下四种状态
（1）ENABLEVALIDATE：约束在创建时，默认就是此状态。此状态会“检查表中原有行和新插入的行”。
（2）ENABLENOVALIDATE：不能输入违反约束的新数据。但是，在创建约束时，并不检查表中原有行。
（3）DISABLEVALIDATE：检查表中原有行是否违反约束,表中可以插入违反约束的行。
（4）DISABLENOVALIDATE：约束处于禁用状态，且此状态检查表中原有数据是否违反约束.
操作如下:
gyj@OCM> drop table t5;
Table dropped.
gyj@OCM> create table t5(idnumber(10),name varchar2(20));
Table created.
gyj@OCM>insert into t5 values(1,'gyj1');
1 row created.
gyj@OCM>insert into t5 values(2,'gyj2');
1 row created.
gyj@OCM> insert into t5values(3,'gyj3');
1 row created.
gyj@OCM>commit;
Commit complete.
gyj@OCM> alter table t5 add(constraint t5_id_c1 check(id>=2));
alter table t5 add(constraint t5_id_c1 check(id>=2))
*
ERROR at line 1:
ORA-02293: cannot validate (GYJ.T5_ID_C1) -check constraint violated
在增加约束时默认是ENABLE VALIDATE，发现表中已有id=1的不满足约束check (id>=2，就报错了。
操作如下添加了：ENABLE NOVALIDATE，不考虑史历数据的约束，执行成功。
gyj@OCM>alter table t5 add(constraint t5_id_c1 check (id>=2) ENABLENOVALIDATE);
Table altered.
四、索引
1、索引按照索引机制的不同大致分为三种：
（1）B树索引（ B-Treeindex）
B树索引是Oracle默认的索引。因为表中的行标识符(rowid)和相关的列值存储在一个平衡的树状结构的索引块中，所以该索相类型被称为B树索引。
使用B树索引有几个原因：
提高SQL语句的性能。
强制执行主键和唯一约束的唯一性
减少通过主键和外键约束关联的父表和子表间潜在的锁的问题。
A）B树索引的结构
B）创建B树索引
gyj@OCM> drop table t10;
Table dropped.
gyj@OCM> create table t10 as select *from dba_objects;
Table created.
gyj@OCM> insert into t10 select * fromt10;
72519 rows created.
gyj@OCM> /
145038 rows created.
gyj@OCM> /
290076 rows created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> create index idx_t0_bit ont10(object_type);
Index created.
（2）位图索引 Bitmap index
位图索引最适用于数据仓库或决策支持系统（DDS）。数据仓库中常见的一种模式是星型模式，其中所括中央事实表和数个相关的维度表，前者包含特定主题的全部详细信息，如客户的收入，后者包含特定维度（如果时间维度或地理维度）的相关引用类型数据。在星型模式中，维度表是父表，而中央事实表是子表。
A）位图索引的结构
B）创建位图索引
gyj@OCM> create table t20 as select *from t10;
Table created.
gyj@OCM> create bitmap index idx_t20_biton t20(object_type);
Index created.
gyj@OCM> select count(*) from t20 whereobject_type='TABLE';
COUNT(*)
----------
22912
gyj@OCM> select count(*) from t20 whereobject_type='TABLE' or object_type='INDEX';
COUNT(*)
----------
54104
（3）全文索 Text index
全文索引一般用在数据仓库或OLAP系统上，在OLTP系统中用的并不多。它是一种完全不同的数据只类型的索引。和B树索引以及位图索引不同的是，全文索引实际上属于语言文字范畴，它的属性和语言文字的属性直接关联起来，比如字，词汇，短语等，全文索引就是通过将文字按照某种语言进行词汇拆分，重新将数据给合存储，来达到快速检索的目的。
A）创建全文索引
gyj@OCM> create table t11(id int,namevarchar2(100));
Table created.
gyj@OCM> insert into t11values(1,'Guo');
1 row created.
gyj@OCM> /
1 row created.
gyj@OCM> insert into t11 values(2,'GuoYi Jun');
1 row created.
gyj@OCM> /
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> create index idx_t11 ont11(name) indextype is ctxsys.context;
Index created.
gyj@OCM> select * from t11 wherecontains(name, 'Guo')>0;
ID NAME
--------------------------------------------------------------------------------------------------------------
1 Guo
1 Guo
2 Guo Yi Jun
2 Guo Yi Jun
2、B树索引与位图索引的比较
（1）B树索引
适用于高基数列
更新关建字的成本相对较低
对OLTP很有用
（2）位图索引
适用于低基数列
更新键列的成本非常高
使用 OR/AND/NOT IN/COUNT(*) 谓词进行查询时效率较高
3、Ｂ树索引的管理
（1）重组索引
rebuild离线重建操作：
alter index ind_name rebuild tablespace tb1;
rebuild在线重建操作：
alter index ind_name rebuild online tablespace tb1;
（2）融合索引:coalescing：
alter index ind_name coalesce;
（3）监控索引
分析索引的逻辑坏损
analyze index index_name validate structure;
分析过索,检查索引结构,分析结果视图：index_stats
Select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;
高度:user_indexes:blevel-->实践经验blevel的值一般小于3,经常为2,偶尔为4;
cost = blevel+
ceiling(leaft_blocks*effective index selectivity) +
ceiling(culstering_factor * effective table selectivity)
五、索引监控
1、监控所有用户下的索引
alter index index_name monitoring usage;
select u.name owner,
io.name index_name,
t.name table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring start_monitoring,
ou.end_monitoring end_monitoring
from sys.user$ u,
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.obj#
and u.user# = io.owner#;
alter index index_name nomonitoring usage;
2、监控当前用户下的索引
alter index reg_id_pk monitoring usage;
select index_name,table_name,used fromv$object_usage;
select * from t1 where id=1;
select index_name,table_name,used fromv$object_usage;
alter index reg_id_pk monitoring usage;
3、查无效索引
select owner,index_name from dba_indexeswhere status='invalid';
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!
答案在：http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
102
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
