[20210420]19c奇怪的過濾條件.txt

lfree發表於2021-04-20

[20210420]19c奇怪的過濾條件.txt

--//昨天看新上線的生產系統ODA,發現一條語句奇怪的過濾條件。好在知識儲備足夠知道這個是由於使用12c以上特性增加新欄位導致的
--//情況,順便說一下我們的生產系統,雖然db time很小僅僅不到10分鐘,完全靠機器的效能掩蓋許多問題。
--//用一句話來概括就是豆腐渣,再增加一個字首就是豆腐渣中的豆腐渣。
--//對於遇到的情況,做一個記錄與分析。

> @ ver1
> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

--//經常看到執行計劃過濾條件如下:
   3 - filter(((DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00021$",0)),NULL,NVL("SP
              D",0),'0',NVL("SPD",0),'1',"SPD")=0 OR
              DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00021$",0)),NULL,NVL("SPD",0),'0',NVL("S
              PD",0),'1',"SPD") IS NULL) AND "YK_YPCD"."JGID"=:JGID))

--//剛開始很感覺很奇怪,什麼會出現這樣的函式SYS_OP_VECBIT。實際上看到的查詢條件是 (SPD = 0 OR SPD IS NULL).
--//看了一下表結構發現SPD欄位的編號是21,馬上明白為什麼出現這樣的情況,透過測試環境說明實際的問題。

1.環境:
XXXX> @ prxx
==============================
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.建立測試表。

create table t (a number);
insert into t select rownum from dual connect by rownum <=10;
commit;
--//分析略。
alter table t add ( b number default 1 not null);

XXXX> select * from t where b=1;
         A          B
---------- ----------
         1          1
..
        10          1
10 rows selected.

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2gcu8c0ucsuts, child number 0
-------------------------------------
select * from t where b=1
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     10 |    60 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("B",1)=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("B",1)[22]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   
--//注意過濾條件filter(NVL("B",1)=1),以及Column Projection Information部分,都可以看到NVL("B",1)。
--//實際上這樣增加欄位在11g或者12c以上版本,都不會修改表結構,不然就不會出現過濾條件是filter(NVL("B",1)=1的情況。

--//預設值儲存在sys.ecol$資料欄位中。
XXXX> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='T';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    252074         252074

XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
   TABOBJ#     COLNUM BINARYDEFVAL                     GUARD_ID
---------- ---------- ------------------------------ ----------
    252074          2 C102
--//TABOBJ# 就是表段號,COLNUM表示欄位順序2.BINARYDEFVAL 對應數字1的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 = 'T') ORDER BY col#;

      OBJ#       COL#    SEGCOL# NAME  DEFAULT$  TYPE#
---------- ---------- ---------- ----- --------- -----
    252074          1          1 A                   2
    252074          2          2 B     1             2

3.繼續測試:
XXXX> alter table t add ( c number default 1 );
Table altered.
--//注意沒有not null的約束。
--//12c以上這樣修改表結構,不會修改表對應資料塊。透過特殊的方式增加一個隱含欄位裡面的bit來表示這些欄位從那裡來讀取.
--//後續的插入即使insert不帶這些欄位,其預設值也插入資料塊中.
--//可以參考連結:http://blog.itpub.net/267265/viewspace-1335561/ =>[20141116]12c下增加欄位與預設值.txt
XXXX> select * from t where a=1 and b=1 and c=1 and rownum=1 ;
         A          B          C
---------- ---------- ----------
         1          1          1

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  c45j01802z7rh, child number 0
-------------------------------------
select * from t where a=1 and b=1 and c=1 and rownum=1
Plan hash value: 508354683
----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY     |      |        |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     9 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - filter(("A"=1 AND NVL("B",1)=1 AND
              DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
              "C",1),'1',"C")=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "A"[NUMBER,22], NVL("B",1)[22],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
       "C",1),'1',"C")[22]
   2 - "A"[NUMBER,22], NVL("B",1)[22],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
       "C",1),'1',"C")[22]

--//注意看過濾條件以及Column Projection Information部分的顯示。
   2 - filter(("A"=1 AND NVL("B",1)=1 AND
              DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
              "C",1),'1',"C")=1))


XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
   TABOBJ#     COLNUM BINARYDEFVAL                     GUARD_ID
---------- ---------- ------------------------------ ----------
    252074          2 C102
    252074          4 C102                                    0
--//你可以發現新增加的欄位c編號是4.GUARD_ID=0,也許這是這兩者增加欄位模式的區別。

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 = 'T') ORDER BY col#;

      OBJ#       COL#    SEGCOL# NAME         DEFAULT$ TYPE#
---------- ---------- ---------- ------------ -------- -----
    252074          0          3 SYS_NC00003$             23
    252074          1          1 A                         2
    252074          2          2 B            1            2
    252074          3          4 C            1            2
--//增加1個隱含欄位SYS_NC00003$,然後才是增加欄位C。oracle 12c就是利用了這個隱含欄位實現這樣修改表結構不修改資料表塊的方
--//法.
--//這兩者模式帶來的缺點就是看過濾條件不容易判斷該建立什麼樣的索引,不過一般問題不會很大,結合謂詞條件,一般不會建立索引
--//錯誤。我開始以為如果建立欄位b或者c的索引會有問題(受過濾條件的干擾),實際上仔細想想不對,建立索引時就將鍵值寫入索引結
--//構裡面,即使資料塊裡面沒有資訊。透過索引探查就能夠知道rowid,定位對應行記錄,看看下面的測試,看來我有點過慮了,oracle
--//肯定已經考慮這樣的情況。

4.繼續測試,看看使用索引的查詢情況:
insert into t select rownum+10,0,0  from dual connect by rownum <=1e5-10;
commit ;
create index i_t_b on t(b);
create index i_t_c on t(c);
--//分析略。建立直方圖:
BEGIN
   sys.DBMS_STATS.gather_table_stats
   (
      OwnName            => USER
     ,TabName            => 't'
     ,Estimate_Percent   => NULL
     ,Method_Opt         => 'FOR ALL COLUMNS SIZE 1 for columns b size 254 for columns c size 254'
     ,Cascade            => TRUE
     ,No_Invalidate      => FALSE
   );
END;
/

XXXX> select * from t where  b=1 and rownum=1;
         A          B          C
---------- ---------- ----------
         1          1          1

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  dbums5ptbbhd8, child number 0
-------------------------------------
select * from t where  b=1 and rownum=1
Plan hash value: 3626036191
-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |        |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY                       |       |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | I_T_B |     10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."B"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   3 - access("B"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], "B"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",
       0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
   2 - "T"."A"[NUMBER,22], "B"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",
       0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
   3 - "T".ROWID[ROWID,10], "B"[NUMBER,22]

--//注意過濾條件是access("B"=1),看看Column Projection Information (identified by operation id):的一些細節:
   3 - "T".ROWID[ROWID,10], "B"[NUMBER,22]
--//掃描索引段獲得rowid以及欄位B的值,不再出現NVL("B",1)=1的情況。
--//繼續Column Projection Information 部分,B欄位的取值也沒有NVL("B",1)=1,也就是欄位B的值來自索引,不再是表。

XXXX> select * from t where  c=1 and rownum=1;
         A          B          C
---------- ---------- ----------
         1          1          1

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b7q2au1j8qqy1, child number 0
-------------------------------------
select * from t where  c=1 and rownum=1
Plan hash value: 2915363186
-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |        |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY                       |       |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | I_T_C |     10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."C"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   3 - access("C"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22], "C"[NUMBER,22]
   2 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22], "C"[NUMBER,22]
   3 - "T".ROWID[ROWID,10], "C"[NUMBER,22]

--//仔細看Predicate Information部分   3 - access("C"=1)。
--//再看Column Projection Information 部分:
--//不再出現DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]這樣
--//的顯示,也就是欄位C來自索引,再看看B欄位明顯的標識來自表,出現了NVL("T"."B",1)[22]。

--//再貼一個複雜一點的情況:
XXXX> select * from t where ( c=1 or b=1 )and rownum=1;
         A          B          C
---------- ---------- ----------
         1          1          1

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  38qc6cvms8a6f, child number 0
-------------------------------------
select * from t where ( c=1 or b=1 )and rownum=1
Plan hash value: 310441820
-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |        |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY                       |       |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |    11 |     2   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |       |        |       |            |          |
|   4 |     BITMAP OR                        |       |        |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS   |       |        |       |            |          |
|*  6 |       INDEX RANGE SCAN               | I_T_C |        |       |     1   (0)| 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS   |       |        |       |            |          |
|*  8 |       INDEX RANGE SCAN               | I_T_B |        |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."C") 2 ("T"."B")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   6 - access("C"=1)
   8 - access("B"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),
       '1',"T"."C")[22]
   2 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),
       '1',"T"."C")[22]
   3 - "T".ROWID[ROWID,10]
   4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   6 - "T".ROWID[ROWID,10]
   7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
   8 - "T".ROWID[ROWID,10]

--//仔細看Predicate Information以及Column Projection Information部分。
--// id = 6,8 僅僅取了rowid,並不包括欄位B,C。這樣id = 1,2部分的欄位B,C的取值只能來之表,這樣有出現了
--//NVL("T"."B",1)[22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
--//這樣的函式呼叫。

XXXX> select * from t where c is null;
no rows selected

XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gwnddk9xpymw7, child number 0
-------------------------------------
select * from t where c is null
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    69 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    11 |    69   (2)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("
              C",1),'0',NVL("C",1),'1',"C") IS NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
       "C",1),'1',"C")[22]

--//注意Predicate Information (identified by operation id)以及Column Projection Information (identified by operation
--//id):
--//因為欄位C允許NULL,這樣索引不包括NULL,這樣有SYS_OP_VECBIT之類的顯示,不展開分析了。

5.如果修改預設值呢?
--//感覺這樣的情況很少遇到不過我還是測試看看,畢竟增加欄位沒有修改表塊。

XXXX> alter table t modify ( c number default 2 );
Table altered.

XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
   TABOBJ#     COLNUM BINARYDEFVAL                     GUARD_ID
---------- ---------- ------------------------------ ----------
    252074          2 C102
    252074          4 C102                                    0

--//這裡還是記錄預設值1.

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 = 'T') ORDER BY col#;

      OBJ#       COL#    SEGCOL# NAME         DEFAULT$ TYPE#
---------- ---------- ---------- ------------ -------- -----
    252074          0          3 SYS_NC00003$             23
    252074          1          1 A                         2
    252074          2          2 B            1            2
    252074          3          4 C            2            2
--//僅僅改動sys.col$表。這樣插入時不包括c欄位,實際上插入時2.而在表塊裡面沒有欄位c的情況下,實際上對應值1.

XXXX> insert into t(a) select 1e5+1  from dual ;
1 row created.

XXXX> commit ;
Commit complete.

XXXX> select t.*,SYS_NC00003$ c20 from t where a=1e5+1;
         A          B          C C20
---------- ---------- ---------- --------------------
    100001          1          2 01

--//SYS_NC00003$=01。
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "A"[NUMBER,22], NVL("T"."B",1)[22], "SYS_NC00003$"[RAW,126],
       DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',
       NVL("T"."C",1),'1',"T"."C")[22]

6.總結:
--//oracle每種新特性,總是帶來新的困惑,這樣的方式給運維帶來好處,缺點以後增加1個隱含欄位,看執行計劃時存在一點點困惑。
--//帶來表每行記錄增加至少2個位元組(如果僅僅小於增加8個欄位)。我感覺在專案實施或者除錯階段應該關閉這個特性,畢竟那個時候表
--//不是很大。看了半天,也不知道那個隱含引數控制這樣的行為,那位知道。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2768847/,如需轉載,請註明出處,否則將追究法律責任。

相關文章