导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于执行计划,sqlserver 和oracle的一点疑惑

专注or全面 跃顺咨询 2013-07-12 02:10:14


create table table1
(
id int,
Name varchar(50),
Remark varchar(200),
CreateDate datetime
)
--在时间字段上创建聚集索引创建索引
create clustered index index_createdate on table1(createDate desc)
--在id字段上创建非聚集索引
create unique index index_id on table1(Id desc)

select * from table1 where id ='1234'


--SELECT * FROM [table1] WHERE [id]=@1
|--Nested Loops(Inner Join, OUTER REFERENCES:([dbtest1].[dbo].[table1].[CreateDate]))
|--Index Seek(OBJECT:([dbtest1].[dbo].[table1].[index_1]), SEEK:([dbtest1].[dbo].[table1].[id]=(1234)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dbtest1].[dbo].[table1].[index_createdate]), SEEK:([dbtest1].[dbo].[table1].[CreateDate]=[dbtest1].[dbo].[table1].[CreateDate]) LOOKUP ORDERED FORWARD)


为什么会出现嵌套循环才能出结果?
按道理,根据非聚集索引查找到聚集索引,
根据聚集索引直接就找到数据的地址了,干嘛再循环嵌套一次呢?







--而oracle中就不一样了,类似的表,是一个索引组织表(相当于id上的聚集索引),根据普通索引来查询
--一样是普通索引找到聚集索引,然后直接出来结果了,看截图
create table indexTable(
ID int,
NAME varchar2 ( 50 ),
CreateDate date,
Remark varchar2(50),
constraint pk_id primary key ( ID )
)
organization index;







.
...全文
349 点赞 收藏 31
写回复
31 条回复
切换为时间正序
请发表友善的回复…
发表回复
hujiiori 2013-07-13
按照我的理解,非聚集索引指向聚集索引的键 根据非聚集索引找到了聚集索引的键,然后按照聚集索引查找 这里,根据非聚集索引找到了聚集索引的键,然后按照聚集索引查找数据行,这个查找数据行的步骤指的就是Key lookup,那么为什么要嵌套循环,因为第一步索引查找可能会查到多行,每行都需要做Key lookup,所以执行计划就是这样显示的。虽然你的非聚集索引是唯一的,理论上只会查到一行,但不影响执行计划这样显示。
回复
KevinLiu 2013-07-13
顺便帮我看看这个帖子http://bbs.csdn.net/topics/390517396
回复
引用 29 楼 SmithLiu328 的回复:
你仔细看,MSSQL中,createDate是聚集索引,而ID是非聚集索引,而oracle中是createDate是非聚集索引,而ID是聚集索引。 呵呵 我就是看到他的Oracle ID是聚集索引所以才问他,其实这样看的话跟SQL SERVER是一样的
嗯啊,完全是一样的,只是oracle没有把nested loop画上去而已。
回复
专注or全面 2013-07-13
引用 19 楼 hujiiori 的回复:
按照我的理解,非聚集索引指向聚集索引的键 根据非聚集索引找到了聚集索引的键,然后按照聚集索引查找 这里,根据非聚集索引找到了聚集索引的键,然后按照聚集索引查找数据行,这个查找数据行的步骤指的就是Key lookup,那么为什么要嵌套循环,因为第一步索引查找可能会查到多行,每行都需要做Key lookup,所以执行计划就是这样显示的。虽然你的非聚集索引是唯一的,理论上只会查到一行,但不影响执行计划这样显示。
谢谢您的回复,你看懂了我的疑问,也只能这样解释了
回复
KevinLiu 2013-07-13
你仔细看,MSSQL中,createDate是聚集索引,而ID是非聚集索引,而oracle中是createDate是非聚集索引,而ID是聚集索引。

呵呵 我就是看到他的Oracle ID是聚集索引所以才问他,其实这样看的话跟SQL SERVER是一样的
回复
现在我已经完全理解你的意思了。 没猜错的话,oracle中的脚本你还少给了一行关键的 create index index_createdate on indexTable(CreateDate) nologging; 现在问题就很简单了, 1 oracle是先根据CreateDate查出ID, 2 然后根据每个ID用INDEX UNIQUE SCAN来查出对应的数据行。 LZ你有没有发现这种逻辑跟SQL SERVER的计划给出的逻辑是一模一样的, 换句话说,还是那句话,虽然oracle的计划中没有明确给出nested loop,但是它编程为C/C++后,还是逃不出SmithLiu328给出那段NESTED LOOP伪代码的逻辑,否则oracle怎么取“下一个”ID 然后去做INDEX UNIQUE SCAN呢?
回复
引用 26 楼 SmithLiu328 的回复:
相当于id上的聚集索引 如果是这样的情况SQL Server也不用需要NESTED LOOP JOIN了啊,直接CLUSTERED INDEX就搞定了
他的2个索引类型在MSSQL跟oracle正好是倒过来的, 你仔细看,MSSQL中,createDate是聚集索引,而ID是非聚集索引,而oracle中是createDate是非聚集索引,而ID是聚集索引。
回复
KevinLiu 2013-07-13
相当于id上的聚集索引
如果是这样的情况SQL Server也不用需要NESTED LOOP JOIN了啊,直接CLUSTERED INDEX就搞定了
回复
专注or全面 2013-07-13
-而oracle中就不一样了,类似的表,是一个索引组织表(相当于id上的聚集索引),根据普通索引来查询
--一样是普通索引找到聚集索引,然后直接出来结果了,看截图
create table indexTable(
       ID int,
       NAME varchar2 ( 50 ),
       CreateDate date,
       Remark varchar2(50),
       constraint pk_id primary key ( ID )
      )
     organization index;

select * from indexTable where createDate = to_date('2013-7-13 10:17:47','yyyy-mm-dd hh24:mi:ss')
回复
那底层的代码应该是很类似的,还是那句话o编译后的代码还是逃不了嵌套循环,请问能不能贴出pl-sql代码?
回复
专注or全面 2013-07-13
引用 22 楼 SQL_Beginner 的回复:
不觉得SQL语句编译为native code的时候,oracle可以逃过嵌套的命运,虽然计划上没有明确表明。 你的PL-SQL是什么?明显感觉跟上面的T-SQL是不同的。
环境当然没办法做的完全一致,但是做了做大程度的一致性 oracle的那个测试表是一个索引组织表,类似于sqlserver的聚集索引表 在查询列上建立了一个B树索引, 查询的时候按照B树索列引作为查询条件,查询表对应的所有列(就是select *)
回复
不觉得SQL语句编译为native code的时候,oracle可以逃过嵌套的命运,虽然计划上没有明确表明。 你的PL-SQL是什么?明显感觉跟上面的T-SQL是不同的。
回复
专注or全面 2013-07-12
出来混总是要还的,以前注意到这个问题了, 就是索引表用非聚集索引查找的时候有个嵌套循环, 今天做oracle,忽然就想起来这个问题了,在oracle上测试,是我预想的那种效果 在sqlserver测试,就出现本帖子的问题了 虽然还是没有完全弄明白……
回复
专注or全面 2013-07-12
引用 16 楼 SmithLiu328 的回复:
为什么是除非聚集索引外的其他列,这里的键查找不是可以查询到所有的列吗? 键查找是可以找到所有的列,但是作Nested Loop Join是要用到数据的啊。你看一下OUTPUT LIST就知道了。具体为什么这样设计需要问微软了。
怪我“二黄眼”,还截图给人家看,哎,的确,键查找时,没有返回那个非聚集索引的列 最后应该还是用聚集索引做关联,嵌套循环出来结果的 这点确实跟oracle不一样啊,你看我oracle那个截图(模拟的索引是类似的,索引组织表+普通B树索引),直接两步查找,就出来了,没有第三部的嵌套循环
回复
KevinLiu 2013-07-12
为什么是除非聚集索引外的其他列,这里的键查找不是可以查询到所有的列吗?

键查找是可以找到所有的列,但是作Nested Loop Join是要用到数据的啊。你看一下OUTPUT LIST就知道了。具体为什么这样设计需要问微软了。
回复
专注or全面 2013-07-12
引用 4 楼 SmithLiu328 的回复:
为什么会出现嵌套循环才能出结果? 按道理,根据非聚集索引查找到聚集索引, 根据聚集索引直接就找到数据的地址了,干嘛再循环嵌套一次呢? 有一个操作是键查找,因为直接使用的非聚集索引无法找到所有的数据,需要用键查找找到除非聚集索引外的其他列,然后用循环嵌套合并数据
需要用键查找找到除非聚集索引外的其他列, 为什么是除非聚集索引外的其他列,这里的键查找不是可以查询到所有的列吗?
回复
KevinLiu 2013-07-12
非聚集索引指向聚集索引的键 没错阿
回复
专注or全面 2013-07-12
按照我的理解,非聚集索引指向聚集索引的键 根据非聚集索引找到了聚集索引的键,然后按照聚集索引查找(就成为了第一种情况), 直接查询出来结果了,所以无需嵌套循环 难道说“非聚集索引指向聚集索引的键”(当然表上有聚集索引)这个观点是不成立的?
回复
专注or全面 2013-07-12
好像是有点眉目了

select * from table1 where CreateDate='2012-1-1'--直接用聚集索引键查询
这句产生的是聚集索引查找


select * from table1 where id='123'--非聚集索引键查询
这句产生的索引查找+键查找

这里的键查找个聚集索引查找的区别是什么呢?

回复
专注or全面 2013-07-12
引用 10 楼 SmithLiu328 的回复:
对于关系R的每个元组 r 将其与关系S的每个元组 s 在JOIN条件的字段上直接比较并筛选出符合条件的元组。写成伪代码就是:

  foreach tuple r Î R do
  foreach tuple s Î S do
  if ri == sj then add to result

这个是Nested loop join的伪代码,你看懂这个就明白上面为什么要用到Nested loop join了。如果没有上面的非聚集索引扫描只扫描Clustered index的话,就变成Clustered index Scan,那么就可以找到所有数据,不需要做其他操作。


你所说的Nested loop join我也有所了解
我理解的Nested loop join是两个表做关联的时候可能产生的一种连接方式
或许是我理解的太狭隘了

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告