The following table highlights SQL hints based on Sybase hints optimization.

CategoryHintNotes
LogicaldistinctNo explicit implementation.
LogicalgroupNo explicit implementation.
Logicalg_joinNo explicit implementation.
Logicalnl_g_joinNot applicable for: statements with chained queries; select statements with group by clause and having clause or group by clause and order by clause.
Logicalm_g_joinNot applicable for: statements with chained queries; select statements with group by clause and having clause or group by clause and order by clause.
LogicaljoinNo explicit implementation.
Logicalnl_join

Not applicable for: select statements with group by clause and having clause or group by clause and order by clause.

Logicalm_joinNot applicable for: select statements with group by clause and having clause or group by clause and order by clause.
Logicalh_join

Not applicable for: select statements with group by clause and having clause or group by clause and order by clause.

LogicalunionNo explicit implementation.
LogicalscanNo explicit implementation.
Logicalscalar_aggOnly used in combination with other operators. It does not change the execution plan itself.
LogicalsequenceIs a keyword that will be used in the implementation of scalar_agg operator.
LogicalhintsWe don’t support a combination of hints.
Logicalprop

Uses a set of pre-defined values.

Logicaltable

Used only in combination with other operators, when referring tables from subqueries.

Logicalwork_t

This operator is applicable only together with store operator.

LogicalinUsed only in combination with other operators, when referring tables from subqueries.
LogicalsubqUsed only in combination with other operators, when referring tables from subqueries.
Physicaldistinct_sorted

Only for SELECT statements containing DISTINCT, and only for tables.

Physicaldistinct_sorting

Only for SELECT statements containing DISTINCT, and only for tables.

Physicaldistinct_hashing

Only for SELECT statements containing DISTINCT, and only for tables.

Physicalgroup_sortedOnly for SELECT statements (not working for views) with no having and no order by clause.
Physicalgroup_hashingOnly for SELECT statements (not working for views) with no having and no order by clause.
Physicalgroup_intertingNot implemented.
Physicalappend_union_all

Not applicable for: UNION chained clauses, nested sub-selects in a from clause, if a group by clause is present or if scalar aggregation is present.

Physicalmerge_union_allNot applicable for: UNION ALL chained clauses, nested sub-selects in a from clause, or if a group by clause is present.
Physicalmerge_union_distinctNot applicable for: UNION ALL chained clauses, nested sub-selects in a from clause, or if a group by clause is present.
Physicalhash_union_distinctNot applicable for: UNION ALL chained clauses, nested sub-selects in a from clause, if a group by clause is present, or if scalar aggregation is present.
Physicali_scanApplied to all table references in the from clause of the main select and of the sub select statements except: 1. statement has sub-selects. 2. table references has no indexes.
Physicalt_scan

Applied to all the table references in the from clause of the main select and of the sub select statements except:

On Sybase 12.5 not applied for tables in the main query if:

  1. statement has chained queries.
  2. Sub queries have group by and having clauses; and not applied to the tables in sub selects if:
    1. has select statements in from clause of the main select.
    2. sub queries have group by and having clauses.
    3. statement has select statements in select clause.
    4. statement has parent statement and insert statement;

On Sybase 15 not applied for tables in sub selects if:

  1. has select statements in from clause of the main select.
  2. statement has chained queries.
Physicalm_scan

Applied for all tables if in the where clause there is a condition like:

table1.indexedColumn1 condition body OR

table1.indexedColumn2 condition body;

Not applied if the LIKE operator is used. For columns that belong to a primary key only the first column is considered.

Physicalstore--
Physicalstore_index--
Physicalsort--
Physicalxchg--



IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal