5.3.3  移除数据表
使用DROP TABLE语句，可以移除数据表中所有的数据和数据表结构及约束，例如要删除invoice_check表，可以使用如下所示的语句：

DROP TABLE invoice_check;

在使用DROP TABLE语句时，Oracle会检查要移除的表是否存在与其他表的依赖关系，如果存在，Oracle将不允许删除，例如在vendors表中包含了两条数据，如下所示。

SQL> set linesize 300;
SQL> SELECT * FROM vendors;
 VENDOR_ID   VENDOR_NAME
---------- --------------
         1     路人甲供应商
         2     路人乙供应商

invoice表中的vendor_id与vendors表中的vendor_id具有主外键关系，同时在invoice表中存在一条记录引用到了vendors表，如下所示。

SQL> SELECT * FROM invoice;
INVOICE_ID    VENDOR_ID   INVOICE_NUMBER    
------------ ---------- -----------------
         1          1            0001

此时如果移除vendors表，将会弹出异常，如下所示。

SQL> DROP TABLE vendors;
DROP TABLE vendors
           *
ERROR 位于第 1 行:
ORA-02449: 表中的唯一/主键被外部关键字引用

如果要让Oracle能成功移除vendors表，必须首先移除invoice表，然后再移除vendors。Oracle提供了在DROP TABLE中可以使用的CASCADE CONSTRAINTS语句来级联移除关联关系，如果使用如下的语法：

DROP TABLE vendors CASCADE CONSTRAINTS;

则vendors表被成功移除，同时移除了vendors表和invoice表之间的主外键约束，但是存储在invoice表中的vendor_id资料并没有被删除，依然存在。

5.3.4  在设计器中修改表
大多数Oracle管理工具都提供了可视化的表的创建与管理工具，例如Toad功能强大的表信息窗口提供了各种各样的可视化窗体，允许用户轻松地管理Oracle数据库的方方面面。
举个例子，如果要向现有的表中添加一行，可以使用如下两种方法打开表信息窗口。
    在Schema Browser中的Tables节点（或标签页）下面的列表中选中某个表，在Toad右侧会显示表信息窗口，由多个Tab页组成。
    在SQL或PL/SQL编辑器中，选中某个表名按F4键，将弹出表信息窗口。
在打开了表信息窗口后，选中Columns标签页，通过单击工具栏的 图标，将弹出向表中添加新列的窗口，如图5.14所示。
可以看到添加列窗口还包含了Foreign Key标签页，允许为所创建的新列指定外键，如图5.15所示。SQL标签页会帮助用户生成ALTER TABLE语句代码，例如图5.14中创建的列，在SQL标签页中会产生如下的代码：

ALTER TABLE APPS.INVOICE
ADD (invoice_name VARCHAR2(50 BYTE) CONSTRAINT invoice_name_ck 
CHECK (invoice_name=UPPER(invoice_name)))
COMMENT ON COLUMN 
APPS.INVOICE.invoice_name IS 
'发票名称检查约束';

表信息的Constraints页提供了所有的约束信息列表，可以通过工具栏的5个按钮完成添加新约束、启用或禁用约束、重命名约束及删除约束等工作，对于每个约束，可以在约束详细信息的Grid中按F4键查看约束的详细信息，Constraints约束列表如图5.16所示。
约束详细信息窗口如图5.17所示，Script标签页会产生约束的SQL代码，以便于开发人员使用。关于Oracle SQL Developer及PL/SQL Developer表修改的用法，请参考相关的产品文档。


[img]http://www.tu265.com/di-b3550e47e9c6c0a702b362aeb50e455c.png[/img]

[img]http://www.tu265.com/di-e52e91da0495cff24b90c40de6e7696c.png[/img]

5.4  索    引
索引是数据管理系统提供的一种用来快速访问表中数据的机制。在数据库管理系统中，索引的意义非常重大，使用索引可以显著提高对数据库数据的查询效率，减少磁盘的IO操作，提升整个数据库系统的性能。在本书前面的内容中实际上已经自动创建过索引，当定义主键或唯一性约束时，Oracle会自动在相应的字段上创建唯一性索引，用户也可以使用SQL语句在其他的列上手动地创建非唯一性索引。
5.4.1  索引简介
索引是建立在数据库表中的一列或多列用来加速访问表中数据的辅助对象。通俗地说，索引类似于一本书的目录，或者是电子书的书签，例如“Oracle SQL Reference”这本书，如果要查找Oracle SQL的操作符部分，那么有如下两种方式。
    顺序访问方式：这种方式按顺序依次定位每一页，判断要翻到的页面是否为需要的页。当一本书的页数过多时，这种方式无疑既花费时间又耗费资源。
    索引访问方式：由电子书制作人员提前将图书的每章每节制作成书签，每个书签都指向章节的特定位置，当要查找某个内容时，只需要在书签中查看，通过书签定位到特定的目标位置。
索引与数据的示意图如图5.18所示。
 
图5.18  索引示意图
使用索引具有如下优点。
    索引可以大大加快检索数据的速度。
    使用唯一性索引可以保证数据库表中每一行数据的唯一性。
    通过索引可以加快表与表之间的连接。
    在使用分组和排序子句进行数据检索时，使用索引可以显著地减少查询中分组和排序的时间。
但是索引需要在表基础上创建，需要占用额外的物理空间，而且对表进行修改时，比如增、删、改数据的时候，需要动态地进行维护，这会降低数据维护的速度。
为了演示索引的作用，下面使用CREATE TABLE..AS语法创建scott.emp表的一个副本，如以下代码所示。

CREATE TABLE emp_index AS SELECT * from emp;
可以通过查询all_indexes表来获知索引的基本信息，使用如下的SELECT语句：

SQL> SELECT * FROM all_indexes WHERE TABLE_NAME='EMP_INDEX';
未选定行

可以看到执行结果不包含任何索引。
现在scott.emp和emp_index两张表具有完全相同的结构，但是emp包含一个唯一性的索引，而emp_index不包含任何索引。如果查询emp_index表中员工工号为7369的信息，使用如下的SQL：

SELECT * FROM emp_index WHERE empno=7369;

Oracle将会使用顺序访问方式逐个比较员工的编号，直到找到工号为7369的员工信息，这种方式称为全表扫描。当数据量成千上万甚至上百万条记录时，使用这种方式由于需要遍历整个列，效率会极其低下。
emp表中基于empno存在一个唯一性的索引，当使用下面的代码查询all_indexes表时，可以看到存在一个UNIQUE索引。

SQL> SELECT owner, index_name, table_name, uniqueness
      FROM all_indexes
     WHERE table_name = 'EMP' AND table_owner = 'SCOTT'
OWNER     INDEX_NAME        TABLE_NAME        UNIQUENESS
------- --------------- -------------- -----------------
SCOTT     SYS_C0094364          EMP            UNIQUE

在Toad中，可以选中要执行的语句按下快捷键Ctrl+E来查看SQL语句的执行路径。对于emp表，其执行使用了唯一性索引扫描，如下所示。

Plan
SELECT STATEMENT  CHOOSE        
    2 TABLE ACCESS BY INDEX ROWID APPS.EMP     
        1 INDEX UNIQUE SCAN UNIQUE APPS.EMP_PK 

对于emp_index，没有使用任可索引机制，可以看到使用的是全表扫描。

Plan
SELECT STATEMENT  CHOOSE    
    1 TABLE ACCESS FULL APPS.EMP_INDEX

5.4.2  索引原理
在Oracle数据表中，每一张表都有一个ROWID伪列，这个ROWID是用来唯一标志一条记录所在物理位置的一个id号，每一行对应的ROWID值是固定而且唯一的。一旦数据存入数据库就确定，不会在对数据库表操作的过程中发生改变，只有在表发生移动或表空间变化等操作产生物理位置变化时，才会发生改变。
下面的代码使用ROWIDTOCHAR内置函数将查询到的ROWID值输出为字符串，代码及输出如下所示。

SQL> set linesize 300;
SQL> SELECT ROWIDTOCHAR(rowid) rowid_char,x.* from emp x;
ROWID_CHAR               EMPNO ENAME      JOB               MGR HIREDATE
-------------------- ------------- ------------- ----------------------
AAAR3sAAEAAAACUAAB      7888 张三          经理                  15-8月 -11
AAAR3sAAEAAAACXAAA      7369 SMITH         CLERK               7902 17-12月-80
AAAR3sAAEAAAACXAAB      7499 ALLEN         SALESMAN         7698 20-2月 -81
......
已选择15行。

当为ename这个列建立了一个索引后，例如使用如下的语句：

CREATE INDEX idx_emp_ename ON emp_index(ename);

Oracle在创建idx_emp_ename索引时，会对emp_index进行一次全表扫描，获取每条记录ename列的数据，并进行升序排列。同时会获取每条记录的ROWID值，连同排序后的ename列一起存储到索引段中，其格式是（索引列值，ROWID），这种组合也称为索引条目。
注意：在索引段中保存排序后的索引列的值及代表着物理地址的ROWID值。
当检索数据时，比如使用WHERE子句按指定条件检索数据时，Oracle将首先对索引中的列进行快速搜索，由于索引列已经过排序，因此可以使用各种快速的搜索算法，这样就可以避免对全表进行扫描。在找到所要检索的数据后，通过ROWID在emp_index中读取具体的记录值。

5.4.3  创建索引
索引的创建方式分为如下两种。
    自动创建：在定义主键约束或唯一约束时，Oracle自动在相应的约束列上建立唯一索引，Oracle不推荐人为地创建唯一索引。
    手动创建：用户可以在其他列上创建非唯一索引。
在Oracle中，索引根据其组织形式又可以分为多种类型，分别如下所示。
    单列索引：索引基于单个列所创建。
    复合索引：索引基于多个列所创建。
    B树索引：这是Oracle默认使用的索引，B树索引可以是单列索引或复合索引、唯一索引或非唯一索引，索引按B树结构组织并存放索引数据。
    位图索引：为索引列的每个取值创建一个位图，对表中的每行使用1位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值。
    函数索引：索引的取值不直接来自列，而是来自包含有列的函数或表达式，这就是函数索引。
索引的创建语法如下所示。

CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];
这些参数的含义如下所示。
    UNIQUE：表示唯一索引，默认情况下，不使用该选项。
    BITMAP：表示创建位图索引，默认情况下，不使用该选项。
    PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。
    NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项。
    ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认情况下，不使用该选项。
    NOSORT：默认情况下，不使用该选项。Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。
要使用CREATE INDEX语句创建索引，需要具有如下两种权限。
    CREATE INDEX：当在用户所在的方案中创建索引时需要具备的权限。
    CREATE ANY INDEX：要在其他用户方案中创建索引时需要具备的权限。
在创建索引时，会对全表进行扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息保存到数据字典中。
如果在使用CREATE INDEX时，不指定任何索引类型参数，默认创建的就是标准的B树索引，例如下面的语句在emp_index表中创建了两个不同的B树索引：

CREATE INDEX idx_emp_empnoname ON emp_index(ename,empno);    --B树索引
CREATE INDEX idx_emp_job ON emp_index(job);                    --B树索引
CREATE BITMAP INDEX idx_emp_job_bitmap ON emp_index(job);    --位图索引
CREATE INDEX idx_emp_name ON emp(UPPER(ename));                --函数索引
注意：当创建复合索引时，索引列的顺序决定了索引的性能，通常要将最常查询的列放在前面，不常查询的列放在后面。两个具有不同名称的复合索引列，使用了相同的字段但是顺序不同是合法的。
Toad提供了具有非常多的选项的可视化索引创建窗口，可以在表信息窗口的“indexes”标签页查看索引的详细信息，如图5.19所示。可以通过图中工具栏上的 按钮来创建索引，Toad将弹出如图5.20所示的新建索引窗口，可以看到Toad提供了非常详细的索引创建选项。
在设置了索引的创建选项后，可以通过单击底部的Show SQL按钮查看向导生成的创建索引的SQL语句。
由于索引的创建会带来一定的性能开销，因此必须要注意创建索引的一些基本原则。下面是创建索引常见的10条原则。
    小表不需要建立索引，比如emp表只有数十行记录，可以不建立索引。
    对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
    对于大部分列值不重复的列可建立索引。
    对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
    对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。
    LONG和LONG RAW列不能创建索引。
    经常进行连接查询的列上应该创建索引。
    在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。
    维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，索引多就有好处，但是，如果一个表经常被更改，则索引应少点。
    在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。

[img]http://www.tu265.com/di-e52e91da0495cff24b90c40de6e7696c.png[/img]

[img]http://www.tu265.com/di-9d6717152642c5696bc0de671af50789.png[/img]

[img]http://www.tu265.com/di-9d6717152642c5696bc0de671af50789.png[/img]

[img]http://www.tu265.com/di-f074ac887122b4e97c41bd1cb926c0e8.png[/img]

5.4.4  修改索引
如果在创建好索引之后，发现索引的命名不符合命名规范，需要重命名，或者是索引在使用一段时间后，需要重建索引，可以使用ALTER INDEX语句。下面分别对几种常见的索引修改方式进行介绍。
1．重命名索引
当对已经创建的索引的名称不满意时，可以通过ALTER TABLE..RENAME TO语句更改索引的名称，使用示例如下所示。

SQL> ALTER INDEX idx_emp_empnoname RENAME TO idx_ename_empno;
索引已更改。

在ALTER INDEX语句的后面，跟上索引的名称（可以使用方案名.索引名称），在RENAME TO语句后面，跟上要进行重命名的最终名称。
2．合并和重建索引
表在使用一段时间后，由于频繁地对表进行操作，而每次对表的更新必然伴随着索引的改变，因此，在索引中会产生大量的碎片，从而降低索引的使用效率。可以使用如下两种方式来清理碎片。
    合并索引：合并索引不改变索引的物理组织结构，只是简单地将B树叶子节点中的存储碎片合并在一起。
    重建索引：重新创建一个新的索引，删除原来的索引。
合并索引使用ALTER INDEX COALESCE语法，例如下面的语法对idx_ename_empno索引进行了合并操作：

SQL> ALTER INDEX idx_ename_empno COALESCE;
索引已更改。

合并只是简单地将B树中的叶子节点中的碎片合在一起，其实并没有改变索引的物理组织结构，例如并不会对叶子节点的存储参数和表空间进行更改，合并执行前与合并执行后的示意图如图5.21所示。
 
图5.21  合并索引示意图
从图5.21中可以看到，合并前放在两个节点中的碎片被合并到了1个节点，而另一个叶子节点就被释放了。
重建索引实际上就是对原有的索引的删除，再重新建一个新的索引，因为这个原因，所以在使用ALTER INDEX时，可以使用各种存储参数，比如使用STORAGE指定存储参数，使用TABLE SPACE指定表空间或利用NOLOGGING选项避免产生重做日志信息。
例如要重建idx_ename_empno索引，可以使用如下的语句：

SQL> ALTER INDEX idx_ename_empno REBUILD;
索引已更改。

也可以使用存储语句更改索引所在的表空间，例如如下语句：

ALTER INDEX idx_ename_empno REBUILD TABLESPACE users;

上面的语句在重建索引的时候，使用TABLESPACE选项将索引移到了users表空间中。
合并索引和重建索引都能消除索引碎片，但二者在使用上有明显的区别。
    合并索引不能将索引移动到其他表空间，但重建索引可以；
    合并索引代价较低，无须额外存储空间，但重建索引恰恰相反；
    合并索引只能在B树的同一子树中合并，不改变树的高度，但重建索引重建整个B树，可能会降低树的高度。
3．分配和释放索引空间
在插入或者加载数据时，如果表中具有索引，会同时在索引中添加数据，如果索引段空间不足，为了能够向索引段添加数据将导致动态地扩展索引段，从而降低了数据的装载速度。为了避免这个问题，可以在执行装载或大批量插入之前为索引段分配足够的空间，如以下语法所示。

SQL> ALTER INDEX idx_ename_empno ALLOCATE EXTENT(SIZE 200K);
索引已更改。

上述语法首先将idx_ename_empno索引段的索引扩容200KB，以便能容纳所插入的索引数据。
当索引段占用了过多的空间，而实际上用不了这样多的空间时，可以通过DEALLOCATE UNUSED来释放多余的空间，如以下语句所示。

SQL> ALTER INDEX idx_ename_empno DEALLOCATE UNUSED;
索引已更改。

上述语句执行后，将释放未曾使用的索引空间。