SQL索引与管理详解-优快云博客

索引类似于一本书的目录，我们通过查询目录就可以找到指定数据所在的具体位置，
而不需要翻越整本书。索引在检索数据方面具有高效性，尤其是从存储了大量数据的表中查询数据时。
索引是一种树状结构，可以分为两类：
1.从逻辑设计
      单列索引，组合索引，唯一性索引，非唯一性索引，基于函数的索引...
2.从物理实现
      分区索引，非分区索引，b树索引，正向索引，反向索引，位图索引，位图联接索引...
oracle提供了这么多索引，常用到的如下：
·B*树索引
    ·索引组织表
    ·B*树聚簇索引
    ·降序索引
    ·反向键索引
·位图索引
·位图联结索引
·基于函数的索引
·应用域索引

索引管理
注意下面问题：
·装载数据后再建立索引
·索引应建立在where子句经常引用的列上
·在联接属性上建立索引
·不要再小表上建立索引
·在经常需要排序操作的列上建立索引
·删除不经常使用的索引
·指定索引块的参数，如果将来会在表上执行大量的insert操作，建立索引时设定较大的ptcfree
·指定索引所在的表空间，将表和索引放在不同的表空间上可以提高性能

创建索引

[c-sharp] view plain copy

create [unique] index index_name on
table_name(col_name[,col_name...])
tablespace table_space_name;
·unique:指定索引列中值必须是唯一的
·index_name:索引名
·table_name:指定要建立索引的表
·col_name:要建立索引的列，可以是多列，那样的索引叫多列索引
·table_space_name:索引存储的表空间

实例1:

[c-sharp] view plain copy

如果我们经常对emp表进行按照empno来查询某个员工的信息，
SQL> select * from emp where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
我们就应该对empno这一列建一个索引。
create index indx_on_empno
on emp(empno)
tablespace users;

实例2:

[c-sharp] view plain copy

如果我们经常查询某个部门工资大于1000的员工信息，
那么我们就可以在job和sal列上建立所以，这叫组合索引：
SQL> select * from emp
2 where job='SALESMAN' and sal>1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
create index indx_on_job_sal
on emp(job,sal)
tablespace users;

实例3:

[c-sharp] view plain copy

我们还可以创建基于函数的索引，不过在此前，我们必须设置一下初始化参数：
conn system/chage_on_install as sysdba;
alter system set query_rewrite_enabled=true;
create index indx_lower_job
on emp(lower(job))
tablespace users;

修改索引

[c-sharp] view plain copy

就像修改表一样，我们在创建索引后可以进行修改
实例1:分配和释放索引空间
alter index indx_on_empno
allocate extent(size 1m);
实例2:释放多余的索引空间
alter index indx_on_empno
deallocate unused;
实例3:重建索引
alter index indx_on_job_sal rebuild;
实例4:联机重建索引，
使用rebuild，若其他用户正在表上执行dml操作，重建会失败，
使用如下语句，就可以成功重建
alter index indx_on_job_sal rebuild online;
实例5:合并索引
当相邻索引块存在剩余空间，通过合并索引将其合并到一个索引块上
alter index indx_on_job_sal coalesce;
实例6:重命名索引
alter index indx_on_job_sal rename to indx_on_jobandsal;

删除索引

[c-sharp] view plain copy

如果索引不再需要了，留在数据库中将会占用资源，我们可以将其删除
drop index indx_on_job_sal;

有关索引的信息

[c-sharp] view plain copy

我们可以从unser_indexes视图中查看有关某表的索引信息，
下面我先来看看unser_indexes视图的信息:
SQL> desc user_indexes;
Name Type Nullable Default Comments
----------------------- -------------- -------- ------- --------------------------------------------------------------------------------------
INDEX_NAME VARCHAR2(30) Name of the index
INDEX_TYPE VARCHAR2(27) Y
TABLE_OWNER VARCHAR2(30) Owner of the indexed object
TABLE_NAME VARCHAR2(30) Name of the indexed object
TABLE_TYPE VARCHAR2(11) Y Type of the indexed object
UNIQUENESS VARCHAR2(9) Y Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"
COMPRESSION VARCHAR2(8) Y Compression property of the index: "ENABLED", "DISABLED", or NULL
PREFIX_LENGTH NUMBER Y Number of key columns in the prefix used for compression
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the index
INI_TRANS NUMBER Y Initial number of transactions
MAX_TRANS NUMBER Y Maximum number of transactions
INITIAL_EXTENT NUMBER Y Size of the initial extent in bytes
NEXT_EXTENT NUMBER Y Size of secondary extents in bytes
MIN_EXTENTS NUMBER Y Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER Y Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER Y Percentage increase in extent size
PCT_THRESHOLD NUMBER Y Threshold percentage of block space allowed per index entry
INCLUDE_COLUMN NUMBER Y User column-id for last column to be included in index-only table top index
FREELISTS NUMBER Y Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER Y Number of freelist groups allocated to this segment
PCT_FREE NUMBER Y Minimum percentage of free space in a block
LOGGING VARCHAR2(3) Y Logging attribute
BLEVEL NUMBER Y B-Tree level
LEAF_BLOCKS NUMBER Y The number of leaf blocks in the index
DISTINCT_KEYS NUMBER Y The number of distinct keys in the index
AVG_LEAF_BLOCKS_PER_KEY NUMBER Y The average number of leaf blocks per key
AVG_DATA_BLOCKS_PER_KEY NUMBER Y The average number of data blocks per key
CLUSTERING_FACTOR NUMBER Y A measurement of the amount of (dis)order of the table this index is for
STATUS VARCHAR2(8) Y Whether the non-partitioned index is in USABLE or not
NUM_ROWS NUMBER Y Number of rows in the index
SAMPLE_SIZE NUMBER Y The sample size used in analyzing this index
LAST_ANALYZED DATE Y The date of the most recent time this index was analyzed
DEGREE VARCHAR2(40) Y The number of threads per instance for scanning the partitioned index
INSTANCES VARCHAR2(40) Y The number of instances across which the partitioned index is to be scanned
PARTITIONED VARCHAR2(3) Y Is this index partitioned? YES or NO
TEMPORARY VARCHAR2(1) Y Can the current session only see data that it place in this object itself?
GENERATED VARCHAR2(1) Y Was the name of this index system generated?
SECONDARY VARCHAR2(1) Y Is the index object created as part of icreate for domain indexes?
BUFFER_POOL VARCHAR2(7) Y The default buffer pool to be used for index blocks
USER_STATS VARCHAR2(3) Y Were the statistics entered directly by the user?
DURATION VARCHAR2(15) Y If index on temporary table, then duration is sys$session or sys$transaction else NULL
PCT_DIRECT_ACCESS NUMBER Y If index on IOT, then this is percentage of rows with Valid guess
ITYP_OWNER VARCHAR2(30) Y If domain index, then this is the indextype owner
ITYP_NAME VARCHAR2(30) Y If domain index, then this is the name of the associated indextype
PARAMETERS VARCHAR2(1000) Y If domain index, then this is the parameter string
GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculated without merging underlying partitions?
DOMIDX_STATUS VARCHAR2(12) Y Is the indextype of the domain index valid
DOMIDX_OPSTATUS VARCHAR2(6) Y Status of the operation on the domain index
FUNCIDX_STATUS VARCHAR2(8) Y Is the Function-based Index DISABLED or ENABLED?
JOIN_INDEX VARCHAR2(3) Y Is this index a join index?
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3) Y Were redundant primary key columns eliminated from iot secondary index?
DROPPED VARCHAR2(3) Y Whether index is dropped and is in Recycle Bin
我们常关心的也就那么几列：index_name,table_owner,table_name,uniqueness,status.
实例7:我们来查询一下scott用户下的索引有哪些
SQL> select index_name,table_owner,table_name,uniqueness,status from user_indexes;
INDEX_NAME TABLE_OWNER TABLE_NAME UNIQUENESS STATUS
------------------------------ ------------------------------ ------------------------------ ---------- --------
PK_EMP SCOTT EMP UNIQUE VALID
PK_DEPT SCOTT DEPT UNIQUE VALID

有关列索引的信息

[sql] view plain copy

我们可以从user_ind_columns中获得列素颜的信息，先看看user_ind_columns视图的结果:
SQL> desc user_ind_columns;
Name Type Nullable Default Comments
--------------- -------------- -------- ------- ---------------------------------------------------------------
INDEX_NAME VARCHAR2(30) Y Index name
TABLE_NAME VARCHAR2(30) Y Table or cluster name
COLUMN_NAME VARCHAR2(4000) Y Column name or attribute of object column
COLUMN_POSITION NUMBER Y Position of column or attribute within index
COLUMN_LENGTH NUMBER Y Maximum length of the column or attribute, in bytes
CHAR_LENGTH NUMBER Y Maximum length of the column or attribute, in characters
DESCEND VARCHAR2(4) Y DESC if this column is sorted descending on disk, otherwise ASC
实例8:查看emp和dept表中列的索引信息
SQL> select index_name,table_name,column_name
2 from user_ind_columns
3 where table_name in ('EMP','DEPT');
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------------------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO