Oracle alter index disable/unusable的區別,alterunusable

來源:互聯網
上載者:User

Oracle alter index disable/unusable的區別,alterunusable

  alter index index_name disable,enable針對函數索引。

SQL> create table test as select * from all_objects;

SQL> create index ind_t_object_id on test(object_id) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> set autotrace traceonly
SQL> select * from test where object_id = 20;
執行計畫
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    96 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST            |     1 |    96 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=20)
統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        910  bytes sent via SQL*Net to client
        327  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> alter index ind_t_object_id disable;
alter index ind_t_object_id disable
*
第 1 行出現錯誤:
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 選項無效


SQL> alter index ind_t_object_id unusable;
索引已更改。

SQL> select * from test where object_id = 20;
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    96 |   168   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    96 |   168   (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=20)
統計資訊
----------------------------------------------------------
        237  recursive calls
          0  db block gets
        795  consistent gets
          0  physical reads
          0  redo size
        910  bytes sent via SQL*Net to client
        327  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
SQL> drop index ind_t_object_id;
索引已刪除。


SQL> create index ind_t_object_id on test(to_char(object_id)) nologging;
索引已建立。


SQL> select * from test where to_char(object_id) = '20';
執行計畫
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   518 | 49728 |    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST            |   518 | 49728 |    24   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJECT_ID |   207 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(TO_CHAR("OBJECT_ID")='20')
統計資訊
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        910  bytes sent via SQL*Net to client
        327  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> alter index ind_t_object_id disable;
索引已更改。
SQL> select * from test where to_char(object_id) = '20';
select * from test where to_char(object_id) = '20'
*
第 1 行出現錯誤:

ORA-30554: 基於函數的索引FWMS4GZ_DEV_DDL.IND_T_OBJECT_ID被禁用


官方文檔:

DISABLE Clause:
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.


UNUSABLE Clause:
   UNUSABLE Clause Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.


Oracle的Procedure中Alter Index為何異常

看你是用什麼使用者執行procedure
應該是需要授予他:ALTER ANY INDEX 的system privilege
 
oracle問題:alter update modify 的不同是什??

modify是修改欄位類型和長度的
alter是修改表的資料結構,modify只是alter的一種
alter table table_name modify column_name type;
alter是DDL語言

update是DML語言,是修改資料內容的,修改過程中在commit之前會產生寫鎖。
 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.