[20231020]增加字段的问题.txt
--//测试生产系统遇到的增加字段的相关问题.非常容易混乱,我还是给自己测试看看,加强记忆.
1.环境:
[email protected]:1521/orcl> @ ver1
[email protected]: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.测试准备:
[email protected]:1521/orcl> create table ty as select deptno from dept;
Table created.
[email protected]: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));
[email protected]:1521/orcl> insert into ty (deptno) values (50);
1 row created.
[email protected]:1521/orcl> commit ;
Commit complete.
[email protected]: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个字段，注意写法与上面都不同。
[email protected]:1521/orcl> alter table ty add (status4 varchar2(10) default 'CCCCC' not null);
Table altered.
[email protected]: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
[email protected]:1521/orcl> insert into ty (deptno) values (60);
1 row created.
[email protected]:1521/orcl> commit ;
Commit complete.
[email protected]: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.
--//主要目的看看是否修改数据块.
[email protected]: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#;
[email protected]: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.
[email protected]: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值不同.
[email protected]:1521/orcl> select * from ty where status1='A' or status2='B' or status3='C' or status4='D' ;
no rows selected
[email protected]: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.继续测试:
[email protected]:1521/orcl> create table tz as select deptno from dept;
Table created.
[email protected]:1521/orcl> @ gts tz '' ''
...
[email protected]: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
--//这样加入并没有引入隐含字段.
[email protected]:1521/orcl> select * from sys.ecol$ where tabobj#= 424128 ;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------------------------ ----------
424128 2 3131313131
--//3131313131= 11111
[email protected]:1521/orcl> @ desczz tz 1=1
[email protected]: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.总结:
--//写的有点乱,总之在一些运维细节上,一定要严格测试,注意细节.