create table test1 as select * from sysindexes
create table test2 as select * from systables
create table test2 as select * from systables
create index i_test1_1 on test1 (tableid)
create index i_test1_2 on test1 (name)
create index i_test2_1 on test2 (id)
create index i_test2_2 on test2 (name)
create index i_test1_2 on test1 (name)
create index i_test2_1 on test2 (id)
create index i_test2_2 on test2 (name)
首先测试exists的:
————————————————————————————————————
select * from test1 a where exists (select 1 from test2 b where a.tableid=b.id)
and (a.name = 'qwqwqqwqwq' )
and (a.name = 'qwqwqqwqwq' )
#RSET:[3, 3, 1];
#HASH SEMI JOIN(HNLP):[3, 3, 1];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[1, 1, 1]; i_test2_1(test2), FULL_SCAN
#HASH SEMI JOIN(HNLP):[3, 3, 1];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[1, 1, 1]; i_test2_1(test2), FULL_SCAN
这种情况,高效的执行方式显然应该是a 通过name上的索引得到数据后 以loop 方式通过b表id上的索引驱动 b
oracle下CBO会利用一种subquer unnest 的技术进行改写
DM下通过手工改写,既能得到上面说的执行计划:
select * from test1 a,test2 b where a.tableid=b.id and a.name = 'qwqwqqwqwq'
#RSET:[3, 1, 1];
#XNLP:[3, 1, 1]; CROSS_JOIN
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test2_1(test2), INDEX_EQU_SEARCH
#XNLP:[3, 1, 1]; CROSS_JOIN
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test2_1(test2), INDEX_EQU_SEARCH
另一种方式也是类似:
select * from test1 a where exists (select 1 from test2 b where a.tableid=b.id
and b.name = 'qwqwqqwqwq' )
and b.name = 'qwqwqqwqwq' )
#RSET:[4, 4, 5];
#HASH SEMI JOIN(HNLP):[4, 4, 5];
#CSEK:[3, 3, 5]; INDEX33555578(test1), FULL_SCAN
#CSEK(SECOND):[1, 1, 1]; i_test2_2(test2), INDEX_EQU_SEARCH
非常低效,a表全表然后hash join b的结果集
—————————————————————————————————————
in 子查询测试:
select * from test1 a,test2 b where a.tableid=b.id and a.name = 'qwqwqqwqwq'
#RSET:[3, 3, 0];
#SNLP:[3, 3, 0];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[2, 1, 80]; i_test2_1(test2), INDEX_EQU_SEARCH
select * from test1 a where a.tableid in (select id from test2 b where b.name = 'qwqwqqwqwq')
#RSET:[2, 1, 1];
#XNLP:[2, 1, 1]; CROSS_JOIN
#XSORT:[0, 0, 0]; keys_num(1), is_distinct(TRUE)
#CSEK(SECOND):[1, 1, 1]; i_test2_2(test2), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test1_1(test1), INDEX_EQU_SEARCH
#XNLP:[2, 1, 1]; CROSS_JOIN
#XSORT:[0, 0, 0]; keys_num(1), is_distinct(TRUE)
#CSEK(SECOND):[1, 1, 1]; i_test2_2(test2), INDEX_EQU_SEARCH
#CSEK(SECOND):[0, 0, 0]; i_test1_1(test1), INDEX_EQU_SEARCH
select * from test1 a where a.tableid
in (select id from test2 b )
and
a.name='nunnn'
in (select id from test2 b )
and
a.name='nunnn'
#RSET:[3, 3, 0];
#SNLP:[3, 3, 0];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[2, 1, 80]; i_test2_1(test2), INDEX_EQU_SEARCH
#SNLP:[3, 3, 0];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[2, 1, 80]; i_test2_1(test2), INDEX_EQU_SEARCH
两种方式都得到了较好的处理
但是某种特殊手误可能导致的情况,还是没有得到很好的处理:
select * from test1 a where a.tableid
in (select id from test2 b where a.name='nunnn')
in (select id from test2 b where a.name='nunnn')
#RSET:[3, 3, 1];
#HASH SEMI JOIN(HNLP):[3, 3, 1];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[1, 1, 2]; i_test2_1(test2), FULL_SCAN
#HASH SEMI JOIN(HNLP):[3, 3, 1];
#CSEK(SECOND):[2, 2, 1]; i_test1_2(test1), INDEX_EQU_SEARCH
#CSEK:[1, 1, 2]; i_test2_1(test2), FULL_SCAN
当然这在oracle下也是一样有问题的
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27378/viewspace-683849/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27378/viewspace-683849/