Vous connaissez la table EMP du schema SCOTT !
desc EMP
Name Null? Type
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
11g vous permet d’y ajouter, comme à toutes vos tables, des colonnes virtuelles. Pour cela, utilisez la clause « GENERATED ALWAYS AS » ou plus simplement « AS » après avoir specifié le type de données de votre colonne comme ci-dessous :
alter table emp add
sal_comm number
generated always as (sal+nvl(comm,0));
Table altered.
Votre table à maintenant une colonne en plus :
desc EMP
Name Null? Type
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SAL_COMM NUMBER
Et vous pouvez simplement l’interroger comme n’importe quelle colonne :
select empno,ename,sal_comm
from emp
where sal_comm>2900
/
EMPNO ENAME SAL_COMM
----- ---------- ----------
7566 JONES 2975
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0vxq332b0gmaz, child number 0
-------------------------------------
select empno,ename,sal_comm from emp where sal_comm>2900
Plan hash value: 3956160932
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 29 | 3 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"+NVL("COMM",0)>2900)
Par contre, comme vous pouvez vous y attendre, vous ne pouvez pas la mettre à jour :
SQL> update emp set sal_comm=0;
update emp set sal_comm=0
*
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns
Vous pouvez l’indexer ; l’index créé est un index fonction :
create index emp_sal_comm
on emp(sal_comm);
Index created.
col INDEX_NAME format a12
col INDEX_TYPE format a21
select index_name, index_type, table_owner
from user_indexes
where index_name='EMP_SAL_COMM';
INDEX_NAME INDEX_TYPE TABLE_OWNER
------------ --------------------- -----------
EMP_SAL_COMM FUNCTION-BASED NORMAL SCOTT
Si vous voulez réaliser un calcul plus complexe, vous pouvez toujours créer un package ou une fonction et l’intégrer dans votre colonne virtuelle. Ne faites pas ce qui suit bien sur ! c’est juste pour l’exemple !
create or replace function dept_name(p_deptno number)
return varchar2 deterministic is
v_out varchar2(50);
begin
select dname into v_out
from dept where deptno=p_deptno;
return v_out;
end;
/
alter table emp add dname varchar2(50) as
(cast (dept_name(deptno) as varchar2(50)));
Enfin, et ça peut être le plus utile pour résoudre une requête associée à cette colonne virtuelle, vous pouvez prendre des statistiques sur cette colonne virtuelle ; j’ai déjà montré dans un post précédent que les statistiques sur des colonnes virtuelles peuvent changer un plan et les performances d’une requête :
select count(ENDPOINT_NUMBER)-1 NUM_BUCKETS
from user_histograms
where table_name='EMP'
and COLUMN_NAME='SAL_COMM';
NUM_BUCKETS
-----------
-1
exec dbms_stats.gather_table_stats(user,'EMP', -
method_opt=>'FOR COLUMNS SAL_COMM SIZE 254');
PL/SQL procedure successfully completed.
select count(ENDPOINT_NUMBER)-1 NUM_BUCKETS
from user_histograms
where table_name='EMP' and COLUMN_NAME='SAL_COMM';
NUM_BUCKETS
-----------
12
Avant de partir, laissez le schéma SCOTT comme vous l’avez trouvé :
alter table emp drop column dname;
alter table emp drop column sal_comm;
Pour en savoir plus sur toutes ces fonctionnalités, reportez-vous à la documentation et en particulier :
