Cause of full table Scan

 

The reason for the full table scan of the table's execution plan is as follows:

No index is created for the u SQL predicate column.

 

The u predicate column has a corresponding index, but the execution plan is not used.

Oracle does not use B * tree indexes.

1: When the where condition is compared with null, the index may not be used.

2: When count, sum, ave, max, min and other aggregation operations are performed, the index may not be used.

3: No index is used because of display or implicit function conversion.

4: In cbo mode, indexes are not used because the statistical information is too old.

5. No leading column is used in the composite index, resulting in no index.

6. The index is not used because the amount of accessed data exceeds a certain proportion.

 

The following describes some of the points.

I. Can an index be used for Null?

Generally, comparing the where condition with null causesFull Table ScanIn fact, if the index column value in the table is null, the row does not exist in the index (B * tree, bitmap index, or clustered index, therefore, to ensure the accuracy of the query structure, oracle uses full table scan instead.IndexScan, so it is reasonable not to go through the index.

Of course, if an index column is defined as not null, in this case, no index column is empty, so in this case, you can use index scan. Therefore, if the where condition contains something similar to is null, = null, whether to take the index depends on whether a column in the index is defined as not null.

The specific experiment is as follows:

SQL> create table t (x char (3), y char (5 ));

SQL> insert into t (x, y) values ('001', 'xxxxx ');

SQL> insert into t (x, y) values ('002 ', null );

SQL> insert into t (x, y) values (null, 'yyyy ');

SQL> insert into t (x, y) values (null, null );

SQL> commit;

SQL> create unique index t_idx on t (x, y );

SQL> analyze table t compute statistics for table for all indexes;

SQL> select blevel, leaf_blocks, num_rows from user_indexes where index_name = upper ('t_ idx ');

BLEVEL LEAF_BLOCKS NUM_ROWS

-------------------------------

0 1 3

IsnertFour records, but the index is saved only3The last entry is not saved in the index.

SQL> set autotrace traceonly explain;

SQL> select * from t where x is null;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 8)

1 0Table access (FULL)OF 'T' (Cost = 2 Card = 1 Bytes = 8)

 

SQL> create table t1 (x char (3), y char (5) not null );

SQL> insert into t1 (x, y) values ('001', 'xxxxx ');

SQL> insert into t1 (x, y) values (null, 'xxxxx ');

SQL> commit;

SQL> create unique index t1_idx on t1 (x, y );

SQL> analyze table t1 compute statistics for table for all indexes;

SQL> select blevel, leaf_blocks, num_rows from user_indexes where index_name = upper ('T1 _ idx ');

BLEVEL LEAF_BLOCKS NUM_ROWS

-------------------------------

0 12

SQL> select * from t1 where x is null;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 1 Card = 1 Bytes = 11)

1 0INDEX (range scan) OF 'T1 _ idx' (UNIQUE)(Cost = 1 Card = 1 Byt

Ii. COUNT (*)Clustered functions such

During aggregation operations such as count, sum, ave, max, and min, indexes are not needed sometimes, because if the optimizer finds that no column in the index column is defined as not null,In addition, no index key Column exists in the where condition, for example, x = x.In this case, the index scan results are inaccurate, and oracle uses full table scan. The preceding two tables are used for illustration.

SQL> select count (*) from t;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 2 Card = 1)

1 0 SORT (AGGREGATE)

2 1 table access (FULL) OF 'T' (Cost = 2 Card = 4)

 

SQL> select sum (x) from t;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 5)

1 0 SORT (AGGREGATE)

2 1 table access (FULL) OF 'T' (Cost = 2 Card = 4 Bytes = 20)

Because the index column (x, y) of the table is not defined as not null, the full table scan is performed, even if the row x = null, y = null is deleted, scan the entire table.

SQL> delete t where x is null and y is null;

One row has been deleted.

Commit

SQL> analyze table t compute statistics for table for all indexes;

SQL> select count (*) from t;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 2 Card = 1)

1 0 SORT (AGGREGATE)

2 1 table access (FULL) OF 'T' (Cost = 2 Card = 3)

 

The sameSQLStatement. For table t1, because the index column y is defined as not null, oracle selects index scan.

SQL> select count (*) from t1;

 

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 1 Card = 1)

1 0 SORT (AGGREGATE)

2 1 INDEX (full scan) OF 'T1 _ idx' (UNIQUE) (Cost = 1 Card = 2)

SQL> select sum (x) from t1;

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 1 Card = 1 Bytes = 5)

1 0 SORT (AGGREGATE)

2 1 INDEX (full scan) OF 'T1 _ idx' (UNIQUE) (Cost = 1 Card = 2

Tes = 10)

Iii. Full table scan due to implicit or show function downgrading

SQL> Select * from t1 where x = 001;

X Y COMM

--------------------------------------------

001 xxxxx 88888

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 26)

1 0 table access (FULL) OF 't1' (Cost = 2 Card = 1 Bytes = 26)

Because x is similar to char, oracle implicitly converts x = 001 to to_number (x) = 001, and the index created in this field does not work, function-based indexes can be used here. Compared with common indexes, the fbi stores the converted values in the indexes.

SQL> create index t1_fbi on t1 (to_number (x ));

SQL> analyze table t1 compute statistics for table for all indexes;

SQL> Select * from t1 where x = 001;

X Y COMM

--------------------------------------------

001 xxxxx 88888

Execution Plan

----------------------------------------------------------

0 select statement ptimizer = CHOOSE (Cost = 1 Card = 1 Bytes = 26)

1 0 table access (by index rowid) OF 't1' (Cost = 1 Card = 1 Bytes

= 26)

2 1 INDEX (range scan) OF 'T1 _ FBI '(NON-UNIQUE) (Cost = 1 Card

= 1)

4. Unable to use because the statistics are not up-to-date

5. No index is used because no leading column is used in the composite index, such as the composite index (x, y). The where condition is similar to where y = ...., At this time, the index is not taken away (if there are few different values of x, then oracle9i may follow the index skip scan, and its principle is similar to select * from t where y =... And x = (a certain value) union all select * from t where y =... And x = (a specific value )........

6. If the proportion of accessed data exceeds a certain range, the optimizer will think that full table scan costs less. This will increase the total cost by performing index scanning. Therefore, an index is used to quickly access a small number of records in a table. It is not suitable for accessing a large number of records in a table.