数据类型的不匹配可能会导致索引失效

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

Sybase和SQL Server在这一点上有所不同,如果条件比较中的数据类型不匹配的话,可能会引起索引失效,导致潜在的Performance问题。

简单说明如下:

Create Table Test(

c1 int not null,

c2 money default 0,

c3 varchar(20),

constraint PK_Test primary key(c1))

go

create index ind_c2_Test on Test(c2)

go

插入一些数据后,我们可以测试如下:

1> set showplan on

2> go

1> declare @var_int int

2> select @var_int=2

3> select * from Test where c1=@var_int

4> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1

The type of query is DECLARE.

QUERY PLAN FOR STATEMENT 2 (at line 2).

STEP 1

The type of query is SELECT.

QUERY PLAN FOR STATEMENT 3 (at line 3).

STEP 1

The type of query is SELECT.

FROM TABLE

Test

Nested iteration.

Using Clustered Index.

Index : PK_Test

Forward scan.

Positioning by key.

Keys are:

c1 ASC

Using I/O Size 2 Kbytes for data pages.

With LRU Buffer Replacement Strategy for data pages.

(1 row affected)

c1 c2 c3

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

2 129.14 Hellen

(1 row affected)

我们看到,sybase的执行计划会使用clustered index来读取数据。

下面,采用money类型来进行测试

1> declare @var_money money

2> select @var_money=2

3> select * from Test where c1=@var_money

4> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1

The type of query is DECLARE.

QUERY PLAN FOR STATEMENT 2 (at line 2).

STEP 1

The type of query is SELECT.

QUERY PLAN FOR STATEMENT 3 (at line 3).

STEP 1

The type of query is SELECT.

FROM TABLE

Test

Nested iteration.

Table Scan.

Forward scan.

Positioning at start of table.

Using I/O Size 2 Kbytes for data pages.

With LRU Buffer Replacement Strategy for data pages.

(1 row affected)

c1 c2 c3

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

2 129.14 Hellen

(1 row affected)

我们可以看到,sybase没有采用索引,而是采用了全表扫描。

实际上,Sybase并不是类型不一致就一定不会使用索引,而是有一个匹配原则,原则上是只要索引列的类型优先级高于搜索条件的数据类型,就会使用索引。

这个优先级,可以通过查询系统表master.dbo.systypes.

1> select hierarchy,name from master.dbo.systypes

2> order by 1

3> go

hierarchy name

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

1 floatn

2 float

3 datetimn

4 datetime

5 real

6 numericn

7 numeric

8 decimaln

9 decimal

10 moneyn

11 money

12 smallmoney

13 smalldatetime

14 intn

15 int

16 smallint

17 tinyint

18 bit

19 univarchar

20 unichar

22 sysname

22 varchar

22 nvarchar

23 char

23 nchar

24 timestamp

24 varbinary

25 binary

26 text

27 image

99 extended type

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航