Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to write nary-nl join in plan?

4 posts in General Discussion Last posting was on 2011-11-22 06:26:35.0Z
Eisen Posted on 2011-11-18 06:44:06.0Z
Sender: 336e.4ec5f43e.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: How to write nary-nl join in plan?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec5feb6.357f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Nov 2011 22:44:06 -0800
X-Trace: forums-1-dub 1321598646 10.22.241.41 (17 Nov 2011 22:44:06 -0800)
X-Original-Trace: 17 Nov 2011 22:44:06 -0800, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30698
Article PK: 73589

Dear all
Usually we know we can write "m_join" for "merge join",
"nl_join" for "nested-loop join" and "h_join" for "hash
join" in the query plan, but what's for nary nested-loop
join? or just simply --
plan"(nl_join
(t_scan t1)
(i_scan idx2 t2)
(i_scan idx3 t3)
(t_scan t4)
)
"?
Thanks in advance for your help.

Best Regards
Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-11-18 13:44:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to write nary-nl join in plan?
References: <4ec5feb6.357f.1681692777@sybase.com>
In-Reply-To: <4ec5feb6.357f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec66152$1@forums-1-dub>
Date: 18 Nov 2011 05:44:50 -0800
X-Trace: forums-1-dub 1321623890 10.22.241.152 (18 Nov 2011 05:44:50 -0800)
X-Original-Trace: 18 Nov 2011 05:44:50 -0800, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30699
Article PK: 73590

What little documentation there is on AQPs is next to useless, as you've probably figured out by now.

If you want to work with AQPs you pretty much need to self-teach yourself, so a few basic suggestions to help you on
your way ...

1 - once you have a potential AQP (like below), try running your query with 'set showplan on'/'set noexec on' enabled to
see if you get the desired query plan

2 - find a sample query that generates the desired query plan, wrap it in a 'set plan dump [on|off]', run the sample
query, and pull the AQP from sysquerymetrics; use this AQP as a template for writing the AQP for your original query

3 - find a sample query that generates the desired query plan, wrap it in a 'set option show_abstract_plan [on|off]',
run the sample query and use the AQP dumped to stdout as a template for your original query

On 11/18/2011 01:44, Eisen wrote:
> Dear all
> Usually we know we can write "m_join" for "merge join",
> "nl_join" for "nested-loop join" and "h_join" for "hash
> join" in the query plan, but what's for nary nested-loop
> join? or just simply --
> plan"(nl_join
> (t_scan t1)
> (i_scan idx2 t2)
> (i_scan idx3 t3)
> (t_scan t4)
> )
> "?
> Thanks in advance for your help.
>
> Best Regards
> Eisen


Rob V Posted on 2011-11-18 19:06:36.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to write nary-nl join in plan?
References: <4ec5feb6.357f.1681692777@sybase.com>
In-Reply-To: <4ec5feb6.357f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec6acbc$1@forums-1-dub>
Date: 18 Nov 2011 11:06:36 -0800
X-Trace: forums-1-dub 1321643196 10.22.241.152 (18 Nov 2011 11:06:36 -0800)
X-Original-Trace: 18 Nov 2011 11:06:36 -0800, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30700
Article PK: 73592


On 18-Nov-2011 07:44, Eisen wrote:
> Dear all
> Usually we know we can write "m_join" for "merge join",
> "nl_join" for "nested-loop join" and "h_join" for "hash
> join" in the query plan, but what's for nary nested-loop
> join? or just simply --
> plan"(nl_join
> (t_scan t1)
> (i_scan idx2 t2)
> (i_scan idx3 t3)
> (t_scan t4)
> )
> "?
> Thanks in advance for your help.
>
> Best Regards
> Eisen

You cannot. The N-ary NL join is a run-time optimization when an NL
join between 3 or more tables occurs. As such, there is no abstract plan
notation for it since it is not an optimizer-generated plan aspect, but
an execution-time optimization. So showplan will always show "N-ary
NLjoin" when 3 or more tables are involved in a join.
The only thing you can do is switch off the run-time optimization (set
nary_nl_join off), but the query plan still show an N-ary NLJ.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Eisen Posted on 2011-11-22 06:26:35.0Z
Sender: 52b2.4ecb3f11.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: How to write nary-nl join in plan?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ecb409b.534c.1681692777@sybase.com>
References: <4ec6acbc$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Nov 2011 22:26:35 -0800
X-Trace: forums-1-dub 1321943195 10.22.241.41 (21 Nov 2011 22:26:35 -0800)
X-Original-Trace: 21 Nov 2011 22:26:35 -0800, 10.22.241.41
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30711
Article PK: 73603

Hi Rob and Mark
Thanks a lot for your help. Followed by Mark's
instruction, I get the AQP by "set plan dump on" -- yes,
same as Rob said -- the nray-nl join are multiple nl_joins.
So I think if I can put it into the plan"" statement and
configured it with a high paralled degree would make it nray
join? Thanks

Best Regards
Eisen

> On 18-Nov-2011 07:44, Eisen wrote:
> > Dear all
> > Usually we know we can write "m_join" for "merge
> > join", "nl_join" for "nested-loop join" and "h_join" for
> > "hash join" in the query plan, but what's for nary
> > nested-loop join? or just simply --
> > plan"(nl_join
> > (t_scan t1)
> > (i_scan idx2 t2)
> > (i_scan idx3 t3)
> > (t_scan t4)
> > )
> > "?
> > Thanks in advance for your help.
> >
> > Best Regards
> > Eisen
>
> You cannot. The N-ary NL join is a run-time optimization
> when an NL join between 3 or more tables occurs. As such,
> there is no abstract plan notation for it since it is not
> an optimizer-generated plan aspect, but an execution-time
> optimization. So showplan will always show "N-ary NLjoin"
> when 3 or more tables are involved in a join. The only
> thing you can do is switch off the run-time optimization
> (set nary_nl_join off), but the query plan still show an
> N-ary NLJ.
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>