QUERY HINT OPTION to optimize TIBCO Data Virtualization Server Query
Article ID: KB0082864
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0.X |
Description
QUERY HINT OPTION to optimize Data Virtualization Query
Issue/Introduction
QUERY-HINT-OPTION-to-optimize-Data Virtualization Server Query
Resolution
Please follow
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 .
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 .
Feedback
Yes
No
Powered by
