| 通过索引返回查询优化SQL | DBA之路：OoNiceDream |

通过索引返回查询优化SQL

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/query-using-index.html

通过索引返回查询，避免TABLE ACCESS BY INDEX ROWID，这时不去读表，与表数据的存放无关，有时能起到优化SQL的作用。

实验一：
创建表BIG_TAB，表空间USER_DATA，创建索引IND_ID，表空间IND_TBS。
将表空间USER_DATA离线，通过索引仍能返回查询，通过TABLE ACCESS BY INDEX ROWID访问数据则报错。

SQL> create table big_tab 
  2  tablespace user_data
  3  as select OBJECT_ID,OWNER 
  4  from dba_objects;
 
Table created.
 
SQL> select count(*) from big_tab;
 
  COUNT(*)
----------
      5900
 
SQL> create index IND_ID on big_tab(object_id) tablespace IND_TBS;
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'BIG_TAB',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> explain plan for
  2  select * from big_tab where object_id=10;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |     8 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TAB     |     1 |     8 |     2 |
|*  2 |   INDEX RANGE SCAN          | IND_ID      |     1 |       |     1 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("BIG_TAB"."OBJECT_ID"=10)
 
Note: cpu costing is off
 
15 rows selected.
 
SQL> explain plan for
  2  select count(*) from big_tab where object_id=10;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     4 |     1 |
|   1 |  SORT AGGREGATE      |             |     1 |     4 |       |
|*  2 |   INDEX RANGE SCAN   | IND_ID      |     1 |     4 |     1 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("BIG_TAB"."OBJECT_ID"=10)
 
Note: cpu costing is off
 
15 rows selected.
 
SQL> alter tablespace user_data offline;
 
Tablespace altered.
 
SQL> select * from big_tab where object_id=10; 
select * from big_tab where object_id=10
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oradata/DBA/USER_DATA01.dbf'
 
 
SQL> select count(*) from big_tab where object_id=10;
 
  COUNT(*)
----------
         1
 
SQL> alter tablespace user_data online;
 
Tablespace altered.
 
SQL>

实验二：
通过创建联合索引，使用索引返回查询，避免TABLE ACCESS BY INDEX ROWID，优化SQL。

SQL> insert into big_tab
  2  select * from big_tab;
 
5900 rows created.
 
SQL> /
 
11800 rows created.
 
SQL> /
 
23600 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from big_tab;
 
  COUNT(*)
----------
     47200
 
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'BIG_TAB',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> set autotrace on
SQL> select /*+ use_table_access_by_rowid */ owner from big_tab where object_id=10;
 
OWNER
------------------------------
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
 
8 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=8 Bytes=64)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TAB' (Cost=9 Card=8 Bytes=64)
   2    1     INDEX (RANGE SCAN) OF 'IND_ID' (NON-UNIQUE) (Cost=1 Card=8)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
 
SQL> create index ind_obj_ow on big_tab(object_id,owner) tablespace ind_tbs;
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'BIG_TAB',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> select /*+ use_index */ owner from big_tab where object_id=10;
 
OWNER
------------------------------
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
 
8 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=8 Bytes=64)
   1    0   INDEX (RANGE SCAN) OF 'IND_OBJ_OW' (NON-UNIQUE) (Cost=2 Card=8 Bytes=64)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
 
SQL> alter tablespace user_data offline;
 
Tablespace altered.
 
SQL> select owner from big_tab where object_id=10;
 
OWNER
------------------------------
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
 
8 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=8 Bytes=64)
   1    0   INDEX (RANGE SCAN) OF 'IND_OBJ_OW' (NON-UNIQUE) (Cost=2 Card=8 Bytes=64)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
 
SQL> drop index ind_obj_ow;
 
Index dropped.
 
SQL> select owner from big_tab where object_id=10;
select owner from big_tab where object_id=10
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oradata/DBA/USER_DATA01.dbf'
 
 
SQL> alter tablespace user_data online;
 
Tablespace altered.
 
SQL> select owner from big_tab where object_id=10;
 
OWNER
------------------------------
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
 
8 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=8 Bytes=64)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TAB' (Cost=9 Card=8 Bytes=64)
   2    1     INDEX (RANGE SCAN) OF 'IND_ID' (NON-UNIQUE) (Cost=1 Card=8)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          8  physical reads
          0  redo size
        598  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
 
SQL>