[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.總結:
--//寫的有點亂,總之在一些運維細節上,一定要嚴格測試,注意細節.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2990587/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201109]11.2.0.4增加欄位與預設值問題.txt
- [20210208]lob欄位與查詢的問題.txt
- MySQL 大欄位問題MySql
- [20210421]12c以上版本增加欄位與預設值.txt
- laravel欄位減少增加Laravel
- Request 增加自定義欄位的方式
- sql語句修改欄位型別和增加欄位SQL型別
- 一種hive的模型設計思路，解決頻繁增加指標欄位的問題Hive模型指標
- [20181020]lob欄位的索引段.txt索引
- oracle增加欄位帶預設值Oracle
- [BUG反饋]模型增加欄位BUG模型
- [BUG反饋]模型增加欄位後field_sort欄位未更新模型
- [20180613]縮短欄位長度.txt
- resultMap 和 resultType 的欄位對映覆蓋問題
- api 介面欄位規範的問題，請教大家API
- 巧用 Base62 解決欄位太短的問題
- [20231020]rename IDL_UB1$後使用bbed的恢復.txt
- [20190227]簡單探究tab$的bojb#欄位.txt
- [20181022]lob欄位的lobid來之那裡.txt
- 帝國CMS欄目管理增加自定義欄位值的為空判斷
- MyBatis 解決欄位名不一致的問題MyBatis
- [20190810]如何索引一個超長欄位.txt索引
- [20210423]建立檢視以及欄位長度.txt
- [20181031]lob欄位與布隆過濾.txt
- 為React Ant-Design Table增加欄位設定React
- django admin中增加自定義超連結欄位Django
- 新增一列並增加列欄位註釋
- Oracle 增加修改刪除欄位與新增註釋Oracle
- 怎麼給模型中的欄位增加自動完成規則模型
- [20180408]那些函式索引適合欄位的查詢.txt函式索引
- [BUG反饋]username欄位的長度設定有點小問題。
- 解決mybatis用Map返回的欄位全變大寫的問題MyBatis
- [提問交流]小問題關於外掛開發的欄位定義bug
- PostgreSQL表增加/刪除欄位是否會重寫表SQL
- [20190116]rman的老問題.txt
- [20181227]bbed的使用問題.txt
- SAP MM 為MB51報表增加查詢欄位
- 增加替代中不存在的欄位GGB1 OBBH OB28