Impact of indexes and Null values on Hints and execution plans

The impact of indexes and Null values on Hints and execution plans because B * Tree indexes do not store Null values, some Oracle queries are allowed when the index field is Null.
If you do not use Index. www.2cto.com many times, it seems that you can use Full Index Scan.
The index is dropped because the Null value exists. in this case, Oracle does not use indexes even if Hints is used. The root cause is the existence of Null values. let's take a look at the following tests. if the username field is Not Null, Index Hints can take effect. SQL> create table t as select username, password from dba_users; Table created. SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- username not null VARCHAR2 (30) PASSWORD VARCHAR2 (30) SQL> create index I _t on t (username); Index created. SQL> set autotrace trace explainSQL> select * from t where username = 'eygl'; Execution Plan ------------------------------------------------------ Plan hash value: 1601196873 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 34 | 2 (0) | 00:00:01 | * 1 | table access full | T | 1 | 34 | 2 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): when 1-filter ("USERNAME" = 'eygl') Note ------dynamic sampling used for this statementSQL> set linesize 120SQL> select/* + index (t, I _t) */* from t where username = 'eygl'; Execution Plan ------------------------------------------------------------ Plan hash value: 2928007915 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 34 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T | 1 | 34 | 2 (0) | 00:00:01 | * 2 | index range scan | I _T | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ----------------------------------------------- 2-access ("USERNAME" = 'eygl') Note ------dynamic sampling used for this statement when the index field allows Null, Oracle abandons this index: SQL> alter table t modify (username null); Table altered. SQL> select/* + index (t, I _t) */* from t; Execution Plan -------------------------------------------------------- Plan hash value: 1601196873 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 27 | 918 | 2 (0) | 00:00:01 | 1 | table access full | T | 27 | 918 | 2 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement when this field is Not Null, the index can be forcibly used: www.2cto.com SQL> alter table t modify (username not null ); table altered. SQL> select/* + index (t, I _t) */* from t; Execution Plan -------------------------------------------------------- Plan hash value: 3593393735 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 27 | 918 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T | 27 | 918 | 2 (0) | 00:00:01 | 2 | index full scan | I _T | 27 | 1 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement. this is the impact of the Null value on the index and query.