Hints used

From DB Optimizer
Jump to: navigation, search

Sybase Hints

CATEGORY HINT 12.5 15.0 Info Status
Logical distinct No explicit implementation no
Physical distinct_sorted Only for SELECT statements containing DISTINCT and only for tables yes
Physical distinct_sorting Only for SELECT statements containing DISTINCT and only for tables yes
Physical distinct_hashing Only for SELECT statements containing DISTINCT and only for tables yes
Logical group No explicit implementation no
Physical group_sorted Only for SELECT statements from tables (not working for views) with no having and no order by clause yes
Physical group_hashing Only for SELECT statements from tables (not working for views) with no having and no order by clause
Physical group_inserting Not implemented - The group_inserting operator describes the way that ASE 15 handles a group by statement when a table is clustered. I don't think this can be forced via the plan statement. I don't think it is worth implementing this hint even if it is possible. Harry Hellal - sybase specialist no
Logical g_join No explicit implementation no
nl_g_join Not applicable for: statements with chained queries; select statements with group by clause and having clause or group by clause and order by clause yes
m_g_join Not applicable for: statements with chained queries; select statements with group by clause and having clause or group by clause and order by clause yes
Logical join No explicit implementation no
nl_join Not applicable for: select statements with group by clause and having clause or group by clause and order by clause yes
m_join Not applicable for: select statements with group by clause and having clause or group by clause and order by clause yes
h_join Not applicable for: select statements with group by clause and having clause or group by clause and order by clause yes
Logical union No explicit implementation. no
Physical append_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. yes
Physical merge_union_all Not applicable for: UNION ALL chained clauses, nested sub-selects in a from clause or if a group by clause is present.
Physical merge_union_distinct Not applicable for: UNION ALL chained clauses, nested sub-selects in a from clause or if a group by clause is present. yes
Physical hash_union_distinct Not 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. yes
Logical scan No explicit implementation. no
Physical i_scan Applied to all the table references in the from clause of the main select and of the sub select statements except: 1. statement has sub-selects 2. table reference has no indexes. yes
Physical t_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 an insert statement; on Sybase 15 not applied for tables in sub selects : 1. has select statements in from clause of the main select 2.statement has chained queries. yes
Physical m_scan Applied for all the 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. yes
store No reasonable usages were found
Physical store_index There isn't much value in implementing this operator.
Physical sort Only for SELECT statements containing ORDER BY and only for tables
Filter nested Only used in combination with other operators. It does not change the execution plan by itself.
Physical xchg Specify on-the-fly partitioning of a derived table.
Logical scalar_agg Only used in combination with other operators. It does not change the execution plan by itself.


sequence Is a keyword that will be used in the implementation of scalar_agg operator
hints We don't support combination of hints at this stage


prop Uses a set of pre-defined values.


table Used only in combination with others operators, when referring tables from subqueries.
work_t This operator is applicable only together with store operator.
in Used only in combination with others operators, when referring tables from subqueries
subq Used only in combination with others operators, when referring tables from subqueries