Sybase ASE 15: New Features

Many firms are now moving their applications from Sybase 12.5 to 15. Surely developers/designers will be looking out for info on 15 in the net (like me ;-)). I thought I will jot down few points which I feel are important for a developer to understand. I am not a DBA and really cant talk on the administration side (changes to caching, systems tables, updating statistics, etc).

So, here we go.

Join Methods

Pre 15, all the queries we fire used Nested loop join. We had Merge Join but I have never seen that being used. But in 15, there are 3 join methods.

Nested Loop Join

This is the same as what we had before. NESTED LOOP JOIN, the simplest join strategy, is a binary operator with the left child forming the outer data stream and the right child forming the inner data stream.

For every row from the outer data stream, the inner data stream is opened. Opening the inner data stream effectively positions the scan on the first row that qualifies all of the searchable arguments.

The qualifying row is returned to the NESTED LOOP JOINs parent operator. Subsequent calls to the join operator continue to return qualifying rows from the inner stream.

After the last qualifying row from the inner stream is returned for the current outer row, the inner stream is closed. A call is made to get the next qualifying row from the outer stream. The values from this row provide the searchable arguments used to open and position the scan on the inner stream. This process continues until the NESTED LOOP JOINs left child returns End Of Scan.

Generally, a NESTED LOOP JOIN strategy is effective when there is a useful index available for qualifying the join predicates on the inner stream.

Merge Join

The MERGE JOIN operator is a binary operator. The left and right children are the outer and inner data streams, respectively. Both data streams must be sorted on the MERGE JOINs key values.

First, a row from the outer stream is fetched. This initializes the MERGE JOINs join key values. Then, rows from the inner stream are fetched until a row with key values that match or are greater than (less than if key column is descending) is encountered. If the join key matches, the qualifying row is passed on for additional processing, and a subsequent next call to the MERGE JOIN operator continues fetching from the currently active stream.

If the new values are greater than the current comparison key, these values are used as the new comparison join key while fetching rows from the other stream. This process continues until one of the data streams is exhausted.

Generally, the MERGE JOIN strategy is effective when a scan of the data streams requires that most of the rows must be processed, and that, if any of the input streams are large, they are already sorted on the join keys.

Below is an example with description on Merge join.

SELECT A.a, A.text FROM A, B

WHERE A.a = B.a

Lets say a in table A has values 1 to 100 and a in B has 50 to 100. When the value of 1 is compared against 50, it doesnt match. Optimizer here knows that the data is sorted and skips all checks for rows with a as 2 to 49 from table A. In NLJ, this clever logic cannot be used as data need not be sorted.

We need to be very careful when the query plan shows Merge join is being used. If the data is not already sorted, then Sybase will create work tables on the keys and sorts it. This is an additional overhead and might slow down the query.

Hash Join

The HASH JOIN operator is again, a binary operator. In Hash join, one of the table (say A) which is smaller in Size will be called Source and the other (say B) is called Probe. The values in the Probe table are hashed. Each row in A will then check the Hash during comparison. As each row in Source is picked and then compared with the Probe, the Source should be smaller among the two tables.

Hash join is used only when it looks beneficial comparing to a table scan.

Generally, the HASH JOIN strategy is good in cases where most of the rows from the source sets must be processed and there are no inherent useful orderings on the join keys or there are no interesting orderings that can be promoted to calling operators (for example, an order by clause on the join key). HASH JOINs perform particularly well if one of the data sets is small enough to be memory resident.

Note: Hash join is not useful for our application so I did not play much with this.

Each of the above mentioned join methods can be turned on/off using set commands. For example, set merge_join off to turn off Merge join. But Sybase does not recommend turning on/off with set commands. Instead, we can modify the optimizer goal (explanation of optimizer goal follows).

Optimization Goals

In Sybase 15, we have something called optimizer goals. Each of the goals have criteria within themselves (like turning on NLJ, turning off MJ, etc). The optimizer goal can be configured at a server level but can be modified at a session level and even at query level.

3 goals are supported:-

allows_oltp

This is useful for OLTP applications. Only Nested Loop Join is used.

allrows_mix

This is for a mixed environment having both OLTP and DSS queries. Both MJ and NLJ turned on.

allrows_dss

This is for DSS applications. All 3 join methods are turned on. It seems this is currently provided on an experimental basis.

Modifying the goal at a session level.

set plan optgoal allrows_oltp

Modifying it at query level (I will explain the plan clause in the later part).

SELECT A.a, A.text FROM A, B

WHERE A.a = B.a

plan

(use optgoal allrows_oltp)

Abstract Query Plans

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))))

)

GROUP BY in Sybase 15

Pre-15, when we do a group by and select the columns, the columns in the group by clause were sorted. With 15, that order is not guaranteed. So, we need to use a ORDER BY clause wherever the order is important.

Temp table names

Pre-15, max of 12 chars were taken from the name we provide and it was followed by a unique integer. But in 15, it is different. Temp table names can be upto 255 characters long. Below is a perfect example.

create table #t12345678901 (a int)

create table #t123456789012 (a int)

The above two commands when executed, succeed in 15 but fail in 12.5.

Thats all I have for now.

Source: Documents on Sybase site and on the job experience.

http://www.sybase.com.mx/files/Product_Overviews/Upgrading-to-ASE15-Migration-010708.pdf

PS: I wrote this in Sep 2010.

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.