[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就没有这样的特点.