Почему Oracle добавляет скрытый столбец здесь?

Недавно мы перенесли клиентскую систему на Oracle 12c и на последнюю версию нашего продукта. Этот процесс включает в себя запуск нескольких сценариев миграции, которые в основном добавляют или меняют таблицы. Мы заметили, что добавление столбца в таблицу, а также предоставление значения по умолчанию, создает дополнительный скрытый столбец SYS_NC00002$.

Вы должны иметь возможность воспроизвести это со следующим кодом

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           |

Когда я заполняю таблицу и просматриваю значения в этом скрытом столбце, они все одинаковы:

select distinct SYS_NC00002$ from xxx;

Sys_Nc00002$|
-------------
01          |

Удивительно, когда я не устанавливаю значение по умолчанию сразу, но в дополнительном выражении не создается дополнительный скрытый столбец.

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           |

Может ли кто-нибудь объяснить, для чего этот скрытый столбец и почему он создан только в первом примере, но не во втором?

Ответы

Ответ 1

В выпуске Oracle 11g Oracle внедрила новую технику оптимизации для повышения производительности DDL-операций. Эта новая функция позволяет очень быстро выполнять время при добавлении столбца NOT NULL со значением по умолчанию в существующую таблицу. Начиная с версии 12c, оптимизация DDL была расширена, включив столбцы NULL, имеющие значение по умолчанию.

Рассмотрим следующую тестовую таблицу с 1.000.000 строк:

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);

Теперь мы добавим дополнительный не нулевой столбец со значением по умолчанию в разных сеансах для 11g и 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

Обратите внимание на разницу во времени выполнения: 1M строк обновлены в 5 мс!?

План выполнения показывает:

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

План выполнения на 12c показывает в отличие от 11g сложную предикатную часть с новым внутренним столбцом SYS_NC00006$.

Этот предикат указывает, что внутри, Oracle по-прежнему рассматривает столбец B, который потенциально может содержать нестандартные значения. Это означает, что Oracle сначала физически не обновляет каждую строку со значением по умолчанию.

Почему создается новый внутренний столбец 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

Обратите внимание на разницу в значениях B и соответствующих внутренних столбцов. Oracle просто проверяет свой внутренний системный столбец (например, SYS_NC00006$) и через функцию SYS_OP_VECBIT, следует ли учитывать значение по умолчанию столбца B или реальное значение, модифицированное с помощью явного оператора DML.

Что такое два отдельных оператора alter?

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

Значение нового столбца остается NULL для всех строк. Никаких реальных обновлений не требуется, поэтому оператор DDL не будет оптимизирован.

Здесь - статья OTN, в которой более подробно объясняется новая оптимизация DDL.