?當字段中存在空值時，創建在字段上的索引并不包含空值。當查詢的條件為is null時，數據庫會采用全表掃的方式。

SQL> create table dayu071702 as select * from dba_objects;Table created.SQL> @GatherTableStat.sqlTO_CHAR(SYSDATE,' ----------------- 20180717 13:55:55"begin Gather Table's Statstics,Please waiting......" Enter value for owner: dayu Enter value for table_name: dayu071702 Enter value for parallel_count: 4PL/SQL procedure successfully completed.TO_CHAR(SYSDATE,' ----------------- 20180717 13:56:06SQL> insert into dayu071702(object_id) values('');1 row created.SQL> commit;Commit complete.SQL> create index dayu071702_ind on dayu071702(object_id);Index created.SQL> select count(*) from dayu071702;COUNT(*) ----------86588SQL> select count(*) from dayu071702_ind; select count(*) from dayu071702_ind* ERROR at line 1: ORA-00942: table or view does not existSQL> @GatherTableStat.sqlTO_CHAR(SYSDATE,' ----------------- 20180717 13:58:33"begin Gather Table's Statstics,Please waiting......" Enter value for owner: dayu Enter value for table_name: dayu071702 Enter value for parallel_count: 4PL/SQL procedure successfully completed.TO_CHAR(SYSDATE,' ----------------- 20180717 13:58:42SQL> desc dba_indexs; ERROR: ORA-04043: object dba_indexs does not existSQL> desc dba_indexes;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------OWNER NOT NULL VARCHAR2(30)INDEX_NAME NOT NULL VARCHAR2(30)INDEX_TYPE VARCHAR2(27)TABLE_OWNER NOT NULL VARCHAR2(30)TABLE_NAME NOT NULL VARCHAR2(30)TABLE_TYPE VARCHAR2(11)UNIQUENESS VARCHAR2(9)COMPRESSION VARCHAR2(8)PREFIX_LENGTH NUMBERTABLESPACE_NAME VARCHAR2(30)INI_TRANS NUMBERMAX_TRANS NUMBERINITIAL_EXTENT NUMBERNEXT_EXTENT NUMBERMIN_EXTENTS NUMBERMAX_EXTENTS NUMBERPCT_INCREASE NUMBERPCT_THRESHOLD NUMBERINCLUDE_COLUMN NUMBERFREELISTS NUMBERFREELIST_GROUPS NUMBERPCT_FREE NUMBERLOGGING VARCHAR2(3)BLEVEL NUMBERLEAF_BLOCKS NUMBERDISTINCT_KEYS NUMBERAVG_LEAF_BLOCKS_PER_KEY NUMBERAVG_DATA_BLOCKS_PER_KEY NUMBERCLUSTERING_FACTOR NUMBERSTATUS VARCHAR2(8)NUM_ROWS NUMBERSAMPLE_SIZE NUMBERLAST_ANALYZED DATEDEGREE VARCHAR2(40)INSTANCES VARCHAR2(40)PARTITIONED VARCHAR2(3)TEMPORARY VARCHAR2(1)GENERATED VARCHAR2(1)SECONDARY VARCHAR2(1)BUFFER_POOL VARCHAR2(7)FLASH_CACHE VARCHAR2(7)CELL_FLASH_CACHE VARCHAR2(7)USER_STATS VARCHAR2(3)DURATION VARCHAR2(15)PCT_DIRECT_ACCESS NUMBERITYP_OWNER VARCHAR2(30)ITYP_NAME VARCHAR2(30)PARAMETERS VARCHAR2(1000)GLOBAL_STATS VARCHAR2(3)DOMIDX_STATUS VARCHAR2(12)DOMIDX_OPSTATUS VARCHAR2(6)FUNCIDX_STATUS VARCHAR2(8)JOIN_INDEX VARCHAR2(3)IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)DROPPED VARCHAR2(3)VISIBILITY VARCHAR2(9)DOMIDX_MANAGEMENT VARCHAR2(14)SEGMENT_CREATED VARCHAR2(3)SQL> select NUM_ROWS from dayu071702_ind; select NUM_ROWS from dayu071702_ind* ERROR at line 1: ORA-00942: table or view does not existSQL> select NUM_ROWS from dba_indexes where index_name='DAYU071702_IND';NUM_ROWS ----------86587SQL> SQL> SQL> select object_type from DAYU071702 where object_id is null;^CSQL> SQL> explain plan for select object_type from DAYU071702 where object_id is null;Explained.SQL> select * from table(dbms_xplan.display) ;PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 804319829-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 350 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| DAYU071702 | 1 | 14 | 350 (1)| 00:00:05 | --------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("OBJECT_ID" IS NULL)13 rows selected.SQL> explain plan for select object_type from DAYU071702 where object_id=200;Explained.SQL> select * from table(dbms_xplan.display) ;PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2727514443---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DAYU071702 | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | DAYU071702_IND | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("OBJECT_ID"=200)14 rows selected.

　　

轉載于:https://www.cnblogs.com/dayu-liu/p/9323040.html

總結

以上是生活随笔為你收集整理的字段中存在空值的问题测试的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。