You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "wangMu (Jira)" <ji...@apache.org> on 2020/11/26 03:01:00 UTC
[jira] [Updated] (PHOENIX-6235) Using the Query Server
automatically filters the forced index
[ https://issues.apache.org/jira/browse/PHOENIX-6235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
wangMu updated PHOENIX-6235:
----------------------------
Description:
I executed the same statement in different ways and got different results
{code:java}
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;
{code}
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
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.
was:
I executed the same statement in different ways and got different results
{code:java}
//代码占位符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;
{code}
!file:///C:/Users/20012634/AppData/Local/Temp/%E4%BC%81%E4%B8%9A%E5%BE%AE%E4%BF%A1%E6%88%AA%E5%9B%BE_16063589193209.png!
!file:///C:/Users/20012634/AppData/Local/Temp/%E4%BC%81%E4%B8%9A%E5%BE%AE%E4%BF%A1%E6%88%AA%E5%9B%BE_16063589516721.png!
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.
> Using the Query Server automatically filters the forced index
> -------------------------------------------------------------
>
> Key: PHOENIX-6235
> URL: https://issues.apache.org/jira/browse/PHOENIX-6235
> Project: Phoenix
> Issue Type: Bug
> Components: queryserver
> Environment: SQuirreL SQL Client 4.0.0
> Phoenix 5.0
> sqlline version 1.2.0
> Reporter: wangMu
> Priority: Major
>
>
> I executed the same statement in different ways and got different results
>
> {code:java}
> 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;
> {code}
> 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
>
>
>
>
>
> 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)