Oracle Database 12c New Feature : Invisible Columnsの怪w s/怪w/謎/ Tweet

Oracle® Database New Features Guide 12c Release 1 (12.1) - 1.5.5.3 Invisible Columns
Oracle® Database Administrator's Guide 12c Release 1 (12.1) - Understand Invisible Columns

---

予告通り Invisible Columnの小ネタを。
（祭りに呼び出されて書けないかかもしれない、という状況にはならずw この時間に自宅にいるのはラッキーw)

本題とは関係ないですが、改行されないのはご愛嬌:)

SYS@orcl12c> startup pluggable database pdborcl;
ラガブル・データベースがオープンされました。SYS@orcl12c> 
SYS@orcl12c> 
SYS@orcl12c> conn scott@pdborcl
パスワードを入力してください: 
接続されました。
SCOTT@pdborcl> 

Invisible columnを含む表を作成します。（問題なく作成されました。当たり前か..)

SCOTT@pdborcl> create table hoge (a number, b number, c number invisible);

表が作成されました。

SQL*Plusのdescコマンドで見てみます。マニュアルの通り、Invisible columnはリストされません。

SCOTT@pdborcl> desc hoge
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  NUMBER

さあ、ここから、気持ち悪い事が起きますよ〜〜。よ〜〜〜く見ててくださいね〜〜。

列は隠れていますが、存在を知って、Invisible Columnでもカラムを指定すればデータを登録できるんです。見えてないだけなんです！

SCOTT@pdborcl> insert into hoge(a,b,c) values(1,2,4);

1行が作成されました。

カラム指定しないとa列と、b列しかないものとして扱われ、見慣れたエラー、「ORA-00913: 値の個数が多すぎます。」となります。

いい感じの気持ち悪さでしょ :)

SCOTT@pdborcl> insert into hoge values(11,12,14);
insert into hoge values(11,12,14)
            *
行1でエラーが発生しました。:
ORA-00913: 値の個数が多すぎます。

2列分なら正常に登録できます。見えるのはa列とb列の２列なので当然と言えば当然。

SCOTT@pdborcl> insert into hoge values(11,12);

1行が作成されました。

SELECTリストに * を指定すれば、ほらほら、自然です。 a列とb列だけがリストされています。

SCOTT@pdborcl> select * from hoge;

         A          B
---------- ----------
         1          2
        11         12

でも...c列の存在を知っている人には....見えちゃうんです。

SCOTT@pdborcl> select a, b, c from hoge;

         A          B          C
---------- ---------- ----------
         1          2          4
        11         12 [null]

同様に削除や更新もできちゃいます。見えないものが見えてる人にはw。
(その存在を知らない人には見えないわですが...)

SCOTT@pdborcl> update hoge set c = 14 where c is null;

1行が更新されました。

SCOTT@pdborcl> select a, b, c from hoge where c = 14;

         A          B          C
---------- ---------- ----------
        11         12         14

SCOTT@pdborcl> delete from hoge where c = 14;

1行が削除されました。

SCOTT@pdborcl> select a, b, c from hoge;

         A          B          C
---------- ---------- ----------
         1          2          4

ORDER BY句でも、その存在さえ知っていればいろいろイタズラできますね。

SCOTT@pdborcl> select a,b,c from hoge order by c desc;

         A          B          C
---------- ---------- ----------
        11         12         14
         1          2          4

おもしろいですね。

SCOTT@pdborcl> select * from hoge order by c desc;

         A          B
---------- ----------
        11         12
         1          2

Invisible columnには索引も作れます！

SCOTT@pdborcl> create index hoge_ix on hoge(c);

索引が作成されました。

SCOTT@pdborcl> set autot trace exp 
SCOTT@pdborcl> select * from hoge where c = 14;

実行計画
----------------------------------------------------------
Plan hash value: 3349209795

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HOGE    |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | HOGE_IX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"=14)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SCOTT@pdborcl>

次のようなイタズラもできます！　うひゃw うひゃw　

相手の腕試をしするにはぴったりなイタズラです（嘘

SCOTT@pdborcl> alter table hoge modify (c number not null);

表が変更されました。

SCOTT@pdborcl> 
SCOTT@pdborcl> insert into hoge(a, b) values(100,111);
insert into hoge(a, b) values(100,111)
*
行1でエラーが発生しました。:
ORA-01400: ("SCOTT"."HOGE"."C")にはNULLは挿入できません。

イタズラはこれくらいにして、Invisible Columnをディクショナリビューから覗いてみましょう。
Invisible Indexもディクショナリビューからいろいろと情報を得ることができましたよね！　
（多分、同じように見れるのでは...と思っていたが...

desc[ribe]コマンドではInvisible Columnは見えませんが、 ALL/DBA/USER_TAB_COLUMNSからは見る事ができます。

racle® Database Reference 12c Release 1 (12.1) - ALL_TAB_COLUMNS

ただし、見る事はできるのですが、その列がInvisible Columnかどうかを確認する列がないんですよ。（ぱっと見）

例えば、Invisible IndexだとALL/DBA/USER_INDEXES確認できます

SCOTT@pdborcl> select index_name,visibility from user_indexes where table_name='HOGE';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
HOGE_IX                        INVISIBLE
HOGE_PK                        VISIBLE

そこで疑問！
SQL*PLusのdesc[ribe]コマンドはどのような情報を基にVisible Columeだけを表示してるんでしょうねぇ〜。

これ、探すのにてこずりましたよ！

以下のマニュアルにも記載されていますが、Visible/InvisibleにするとColumn Orderが変化することにも関連しているようです。
Oracle® Database Administrator's Guide 12c Release 1 (12.1) - Understand Invisible Columns

desc[ribe]ではInvisible Columnは見えません

SCOTT@pdborcl> desc hoge
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  NUMBER

どのような情報を見てInvisibleであると判断しているのか....

SCOTT@pdborcl> select column_name,data_type,nullable,column_id from user_tab_columns where table_name='HOGE' order by column_id;

COLUMN_NAME                    DATA_TYPE  N  COLUMN_ID
------------------------------ ---------- - ----------
A                              NUMBER     Y          1
B                              NUMBER     Y          2
C                              NUMBER     N [null]

COLUMN_IDがNULLである列がInvisible Columns !!!!

SCOTT@pdborcl> l
  1  select
  2  	column_name
  3  	,data_type
  4  	,nullable
  5  	,column_id
  6  from
  7  	user_tab_columns
  8  where
  9  	table_name='HOGE'
 10  	and column_id is not null
 11  order by
 12* 	column_id
SCOTT@pdborcl> /

COLUMN_NAME                    DATA_TYPE  N  COLUMN_ID
------------------------------ ---------- - ----------
A                              NUMBER     Y          1
B                              NUMBER     Y          2

いずれ、ALL/DBA/USER_TAB_COLUMNSに、VISIBILITYという列が追加されるんじゃないかと想像してます :)

それまでは俺俺 USER_TAB_COLUMNSビューでも作っておくと便利かも...しれない。

SCOTT@pdborcl> create view my_tab_columns as
   2  select
   3      user_tab_columns.*
   4      ,case when user_tab_columns.column_id is null 
   5         then 'INVISIBLE' else 'VISIBLE' end as VISIBILITY
   6  from
   7      user_tab_columns
   8  ;

ビューが作成されました。

SCOTT@pdborcl> select column_name,data_type,nullable,visibility from my_tab_columns where table_name='HOGE' order by column_id;

COLUMN_NAME                    DATA_TYPE  N VISIBILIT
------------------------------ ---------- - ---------
A                              NUMBER     Y VISIBLE
B                              NUMBER     Y VISIBLE
C                              NUMBER     N INVISIBLE

このような特性を理解した上で、どのような場面で使うと便利なのか、じ〜〜〜〜っくり考えてみたいと...と思いつつ...寝ます。パタリ。 1:48am