Sybase ASE 15: AQPs at query level

Posted by Vishnu Vardhan Chikoti

Posted on 10-Aug-2018 19:31:21

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"
(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"
(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.

(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.

With an ability to conduct deep analysis, strong execution skills and an innovative mindset, he has successfully led R&D teams to build engineering solutions to improve reliability of applications. He also has deep expertise in building high volume transaction processing applications for middle & back office functions at Investment Banks using a variety of architectures.

He has been part of leadership teams in driving Site Reliability Engineering transformation and Agile transformation.

Would you like to get notifications about New Blogs?