表扫描< - >索引扫描，版本依赖？ [英] Table scan <--> Index scan, Version dependent ?
问题描述
在我工作的公司，工作申请人需要做一点测试。
人力资源经理最近有一位候选人声称其中一个是
这些问题含糊不清。
根据DB2的版本，一个或多个选择问题的另一个答案可能是正确的，候选人说。人力资源管理局要求我调查此事，但由于我不是专家，因为我在DB2的运作方式中提出问题，我希望你们中的任何一个人都能提出这个问题。可以
给我一个明确的答案。
考试的问题是：
！给出一个带有主键的表PARTS
！ PARTNO CHAR（15）
！
！选择
！
！ SELECT * FROM PARTS
！ PARTNO =：WS-PART
！
！其中主变量声明为
！
！ WS-PART PIC X（12）。
！
！这是真的吗？
！
！ 1）这将使用索引扫描执行
！ 2）这将使用表扫描执行
！ 3）这将产生编译错误
候选人声称在版本8之前使用了表格扫描
DB2
V8使用索引扫描。
任何人都可以确认这个吗？
是否有人知道手册或其他信息来源
IBM描述了什么？
主机变量的影响比
列声明短吗？
我非常感谢你的帮助让我们的考试正确
Dia
"直径" <迪**** @ techemail.com>在消息中写道
news：11 ********************** @ o13g2000cwo.googlegr oups.com ...在我工作的公司工作申请人需要做一点测试。
人力资源经理最近有一位候选人声称其中一个问题含糊不清。 />
根据DB2的版本，一个或多个选择问题的其他答案可能是正确的，候选人说。
HRM让我调查问题，但由于我不是DB2的工作专家，我向你提问，希望你们中的任何人能给我一个明确的答案。
关于考试是：
！给出一个带有主键的表PARTS
！ PARTNO CHAR（15）
！
！选择
！
！ SELECT * FROM PARTS
！ PARTNO =：WS-PART
！
！其中主变量被声明为
！
！ WS-PART PIC X（12）。
！
！这是真的吗？
！
！ 1）这将使用索引扫描执行
！ 2）这将使用表扫描执行
！ 3）这将产生编译错误
候选人声称在DB2版本8之前使用了表扫描，而在V8开始时使用了索引扫描。 br />
任何人都可以确认一下吗？
有没有人知道来自IBM的手册或其他信息来源？
<主机变量比
列声明更短的影响是什么？
我非常感谢你的帮助让我们的测试正确
Dia
我假设你在谈论DB2 for z / OS？
Dia写道：在我工作的公司工作申请人需要做一点测试。
人力资源经理最近有一位候选人声称其中一个问题含糊不清。
<根据DB2的版本，多个选择问题的一个或另一个答案可能是正确的，可以didate说道。
人力资源管理局让我调查此事，但由于我不是DB2的工作专家，我向你提出问题，希望你们中的任何人能够给我一个确定的答案。
考试的问题是：
！给出一个带有主键的表PARTS
！ PARTNO CHAR（15）
！
！选择
！
！ SELECT * FROM PARTS
！ PARTNO =：WS-PART
！
！其中主变量被声明为
！
！ WS-PART PIC X（12）。
！
！这是真的吗？
！
！ 1）这将使用索引扫描执行
！ 2）这将使用表扫描执行
！ 3）这将产生编译错误
候选人声称在DB2版本8之前使用了表扫描，而在V8开始时使用了索引扫描。 br />
任何人都可以确认一下吗？
有没有人知道来自IBM的手册或其他信息来源？
<主机变量比
列声明更短的影响是什么？
我非常感谢你的帮助让我们的测试正确
Dia
就像Mark A一样，我认为这是针对zOS的DB2？
无论哪种方式，计划都不依赖于任何功能...... / b $ b优化器提供（当然会随着时间的推移而得到改善），但也可以通过统计数据获得
。
如果表格的行数太少，那么它的内容适合单个页面，
然后一个表扫描实际上可能至少如果不是更有效，就像
ISCAN / FETCH组合一样。
我很难相信DB2 for zOS V7会在给出合理大小的表格的情况下对
a PK访问进行表扫描。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
DB2 UDB for Linux，Unix，Windows
IBM多伦多实验室
" Serge Rielau" < SR ***** @ ca.ibm.com>在消息中写道
news：42 ************* @ individual.net ...就像马克A一样，我假设这个DB2 for zOS是什么？
无论哪种方式，计划不仅取决于优化器提供的任何功能（当然会随着时间的推移而得到改进），还可以通过
统计数据。
如果该表的行数非常少，以至于它的内容适合单个页面，然后表扫描实际上至少可能与
ISCAN / FETCH组合相比效率更高。
我很难相信DB2 for zOS V7会在一张合理大小的桌子上进行表格扫描以获得PK访问。
干杯
Serge -
Serge Rielau
DB2解决方案开发
适用于Linux，Unix，Windows的DB2 UDB
IBM多伦多实验室
问题中的关键问题是在谓词中比较的主变量与表格列的长度不同。我知道，在一个
时间内，DB2 for z / OS不允许DB2使用索引，因为长度不匹配。
所以问题是这个限制是否已被解除，是否在DB2 for z / OS版本8中取消了
。在V8中肯定有一些增强功能
与varchar列的填充有关，但是快速的b / b $ b一瞥手册没有告诉我这是否会影响不同
长度固定长度的char列。
At the company I work job applicants are required to do a little test.
The human resource manager recently had a candidate who claimed one of
the questions was ambiguous.
Dependent upon the version of DB2 one or the other answer of a multiple
choice question could be right, the candidate said.
The HRM asked me to look into the matter, but since I am no expert in
the workings of DB2 I submit the question to you hoping any of you can
give me a definitive answer.
The question on the exam is :
! Given a table PARTS with a primary key
! PARTNO CHAR(15)
!
! A select
!
! SELECT * FROM PARTS
! WHERE PARTNO = :WS-PART
!
! in which the host variable is declared as
!
! WS-PART PIC X(12).
!
! Which is true ?
!
! 1) This will be executed using an index scan
! 2) This will be executed using a table scan
! 3) This will give a compilation error
The candidate claimed that a table scan was used prior to Version 8 of
DB2
while starting at V8 an index scan is used.
Can anybody confirm this ??
Does anybody know of a manual or an other source of information from
IBM where this is described ??
What is the influence of the host variable being shorter than the
column declaration ?
I would very much appreciate your help to get our test right
Dia
"Dia" <Di****@techemail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...At the company I work job applicants are required to do a little test.
The human resource manager recently had a candidate who claimed one of
the questions was ambiguous.
Dependent upon the version of DB2 one or the other answer of a multiple
choice question could be right, the candidate said.
The HRM asked me to look into the matter, but since I am no expert in
the workings of DB2 I submit the question to you hoping any of you can
give me a definitive answer.
The question on the exam is :
! Given a table PARTS with a primary key
! PARTNO CHAR(15)
!
! A select
!
! SELECT * FROM PARTS
! WHERE PARTNO = :WS-PART
!
! in which the host variable is declared as
!
! WS-PART PIC X(12).
!
! Which is true ?
!
! 1) This will be executed using an index scan
! 2) This will be executed using a table scan
! 3) This will give a compilation error
The candidate claimed that a table scan was used prior to Version 8 of
DB2
while starting at V8 an index scan is used.
Can anybody confirm this ??
Does anybody know of a manual or an other source of information from
IBM where this is described ??
What is the influence of the host variable being shorter than the
column declaration ?
I would very much appreciate your help to get our test right
Dia
I assume you are talking about DB2 for z/OS?
Dia wrote:At the company I work job applicants are required to do a little test.
The human resource manager recently had a candidate who claimed one of
the questions was ambiguous.
Dependent upon the version of DB2 one or the other answer of a multiple
choice question could be right, the candidate said.
The HRM asked me to look into the matter, but since I am no expert in
the workings of DB2 I submit the question to you hoping any of you can
give me a definitive answer.
The question on the exam is :
! Given a table PARTS with a primary key
! PARTNO CHAR(15)
!
! A select
!
! SELECT * FROM PARTS
! WHERE PARTNO = :WS-PART
!
! in which the host variable is declared as
!
! WS-PART PIC X(12).
!
! Which is true ?
!
! 1) This will be executed using an index scan
! 2) This will be executed using a table scan
! 3) This will give a compilation error
The candidate claimed that a table scan was used prior to Version 8 of
DB2
while starting at V8 an index scan is used.
Can anybody confirm this ??
Does anybody know of a manual or an other source of information from
IBM where this is described ??
What is the influence of the host variable being shorter than the
column declaration ?
I would very much appreciate your help to get our test right
Dia
Just like Mark A, I assume this is DB2 for zOS?
Either way plans are not only dependent on whatever fucntion teh
optimizer provides (which of course gets improved over time), but also
by statisticts.
If the table has so few rows that it''s content fits onto a single page,
then a table scan may actually be at least if not more efficient as as
ISCAN/FETCH combination.
I have hard time believing that DB2 for zOS V7 would do a table scan for
a PK access given a reasonably sized table.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:42*************@individual.net...Just like Mark A, I assume this is DB2 for zOS?
Either way plans are not only dependent on whatever fucntion teh optimizer
provides (which of course gets improved over time), but also by
statisticts.
If the table has so few rows that it''s content fits onto a single page,
then a table scan may actually be at least if not more efficient as as
ISCAN/FETCH combination.
I have hard time believing that DB2 for zOS V7 would do a table scan for a
PK access given a reasonably sized table.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
The key issue in the question is that the host variable which is compared in
the predicate is a different length than table column. I know that at one
time, DB2 for z/OS would not allow DB2 to use an index because of the length
mis-match.
So the question is whether this restriction has been lifted, and was it
lifted in version 8 of DB2 for z/OS. There definitely are some enhancements
in V8 relating to padding of varchar columns that are indexed, but a quick
glance of the manual did not reveal to me whether this affects different
lengths of fixed length char columns.
这篇关于表扫描< - >索引扫描，版本依赖？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
