Shulou(Shulou.com)05/31 Report--

This article is about how to use composite indexes and null indexes in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Discuss a SQL optimization problem in the QQ group, the sentence is roughly as follows:

Select A _ mmin (B) from table group by A _ polim A _ mam B all have no not null constraint, column A has no null value, column B has null value. -- there is a composite index IX_TEST (Ameme B)

So the manual test is carried out, and the environment uses the emp table under scott users that comes with Oracle.

1. First, look at the execution plan of the following statement (at this time the table has only the primary key index):

two。 View the execution plan after adding IX_TEST (deptno,comm):

It was found that it was still a full table scan.

3. After adding a non-null constraint to the deptno column, view the execution plan again:

4. Summary:

Btree indexes do not store null values, which is common to all databases that use Btree indexes.

In this example, we created a compliance index for deptno,comm. If deptno does not have a non-null constraint, then some record will not appear in the index, and if you want to find min (comm), you must go back to the table to determine whether the row where deptno is null has the value of comm. At this point, the optimizer thinks that a full table scan is more reasonable than scanning the index and then returning to the table, so it chooses a full table scan.

When we add a non-null constraint, deptno cannot be empty, so the number of key values of the index is equal to the total number of rows of the table. Even if the other column comm is empty, it does not affect the value of min (). You only need to scan the index to get the desired results. In this case, the optimizer chooses the index scan.

In Mysql, the index is used regardless of whether there is a non-null constraint in the first column of the composite index. The whole part of the deptno null will be fetched min (comm) in a group. It may be that the BTREE index of Mysql is different from that of Oracle, so that the first column is empty without going back to the table.

Finally: Oracle columns can add non-empty constraints must be added.

Thank you for reading! On the "Oracle composite index and null index how to use" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.