字段中存在空值的问题测试
当字段中存在空值时，创建在字段上的索引并不包含空值。当查询的条件为is null时，数据库会采用全表扫的方式。
SQL> create table dayu071702 as select * from dba_objects;
Table created.
SQL> @GatherTableStat.sql
TO_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: 4
PL/SQL procedure successfully completed.
TO_CHAR(SYSDATE,'
-----------------
20180717 13:56:06
SQL> 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(*)
----------
86588
SQL> select count(*) from dayu071702_ind;
select count(*) from dayu071702_ind
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> @GatherTableStat.sql
TO_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: 4
PL/SQL procedure successfully completed.
TO_CHAR(SYSDATE,'
-----------------
20180717 13:58:42
SQL> desc dba_indexs;
ERROR:
ORA-04043: object dba_indexs does not exist
SQL> 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 NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE 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 NUMBER
ITYP_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 exist
SQL> select NUM_ROWS from dba_indexes where index_name='DAYU071702_IND';
NUM_ROWS
----------
86587
SQL>
SQL>
SQL> select object_type from DAYU071702 where object_id is null;^C
SQL>
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.
