[20231020]增加字段的问题.txt

[20231020]增加字段的问题.txt --//测试生产系统遇到的增加字段的相关问题.非常容易混乱,我还是给自己测试看看,加强记忆. 1.环境: TTT@192.168.2.7:1521/orcl> @ ver1 TTT@192.168.2.7:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试准备: TTT@192.168.2.7:1521/orcl> create table ty as select deptno from dept; Table created. TTT@192.168.2.7:1521/orcl> @ gts ty '' '' '' Gather Table Statistics for table ty... exec dbms_stats.gather_table_stats('TTT', 'TY', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) if lock table ty, add force=>true. press ctrl+c cancel, enter continue... PL/SQL procedure successfully completed. 3.增加字段: --//操作顺序如下： alter table ty add (status1 varchar2(10) ); alter table ty modify status1 default 'AAAAA'; alter table ty add (status2 varchar2(10) default 'BBBBB'); alter table ty add (status3 varchar2(10)); TTT@192.168.2.7:1521/orcl> insert into ty (deptno) values (50); 1 row created. TTT@192.168.2.7:1521/orcl> commit ; Commit complete. TTT@192.168.2.7:1521/orcl> select ty.*,SYS_NC00003$ from ty; DEPTNO STATUS1 STATUS2 STATUS3 SYS_NC0000 ------ ------- ------- ------- ----------     10         BBBBB     20         BBBBB     30         BBBBB     40         BBBBB     50 AAAAA   BBBBB           01 --//再次增加1个字段，注意写法与上面都不同。 TTT@192.168.2.7:1521/orcl> alter table ty add (status4 varchar2(10)  default 'CCCCC' not null); Table altered. TTT@192.168.2.7:1521/orcl> select ty.*,SYS_NC00003$ from ty; DEPTNO STATUS1 STATUS2 STATUS3 STATUS4 SYS_NC0000 ------ ------- ------- ------- ------- ----------     10         BBBBB           CCCCC     20         BBBBB           CCCCC     30         BBBBB           CCCCC     40         BBBBB           CCCCC     50 AAAAA   BBBBB           CCCCC   01 TTT@192.168.2.7:1521/orcl> insert into ty (deptno) values (60); 1 row created. TTT@192.168.2.7:1521/orcl> commit ; Commit complete. TTT@192.168.2.7:1521/orcl> select ty.*,SYS_NC00003$ from ty; DEPTNO STATUS1 STATUS2 STATUS3 STATUS4 SYS_NC0000 ------ ------- ------- ------- ------- ----------     10         BBBBB           CCCCC     20         BBBBB           CCCCC     30         BBBBB           CCCCC     40         BBBBB           CCCCC     50 AAAAA   BBBBB           CCCCC   01     60 AAAAA   BBBBB           CCCCC   01 6 rows selected.          --//主要目的看看是否修改数据块. TTT@192.168.2.7:1521/orcl> select rowid from ty; ROWID ------------------ AABni7AAMAAC8KDAAA AABni7AAMAAC8KDAAB AABni7AAMAAC8KDAAC AABni7AAMAAC8KDAAD AABni7AAMAAC8KHAAA AABni7AAMAAC8KHAAB block_row_dump: tab 0, row 0, @0x1f7a tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 0b tab 0, row 1, @0x1f71 tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 15 tab 0, row 2, @0x1f68 tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 1f tab 0, row 3, @0x1f5f tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 29 end_of_block_dump block_row_dump: tab 0, row 0, @0x1f84 tl: 20 fb: --H-FL-- lb: 0x1  cc: 4 col  0: [ 2]  c1 33 col  1: [ 5]  41 41 41 41 41 col  2: [ 1]  01 col  3: [ 5]  42 42 42 42 42 tab 0, row 1, @0x1f69 tl: 27 fb: --H-FL-- lb: 0x2  cc: 6 col  0: [ 2]  c1 3d col  1: [ 5]  41 41 41 41 41 col  2: [ 1]  01 col  3: [ 5]  42 42 42 42 42 col  4: *NULL* col  5: [ 5]  43 43 43 43 43 end_of_block_dump End dump data blocks tsn: 5 file#: 41 minblk 770695 maxblk 770695 --//可以看出这样增加字段 alter table ty add (status4 varchar2(10)  default 'CCCCC' not null);,oracle并不修改数据块. --//那这样的方式如何处理的呢? SELECT obj#         ,col#         ,segcol#         ,name         ,default$         ,type#     FROM sys.col$    WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = user AND object_name = 'TY') ORDER BY segcol#;         TTT@192.168.2.7:1521/orcl> /   OBJ# COL# SEGCOL# NAME          DEFAULT$ TYPE# ------ ---- ------- ------------- -------- ----- 424123    1       1 DEPTNO                     2 424123    2       2 STATUS1       'AAAAA'      1 424123    0       3 SYS_NC00003$              23 424123    3       4 STATUS2       'BBBBB'      1 424123    4       5 STATUS3                    1 424123    5       6 STATUS4       'CCCCC'      1 6 rows selected. TTT@192.168.2.7:1521/orcl> select * from sys.ecol$ where tabobj#= 424123 ; TABOBJ# COLNUM BINARYDEFVAL GUARD_ID ------- ------ ------------ --------  424123      4 4242424242          0  424123      6 4343434343 --//status2,status4字段在sys.ecol$都有记录，但是GUARD_ID值不同. TTT@192.168.2.7:1521/orcl> select * from ty where status1='A' or status2='B' or status3='C' or status4='D' ; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  0jqyswgwf35q1, child number 0 ------------------------------------- select * from ty where status1='A' or status2='B' or status3='C' or status4='D' Plan hash value: 1260447134 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     4 (100)|          | |*  1 |  TABLE ACCESS FULL| TY   |      2 |    38 |     4   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / TY@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter((DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL(               "STATUS2",'BBBBB'),'0',NVL("STATUS2",'BBBBB'),'1',"STATUS2")='B' OR               NVL("STATUS4",'CCCCC')='D' OR "STATUS1"='A' OR "STATUS3"='C')) --//注意看加入的过滤条件. "STATUS1"='A' (DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("STATUS2",'BBBBB'),'0',NVL("STATUS2",'BBBBB'),'1',"STATUS2")='B' "STATUS3"='C' NVL("STATUS4",'CCCCC')='D' --//感觉有时候oracle为了加入新特性,搞的有点复杂. --//再次提醒,一定要结合sql语句上下文,不要想当然根据NVL("STATUS4",'CCCCC')='D'条件,建立这样的函数索引. 4.继续测试: TTT@192.168.2.7:1521/orcl> create table tz as select deptno from dept; Table created. TTT@192.168.2.7:1521/orcl> @ gts tz '' '' ... TTT@192.168.2.7:1521/orcl> alter table tz add (z1 varchar2(10)  default '11111' not null); Table altered. SELECT obj#         ,col#         ,segcol#         ,name         ,default$         ,type#     FROM sys.col$    WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = user AND object_name = 'TZ') ORDER BY segcol#;   OBJ# COL# SEGCOL# NAME   DEFAULT$ TYPE# ------ ---- ------- ------ -------- ----- 424128    1       1 DEPTNO              2 424128    2       2 Z1     '11111'      1 --//这样加入并没有引入隐含字段. TTT@192.168.2.7:1521/orcl> select * from sys.ecol$ where tabobj#= 424128 ;    TABOBJ#     COLNUM BINARYDEFVAL                     GUARD_ID ---------- ---------- ------------------------------ ----------     424128          2 3131313131 --//3131313131= 11111 TTT@192.168.2.7:1521/orcl> @ desczz tz 1=1 TTT@192.168.2.7:1521/orcl> @ pr ============================== DESC_OWNER                    : TTT DESC_TABLE_NAME               : TZ SAMPLE_SIZE                   : 4 LAST_ANALYZED                 : 2023-10-20 09:29:31 DESC_COLUMN_ID                :    1 DESC_COLUMN_NAME              : DEPTNO DESC_NULLABLE                 : NOT NULL DESC_DATA_TYPE                : NUMBER(2,0) NUM_DISTINCT                  : 4 DESC_DENSITY                  : .25 NUM_NULLS                     : 0 HISTOGRAM                     : NUM_BUCKETS                   : 1 TRANS_LOW                     : 10 TRANS_HIGH                    : 40 ============================== DESC_OWNER                    : TTT DESC_TABLE_NAME               : TZ SAMPLE_SIZE                   : LAST_ANALYZED                 : DESC_COLUMN_ID                :    2 DESC_COLUMN_NAME              : Z1 DESC_NULLABLE                 : NOT NULL DESC_DATA_TYPE                : VARCHAR2(10) NUM_DISTINCT                  : DESC_DENSITY                  : NUM_NULLS                     : HISTOGRAM                     : NUM_BUCKETS                   : TRANS_LOW                     : TRANS_HIGH                    : PL/SQL procedure successfully completed. --//可以看出我同事应该采用这样的方式增加字段的,这样没有出现隐含字段. 5.总结: --//写的有点乱,总之在一些运维细节上,一定要严格测试,注意细节.