测试类型

SQL功能

测试版本

7.0.0LTS

问题描述

用例在v3走了hashjoin，ograc走的nestloop

操作系统和硬件信息

任意

测试环境

oGRAC单节点

被测功能

预置条件

操作步骤

用例：Hash_join/Hash_join_compare/tc_dss_hash_join_compare_000

-- 1. 创建表：保留宽行，模拟原用例的大字段访问成本
drop table if exists qz_left cascade CONSTRAINTS;
drop table if exists qz_dup cascade CONSTRAINTS;
drop table if exists qz_right cascade CONSTRAINTS;

create table qz_left(k int, pad varchar2(1000));
create table qz_dup(k int, pad varchar2(1000));
create table qz_right(k int, pad varchar2(1000));

create index qz_left_k_idx on qz_left(k);
create index qz_dup_k_idx on qz_dup(k);
create index qz_right_k_idx on qz_right(k);

-- 2. 插入数据：qz_dup 生成 1000000 行，并让连接键大量重复
insert into qz_left values(1,rpad('L',1000,'L'));
insert into qz_left values(2,rpad('L',1000,'L'));
insert into qz_left values(3,rpad('L',1000,'L'));
insert into qz_left values(4,rpad('L',1000,'L'));
insert into qz_left values(5,rpad('L',1000,'L'));
insert into qz_left values(6,rpad('L',1000,'L'));
insert into qz_left values(7,rpad('L',1000,'L'));
insert into qz_left values(8,rpad('L',1000,'L'));
insert into qz_left values(9,rpad('L',1000,'L'));
insert into qz_left values(10,rpad('L',1000,'L'));
insert into qz_right select k,rpad('R',1000,'R') from qz_left;
insert into qz_dup select a.k,rpad('D',1000,'D') from qz_left a,qz_left b,qz_left c,qz_left d,qz_left e,qz_left f;
commit;

-- 3. 查询
explain select count(*) from qz_left a left join qz_dup b on a.k=b.k right join qz_right c on a.k=c.k;

select systimestamp as select_start_time;
set timing on
select count(*) from qz_left a left join qz_dup b on a.k=b.k right join qz_right c on a.k=c.k;
set timing off
select systimestamp as select_end_time;

预期输出

实际输出

NESTED LOOPS

日志信息

提单组织

内部开发

测试代码