Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Cannot Reproduce
-
None
-
None
-
None
-
SQuirreL SQL Client 4.0.0
Phoenix 5.0
sqlline version 1.2.0
Description
I executed the same statement in different ways and got different results
explain select /*+USE_SORT_MERGE_JOIN */ s.loanacno,s.term,s.paiddate,s.shouldprin,s.shouldinter,s.shouldfee,s.shouldprinpenalty,s.shouldinterpenalty,s.sotherkillamt,s.discoutinter, r.realprin,r.realinter,r.realfee,r.realprinpenalty,r.realinterpenalty,r.otherkillamt,r.billdate,r.overdueflag,r.paidflag,r.channel,r.subssource, d.paiddate,d.yd_smonthprofit,d.rd_sguafee,d.rd_sassushareamt,d.yd_rmonthprofit,d.rd_rguafee,d.rd_rassushareamt from lake.yd_lake_plan_s s left join lake.yd_lake_plan_r r on s.loanacno = r.loanacno and s.term = r.term left join lake.yd_lake_plan_rd d on s.loanacno = d.loanacno and s.term = d.term left join lake.yd_lake_loan l on s.loanacno = l.loanacno where l.orgcode = '01G0' limit 10;
Squirrel client:
CLIENT 13-CHUNK 10 ROWS 1360 BYTES SERIA 943718400 4442183 1606324866213
SERVER FILTER BY FIRST KEY ONLY 943718400 4442183 1606324866213
SERVER 10 ROW LIMIT 943718400 4442183 1606324866213
CLIENT 10 ROW LIMIT 943718400 4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 0 943718400 4442183 1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314 943718400 4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400 4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 1 943718400 4442183 1606324866213
CLIENT 6-CHUNK 2496609 ROWS 3145 943718400 4442183 1606324866213
SERVER FILTER BY FIRST KEY O 943718400 4442183 1606324866213
PARALLEL LEFT-JOIN TABLE 2 943718400 4442183 1606324866213
CLIENT 24-CHUNK 50558 ROWS 31457 943718400 4442183 1606324866213
AFTER-JOIN SERVER FILTER BY L.ORGCOD 943718400 4442183 1606324866213
JOIN-SCANNER 10 ROW LIMIT 943718400 4442183 1606324866213
shell sqlline:
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
SORT-MERGE-JOIN (LEFT) TABLES 1258291200 6755218 1606324866213
CLIENT 13-CHUNK 2313035 ROWS 314572800 BYTES PARALLEL 13-WAY FULL SCAN OVER LAKE:YD_LAKE_PLAN_S_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 12-CHUNK 1895016 ROWS 314572800 BYTES PARALLEL 12-WAY FULL SCAN OVER LAKE:YD_LAKE_PLAN_R_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 6-CHUNK 2496609 ROWS 314572800 BYTES PARALLEL 6-WAY FULL SCAN OVER LAKE:YD_LAKE_PLAN_RD_INDEX 1258291200 6755218 1606324866213
SERVER FILTER BY FIRST KEY ONLY 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
AND 1258291200 6755218 1606324866213
CLIENT 24-CHUNK 50558 ROWS 314572800 BYTES PARALLEL 24-WAY FULL SCAN OVER LAKE:YD_LAKE_LOAN 1258291200 6755218 1606324866213
CLIENT MERGE SORT 1258291200 6755218 1606324866213
CLIENT FILTER BY L.ORGCODE = '01G0' 1258291200 6755218 1606324866213
CLIENT 10 ROW LIMIT 1258291200 6755218 1606324866213
Squirrel used query server connection.I guess the forced index was unexpectedly escaped during the serialization pass, causing the query statement to become a normal query.