Custom Search : Technical Guides Sybase Oracle UNIX Javascript

Technical Guides

Of Interest

Be responsible for your future
Enter the USA legally!

Visa, Green Card, Citizenship, Passport
Consultation Services

Sybase » T-SQL » Performance » Joins

ASE 15 Hints: Query Plan Optimization


ASE 15 Set Plan Commands

These commands allow you to set an optimization goal which fits your database environment.

DSS / Reporting Environments may benefit by giving the optimizer more time to compile a query plan. Sample command

-- set time to 999 ms

set plan opttimeoutlimit 999

Using the "hash" join operator introduced in ASE 15

Hash Joins have been shown to improve queries performance by as much as 500%

This example illustrates a forced hash join.

select t1.invoice_id,, t1.status_cd, t1.fiscal_qtr, t1.margin
from invoice_master t1, client_master d
where d.region_id = 2001
and t1.invoice_id = d.invoice_id
and t1.fiscal_qtr between 20031 and 20044
order by t1.invoice_id, t1.fiscal_qtr
plan " (h_join (scan t1) (scan d))"

Join Queries: Choosing an optimization goal

--Examples: session level

set plan optgoal allrows_mix
set plan optgoal allrows_oltp
set plan optgoal allrows_dss

--Example: server level

sp_configure "optimization goal", 0, "allrows_oltp"

Optimization Goal Details

allrows_oltp [nested loop join]
Nested-loop joins provide efficient access when tables are indexed on join columns.

allrows_mix [merge joins + allrows_oltp]
A merge join can use multiple worker processes to perform:. The scan that selects rows into a worktable, note a merge join may requires a sort.

allrows_dss [hash joins + allrows_mix]
The hash join algorithm builds an in-memory hash table of the smaller of its targets.

Sybase : Related Topics

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site

Get the latest Rocket99 news and tech tips via

Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site

Copyright © 2019 Stoltenbar Inc All Rights Reserved.