reportes - oracle transponer registros columnas
¿Por qué Oracle agrega una columna oculta aquí? (1)
Recientemente hemos migrado un sistema de cliente a Oracle 12c y a la última versión de nuestro producto. Este proceso incluye la ejecución de una serie de scripts de migración que en su mayoría agregan o cambian tablas. Nos dimos cuenta de que al agregar una columna a una tabla y al mismo tiempo proporcionar un valor predeterminado, se crea una columna oculta adicional SYS_NC00002$ .
Debes poder reproducir esto con el siguiente código
create table xxx (a integer);
alter table xxx add (b integer default 1);
select table_name, column_name, data_type, data_length, column_id, default_length, data_default from user_tab_cols where table_name=''XXX'';
Table_Name|column_Name |data_Type|data_Length|column_Id|default_Length|data_Default|
------------------------------------------------------------------------------------
XXX |A |NUMBER | 22| 1| | |
XXX |SYS_NC00002$|RAW | 126| | | |
XXX |B |NUMBER | 22| 2| 1|1 |
Cuando relleno la tabla y miro los valores en esa columna oculta, todos son iguales:
select distinct SYS_NC00002$ from xxx;
Sys_Nc00002$|
-------------
01 |
Sorprendentemente, cuando no configuro el valor predeterminado de inmediato, pero en una declaración adicional, no se crea una columna oculta adicional.
create table xxy (a integer);
alter table xxy add (b integer);
alter table xxy modify b default 1;
select table_name, column_name, data_type, data_length, column_id, default_length, data_default from user_tab_cols where table_name=''XXY'';
Table_Name|column_Name|data_Type|data_Length|column_Id|default_Length|data_Default|
-----------------------------------------------------------------------------------
XXY |A |NUMBER | 22| 1| | |
XXY |B |NUMBER | 22| 2| 1|1 |
¿Alguien puede explicar para qué es esta columna oculta y por qué solo se crea en el primer ejemplo, pero no en el segundo?
En la versión 11g de Oracle, Oracle ha presentado una nueva técnica de optimización para mejorar el rendimiento de las operaciones DDL. Esta nueva característica permite un tiempo de ejecución extremadamente rápido al agregar una columna NOT NULL con un valor predeterminado a una tabla existente. Desde la versión 12c, la optimización de DDL se ha ampliado para incluir columnas NULL con valor predeterminado.
Considere la siguiente tabla de prueba con 1.000.000 filas:
sql> create table xxy
as select rownum a from dual connect by level <= 1e6
;
sql> select /*+ gather_plan_statistics */ count(1) from xxy;
sql> select * from table(dbms_xplan.display_cursor);
Ahora vamos a agregar una columna extra no nula con un valor predeterminado en diferentes sesiones para 11g y 12c:
11g> alter table xxy add b number default 1;
--Table XXY altered. Elapsed: 00:01:00.998
12c> alter table xxy add b number default 1;
--Table XXY altered. Elapsed: 00:00:00.052
Observe la diferencia en el tiempo de ejecución: ¡1M filas actualizadas en 5 ms!
El plan de ejecución muestra:
11g> select count(1) from xxy where b = 1;
COUNT(1)
----------
1000000
11g> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1040 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| XXY | 898K| 11M| 1040 (1)| 00:00:13 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
12c> select count(1) from xxy where b = 1;
12c> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 429 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| XXY | 1000K| 4882K| 429 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("
B",1),''0'',NVL("B",1),''1'',"B")=1)
Note
-----
- statistics feedback used for this statement
El plan de ejecución en 12c muestra, en contraste con 11g, una parte de predicado complejo que incluye una nueva columna interna SYS_NC00006$ .
Este predicado indica que, internamente, Oracle aún considera que la columna B puede contener valores no predeterminados. Esto significa que, al principio, Oracle no actualiza físicamente cada fila con el valor predeterminado.
¿Por qué se crea una nueva columna interna SYS_NC00006$ ?
12c> select column_name, virtual_column, hidden_column, user_generated
from user_tab_cols
where table_name = ''XXY''
;
COLUMN_NAME VIR HID USE
---------------- --- --- ---
B NO NO YES
SYS_NC00002$ NO YES NO
A NO NO YES
12c> select a, b, SYS_NC00002$ hid from xxy where a in (1,10);
A B HID
---------- ---------- ----------------
1 1
10 1
12c> update xxy set b=1 where a=10 and b=1;
1 row updated.
12c> select a, b, SYS_NC00002$ hid from xxy where a in (1,10);
A B HID
---------- ---------- ----------------
1 1
10 1 01
Observe la diferencia en los valores de B y las columnas internas relacionadas. Oracle simplemente está verificando a través de su columna interna generada por el sistema (por ejemplo, SYS_NC00006$ ) y a través de la función SYS_OP_VECBIT si considera el valor predeterminado de la columna B o el valor real a través de una declaración DML explícita.
¿Qué pasa con dos declaraciones alter separadas?
12c> alter table xxy add (b integer);
12c> alter table xxy modify b default 1;
12c> select count(b), count(coalesce(b,0)) nulls from xxy where b = 1 or b is null;
COUNT(B) NULLS
---------- ----------
0 1000000
El valor de la nueva columna permanece en NULL para todas las filas. No se necesitan actualizaciones reales, por lo tanto, la declaración DDL no se optimizará.
oracle.com/technetwork/articles/database/… hay un artículo de OTN que explica la nueva optimización de DDL con más detalle.