Use the indexed misunderstanding five: null value
It is not because the entry that is completely empty is not recorded in an index. It cannot use null values. Instead, sometimes rationally use Oracle's null value will bring several times or even dozens of efficiency to our query.
For example, add a table, there is a field is "processing time", if there is no transaction, the column is empty, and in most cases, the handled transaction is always 10% of the total number of records. Or less, while the record ("Processing Time" is empty) Always most records, then establish an index on the "waiting time", the index will always save little records. We hope to access the way, when accessing all the records in the table (ie 10% or more records), we hope to retrieve through the full table scanning; however, when we want to access has been processed When a transaction (ie 5% or less records), we hope to access through an index, because the number of records in the index is very small, please see the example below:
SQL> CREATE TABLE TT AS SELECT * from sys.dba_objects;
Table created
Executed in 0.601 seconds
SQL> ALTER TABLE TT ADD (T);
Table altered
Executed in 0.061 seconds
SQL> SELECT Count (*) from TT;
Count (*)
------------
6131C
Executed in 0.01 seconds
SQL> Update TT SET T = 1 Where Owner = 'DEMO';
10 Rows Updated
Executed in 0.03 seconds
SQL> commit;
Commit completion
Executed in 0 seconds
SQL> SELECT Count (*) from TT Where Owner = 'DEMO';
Count (*)
------------
10 -------------- has been processed
Executed in 0.08 seconds
s
SQL> SELECT Count (*) from TT;
Count (*)
------------
6131 -------------- Number of total records
Executed in 0.01 seconds
The following query can be seen as most records in the table (the number of records of processing, that is, the number of records of 10%), it can be seen that it uses a full table scan as we want:
Select Object_name from TT WHERE T is NULL;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- ------------------
| 0 | SELECT Statement | | | | || * 1 | Table Access Full | TT | | | | |
-------------------------------------------------- ------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
1 - Filter ("tt". "T" is null)
NOTE: Rule Based Optimization
14 rows selected
Executed in 0.05 seconds
The following query is because we want to access to the table in the table, we want to access by an index:
Select Object_name from TT WHERE T = 1;
Plan_table_output
-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------
| ID | OPERATION | Name | Rows | BYTES | COST |
-------------------------------------------------- ------------------
| 0 | SELECT Statement | | | | | |
| * 1 | Table Access Full | TT | | | | |
-------------------------------------------------- ------------------
Predicate Information (Identified by Operation Id):
-------------------------------------------------- -
1 - Filter ("tt". "T" = 1)
NOTE: Rule Based Optimization
14 rows selected
Executed in 0.06 seconds
Please note that there is no use index as we want, but use full table scans, there is a conclusion here:
After establishing an index, you must use the index in CBO, you must regularly update statistics.
Below we analyze the index, see what the effect:
SQL> Analyze Index TT_IDX VALIDATE STRUCTURE
INDEX Analyzed
Executed in 0 seconds
SQL> SELECT LF_ROWS from Index_stats;
LF_ROWS
------------
10 ---------- There are 10 lines in the index.
Executed in 0.05 seconds
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS ('Demo', 'TT_IDX');
PL / SQL Procedure SuccessFully Completed
Executed in 0.03 Secondssql> Select Distinct_Keys from User_indexes;
Distinct_Keys
-------------
1 ---------- only one key value
Executed in 0.05 seconds
SQL> SELECT * from TT WHERE T is NULL;
6121 lines have been selected.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (Full) of 'TT'
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
485 Consistent Gets
0 Physical READS
0 redo size
355012 BYTES SENT VIA SQL * NET to Client
4991 BYtes Received Via SQL * Net from Cliant
410 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
6121 Rows Processed
SQL> SELECT * from TT WHERE T = 5;
Unselected
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by index rowid) of 'tt'
2 1 Index (Range Scan) of 'TT_IDX' (Non-Unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
1 Consistent Gets
0 Physical READS
0 redo size
964 BYTES SENT VIA SQL * NET to Client
372 BYtes Received Via SQL * Net from Cliant
1 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
0 Rows Processed
SQL> SELECT * from TT WHERE T = 1;
10 lines have been selected.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by index rowid) of 'tt'
2 1 Index (Range Scan) of 'TT_IDX' (Non-Unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Gets4 Conistent Gets
0 Physical READS
0 redo size
1639 BYTES SENT VIA SQL * NET to Client
503 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
10 Rows Processed
SQL> Update TT SET T = 2 WHERE T = 1;
10 lines have been updated.
Execution Plan
-------------------------------------------------- ------------
0 Update Statement Optimizer = choose
1 0 Update of 'TT'
2 1 Index (Range Scan) of 'TT_IDX' (Non-Unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
14 DB Block Gets
1 Consistent Gets
0 Physical READS
3216 Redo Size
616 BYTES SENT VIA SQL * NET to Client
527 Bytes Received Via SQL * Net from Cliant
3 SQL * NET ROUNDTRIPS TO / FROM Client
2 Sorts (Memory)
0 Sorts (Disk)
10 Rows Processed
SQL> Set Autotrace Traceonly
SQL> Update TT Set T = 3 WHERE T IS NULL;
6121 rows updated.
Execution Plan
-------------------------------------------------- ------------
0 Update Statement Optimizer = choose
1 0 Update of 'TT'
2 1 Table Access (Full) of 'TT'
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
18683 DB Block Get
80 Consistent Gets
0 Physical READS
2583556 Redo size
618 BYTES SENT VIA SQL * NET to Client
533 BYtes Received Via SQL * Net from Clom Cliant
3 SQL * NET ROUNDTRIPS TO / FROM Client
1 Sorts (Memory)
0 Sorts (Disk)
6121 Rows Processed
SQL>
