#sql #oracle #oracle12c
Недавно проводили миграцию пользовательской системы на 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
В релизе 11g была введена новая техника оптимизации для повышения производительности DDL операций. Это нововведение позволяет экстремально снизить время выполнения, если добавить NOT NULL колонку со значением по-умолчанию к существующей таблице. В релизе 12c эта техника оптимизации была расширена так же и для NULL колонок имеющих значение по-умолчанию. Посмотрим на примере таблицы с 1.000.000 строчек: sql> create table xxy as select rownum a from dual connect by level <= 1e6 ; Теперь добавим новую колонку и сравним 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 ms! План выполнения показывает: 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$. Этот предикат даёт понять, что БД внутри себя считает, что колонка B потенциально может содержать значения отличающиеся от таковых по-умолчанию. Из этого следует, БД в действительности не производит сразу изменение всех строк, чтобы добавить значение по-умолчанию для новой колонки. Для чего всё таки колонка 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 и им соответствующих значениях скрытой колонки SYS_NC00006$. БД просто проверяет на основании значения скрытой внутренней колонки и с помощью функции SYS_OP_VECBIT, надо ли применять для колонки B значение по-умолчанию, или колонка уже содержит реальное значение добавленное явным путём. Почему скрытая колонка не создаётся в случае двух раздельных DDL выражений? 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 для всех строчек. Так как никакого обновления в действительности не требуется, то и в оптимизации нет никакой необходимости. Статья на OTN, использованая как источник, была удалена или временно недостуна. В настоящее время доступен только её перевод на китайский.
Комментариев нет:
Отправить комментарий