Index series 4-count optimization of stored values for index characteristics
Point: as long as the index can answer the question, the index can be treated as a "thin table" and the access path will be reduced. Also remember not to store null values
Drop table t purge
Create table t as select * from dba_objects
Update t set object_id=rownum
Commit
Create index idx1_object_id on t (object_id)
Set autotrace on
Select count (*) from t
Carry out the plan
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 292 (1) | 00:00:04 |
| | 1 | SORT AGGREGATE | | 1 |
| | 2 | TABLE ACCESS FULL | T | 69485 | 292 (1) | 00:00:04 |
-
Statistical information
0 recursive calls
0 db block gets
1048 consistent gets
Why not use the index, because the index cannot store null values, so add an is not null and try again
Select count (*) from t where object_id is not null
Carry out the plan
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 13 | 50 (2) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 | 13 |
| | * 2 | INDEX FAST FULL SCAN | IDX1_OBJECT_ID | 69485 | 882k | 50 (2) | 00:00:01 |
-
Statistical information
0 recursive calls
0 db block gets
170 consistent gets
-- you can also set the property of the column to not null without adding is not null, or continue to experiment as follows:
Alter table t modify OBJECT_ID not null
Select count (*) from t
Carry out the plan
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 49 (0) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 |
| | 2 | INDEX FAST FULL SCAN | IDX1_OBJECT_ID | 69485 | 49 (0) | 00:00:01 |
Statistical information
0 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If it is a primary key, there is no need to define whether the column is allowed to be empty.
Drop table t purge
Create table t as select * from dba_objects
Update t set object_id=rownum
Alter table t add constraint pk1_object_id primary key (OBJECT_ID)
Set autotrace on
Select count (*) from t
Carry out the plan
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 46 (0) | 00:00:01 |
| | 1 | SORT AGGREGATE | | 1 |
| | 2 | INDEX FAST FULL SCAN | PK1_OBJECT_ID | 69485 | 46 (0) | 00:00:01 |
Statistical information
0 recursive calls
0 db block gets
160 consistent gets
