pantheon-4771206_640

Columnas virtuales en Oracle. Uso y limitaciones

Hola a todos, hoy queríamos refrescar con vosotros una utilidad de Oracle que aunque es muy potente la solemos utilizar poco, las columnas virtuales. Las columnas virtuales son columnas de tablas Oracle cuyo contenido está basado en una fórmula o expresión que hace uso de los datos de otras columnas. Se deben entender casi como una columna mas de la tabla, pudiéndose ser utilizadas como parte de la indexación de la tabla, como claves foráneas en otra tabla, incluir comentarios, etc. Pero OJO, son de SÓLO LECTURA, no se puede volcar información en ellas a través de una sentencia INSERT o UPDATE.

Estas columnas “penalizan” la CPU, puesto que son calculadas “al vuelo” (en insert, update, deletes, select y otras, como habilitar y deshabilitar constraints), pero no consumen espacio de almacenamiento. También consumen memoria, ya que si hacemos un fetch, los valores se guardan en la memoria al igual que ocurre con el resto de campos.

La fórmula o expresión que se puede usar puede incluir funciones PL/SQL, pero se corre el riesgo de penalizar el rendimiento, por lo que se recomiendan dejar lo más sencillas posibles para no tener este problema.

Limitaciones de las columnas virtuales en Oracle

Habrá que tener en cuenta una serie de limitaciones. En estas columnas no se puede:

  • “Anidar” columnas virtuales, o sea, no podemos usar una columna virtual en la expresión o fórmula de otra columna virtual.
  • Usar columnas de otras tablas para la expresión (en realidad si, si se hace dentro de una función PL/SQL), solo las de la tabla a la que pertenezca la columna virtual.
  • Utilizar ciertos tipos de columna (campos LONG o definidas por el usuario por ejemplo) para las columnas virtuales.

La sintaxis sería la siguiente:

Sintaxis Columnas virtuales en Oracle. GPSOS

Nuestra columna virtual en este caso es la col4 y contiene la diferencia en días entre dos fechas. Si insertamos registros en nuestra nueva tabla vemos:

SQL> insert into PRUEBA (col1, col2, col3) values (1, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'));
  
 1 row created.
  
 SQL> select * from PRUEBA;
  
       COL1 COL2      COL3            COL4
 ---------- --------- --------- ----------
          1 20-FEB-21 22-FEB-21          2 

En col4 ya nos calcula directamente la diferencia en días de las dos fechas, 2 en este caso. Aunque podemos utilizar la columna como cualquier otra de la base de datos, las operaciones de escritura sobre ella no están permitidas. Si intentamos por ejemplo insertar el valor:

insert into PRUEBA (col1, col2, col3, col4) values (2, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'), 2);     
ERROR at line 1: 
 ORA-54013: INSERT operation disallowed on virtual columns  

Tampoco podemos insertar como si la tabla tuviera solo tres campos ya que nos fallará:

insert into PRUEBA values (1, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'));
  
 ERROR at line 1:
 ORA-00947: not enough values 

Para comprobar que el valor se actualiza automáticamente probamos a cambiar una de las fechas:

 SQL> update PRUEBA set col3 = to_date('25/02/2021','DD/MM/YYYY') where col1=1;
 1 row updated.
 SQL> select * from PRUEBA;
       COL1 COL2      COL3            COL4
 ---------- --------- --------- ----------
          1 20-FEB-21 25-FEB-21          5 

El valor cambia de 2 a 5 porque se recalcula en la select.

Uso de índices en estas columnas

Una utilidad importante de la funcionalidad es la de poder crear índices sobre las columnas virtuales, aquí es donde podemos obtener importantes ventajas de rendimiento. En este caso la columna virtual sí ocupa espacio, en este caso en el índice:

create index PRUEBA_I1 on PRUEBA (col4);

Si miramos el plan de ejecución nuevo cuando utilizamos el campo col4:

 SQL> explain plan for select * from PRUEBA where col4<5;
  
 Explained.
  
 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
 Plan hash value: 38723808
  
 -----------------------------------------------------------------------------------------
 | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |           |     1 |    44 |     0   (0)| 00:00:01 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| PRUEBA    |     1 |    44 |     0   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | PRUEBA_I1 |     1 |       |     0   (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
    2 - access("COL4"<5)
  
 Note
 -----
    - dynamic sampling used for this statement (level=2)
  
 18 rows selected. 

Vemos que lo utiliza y esto puede hacer que el rendimiento mejore notablemente al poder evitarnos el cálculo y la búsqueda de los registros uno a uno.

También podemos ver los valores de nuestras columnas virtuales con la siguiente query:

 SELECT 
     column_name, 
     virtual_column,
     data_default
 FROM 
     all_tab_cols
 WHERE table_name = 'PRUEBA';
  
  
 COLUMN_NAME                    VIR DATA_DEFAULT
 ------------------------------ --- ------------
 COL1                           NO
 COL2                           NO
 COL3                           NO
 COL4                           YES "COL3"-"COL2" 

Esperamos que la entrada sea de vuestro interés, nos vemos en la siguiente entrada.

Un saludo.

Para no perderte la siguiente entrada ni ninguna de nuestras publicaciones. Suscríbete a nuestra newsletter. Con un solo email al mes estarás informado de nuestras publicaciones.

Más información: https://oracle-base.com/articles/11g/virtual-columns-11gr1

Comments are closed.