Sybase ASE 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 a 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))))
)

PS: I wrote this in Jan 2011.

About the author

Vishnu Vardhan Chikoti is a co-author for the book "Hands-on Site Reliability Engineering". He is a technology leader with diverse experience in the areas of Application and Database design and development, Micro-services & Micro-frontends, DevOps, Site Reliability Engineering and Machine Learning.