oracle 索引的建立與管理

邱東陽發表於2014-06-03

索引的相關建立與使用

建立普通索引

 

SQL> select * from temp;

 

        ID NAME

---------- --------------------

      1456 sony

      1379 sony

      1568 lenovo

      1788 lenovo

 

 

SQL> desc user_ind_columns

Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 INDEX_NAME                                         VARCHAR2(30)

 TABLE_NAME                                         VARCHAR2(30)

 COLUMN_NAME                                        VARCHAR2(4000)

 COLUMN_POSITION                                    NUMBER

 COLUMN_LENGTH                                      NUMBER

 CHAR_LENGTH                                        NUMBER

 DESCEND                                            VARCHAR2(4)

查詢表是否有索引

SQL> select index_name,column_name from user_ind_columns where table_name='TEMP';

 

no rows selected

建立索引

SQL> create index temp_id_i on temp(id);

 

Index created.

 

開啟追蹤

SQL> set autot trace exp

SQL> select * from temp where id=1379;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3945629271

 

--------------------------------------------------------------------------------

---------

 

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

 

--------------------------------------------------------------------------------

---------

 

|   0 | SELECT STATEMENT            |           |     1 |    25 |     2   (0)| 0

0:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP      |     1 |    25 |     2   (0)| 0

0:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_ID_I |     1 |       |     1   (0)| 0

0:00:01 |

 

--------------------------------------------------------------------------------

---------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ID"=1379)

 

Note

-----

   - dynamic sampling used for this statement

 

SQL>

刪除索引在查詢

SQL> drop index temp_id_i;

 

Index dropped.

 

SQL> select * from temp where id=1379;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1896031711

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEMP |     1 |    25 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=1379)

 

Note

-----

   - dynamic sampling used for this statement

 

SQL>

 

 

 

建立排序索引

 

SQL> select * from temp;

 

        ID NAME                        SAL

---------- -------------------- ----------

      1456 sony                       1254

      1379 sony                       1106

      1568 lenovo                     1018

      1788 lenovo                     1758

 

SQL>

想要查詢的語句

SQL> select * from temp where sal>1000 order by sal asc;

 

        ID NAME                        SAL

---------- -------------------- ----------

      1568 lenovo                     1018

      1379 sony                       1106

      1456 sony                       1254

      1788 lenovo                     1758

 

SQL>

建立升序索引

QL> create index temp_sal_i on temp(sal asc);

 

Index created.

 

SQL>

檢視執行計劃

SQL> set autot trace exp

SQL> select * from temp where sal>1100 order by sal asc;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1493261289

 

--------------------------------------------------------------------------------

----------

 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

Time     |

 

--------------------------------------------------------------------------------

----------

 

|   0 | SELECT STATEMENT            |            |     3 |   114 |     2   (0)|

00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP       |     3 |   114 |     2   (0)|

00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_SAL_I |     3 |       |     1   (0)|

00:00:01 |

 

--------------------------------------------------------------------------------

----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("SAL">1100)

       filter("SAL">1100)

 

 

SQL>

如果變為降序

SQL> select * from temp where sal>1100 order by sal desc;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3579856106

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT             |            |     3 |   114 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID | TEMP       |     3 |   114 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN DESCENDING| TEMP_SAL_I |     3 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("SAL">1100)

       filter("SAL">1100)

 

 

SQL>

使索引不對錶進行排序可以建立降序索引

SQL> drop index temp_sal_i;

 

Index dropped.

 

SQL> create index temp_sal_i on temp(sal desc);

 

Index created.

 

SQL> select * from temp where sal>1100 order by sal desc;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1493261289

 

--------------------------------------------------------------------------------

----------

 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

Time     |

 

--------------------------------------------------------------------------------

----------

 

|   0 | SELECT STATEMENT            |            |     3 |   114 |     2   (0)|

00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP       |     3 |   114 |     2   (0)|

00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_SAL_I |     1 |       |     1   (0)|

00:00:01 |

 

--------------------------------------------------------------------------------

----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access(SYS_OP_DESCEND("SAL")

       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SAL"))>1100 AND

              SYS_OP_DESCEND("SAL")

 

Note

-----

   - dynamic sampling used for this statement

 

SQL>

 

建立不排序索引

建立不排序索引的前提是在要建索引的列必須是已經升序排序過的了

 

SQL> create table temp1 as select * from temp;

 

Table created.

截斷表

SQL> truncate table temp1;

 

Table truncated.

插入資料並排序

SQL> insert into temp1 select * from temp order by id;

4 rows created.

 

SQL>

SQL> select * from temp1;

 

        ID NAME                        SAL

---------- -------------------- ----------

      1379 sony                       1106

      1456 sony                       1254

      1568 lenovo                     1018

      1788 lenovo                     1758

 

SQL>

建立不排序索引

SQL> create index temp1_id_i on temp1(id) nosort;

 

Index created.

 

SQL>

 

建立組合索引

注意事項:

要把去掉重複行之後個數少的列放前面 (如果是空值,就把空值最多的放在前面)

例:

SQL> select count(distinct(name)) from temp;

 

COUNT(DISTINCT(NAME))

---------------------

                    2

 

SQL> select count(distinct(sal)) from temp;

 

COUNT(DISTINCT(SAL))

--------------------

                   4

 

SQL>

所以 name列應放在sal列的前面

 

 

QL> select * from temp where name='sony' and sal=1106;

 

        ID NAME                        SAL

---------- -------------------- ----------

      1379 sony                       1106

 

SQL> create index temp_name_sal_i on temp(name,sal);

 

Index created.

 

SQL> set autot trace exp

SQL> select * from temp where name='sony' and sal=1106;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1649455405

 

--------------------------------------------------------------------------------

---------------

 

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C

PU)| Time     |

 

--------------------------------------------------------------------------------

---------------

 

|   0 | SELECT STATEMENT            |                 |     1 |    38 |     2

(0)| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP            |     1 |    38 |     2

(0)| 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_NAME_SAL_I |     1 |       |     1

(0)| 00:00:01 |

 

--------------------------------------------------------------------------------

---------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("NAME"='sony' AND "SAL"=1106)

       filter(SYS_OP_DESCEND("SAL")=HEXTORAW('3DF3F8FF') )

 

 

SQL>

如果索引分開建在查詢時只能用到一個索引

SQL> drop index temp_name_sal_i;

 

Index dropped.

 

SQL>

SQL> create index temp_name_i on temp(name);

 

Index created.

 

SQL>

SQL> create index temp_sal_i on temp(sal);

 

Index created.

 

SQL>

SQL> select * from temp where name='sony' and sal=1106;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1493261289

 

--------------------------------------------------------------------------------

----------

 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

Time     |

 

--------------------------------------------------------------------------------

----------

 

|   0 | SELECT STATEMENT            |            |     1 |    38 |     2   (0)|

00:00:01 |

 

|*  1 |  TABLE ACCESS BY INDEX ROWID| TEMP       |     1 |    38 |     2   (0)|

00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_SAL_I |     1 |       |     1   (0)|

00:00:01 |

 

--------------------------------------------------------------------------------

----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("NAME"='sony')

   2 - access("SAL"=1106)

 

建立無效索引

 

SQL> create index temp_name_i on temp(name) unusable;

 

Index created.

 

SQL>

SQL> select index_name,status from user_indexes;

 

INDEX_NAME                     STATUS

------------------------------ --------

TEMP_NAME_I                    UNUSABLE

TEMP1_ID_I                     VALID

SYS_C005242                    VALID

 

SQL>

SQL> set autot trace exp

SQL> select * from temp where name='sony';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1896031711

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     2 |    76 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEMP |     2 |    76 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("NAME"='sony')

 

 

SQL>

 

重建索引可以使其有效

SQL> alter index temp_name_i rebuild;

 

Index altered.

 

SQL> select * from temp where name='sony';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1257893614

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT            |             |     2 |    76 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP        |     2 |    76 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_NAME_I |     2 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("NAME"='sony')

 

 

SQL>

建立反鍵索引

 

普通索引

SQL> create index temp_name on temp(name);

 

Index created.

 

SQL>

反鍵索引

SQL> create index temp_name_i on temp( reverse(name));

 

Index created.

 

SQL>

查詢以a開頭的走的是普通索引

SQL> select * from temp where name like 'A%';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 953236590

 

--------------------------------------------------------------------------------

---------

 

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

 

--------------------------------------------------------------------------------

---------

 

|   0 | SELECT STATEMENT            |           |     1 |    38 |     0   (0)| 0

0:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP      |     1 |    38 |     0   (0)| 0

0:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_NAME |     1 |       |     0   (0)| 0

0:00:01 |

 

查詢以什麼結尾的會走索引全表掃描

SQL> select * from temp where name like '%Y';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3226310466

 

--------------------------------------------------------------------------------

---------

 

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

 

--------------------------------------------------------------------------------

---------

 

|   0 | SELECT STATEMENT            |           |     1 |    38 |     2   (0)| 0

0:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP      |     1 |    38 |     2   (0)| 0

0:00:01 |

 

|*  2 |   INDEX FULL SCAN           | TEMP_NAME |     1 |       |     1   (0)| 0

0:00:01 |

 

--------------------------------------------------------------------------------

---------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%Y')

 

 

SQL>

 

如果不想索引全表掃描那麼可以使用反鍵

SQL> select * from temp where reverse(name) like 'Y%';

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1257893614

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT            |             |     1 |    38 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP        |     1 |    38 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_NAME_I |     1 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access(REVERSE("NAME") LIKE 'Y%')

       filter(REVERSE("NAME") LIKE 'Y%')

 

 

SQL>

 

對於熱點塊就可以使用反鍵索引(將儲存的值分散到不同的塊中。)

 

將普通索引重建為反鍵索引

SQL>alter index temp1_sal_i rebuild reverse;

 

如果以前列中的值為 0001,0002,0003,0004… 這種連續的

那麼使用反鍵索引就會將值變為1000,2000,3000,4000這種不連續的達到儲存到不同塊中的目的。

 

建立文字索引

 

 

SQL> conn / as sysdba

 

SQL> create table u1.test1 as select * from tab;

 

SQL> conn u1/u1

建立普通索引

SQL>create index test1_tname_i on test1(tname);

SQL>set autot trace exp

查詢在tname列包含SEG的所有值 發現是不走索引的

SQL> select tname from test1 where tname like '%SEG%';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1751340739

 

--------------------------------------------------------------------------------

------

 

| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time

     |

 

--------------------------------------------------------------------------------

------

 

|   0 | SELECT STATEMENT     |               |    25 |   425 |     6   (0)| 00:0

0:01 |

 

|*  1 |  INDEX FAST FULL SCAN| TEST1_TNAME_I |    25 |   425 |     6   (0)| 00:0

0:01 |

 

建立文字索引

SQL> drop index test1_tname_i;

 

SQL> create index test1_tname_i on test1(tname) indextype is ctxsys.context;

 

Index created.

 

SQL>

SQL> select tname from test1 where contains(tname, '%SEG%')>0;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2080224863

 

--------------------------------------------------------------------------------

-------------

 

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU

)| Time     |

 

--------------------------------------------------------------------------------

-------------

 

|   0 | SELECT STATEMENT            |               |     2 |    58 |     4   (0

)| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1         |     2 |    58 |     4   (0

)| 00:00:01 |

 

|*  2 |   DOMAIN INDEX              | TEST1_TNAME_I |       |       |     4   (0

)| 00:00:01 |

 

--------------------------------------------------------------------------------

-------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("CTXSYS"."CONTAINS"("TNAME",'%SEG%')>0)

 

SQL>

 

建立函式索引

要使用函式索引必須保證sysdba使用者以下值是正確的

 

SQL> conn / as sysdba

Connected.

SQL> show parameter que

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      fengzi

job_queue_processes                  integer     10

query_rewrite_enabled                string      TRUE   query_rewrite_enabled 引數為true

query_rewrite_integrity            string      enforced        query_rewrite_integrity數為enforced或者為truseted     函式索引就有效)                                                                                

SQL>

 

 

 

SQL> select * from temp where sal+nvl(comm,0)>4000;

 

        ID NAME                        SAL       COMM

---------- -------------------- ---------- ----------

      1568 lenovo                     1018       3000

      1788 lenovo                     1758       3100

 

SQL>

 

SQL> create index temp_sal_nvl on temp (sal+nvl(comm,0));

 

Index created.

 

SQL>

SQL> set autot trace exp

SQL> select * from temp where sal+nvl(comm,0)>4000;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2060561913

 

--------------------------------------------------------------------------------

------------

 

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

 

--------------------------------------------------------------------------------

------------

 

|   0 | SELECT STATEMENT            |              |     2 |   102 |     2   (0)

| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP         |     2 |   102 |     2   (0)

| 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_SAL_NVL |     1 |       |     1   (0)

| 00:00:01 |

 

--------------------------------------------------------------------------------

------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("SAL"+NVL("COMM",0)>4000)

 

SQL>

 

如果資料庫採用了加密技術如何實現以下語句走索引(採用加密技術的資料庫不等式是不走索引的)

SQL> select * from temp where sal<2000;

SQL> select * from temp where sal between 2000 and 3000 ;

SQL> select * from temp where sal>3000;

可以自己定義函式建立函式索引

SQL> create or replace function gsal(vsal number)

  2  return number  deterministic

  3  as

  4  begin

  5  if vsal<2000 then return 1;

  6  elsif vsal between 2000 and 3000 then return 2;

  7  else return 3;

  8  end if;

  9  end gsal;

10  /

Function created.

 

SQL>

驗證函式是否有效

SQL> select gsal(500) from dual;

 

 GSAL(500)

----------

         1

 

SQL> select gsal(2500) from dual;

 

GSAL(2500)

----------

         2

 

SQL> select gsal(3200) from dual;

 

GSAL(3200)

----------

         3

 

SQL>

建立函式索引

SQL> create index temp_sal_f on temp(gsal(sal));

 

Index created.

 

SQL>

檢視執行計劃

SQL> set autot trace exp

SQL> select * from temp where gsal(sal)=1;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1671644050

 

--------------------------------------------------------------------------------

----------

 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

Time     |

 

--------------------------------------------------------------------------------

----------

 

|   0 | SELECT STATEMENT            |            |     1 |    51 |     2   (0)|

00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP       |     1 |    51 |     2   (0)|

00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_SAL_F |     1 |       |     1   (0)|

00:00:01 |

 

--------------------------------------------------------------------------------

----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("U1"."GSAL"("SAL")=1)

 

建立空值索引

 

建立普通索引

SQL> create index temp_comm_i on temp(comm);

Index created.

SQL>

SQL> set autot trace exp

SQL> select * from temp where comm is null;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1896031711

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     4 |   204 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEMP |     4 |   204 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

建立空值索引

SQL> drop index temp_comm_i;

 

Index dropped.

 

SQL>

SQL> create index temp_comm_i on temp(sys_op_map_nonnull(comm));

 

Index created.

 

SQL> select * from temp where sys_op_map_nonnull(comm)=sys_op_map_nonnull(null);

 

Execution Plan

----------------------------------------------------------

Plan hash value: 285497915

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT            |             |     1 |    51 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP        |     1 |    51 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_COMM_I |     1 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

 

還可以建立組合索引,將有空值的列放在組合索引的前端。

建立隱藏索引

11g以前的版本是不能建立的。

 

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

SALGRADE                       TABLE

 

SQL> create index emp_ename_i on emp(ename) invisible;

 

Index created.

 

SQL>

檢視索引狀態

SQL> select index_name,visibility from user_indexes;

 

INDEX_NAME                     VISIBILIT

------------------------------ ---------

PK_EMP                         VISIBLE

EMP_ENAME_I                    INVISIBLE

PK_DEPT                        VISIBLE

 

SQL>

查詢一下執行計劃看是否走索引

SQL> set autot trace exp

SQL> select * from emp where ename='KING';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ENAME"='KING')

 

實現隱藏索引正常使用需要sysdba使用者修改optimizer_use_invisible_indexes引數為true

SQL> conn / as sysdba    

Connected.

SQL> show parameter optim

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

object_cache_optimal_size            integer     102400

optimizer_capture_sql_plan_baselines boolean     FALSE

optimizer_dynamic_sampling           integer     2

optimizer_features_enable            string      11.2.0.2

optimizer_index_caching              integer     0

optimizer_index_cost_adj             integer     100

optimizer_mode                       string      ALL_ROWS

optimizer_secure_view_merging        boolean     TRUE

optimizer_use_invisible_indexes      boolean     FALSE

optimizer_use_pending_statistics     boolean     FALSE

optimizer_use_sql_plan_baselines     boolean     TRUE

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

plsql_optimize_level                 integer     2

SQL>

SQL> alter session set optimizer_use_invisible_indexes=true;

 

Session altered.

 

SQL> set autot trace exp

SQL> select * from scott.emp where ename='KING';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 549418132

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT            |             |     1 |    87 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    87 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | EMP_ENAME_I |     1 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ENAME"='KING')

 

 

SQL>

可以修改隱藏索引為正常索引

SQL> alter index scott.emp_ename_i visible;

 

Index altered.

 

SQL>

同樣也可以修改正常索引為隱藏索引

SQL> alter index scott.emp_ename_i invisible;

 

Index altered.

 

SQL>

 

 

建立點陣圖索引

 

建立普通索引

SQL> create index temp_name_i on temp(name);

 

Index created.

檢視執行計劃

SQL> set autot trace exp

SQL> select * from temp where name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1257893614

 

--------------------------------------------------------------------------------

-----------

 

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

 

--------------------------------------------------------------------------------

-----------

 

|   0 | SELECT STATEMENT            |             |     4 |   204 |     2   (0)|

 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP        |     4 |   204 |     2   (0)|

 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | TEMP_NAME_I |     4 |       |     1   (0)|

 00:00:01 |

 

--------------------------------------------------------------------------------

-----------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("NAME"='sonny')

 

 

建立點陣圖索引

SQL> drop index temp_name_i;

 

Index dropped.

 

SQL> create bitmap index  temp_name_i on temp(name);

 

Index created.

 

SQL> select * from temp where name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1986849256

 

--------------------------------------------------------------------------------

------------

 

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)

| Time     |

 

--------------------------------------------------------------------------------

------------

 

|   0 | SELECT STATEMENT             |             |     4 |   204 |     1   (0)

| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID | TEMP        |     4 |   204 |     1   (0)

| 00:00:01 |

 

|   2 |   BITMAP CONVERSION TO ROWIDS|             |       |       |

|          |

 

|*  3 |    BITMAP INDEX SINGLE VALUE | TEMP_NAME_I |       |       |

|          |

 

--------------------------------------------------------------------------------

------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("NAME"='sonny')

 

Note

-----

   - dynamic sampling used for this statement

 

SQL>

 

如果發現不走索引可以強制讓語句走索引

SQL> select /*+ index(temp,temp_name_i) */ * from temp where name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1986849256

 

--------------------------------------------------------------------------------

------------

 

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)

| Time     |

 

--------------------------------------------------------------------------------

------------

 

|   0 | SELECT STATEMENT             |             |     4 |   204 |     1   (0)

| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID | TEMP        |     4 |   204 |     1   (0)

| 00:00:01 |

 

|   2 |   BITMAP CONVERSION TO ROWIDS|             |       |       |

|          |

 

|*  3 |    BITMAP INDEX SINGLE VALUE | TEMP_NAME_I |       |       |

|          |

 

--------------------------------------------------------------------------------

------------

 建立點陣圖連線索引

 

SQL> set autot trace exp

SQL> select  count(*) from temp t,coty c where t.id=c.id and c.name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 570564326

 

--------------------------------------------------------------------------------

---------

 

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

ime     |

 

--------------------------------------------------------------------------------

---------

 

|   0 | SELECT STATEMENT              |         |     1 |    38 |     4   (0)| 0

0:00:01 |

 

|   1 |  SORT AGGREGATE               |         |     1 |    38 |            |

        |

 

|   2 |   NESTED LOOPS                |         |     1 |    38 |     4   (0)| 0

0:00:01 |

 

|   3 |    TABLE ACCESS FULL          | TEMP    |     8 |   104 |     3   (0)| 0

0:00:01 |

 

|*  4 |    TABLE ACCESS BY INDEX ROWID| COTY    |     1 |    25 |     1   (0)| 0

0:00:01 |

 

|*  5 |     INDEX UNIQUE SCAN         | PK_COTY |     1 |       |     0   (0)| 0

0:00:01 |

 

--------------------------------------------------------------------------------

---------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("C"."NAME"='sonny')

   5 - access("T"."ID"="C"."ID")

 

Note

-----

   - dynamic sampling used for this statement

 

SQL>

 

建立點陣圖連線索引

SQL> create bitmap index temp_bit on temp(c.name) from temp t,coty c where t.id=c.id;

 

Index created.

 

SQL>

檢視索引狀態

SQL> select index_name,index_type from user_indexes;

 

INDEX_NAME                     INDEX_TYPE

------------------------------ ---------------------------

TEST1_TNAME_I                  DOMAIN

SYS_IOT_TOP_51560              IOT - TOP

SYS_IL0000051557C00002$$       LOB

SYS_IOT_TOP_51555              IOT - TOP

SYS_IL0000051552C00006$$       LOB

DR$TEST1_TNAME_I$X             NORMAL

TEMP_BIT                       BITMAP

SYS_C005242                    NORMAL

PK_COTY                        NORMAL

 

9 rows selected.

 

SQL>

檢視執行計劃

SQL> set autot trace exp

SQL> select  count(*) from temp t,coty c where t.id=c.id and c.name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2037991701

 

--------------------------------------------------------------------------------

--------

 

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

me     |

 

--------------------------------------------------------------------------------

--------

 

|   0 | SELECT STATEMENT            |          |     1 |    13 |     1   (0)| 00

:00:01 |

 

|   1 |  SORT AGGREGATE             |          |     1 |    13 |            |

       |

 

|   2 |   BITMAP CONVERSION COUNT   |          |     4 |    52 |     1   (0)| 00 (發現使用索引資源消耗大大減小)

:00:01 |

 

|*  3 |    BITMAP INDEX SINGLE VALUE| TEMP_BIT |       |       |            |

       |

 

--------------------------------------------------------------------------------

--------

建立虛擬索引

虛擬索引沒有段 是不佔用儲存空間的

 

 

SQL> create index temp_name_i on temp(name) nosegment;

 

Index created.

 

SQL>

收集統計資訊

 

SQL> exec dbms_stats.gather_table_stats('U1','TEMP',cascade=>true);

 

PL/SQL procedure successfully completed.

 

SQL>

檢視統計資訊

SQL> set autot trace exp

SQL> select  name from temp where name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1896031711

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     4 |    24 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEMP |     4 |    24 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("NAME"='sonny')

 

SQL>

需要使用隱藏引數才可以正常使用

 

SQL> alter session set "_use_nosegment_indexes"=true;

 

Session altered.

 

SQL>

SQL> select * from temp where name='sonny';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1896031711

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |               |     1 |    6 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| TEMP_NAME_I  |    1|    6 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("NAME"='sonny')

 

SQL>

建隱藏引數設為false虛擬索引就會無效

SQL> alter session set "_use_nosegment_indexes"=false;

 

Session altered.

 

SQL>

 

監控索引

 

首先要知道索引的名字

SQL> select index_name from user_indexes;

 

 

INDEX_NAME

------------------------------

TEST1_TNAME_I

SYS_IOT_TOP_51560

SYS_IL0000051557C00002$$

SYS_IOT_TOP_51555

SYS_IL0000051552C00006$$

DR$TEST1_TNAME_I$X

TEMP_NAME_I

SYS_C005242

PK_COTY

 

9 rows selected.

 

開啟索引監控

SQL> alter index temp_name_i monitoring usage;

Index altered.

執行跟索引有關查詢語句

SQL> select * from temp where name='sonny';

 

        ID NAME                        SAL       COMM

---------- -------------------- ---------- ----------

      1534 sonny                      1542

      1534 sonny                      1542

      1534 sonny                      1542

      1534 sonny                      1542

 

查詢檢視

SQL> select * from v$object_usage;

 

INDEX_NAME                     TABLE_NAME                     MON USE

------------------------------ ------------------------------ --- ---

START_MONITORING    END_MONITORING

------------------- -------------------

TEMP_NAME_I                    TEMP                           YES YES 

04/28/2014 14:33:57

 

關閉監控

SQL> alter index temp_name_i nomonitoring usage;

 

Index altered.

 

SQL>

 

 

索引重新命名

 

 

SQL> alter index temp_name_i rename to temp_name_index;

 

Index altered.

 

SQL>

 

 

對於分割槽表的索引

全域性索引只有一個根是一個段

 

 

檢視分割槽表是根據那個列分的區

SQL> select name,column_name from user_part_key_columns;

 

  NAME             COLUMN_NAME

---------------------------------------------------------

EMPL               DEPTNO

SQL>

 

全域性索引的建立

SQL> create index empl_deptno_i on empl(deptno);

 

SQL>

如果對這個分割槽進行修改,那麼所有的索引都將無效

 

可以建立一個本地索引這樣對分割槽修改時不會對索引有影響的

 

SQL> create index empl_deptno_ix on empl(deptno) local;

 

如果我新增一個分割槽

SQL> alter table empl add partition empl_p5 values(50);

只需要對新加分割槽重建索引

SQL> alter index empl_deptno_i rebuild partition empl_p5;

 

查詢無效的物件

 

 

SQL> select object_name,object_type,status from dba_objects where owner='SCOTT' and status <> 'VALID';

 

no rows selected

 

SQL>

 

 

查詢索引無效的

 

SQL> select index_name,status from dba_indexes where owner='SCOTT' and status='UNUSABLE';

 

no rows selected

 

SQL>

 

 

分割槽表無效索引

 

SQL> select index_name,status from dba_ind_partitions  where  status='UNUSABLE';

 

no rows selected

 

SQL>

 

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29532781/viewspace-1174694/,如需轉載,請註明出處,否則將追究法律責任。

相關文章