Oracle이 여기에 숨겨진 열을 추가하는 이유는 무엇입니까?

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           |

이 숨겨진 열이 무엇을 위한 것인지, 그리고 왜 첫 번째 예에서만 생성되고 두 번째 예에서는 생성되지 않는지 설명할 수 있는 사람이 있습니까?

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에 대해 서로 다른 세션에 기본값이 있는 null이 아닌 열을 추가하겠습니다.

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 행이 5ms 내에 업데이트되었습니다!?

실행 계획은 다음을 참조하십시오.

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_VECBITB 열의 기본값을 고려할지 또는 명시적인 DML 문을 통해 수정된 실제 값을 고려할지 여부를 함수로 나타냅니다.

두 개의 개별 변경문은 무엇입니까?

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 문이 최적화되지 않습니다.

다음은 새로운 DDL 최적화에 대해 자세히 설명하는 OTN 기사입니다.

언급URL : https://stackoverflow.com/questions/45756882/why-does-oracle-add-a-hidden-column-here