QUERY HINT OPTION to optimize ADS Query
Article ID: KB0082857
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | ALL VERSIONS |
Description
QUERY HINT OPTION to optimize ADS Query
Resolution
Below mentioned query hints can be used to optimize query:
1. Select {option DISABLE_DATASOURCE_GROUPING, REWRITE_IN_SUBQUERIES_AS_JOINS, DISABLE_STATISTICS} count(*) from QUERY
2. Add {option force_order} on the first JOIN, like this: /shared/Table1
INNER {option force_order} JOIN
/shared/Table2
ON Col1= Col2
3. add {option disable_derived_table_remover} after top-level SELECT
4. add {option disable_datasource_grouping} after top-level SELECT
To push Oracle hints do the following:
1. Select {option DISABLE_DATASOURCE_GROUPING, REWRITE_IN_SUBQUERIES_AS_JOINS, DISABLE_STATISTICS} count(*) from QUERY
2. Add {option force_order} on the first JOIN, like this: /shared/Table1
INNER {option force_order} JOIN
/shared/Table2
ON Col1= Col2
3. add {option disable_derived_table_remover} after top-level SELECT
4. add {option disable_datasource_grouping} after top-level SELECT
To push Oracle hints do the following:
- Keep “Administration> Configuration>CompsiteServer->Sql Engine->Sql Language-> Keep comments “ to False.
- Set “Administration> Configuration>Data Sources -> Oracle Sources-> Push Oracle query hints” to True .
Issue/Introduction
QUERY HINT OPTION to optimize ADS Query
Feedback
Yes
No
Powered by
