oracle 11g 虛擬列

11g虛擬列
virtual column不儲存資料，執行時候根據一個基於其它欄位的表示式生成該列的內容(重點在於不儲存資料),應用長期使用表示式比較有用，只對cbo有效.

簡單使用
SQL> create table t_extent (a int,b as(1+a));

Table created.

SQL> insert into t_extent(a) values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_extent;

A B
---------- ----------
1 2

alter table add column

SQL> create table t1(a int);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> commit;

SQL> alter table t1 add b as (a+1);

Table altered.

SQL> SQL> select * from t1;

A B
---------- ----------
1 2

統計資訊與虛擬列

create table t1
nologging
as
select
decode( mod(rownum,4),
  0, 'England',
  1, 'Northern Ireland',
  2, 'Scotland',
  3, 'Wales',
     'Great Britain'
)   country,
decode( mod(rownum,2),
  0,'Australia',
  1,'America',
    'Other'
)   continent,
'Earth'   planet,
1   one
from
all_objects
where
rownum <= 3000
;

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,density,num_nulls,num_distinct from user_tab_col_statistics where table_name='T1';

TABLE_NAME                     COLUMN_NAME                       DENSITY NUM_NULLS NUM_DISTINCT
------------------------------ ------------------------------ ---------- ---------- ------------
T1                             COUNTRY                               .25          0            4
T1                             CONTINENT                              .5          0            2
T1                             PLANET                                  1          0            1
T1                             ONE                                     1          0            1

SQL> select count(*) from t1 where COUNTRY='Wales';

COUNT(*)
----------
750

SQL> set autotrace trace exp
SQL> select * from t1 where COUNTRY='Wales';

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   750 | 21000 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   |   750 | 21000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("COUNTRY"='Wales')

SQL> select count(*) from t1 where upper(COUNTRY)='WALES';

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    10 |            |          |
|* 2 |   TABLE ACCESS FULL| T1   |    30 |   300 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter(UPPER("COUNTRY")='WALES')

建立虛擬列
SQL> alter table t1 add up_COUNTRY as (upper(COUNTRY));

Table altered.
收集統計資訊

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where upper(COUNTRY)='WALES';

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    20 |            |          |
|* 2 |   TABLE ACCESS FULL| T1   |   750 | 15000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("T1"."UP_COUNTRY"='WALES')
可以看到虛擬列上收集統計資訊後正確，另外為此 oracle還加入了擴充套件統計資訊收集

類似使用 SQL> select dbms_stats.create_extended_stats(ownname=>'XH',tabname=>'T2',extension=>'(upper(a))') from dual;
具體擴充套件統計資訊 (具體測試記錄 http://space.itpub.net/12020513/viewspace-624110)

虛擬列能解決的問題，可以代替函式index，建議虛擬列然後在上面建index,比如upper(name)這個可以建成一個虛擬列並在上面建立index，而不用建立一個函式index

SQL> create table t2 (a int,b varchar2(10));

Table created.

declare
begin
for i in 1..5000 loop
insert into t2 values(i,'a'||i);
end loop;
commit;
end;

PL/SQL procedure successfully completed.

SQL> insert into t2 values(6000,'xhb');

1 row created.

SQL> insert into t2 values(6000,'axh');

1 row created.

SQL> commit;

Commit complete.
SQL> create index t2_id on t2(b);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> SQL> select * from t2 where b like '%xh%';

         A B
---------- ----------
      6000 xhb
      6000 axh

SQL> select CLUSTERING_FACTOR from user_indexes where index_name='T2_ID';

CLUSTERING_FACTOR
-----------------
904

like 開頭為% 或_，不會使用index，使用了fts，解決這種問題通常是建立一個 reverse(X) 的函式index，查詢時候使用reverse(XX) like reverse('%a') 可以走這個函式index
，現在有了虛擬列我們可以建立一個虛擬列，並在上面建立一個普通 index既可

SQL> select * from t2 where b like 'xh%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3119810522

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 9 | 3 (0)| 00:00
:01 |

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

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

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

2 - access("B" LIKE 'xh%')
filter("B" LIKE 'xh%'

SQL> select * from t2 where b like '%xh';

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   250 | 2250 |     5   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2   |   250 | 2250 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("B" LIKE '%xh')

SQL> create index t2_id_re on t2(reverse(b));

Index created.

SQL> select CLUSTERING_FACTOR from user_indexes where index_name='T2_ID_RE';

CLUSTERING_FACTOR
-----------------
4982

SQL> Select * from t2 where reverse(b) like reverse('%xh');

A B
---------- ----------
6000 axh

SQL> select * from t2 where reverse(b) like reverse('%xh');

Execution Plan
----------------------------------------------------------
Plan hash value: 3865927368

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

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

| 0 | SELECT STATEMENT | | 250 | 2250 | 4 (0)| 00
:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 250 | 2250 | 4 (0)| 00
:00:01 |

|* 2 | INDEX RANGE SCAN | T2_ID_RE | 45 | | 2 (0)| 00
:00:01 |

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

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

2 - access(REVERSE("B") LIKE 'hx%')
filter(REVERSE("B") LIKE 'hx%')

SQL> alter table t2 add d as (reverse(b));

Table altered.
SQL> create index t2_like_re on t2(d);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> select CLUSTERING_FACTOR from user_indexes where index_name='T2_LIKE_RE';

CLUSTERING_FACTOR
-----------------
4982

SQL> select * from t2 where d like reverse('%xh');

A B D
---------- ---------- ----------
6000 axh hxa

SQL> select * from t2 where d like reverse('%xh');

Execution Plan
----------------------------------------------------------
Plan hash value: 194083129

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 3 (0)|
00:00:01 |

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

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

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

2 - access("D" LIKE 'hx%')
filter("D" LIKE 'hx%')

分割槽表針對FTS 的優化基本就是優化logical reads，那麼分割槽表的分割槽修剪是極其有必要的，當<>,not in ,not null,表示式，函式時候將不會使用分割槽修剪
那麼針對，函式，表示式的情況可以用虛擬列解決（必須要使用虛擬列作為分割槽KEY，分割槽修剪必須要where條件中有分割槽key）

create table t3 (id number,name varchar2(50))
partition by range(id)(
partition t3_range_p1 values less than (1000),
partition t3_range_p2 values less than (2000),
partition t3_range_p3 values less than (3000) ,
partition t3_range_pmax values less than (maxvalue));

Table created.

declare
begin
for i in 1..5000 loop
insert into t3 values(i,'a'||i);
end loop;
commit;
end;
/

execute dbms_stats.gather_table_stats('SYS','T3')

SQL> set linesize 1000
SQL> select * from t3 where id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2814083523

-----------------------------------------------------------------------------------------------
| Id | Operation              | Name | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     8 |     2   (0)| 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE|      |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
|* 2 |   TABLE ACCESS FULL    | T3   |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

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

2 - filter("ID"=100)

SQL> select * from t3 where to_char(id)='100';

Execution Plan
----------------------------------------------------------
Plan hash value: 947723311

--------------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     9 |     5   (0)| 00:00:01 |       |       |
|   1 | PARTITION RANGE ALL|      |     1 |     9 |     5   (0)| 00:00:01 |     1 |     4 |
|* 2 |   TABLE ACCESS FULL | T3   |     1 |     9 |     5   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------

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

2 - filter(TO_CHAR("ID")='100')
可以看到掃描了 4個分割槽

create table t4 (id number,name varchar2(50),char_id as (to_char(id)))
partition by range(char_id)(
partition t4_range_p1 values less than (1000),
partition t4_range_p2 values less than (2000),
partition t4_range_p3 values less than (3000) ,
partition t4_range_pmax values less than (maxvalue));

declare
begin
for i in 1..5000 loop
insert into t4(id,name) values(i,'a'||i);
end loop;
commit;
end;

SQL> execute dbms_stats.gather_table_stats('SYS','T4')

PL/SQL procedure successfully completed.

SQL> select * from t4 where char_id='100';

Execution Plan
----------------------------------------------------------
Plan hash value: 3296030324

-----------------------------------------------------------------------------------------------
| Id | Operation              | Name | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    10 |     2   (0)| 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE|      |     1 |    10 |     2   (0)| 00:00:01 |     1 |     1 |
|* 2 |   TABLE ACCESS FULL    | T4   |     1 |    10 |     2   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

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

2 - filter("CHAR_ID"='100')

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

oracle 11g 虛擬列

相關文章