oracle實驗記錄 （函式index）

要求
QUERY_REWRITE_ENABLED=TRUE和QUERY_REWRITE_INTEGRITY=RUSTED
SQL>  create table test (a varchar2(5));

Table created.

 1  declare
 2  begin
 3  for i in 1..100 loop
 4  insert into test values ('a');
 5  end loop;
 6  commit;
 7* end;

SQL> explain plan for select * from test where upper(a)='A';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3836831076

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   400 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   100 |   400 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(UPPER("A")='A')**********************************

Note
-----

   - dynamic sampling used for this statement ~~動態採集

SQL> create index test_fun on test(upper(a));

Index created.

SQL> explain plan for select * from test where upper(a)='A';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1975250114

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

--------

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

me     |

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

--------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |   100 |   400 |     1   (0)| 00

:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |   100 |   400 |     1   (0)| 00

:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST_FUN |     1 |       |     1   (0)| 00

:00:01 |

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

--------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("A")='A')************************

Note
-----
   - dynamic sampling used for this statement

18 rows selected.
SQL> select file_id,extent_id,block_id,blocks from dba_extents where segment_nam
e='TEST_FUN';

   FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         1          0      57393          8

SQL> alter system dump datafile 1  block 57394;

System altered.
row#0[8025] flag: ------, lock: 0, len=11
col 0; len 1; (1):  41
col 1; len 6; (6):  00 40 dd 7a 00 00~~~~~~~~~有6字的rowid

節的rowid
row#1[8014] flag: ------, lock: 0, len=11
col 0; len 1; (1):  41
col 1; len 6; (6):  00 40 dd 7a 00 01
row#2[8003] flag: ------, lock: 0, len=11
col 0; len 1; (1):  41
col 1; len 6; (6):  00 40 dd 7a 00 02
row#3[7992] flag: ------, lock: 0, len=11
col 0; len 1; (1):  41
col 1; len 6; (6):  00 40 dd 7a 00 03
row#4[7981] flag: ------, lock: 0, len=11

SQL> select chr(to_number(41,'xxxx'))  from dual;

CH
--
A~~~~~~~~~~~~~~函式計算後的值存index中 成為一個虛擬列

函式index對 inser,update有影響 對應更新index有麻煩

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