ANBOB™

有时从dba_tab_cols看到的表名是奇怪的sys_开头，有时在desc table时不显示，这里记录一下 Function index \VIRTUAL column \ unused column对列的影响，今天同事问在drop 一个column 时很慢，半小时都没删完， 对于一个大表drop column操作会在表上加TM 的排它锁长时间运行会影响业务时，可以考虑先将表unused把列隐藏后，后期再drop unused, 当然可以使用checkpoint选项，其实drop 列时需要把每个块从读一遍再重写会数据块，所以在大表的drop 列时一定要规划好时间。在drop过程中如果kill session或终止操作在11.1版本中可能会出现Ora-600 [17016]。看到Jonathan Lewis在12.1版本中做了个测试很有意思，回头再分析。

unsed column 后有个应用报错，应用中居然使用了xxx_tab_columns这样的dict对象，除了unsed column会把列搞的很“不一般” ，还有一些特殊场景。

create table test1   (id int,name varchar2(20));
insert into test1 values(1,'b');

-- 函数索引
create index idx_u_nam on test1 (upper(name));

-- 虚拟列
alter table test1 add lname CHAR(50) GENERATED ALWAYS AS (LOWER(NAME)) VIRTUAL;

alter table test1 add uname varchar2(20);
-- unused 列
alter table test1 set unused (uname );

-- 扩展统计信息
begin
    dbms_output.put_line(
        dbms_stats.create_extended_stats(
            ownname     => 'im',
            tabname     => 'TEST1',
            extension   => '(ID, NAME)'
        )
    );
 
    dbms_stats.gather_table_stats(
        ownname      => 'IM',
        tabname      =>'TEST1',
        block_sample     => true,
        method_opt   => 'for all hidden columns size 1'
    );
end;
/

SQL> select column_name,column_id,hidden_column,virtual_column,segment_column_id,internal_column_id
  2   from dba_tab_cols where owner='SYSTEM' and table_name='TEST1';

COLUMN_NAME                     COLUMN_ID HID VIR SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------------------------ ---------- --- --- ----------------- ------------------
ID                                      1 NO  NO                  1                  1
NAME                                    2 NO  NO                  2                  2
SYS_NC00003$                              YES YES                                    3   -- function-based column
LNAME                                   3 NO  YES                                    4   -- virtual column
SYS_C00005_16013015:38:04$                YES NO                  3                  5   -- unused column
SYS_STUIA0V924QODN5R5SCAKM60G#            YES YES                                    6   -- extended statistics

— update 12c invisible column
oracle 12c new feature 隐藏列(invisible columns)

SQL> create table test1(id int,name varchar2(10) invisible);
Table created.

SQL> @desc test1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER(38)

SQL> @init
SQL> select column_name,column_id,hidden_column,virtual_column,segment_column_id,internal_column_id
  2     from dba_tab_cols where owner='SYS' and table_name='TEST1';

COLUMN_NAME             COLUMN_ID HID VIR SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------------------- ---------- --- --- ----------------- ------------------
ID                              1 NO  NO                  1                  1
NAME                              YES NO                  2                  2

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta                    0
PL/SQL Release 12.2.0.0.1 - Beta                                                          0
CORE    12.2.0.0.0      Beta                                                              0
TNS for Linux: Version 12.2.0.0.0 - Beta                                                  0
NLSRTL Version 12.2.0.0.0 - Beta

打赏

微信扫一扫，打赏作者吧～