本帖最后由 macrowho 于 2014-1-22 17:24 编辑
例如表T
create table t
(
id number,
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10)
);
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL1 VARCHAR2(10)
COL2 VARCHAR2(10)
COL3 VARCHAR2(10)
SQL>
需要加一列
alter table t add col0 varchar2(10);
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL1 VARCHAR2(10)
COL2 VARCHAR2(10)
COL3 VARCHAR2(10)
COL0 VARCHAR2(10)
我希望新加的COL0排在COL1的前面，不考虑重新建表，做视图，同义词等手段，有什么其他方法吗？
--------------------------------------------------------------------------------
首先感谢 @带来的12C新特性,12C版本以下的童鞋只能观望一下喽 做了个小实验12C中确实可以解决我的需求，同时物理结构不发生改变，在我看到结果的时候也比较困惑oracle是怎么实现的，整理了一下头脑后，发现column_id很可疑，遂又继续验证了一步，嘿嘿 过程很有趣哟~~，下面是实验过程
试图测试性能(起初以为会有物理变化)
SQL> drop table t purge;
Table dropped.
SQL> create table t
2 (
3 id number,
4 col1 varchar2(128),
5 col2 varchar2(128),
6 col3 varchar2(128)
7 );
Table created.
SQL> insert into t select object_id,object_name,owner,object_type from dba_objects;
19386 rows created.
Elapsed: 00:00:02.56
......
SQL> insert into t select * from t;
620352 rows created.
Elapsed: 00:00:02.40
......
SQL> insert into t select * from t;
9925632 rows created.
Elapsed: 00:01:54.85
SQL> commit;
Commit complete.
Elapsed: 00:00:00.09
SQL> select count(1) from t;
COUNT(1)
----------
19851264
Elapsed: 00:00:42.43
SQL>
查看当前表中列的的顺序
SQL> SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'T' order by 2;
COLUMN_NAME COLUMN_ID
-------------------- ----------
ID 1
COL1 2
COL2 3
COL3 4
Elapsed: 00:00:00.07
ROWID:AAAE1NAAEAAAACDAAA 4号文件131号块的第一行数据
SQL> SELECT dbms_rowid.rowid_block_number(ROWID) bno, dbms_rowid.rowid_relative_fno(ROWID) fno, ROWID, t.* FROM t where rownum=1;
BNO FNO ROWID ID
---------- ---------- ------------------ ----------
131 4 AAAE1NAAEAAAACDAAA 652
Elapsed: 00:00:00.07
设置列为invisible之前dump出的block结果
SQL> alter system dump datafile 4 block 131;
System altered.
节选前三行：
block_row_dump:
tab 0, row 0, @0xb19
tl: 37 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 57
col 1: [14] 56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
tab 0, row 1, @0xb3e
tl: 40 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 58
col 1: [23]
56 5f 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 3] 53 59 53
col 3: [ 4] 56 49 45 57
tab 0, row 2, @0xb66
tl: 45 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 59
col 1: [22]
56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
修改列为invisible
SQL> ALTER TABLE t MODIFY( COL1 INVISIBLE, COL2 INVISIBLE, COL3 INVISIBLE);
Table altered.
Elapsed: 00:00:00.94
此时column_id变成了NULL
SQL> SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'T' order by 2;
COLUMN_NAME COLUMN_ID
-------------------- ----------
ID 1
COL1
COL3
COL2
Elapsed: 00:00:00.08
SQL> alter system dump datafile 4 block 131;
System altered.
设置invisible之后，查看dump结果：
block_row_dump:
tab 0, row 0, @0xb19
tl: 37 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 57
col 1: [14] 56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 --为发生变化
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
tab 0, row 1, @0xb3e
tl: 40 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 58
col 1: [23]
56 5f 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 3] 53 59 53
col 3: [ 4] 56 49 45 57
tab 0, row 2, @0xb66
tl: 45 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 59
col 1: [22]
56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
Elapsed: 00:00:00.03
添加新的列COL0
SQL> ALTER TABLE t ADD ( COL0 VARCHAR2(10));
Table altered.
Elapsed: 00:00:00.16
新添加的列的id变为了2
SQL> SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'T' order by 2;
COLUMN_NAME COLUMN_ID
-------------------- ----------
ID 1
COL0 2
COL1
COL3
COL2
添加col0之后,此块的dump结果：
block_row_dump:
tab 0, row 0, @0xb19
tl: 37 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 57
col 1: [14] 56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 --仍然没有变化原因是因为这列上没有值
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
tab 0, row 1, @0xb3e
tl: 40 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 58
col 1: [23]
56 5f 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 3] 53 59 53
col 3: [ 4] 56 49 45 57
tab 0, row 2, @0xb66
tl: 45 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 3] c2 16 59
col 1: [22]
56 24 49 4f 46 55 4e 43 4d 45 54 52 49 43 5f 48 49 53 54 4f 52 59
col 2: [ 6] 50 55 42 4c 49 43
col 3: [ 7] 53 59 4e 4f 4e 59 4d
更新此列上的值
SQL> update t set col0='000' where rowid='AAAE1NAAEAAAACDAAA';
1 row updated.
Elapsed: 00:00:00.03
SQL> commit;
Commit complete.
查询结果，COL的值为000
SQL> SELECTt.id,t.col0,t.col1,t.col2,t.col3from t WHERE ROWID = 'AAAE1NAAEAAAACDAAA';
ID COL0 COL1 COL2 COL3
---------- ---------- ---------------------------------------- ---------- ----------
652 000SYS_LOB0000000651C00002$$ SYS LOB
Elapsed: 00:00:00.01
到这里，偶然发现一个可爱的情况，使用select * 时，在结果中看不到被设置为invisible的列，desc同样看不见，但是如果在select columns_list中手动指定出列名仍然可以被查出来，应该是默认不现实invisible的列
SQL> SELECT * from t WHERE ROWID = 'AAAE1NAAEAAAACDAAA';
ID COL0
---------- ----------
652 000
Elapsed: 00:00:00.00
更新后继续dump：
SQL> alter system dump datafile 4 block 131;
System altered.
dump结果发生了变化：第一行显示有5列，后面的都是4列
block_row_dump:
tab 0, row 0, @0x4b1
tl: 45 fb: --H-FL-- lb: 0x2 cc: 5
col 0: [ 3] c2 07 35
col 1: [25]
53 59 53 5f 4c 4f 42 30 30 30 30 30 30 30 36 35 31 43 30 30 30 30 32 24 24
col 2: [ 3] 53 59 53
col 3: [ 3] 4c 4f 42
col 4: [ 3] 30 30 30
tab 0, row 1, @0x13d1
tl: 24 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 3] c2 07 38
col 1: [ 6] 4b 4f 54 41 44 24
col 2: [ 3] 53 59 53
col 3: [ 5] 54 41 42 4c 45
tab 0, row 2, @0x13e9
tl: 28 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 3] c2 07 3b
col 1: [10] 53 59 53 5f 43 30 30 38 32 31
col 2: [ 3] 53 59 53
col 3: [ 5] 49 4e 44 45 58
验证数据，观察发现COL 4的值“303030” 是新加进来的，所以尝试转换，结果与我之前更新的000相符
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 str VARCHAR2(100);
3 BEGIN
4 dbms_stats.convert_raw_value('303030', str);
5 dbms_output.put_line(str);
6 END;
7 /
000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.13
把所有列设置为visible，并查看结果：
SQL> ALTER TABLE t MODIFY( COL1 VISIBLE, COL2 VISIBLE, COL3 VISIBLE);
Table altered.
Elapsed: 00:00:00.43
顺序发生了变化
SQL> SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'T' order by 2;
COLUMN_NAME COLUMN_ID
-------------------- ----------
ID 1
COL0 2
COL1 3
COL2 4
COL3 5
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
COL0 VARCHAR2(10)
COL1 VARCHAR2(128)
COL2 VARCHAR2(128)
COL3 VARCHAR2(128)
Elapsed: 00:00:00.21
查询结果也发生了变化
SQL> SELECT * from t WHERE ROWID = 'AAAE1NAAEAAAACDAAA';
ID COL0 COL1 COL2 COL3
---------- ---------- ---------------------------------------- ---------- ----------
652 000 SYS_LOB0000000651C00002$$ SYS LOB
Elapsed: 00:00:00.00
把所有列都设为visible之后 dump结果:
SQL> alter system dump datafile 4 block 131;
System altered.
block_row_dump:
tab 0, row 0, @0x4b1
tl: 45 fb: --H-FL-- lb: 0x2 cc: 5
col 0: [ 3] c2 07 35
col 1: [25]
53 59 53 5f 4c 4f 42 30 30 30 30 30 30 30 36 35 31 43 30 30 30 30 32 24 24 -----结果与之前一致
col 2: [ 3] 53 59 53
col 3: [ 3] 4c 4f 42
col 4: [ 3] 30 30 30
tab 0, row 1, @0x13d1
tl: 24 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 3] c2 07 38
col 1: [ 6] 4b 4f 54 41 44 24
col 2: [ 3] 53 59 53
col 3: [ 5] 54 41 42 4c 45
tab 0, row 2, @0x13e9
tl: 28 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 3] c2 07 3b
col 1: [10] 53 59 53 5f 43 30 30 38 32 31
col 2: [ 3] 53 59 53
col 3: [ 5] 49 4e 44 45 58
在这次实验中证实oracle 12c中通过设置列的invisible属性，可以实现我问题中的要求；同时物理结构未发生改变，从修改的时间和dump出来的结果来看确实未发生改变，我觉得可能是修改字典表中COLUMN_ID来实现此功能的，纯属个人猜测，没办法想到了就继续验证，其实上面的内容中已有证据，无奈当时未想到，此时重新再来验证一次，实验步骤如下：
SQL> drop table t purge;
Table dropped.
SQL> create table t
2 (
3 id number,
4 col1 varchar2(128),
5 col2 varchar2(128),
6 col3 varchar2(128)
7 );
Table created.
查看原表column_id
SQL> SELECT table_name, column_name, data_type, column_id FROM dba_tab_columns WHERE table_name = 'T' ORDER BY column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID
-------------------- -------------------- -------------------- ----------
T ID NUMBER 1
T COL1 VARCHAR2 2
T COL2 VARCHAR2 3
T COL3 VARCHAR2 4
修改为invisible
SQL> ALTER TABLE t MODIFY( COL1 INVISIBLE, COL2 INVISIBLE, COL3 INVISIBLE);
Table altered.
Elapsed: 00:00:00.06
查看修改后的结果
SQL> SELECT table_name, column_name, data_type, column_id FROM dba_tab_columns WHERE table_name = 'T' ORDER BY column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID
-------------------- -------------------- -------------------- ----------
T ID NUMBER 1
T COL2 VARCHAR2
T COL3 VARCHAR2
T COL1 VARCHAR2
添加新列col0
SQL> ALTER TABLE t ADD ( COL0 VARCHAR2(10));
Table altered.
Elapsed: 00:00:00.03
查看新加列后的结果
SQL> SELECT table_name, column_name, data_type, column_id FROM dba_tab_columns WHERE table_name = 'T' ORDER BY column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID
-------------------- -------------------- -------------------- ----------
T ID NUMBER 1
T COL0 VARCHAR2 2
T COL1 VARCHAR2
T COL3 VARCHAR2
T COL2 VARCHAR2
将所有列设置为visible
SQL> ALTER TABLE t MODIFY( COL1 VISIBLE, COL2 VISIBLE, COL3 VISIBLE);
Table altered.
Elapsed: 00:00:00.08
查看最终结果
SQL> SELECT table_name, column_name, data_type, column_id FROM dba_tab_columns WHERE table_name = 'T' ORDER BY column_id;
TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID
-------------------- -------------------- -------------------- ----------
T ID NUMBER 1
T COL0 VARCHAR2 2
T COL1 VARCHAR2 3
T COL2 VARCHAR2 4
T COL3 VARCHAR2 5
实验暂时画上一个句号。我在所有查询dba_tab_columns表的语句都是order by column_id，结果中column_id为null排在了后面，大家都知道 这是oracle对null的处理，所以我大胆的猜测ORACLE也是使用这种方法来处理对列的显示顺序，当我们设置invisible就将column_id设置为NULL,设置为visible时再设置column_id=表中max(column_id)+1。这算是完美解决吗？
.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
