Oracle为什么在这里添加一个隐藏的列? [英] Why does Oracle add a hidden column here?
问题描述
我们最近将客户系统迁移到Oracle 12c和产品的最新版本.此过程包括运行许多迁移脚本，这些脚本主要添加或更改表.我们注意到，在向表中添加列同时提供默认值的同时，还会创建一个额外的隐藏列SYS_NC00002$.
We recently migrated a customer system to Oracle 12c and to the latest version of our product. This process includes running a number of migration scripts which mostly add or change tables. We noticed that adding a column to a table while also providing a default value, creates an additional hidden column SYS_NC00002$.
您应该可以使用以下代码重制此
You should be able to reproduce this with the following code
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 |
当我填充表格并查看该隐藏列中的值时，它们都是相同的:
When I populate the table and look at the values in that hidden column, they are all the same:
select distinct SYS_NC00002$ from xxx;
Sys_Nc00002$|
-------------
01 |
令人惊讶的是，当我没有立即设置默认值，而是在一条额外的语句中，没有创建任何其他隐藏列.
Amazingly, when I don't set the default value right away but in an extra statement, no additional hidden column is created.
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 |
谁能解释这个隐藏的列是干什么用的，为什么只在第一个示例中创建它，而不是在第二个示例中创建?
Can anyone explain what this hidden column is for and why it is only created in the first example, but not in the second?
推荐答案
在Oracle 11g版中，Oracle引入了一种新的优化技术，用于改善DDL操作的性能.当将具有默认值的 NOT NULL 列添加到现有表时，此新功能可以极大地缩短执行时间.自12c版以来，DDL优化已扩展为包括具有默认值的 NULL 列.
In Oracle release 11g Oracle has introdused a new optimization technique for improving the performance of DDL operations. This new feature allows extremely rapid execution time when adding a NOT NULL column with default value to an existing table. Since release 12c the DDL optimization has been extended to include NULL columns having default value.
考虑以下具有1.000.000行的测试表:
Consider following test table with 1.000.000 rows:
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的不同会话中添加一个额外的非空列，该列具有默认值:
Now we're going to add an extra not null column having a default value in different sessions for 11g and 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
请注意执行时间的差异:5毫秒内更新了1M行！
Notice the difference in the execution time: 1M rows updated in 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
与11g相比，在12c上的执行计划显示了包含新内部列SYS_NC00006$的复杂谓词部分.
The execution plan on 12c shows by contrast with 11g a complex predicate part involving a new internal column SYS_NC00006$.
此谓词表明，在内部，Oracle仍在考虑B列可能包含非默认值.这意味着-Oracle首先不会物理地使用默认值更新每一行.
This predicate indicates that, internally, Oracle is still considering the B column to be potentially able to contain non default values. It means - Oracle at first do not physically updates each row with the default value.
为什么要创建一个新的内部列SYS_NC00006$?
Why a new internal column SYS_NC00006$ is created?
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语句修改的实际值.
Notice the difference in the values of B and related internal columns. Oracle is simply checking through its system generated internal column (e.g. SYS_NC00006$) and via the SYS_OP_VECBIT function whether to consider the default value of the B column or the real value modiefed via an explicit DML statement.
两个单独的alter语句是什么?
What is with two separate alter statements?
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
所有行的new列的值均保持为NULL.不需要真正的更新，因此不会优化DDL语句.
The value of new column remains NULL for all rows. No real updates are needed therefore the DDL statement will be not optimized.
此处是一个OTN本文详细介绍了新的DDL优化.
Here is an OTN article that explains the new DDL optimization in more detail.
这篇关于Oracle为什么在这里添加一个隐藏的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
