Home  >  Article  >  Database  >  Oracle有效建立索引的小技巧

Oracle有效建立索引的小技巧

WBOY
WBOYOriginal
2016-06-07 17:10:171325browse

数据库版本: SQLgt; select * from v$version; BANNER ---------------------------------------------------------------- Or

数据库版本:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

以SCOTT用户的表为例,看一下表DEPT的索引:
SQL> select index_name,column_name,column_position from user_ind_columns where table_name='DEPT';

INDEX_NAME           COLUMN_NAME                              COLUMN_POSITION
-------------------- ---------------------------------------- ---------------
PK_DEPT              DEPTNO                                                 1

分析一下表:
SQL> analyze table dept estimate statistics;
SQL> analyze table emp  estimate statistics;

执行两个查询:
SQL> select deptno,dname from dept;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=44)
   1    0   TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)
  
SQL> select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=5 Card=14 Bytes=252)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)
  
可以看到,两个查询都是全表扫描。如果DEPT表比较小,全表扫描也不错,但数据量大似乎不太好。

建个联合索引,
SQL> create index idx_dept_multi on dept (deptno,dname);

重新分析一下表DEPT,
SQL> analyze table dept estimate statistics;

再执行上面的两个查询。
SQL> select deptno,dname from dept;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=44)
   1    0   INDEX (FULL SCAN) OF 'IDX_DEPT_MULTI' (NON-UNIQUE) (Cost=1 Card=4 Bytes=44)
  
SQL> select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=4 Card=14 Bytes=252)
   2    1    INDEX (FULL SCAN) OF 'IDX_DEPT_MULTI' (NON-UNIQUE) (Cost =1 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)
  
通过执行计划,看到DEPT已经不是全表扫描了,COST也有所降低。对于一个有多个字段的表,如果经常查询的只是其中两、三个字段,如用户表、客户表等,把常用字段一起建一个索引,,可以起到不错的效果。

linux

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn