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

This article mainly explains "what is the selection principle of database combination index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the selection principle of database combinatorial index"?

What are the indexes on the table based on, especially the combined indexes? how does the optimizer choose which one to use when there are multiple indexes?

Do an experiment today to verify it.

Create this table testindex

Table structure

Name Type Nullable Default Comments

--

CREATETIME DATE Y

LOC VARCHAR2 (100) Y

STATUS VARCHAR2 (20) Y

COL1 VARCHAR2 (10) Y

There are only two indexes on testindex

Right1 loc,createtime,status

Right2 createtime,loc,status

Collect table statistics

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (ownname = > 'TEST'

Tabname = > 'TESTINDEX'

Estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

Method_opt = > 'for all columns size skewonly'

Cascade= > TRUE

);

END

To collect statistical information, please refer to

Http://blog.csdn.net/robinson1988/article/details/5329522

Execute sql according to the value range of the condition

Select *

From testindex

Where CREATETIME =: 1

And loc >: 3

And status =: 4

Take the createtime,loc,status index

Select *

From testindex

Where CREATETIME >: 1

And loc =: 3

And status =: 4

Take the loc,createtime,status index

It shows that when selecting a combined index, according to the query conditions, the more accurate the value is, the smaller the field createtime/loc is, the more the index in front of it is selected.

It is necessary to collect statistical information here. The above rules and histograms for selecting indexes should not have any other index relationships.

At this point, I believe you have a deeper understanding of "what is the selection principle of database combination index". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.