RE: [外部邮件] Re: How to get vsbb_scan plan?

[Eric Owhadi <er...@esgyn.com>]

Select * from t where pk in ('a','b','c','d');
Can also lead to internally be transformed into nested join to manufactured value table if the proper CQD forbid it to result in a MDAM scan. 
I think with current release, if the in list is bellow 100, we pick MDAM, if it is above, we pick VSBB Scan with nested join. 
Hope this help,
Eric 

-----Original Message-----
From: Anoop Sharma <an...@esgyn.com> 
Sent: Wednesday, October 17, 2018 11:38 PM
To: dev@trafodion.apache.org
Subject: RE: [外部邮件] Re: How to get vsbb_scan plan?

External

you may also be able to get a vsbb scan with rowset operations on a single table.
something like:
  select * from t where a = <rowsets>
This will internally be changed to a nested join with rowset of key values being passed to the inner table being scanned.

So ultimately a nested join with multiple key preds is needed to get vsbb scan, but it doesn't always have to be a join between multiple tables.

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) <yu...@esgyn.cn>
Sent: Wednesday, October 17, 2018 7:03 PM
To: dev@trafodion.apache.org
Subject: RE: [外部邮件] Re: How to get vsbb_scan plan?

Thanks Suresh.



Best regards

Yuan

-----Original Message-----
From: Suresh Subbiah <su...@gmail.com>
Sent: Wednesday, October 17, 2018 11:04 PM
To: dev@trafodion.apache.org
Subject: [外部邮件] Re: How to get vsbb_scan plan?

Hi Yuan,

 Yes, nested join on primary key is the only case I can think of too.
We need a full key match on the predicate, there also has to be multiple requests so that the result contains more than one row, even with a full key predicate.
I think nested join on primary key is the only case.

Here is a quick example

create table tw (a int not null primary key);

>>control query shape nested_join(cut,cut) ;
--- SQL operation complete.
>>prepare s1 from select * from tw a, tw b where a.a = b.a ;
--- SQL command prepared.
>>explain options 'f' s1 ;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------
---------

3    .    4    root
1.50E+004
1    2    3    nested_join
 1.50E+004
.    .    2    trafodion_vsbb_scan             TW
1.00E+000
.    .    1    trafodion_scan                  TW
1.50E+004

--- SQL operation complete.

The method that determines what gets printed in explain is shown below.

Thank you
Suresh

const NAString HbaseAccess::getText() const { NAString op(CmpCommon::statementHeap()); NAString tname(getTableName().getText(),CmpCommon::statementHeap());
NAString sampleOpt(CmpCommon::statementHeap());
if (isSampleScan())
sampleOpt = "sample_";
if (getIndexDesc() == NULL OR getIndexDesc()->isClusteringIndex())
{
if (isSeabaseTable())
{
if (uniqueRowsetHbaseOper())
(op += "trafodion_vsbb_") += sampleOpt += "scan "; else (op += "trafodion_") += sampleOpt += "scan "; } else (op += "hbase_") += sampleOpt += "scan "; } else { if (isSeabaseTable()) (op += "trafodion_index_") += sampleOpt += "scan "; else (op += "hbase_index_") += sampleOpt += "scan "; tname = getIndexDesc()->getIndexName().getQualifiedNameAsString() + "(" + tname + ")"; } if (getReverseScan()) op += NAString("rev "); return op + tname; }

On Sat, Oct 13, 2018 at 11:23 AM Liu, Yuan (Yuan) <yu...@esgyn.cn> wrote:

> Hi trafodioneers,
>
>
>
> For scan operator, we have trafodion scan, index scan and vsbbscan.
>
> In which scenarios can we get a vsbb scan? Nested join on primary key?
>
>
>
> Best regards
>
>
>
> 刘源（Yuan）
>
> *上海易鲸捷信息技术有限公司* <http://www.esgyn.cn/>
>
> 地址：上海市浦东新区金科路2889号长泰广场A座603
>
> 手机：13671935540
>
> 邮箱：yuan.liu@esgyn.cn
>
> [image: 易鲸捷logo中文 - 副本]
>
>
>