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博客

oracle 11g 虚拟列

最新文章