The following table highlights SQL hints based on Sybase hints optimization.
Category | Hint | Notes |
---|---|---|
Logical | distinct | No explicit implementation. |
Logical | group | No explicit implementation. |
Logical | g_join | No explicit implementation. |
Logical | 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. |
Logical | 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. |
Logical | join | No explicit implementation. |
Logical | nl_join | Not applicable for: select statements with group by clause and having clause or group by clause and order by clause. |
Logical | m_join | Not applicable for: select statements with group by clause and having clause or group by clause and order by clause. |
Logical | h_join | Not applicable for: select statements with group by clause and having clause or group by clause and order by clause. |
Logical | union | No explicit implementation. |
Logical | scan | No explicit implementation. |
Logical | scalar_agg | Only used in combination with other operators. It does not change the execution plan itself. |
Logical | sequence | Is a keyword that will be used in the implementation of scalar_agg operator. |
Logical | hints | We don’t support a combination of hints. |
Logical | prop | Uses a set of pre-defined values. |
Logical | table | Used only in combination with other operators, when referring tables from subqueries. |
Logical | work_t | This operator is applicable only together with store operator. |
Logical | in | Used only in combination with other operators, when referring tables from subqueries. |
Logical | subq | Used only in combination with other operators, when referring tables from subqueries. |
Physical | distinct_sorted | Only for SELECT statements containing DISTINCT, and only for tables. |
Physical | distinct_sorting | Only for SELECT statements containing DISTINCT, and only for tables. |
Physical | distinct_hashing | Only for SELECT statements containing DISTINCT, and only for tables. |
Physical | group_sorted | Only for SELECT statements (not working for views) with no having and no order by clause. |
Physical | group_hashing | Only for SELECT statements (not working for views) with no having and no order by clause. |
Physical | group_interting | Not implemented. |
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. |
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. |
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. |
Physical | i_scan | Applied 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. |
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:
On Sybase 15 not applied for tables in sub selects if:
|
Physical | m_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. |
Physical | store | -- |
Physical | store_index | -- |
Physical | sort | -- |
Physical | xchg | -- |