Federated Index of database __ database



Federated Index Use conclusion:

1): The first column of the Union index appears in the query condition, or all of them, the federated index can be used.

2: As long as the conditions are linked together in the conditional column, this example is:

Last_name= ' 1′and first_name= ' 1′

And

First_name= ' 1′and last_name= ' 1′

, both before and after, the federated index will be used.

3): The first column of the Federated Index does not appear in the query condition, and the second column of the Federated Index, or the third column, does not use the Federated Index query.

Application conclusion of single column index:

1: As long as the index column in the Condition column, no matter where, can use the index query.

The common denominator of the two:

1): To use the index, must conform to the SARG standard.

2): To improve the speed of query.

3): Additional system overhead, disk space required.

Supplemental Note: Stmttext information to produce, preceded by a query statement: SET STATISTICS profile on. You can run it to see if your query is reasonable, so you can really optimize.

The purpose of this article is to discuss under what circumstances the index can be used.

Indexes: Creating indexes can be divided into two types based on the different query business: Single column index, federated Index. As the name suggests, a single column index is the creation of an index on a column of a table, and a federated index is a federated index on more than one column.

Advantages and Disadvantages Comparison:

1: The space occupied by the index: a single column index is relatively small.

2): Index creation time: A single column index is relatively short.

3): Index on the impact of Insert,update,delete program: A single column index is relatively low.

4): In a multiple condition query, the joint index efficiency is high.

The use of an index: a single column index can appear anywhere in the Where condition, and the federated indexes need to be written in a certain order.

The test software environment used in this paper is as follows: SQL05

DEMO: Create a People table that contains the person ID and name. Create a clustered index on the person ID and create a union on first_name and Last_Name

Index.

CREATE TABLE person (ID int, last_name varchar (), first_name varchar (30))

Create unique clustered index person_id on person (ID)

Create index person_name on person (last_name, first_name)

In the example above, a clustered index was created on the ID, and the following query uses a clustered index.

where id=1

where id>1

where id<1

where ID between 1 and N

Where id like ' 1% '

where ID in (1,2,3 ...)

Description: The ID lists the position in the current condition and does not necessarily require the first column to be unaffected by the position.

However, the following query method does not use the clustered index.

where person_id +1=n

where person_id like '%5′

where person_id like '%5% '

where person_id ABS (15)

The best advantage of a federated indexed column over a single column index is that it is more precise than a single column index for a multiple-condition query. Take the above people's table and say, if

To query a person's full name, only know that first_name is difficult to find the full name of the person immediately, if you know first_name and last_name will be very easy to find.

The following describes the application of the index according to the different conditions and output column order.

First case: – The condition and output columns are the same as the indexed column order

Select Last_name,first_name from person where last_name= ' 1′and first_name= ' 1′

Stmttext

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

Seek: ([bdg_web_vaction]. [dbo]. [Person]. [last_name]=[@1]

and [Bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@2]) ORDERED FORWARD)

Results: Using Person_name federated Index to find

Second Scenario: – The conditional column is different from the indexed column order, but the output column is the same

Select Last_name,first_name from person where first_name= ' 1′and last_name= ' 1′

Stmttext

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

Seek: ([bdg_web_vaction]. [dbo]. [Person]. [Last_name]=[@2] and [bdg_web_vaction].

[dbo]. [Person]. [first_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name federated Index to find

Third Scenario: – The order of the conditional and output columns is not the same as the indexed columns

Select First_name,last_name from person where first_name= ' 1′and last_name= ' 1′

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

Seek: ([bdg_web_vaction]. [dbo]. [Person].

[Last_name]=[@2] and [bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name federated Index to find

Situation Fourth: – Conditions listed in the middle of first_name and last_name add another condition

SELECT ID, first_name,last_name from where First_name= ' 1′and id=1 and Last_name= ' 1′

Clustered Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [person_id]),

Seek: ([bdg_web_vaction]. [dbo]. [Person]. [Id]=convert_implicit (int,[@2],0)),

WHERE: ([bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1] and [bdg_web_vaction]. [dbo]. [Person]. [Las

Result: Cannot use Person_name federated index to find

Fifth:--separate first_name and last_name in output columns

SELECT first_name,id,last_name from person where first_name= ' 1 ' and last_name= ' 1 '

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

Seek: ([bdg_web_vaction]. [dbo]. [Person].

[Last_name]=[@2] and [bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1])

ORDERED FORWARD)

Results: Using Person_name federated Index to find

Sixth: The condition column does not appear in the first column of the Federated Index

SELECT first_name,id,last_name from where First_name= ' 1′

SELECT first_name,last_name from where First_name= ' 1′

SELECT last_name, first_name from where First_name= ' 1′

Index Scan (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

WHERE: ([bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1])

Results: The Person_name Federated index could not be exploited.

Situation Seventh: – The first column of the Federated Index appears in the Criteria column

SELECT first_name,id,last_name from where Last_name= ' 1′

SELECT first_name,last_name from where Last_name= ' 1′

SELECT last_name, first_name from where Last_name= ' 1′

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

Seek: ([bdg_web_vaction]. [dbo]. [Person]. [last_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name federated Index to find

Federated Index Usage Summary:

1): The first column of the Union index appears in the query condition, or all of them, the federated index can be used.

2: As long as the conditions are linked together in the conditional column, this example is:

Last_name= ' 1′and first_name= ' 1′

And

First_name= ' 1′and last_name= ' 1′

, both before and after, the federated index will be used.

3): The first column of the Federated Index does not appear in the query condition, and the second column of the Federated Index, or the third column, does not use the Federated Index query.

Summary of application of single column index:

1: As long as the index column in the Condition column, no matter where, can use the index query.

The common denominator of the two:

1): To use the index, must conform to the SARG standard.

2): To improve the speed of query.

3): Additional system overhead, disk space required.

Supplemental Note: Stmttext information to produce, preceded by a query statement: SET STATISTICS profile on. You can run it to see if your query is reasonable, so you can really optimize.

Summary: Even if an index is created on a table, it does not help if the query writes unscientific (not conforming to the SARG standard), optimizes the query statement based on the table index, and creates the appropriate index if no suitable index is available.