Sybase 15: AQPs at query level

While tuning queries in 12.5, we turn on forceplan to force the table order and also hint indexes. Now in 15, we see that not only table order in a join but also the join method plays a role. So how do we force Sybase to use a particular join method in addition to the order we give? “Abstract Query Plan” is the way to go. For now I am only going to talk about specifying AQP to a query using the “plan” clause. There is a lot more in AQP (storing AQPs in sysqueryplans, create plan command, plan dumps, AQPs with procedures, etc).
While writing queries, we can use a plan clause at the end and provide an AQP specifying the join method, join order, indexes to be used or we can also change the optimization goal. Below is an example of a plan attached to a query.
select A.a, B.c, C.d
from A, B, C
where A.a = B.a
and B.a = C.a
and B.b = “Some text”
plan
“(nl_join
(t_scan A)
(i_scan a_indx B)
(i_scan comp_indx C)
)”
Here, we are specifying optimizer to use NLJ with table scan on A and indexes to be used on B and C. Below is another example where we just specify the goal.
select A.a, B.c, C.d
from A, B, C
where A.a = B.a
and B.a = C.a
and B.b = “Some text”
plan
“(use optgoal allrows_oltp)”
In case we want to force the table order in the above query, just set “forceplan on” in 12.5 way.
Few important points.
(a) AQPs can be partial. It is not necessary to mention complete plans. The optimizer will take the partial plan and decide the rest.
(b) If forceplan is turned on and we also mention a full plan, AQP will take the precedence.
(c) In Sybase 12.5, though we say forceplan on, we cannot control the order within a view. But with AQPs, we can mention the tables in views also. Below is an example.
(nl_join
(scan (table t2(in(view v2))))
(i_scan Key_Index t1)
(scan (table t3 (in (view v2))))
)
About the Author
The author of the blog is a cross-functional leader and part of the Core team at Tectoro Consulting, India. He currently works on multi-market multi-asset enhancements to Gloss for our client in Hyderabad. His prior expertise is in analysis, design and development for Derivatives Regulations – Dodd Frank, EMIR, MiFID, JFSA, etc across product/entity/counterparty scope, entity registration, mandatory clearing and  transaction reporting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s