索引问题，完全扫描 [英] Issues with Index, Full Scans instead
问题描述
我有以下sintax：
从库存中选择*
其中PartId = Coalesce（v_PartId，PartId）
这种类型的查询在提供
v_PartId参数的商店过程中使用，数据库上PartId的数据类型是
CHAR（25）参数相同。我遇到的问题是这个
查询对表使用全扫描而不是使用此表具有的PartId索引
。如果我将查询更改为
从库存中选择*
其中PartId = v_PartId
然后，使用索引。
这只是一个示例，而不是我的应用程序中的真实查询，但
它''为了说明我的问题，我有一个应用程序使用这个
sintax并运行SQL Server并且运行正常，而不是
Coalesce，我正在使用函数IsNull for SQL Server。我有很多
的商店程序使用这个sintax，因为我无法预料应用程序的用户将使用哪个
搜索条件，任何人
请解释为什么DB2引擎不喜欢这个sintax而是
使用索引运行全扫描。我试图更改
Coalesce for CASE并且不能正常工作。
提前致谢，
Luis Fajardo
lu*********@hotmail.com
I have the following sintax:
Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be
Select *
From Inventory
Where PartId = v_PartId
then, the index is used.
This is just a sample an not a real query within my application, but
it''s to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I''m using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can''t anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn''t like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn''t work as well.
Thanks in advance,
Luis Fajardo
lu*********@hotmail.com
推荐答案
" LUIS FAJARDO" <卢********* @ hotmail.com>在留言中写道
news：78 ************************** @ posting.google.c om ...
"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...
我有以下sintax：
选择*
来自库存
其中PartId = Coalesce（v_PartId，PartId）
>这种类型的查询在提供
v_PartId参数的存储过程中使用，数据库上PartId的数据类型与参数的CHAR（25）相同。我遇到的问题是这个
查询对表使用了全扫描，而不是使用此表所具有的PartId索引。如果我将查询更改为
从库存中选择*
其中PartId = v_PartId
那么，使用索引。
这只是一个示例，而不是我的应用程序中的真实查询，但是它是为了说明我的问题，我有一个应用程序使用这个
sintax并运行SQL Server和工作得很好，而不是Coalesce，我正在使用SQLN的函数IsNull。我有很多使用这个sintax的商店程序，因为我无法预料应用程序的用户将使用哪个搜索条件，任何人
请解释为什么DB2引擎没有不喜欢这个sintax，而是使用索引运行全扫描。我试图更改
Coalesce for a CASE并且不能正常工作。
提前致谢，
Luis Fajardo
lu ********* @ hotmail.com
I have the following sintax:
Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be
Select *
From Inventory
Where PartId = v_PartId
then, the index is used.
This is just a sample an not a real query within my application, but
it''s to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I''m using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can''t anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn''t like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn''t work as well.
Thanks in advance,
Luis Fajardo
lu*********@hotmail.com
为什么你不能使用是空的在DB2中？
Why can''t you use "is null" in DB2?
当然，显而易见的问题是你是否有runnats。
一旦确定你有什么样的索引？
如果所有其他方法都失败了，这是一个神奇的伎俩：
db2set DB2_SELECTIVITY = ALL
重启db2。
然后试试这个：
SELECT选择*
来自库存
其中PartId = Coalesce（v_PartId，PartId）SELECTIVITY 0.0001
这将告诉DB2你希望这个谓词具有高度的选择性。
干杯
Serge
The obvious question of course is whether you have doen runstats.
Once that is settle what sort of index do you have?
If all else fails here is a magic trick:
db2set DB2_SELECTIVITY=ALL
Restart db2.
Then try this:
SELECT Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId) SELECTIVITY 0.0001
That will tell DB2 that you expect this predicate to be highly selective.
Cheers
Serge
你将如何做？我搜索IsNull，在我看来，它不是DB2可用的
。请给我一个样品。
谢谢
" Mark A" <无**** @ nowhere.com>在消息新闻中写道：< dY **************** @ news.uswest.net> ...
How you will do it? I search for the IsNull and seems to me that it''s
not available for DB2. Give me a sample please.
Thanks
"Mark A" <no****@nowhere.com> wrote in message news:<dY****************@news.uswest.net>...
" LUIS FAJARDO" <卢********* @ hotmail.com>在消息中写道
新闻：78 ************************** @ posting.google.c om ...
"LUIS FAJARDO" <lu*********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...
我有以下sintax：
选择*
来自库存
其中PartId = Coalesce（v_PartId，PartId）
这种类型的查询在提供
v_PartId参数的存储过程中使用，数据库上PartId的数据类型与参数的CHAR（25）相同。我遇到的问题是这个
查询对表使用了全扫描，而不是使用此表所具有的PartId索引。如果我将查询更改为
从库存中选择*
其中PartId = v_PartId
那么，使用索引。
这只是一个示例，而不是我的应用程序中的真实查询，但是它是为了说明我的问题，我有一个应用程序使用这个
sintax并运行SQL Server和工作得很好，而不是Coalesce，我正在使用SQLN的函数IsNull。我有很多使用这个sintax的商店程序，因为我无法预料应用程序的用户将使用哪个搜索条件，任何人
请解释为什么DB2引擎没有不喜欢这个sintax，而是使用索引运行全扫描。我试图更改
Coalesce for a CASE并且不能正常工作。
提前致谢，
Luis Fajardo
lu ********* @ hotmail.com
I have the following sintax:
Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
that this table has. If I change the query to be
Select *
From Inventory
Where PartId = v_PartId
then, the index is used.
This is just a sample an not a real query within my application, but
it''s to illustrate my problem, I have an application using this
sintax and running with an SQL Server and works just fine, instead of
Coalesce, I''m using the function IsNull for SQL Server. I have a lot
of store procedures using this sintax because I can''t anticipate which
search criteria the users of the application are going to use, anybody
please explain why the DB2 engine doesn''t like this sintax and instead
of using the index is running a FULL SCAN. I tried to change the
Coalesce for a CASE and doesn''t work as well.
Thanks in advance,
Luis Fajardo
lu*********@hotmail.com
为什么可以' 你使用是否为null在DB2？
Why can''t you use "is null" in DB2?
这篇关于索引问题，完全扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
