[20170516]nvl與非NULL約束2.txt

[20170516]nvl與非NULL約束2.txt

--//接著上午的測試看看COALESCE看看過濾的情況.

1.環境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as select rownum id,'test' name , 0 flag_num ,lpad('0',1,'0') flag_varchar from dual connect by level<=2e4;
Table created.

SCOTT@book> update t set flag_num=1, flag_varchar='1' where id=1e4 or id=2e4;
2 rows updated.

SCOTT@book> commit ;
Commit complete.

--//分析表略.
--//選擇lpad函式,這樣資料型別varchar2.
SCOTT@book> @ &r/desc t
           Name           Null?    Type
           -------------- -------- -------------
    1      ID                      NUMBER
    2      NAME                    CHAR(4)
    3      FLAG_NUM                NUMBER
    4      FLAG_VARCHAR            VARCHAR2(1)

2.設定:
SCOTT@book> alter table t modify  FLAG_VARCHAR not null;
Table altered.

SCOTT@book> create index i_t_flag_varchar on t(flag_varchar);
Index created.

--//在flag_varchar欄位建立直方圖:

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag_varchar size 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@book> select * from t where nvl(flag_varchar,0)='1';
        ID NAME                   FLAG_NUM F
---------- -------------------- ---------- -
     10000 test                          1 1
     20000 test                          1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9y06mwu7wncxx, child number 0
-------------------------------------
select * from t where nvl(flag_varchar,0)='1'
Plan hash value: 932711470
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |       |     2 (100)|          |      2 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T                |      1 |      1 |    13 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | I_T_FLAG_VARCHAR |      1 |      1 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG_VARCHAR"='1')

--//能很好的使用索引.測試使用COALESCE的情況如何?

SCOTT@book> select * from t where COALESCE(flag_varchar,'0')='1';
        ID NAME         FLAG_NUM F
---------- ---------- ---------- -
     10000 test                1 1
     20000 test                1 1

SCOTT@book> @&r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bbq0q2zs1s9j1, child number 1
-------------------------------------
select * from t where COALESCE(flag_varchar,'0')='1'

Plan hash value: 1601196873

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    17 (100)|          |      2 |00:00:00.01 |      55 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      2 |    26 |    17   (0)| 00:00:01 |      2 |00:00:00.01 |      55 |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1

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

   1 - filter(COALESCE("FLAG_VARCHAR",'0')='1')

Note
-----
   - cardinality feedback used for this statement

--//注意看過濾條件,COALESCE就沒有這樣的特點.