Oracle compound index + null index usage problems, oracle Index

Oracle compound index + null index usage problems, oracle Index

Yesterday, I discussed an SQL Optimization Problem in the QQ group. The statements are roughly as follows:

Select A, min (B) from table group by;
-- Neither A nor B has the not null constraint. Column A has no null values and column B has null values.
-- Compound index IX_TEST (A, B) exists)

Therefore, for manual testing, the environment uses the emp table under scott user of Oracle.

1. First, check the execution plan of the following statement (in this case, the table only has the primary key index ):

2. Add IX_TEST (deptno, comm) and view the execution plan:

It is still a full table scan.

3. Add non-null constraints to the deptno column and view the execution plan again:

 

4. Conclusion:

B-tree indexes do not store null values. This is what all databases that use B-tree indexes have in common.

In this example, deptno and comm matching indexes are created. If deptno does not have a non-empty constraint, it means that some record will not appear in the index. In this case, you want to find min (comm) you must go back to the table to check whether the row whose deptno is null has the comm value. In this case, the optimizer considers that full table scan is more reasonable than scanning indexes and then returning to the table. Therefore, full table scan is selected.

After a non-empty constraint is added, deptno cannot be empty. Therefore, the number of keys in the index is equal to the total number of rows in the table. The min () value is not affected even if the other column is empty, you only need to scan the index to obtain the desired results. In this case, the optimizer selects index scanning.

In Mysql, indexes are used regardless of whether the first column of the composite index has a non-null constraint. If deptno is null, all columns in the same group take min (comm ), it may be because the BTREE index of Mysql is different from that of Oracle, so that the first column can be empty without returning to the table.

Finally, you must add non-null constraints to Oracle columns.