Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.