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
|
本类阅读TOP10