Why does ADB Adapter perform full table scan on Oracle tables when the where clause of the queries have fields of NUMBER data type with their corresponding AE Type as r8?
Article ID: KB0091422
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks Plug-in for Database | - |
| Not Applicable | - |
Description
Resolution:
Datadirect 5.3 driver sends floating point data (eg. r8 ae type) in Oracle 10g's new BINARY_DOUBLE format, if the column is of NUMBER type. Due to a limitation in the Oracle 10g query optimizer it does not use an index when a BINARY_DOUBLE value is sent for an indexed NUMBER column. This will result in a full table scan and degraded performance.
To address this Oracle limitation, you could implement one of the following alternatives.
1. Set the connection property FloatingPointParameters=1 in the data source configuration. This will direct the driver to always send floating point values as NUMBER.
On Unix platforms, this can be done by adding the following parameter value to the ODBC DSN configuration in the odbc.ini file which is available at <TIB Install>/adapter/adadb/<version>/odbc/.
FloatingPointParameters=1
On Windows platforms, to add this parameter value to the ODBC DSN, open Windows Registry Editor and select the key 'HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<ODBC DSN Name>'. Right-click and select 'New > String Value'. Provide the name of the new string parameter as FloatingPointParameters. Double click on this parameter and provide the value as 1.
Note 1: On 64-bit Windows platforms the above key would be 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\<ODBC DSN Name>'.
Note 2: If you are using Oracle Client driver and experience the same problem, please check the option of 'Bind NUMBER as FLOAT' that is provided in the ODBC DSN options under Workaround tab.
Copied below is the information that we have received from DataDirect in this regard.
2. In the Adapter ae schemas, if the ae type of the primary keys in the parent table or join keys in the child table is r8, change it to string. Thus the driver will handover the number as string to database and the database will by default convert it to number and there by using the index to scan the table.
3. Alternatively, you can add an additional functional index for the key columns of the parent table and join columns of the child tables (for eg. CREATE UNIQUE INDEX <index name> ON <child table name> to_binary_double(join col1), to_binary_double(join col2), (join col3),...). This will ensure that the even with ae type as r8, the table scan will be index range.
Note:
This problem will not faced when the Adapter is configured using ADB 6.0 in Eclipse UI (i.e. using TIBCO Business Studio for designing ActiveMatrix Projects), wherein the NUMBER data type is defaulted to STRING ae type. However if necessary, the user can modify the ae type to r8 by deselecting the option 'convert to string'.
Datadirect 5.3 driver sends floating point data (eg. r8 ae type) in Oracle 10g's new BINARY_DOUBLE format, if the column is of NUMBER type. Due to a limitation in the Oracle 10g query optimizer it does not use an index when a BINARY_DOUBLE value is sent for an indexed NUMBER column. This will result in a full table scan and degraded performance.
To address this Oracle limitation, you could implement one of the following alternatives.
1. Set the connection property FloatingPointParameters=1 in the data source configuration. This will direct the driver to always send floating point values as NUMBER.
On Unix platforms, this can be done by adding the following parameter value to the ODBC DSN configuration in the odbc.ini file which is available at <TIB Install>/adapter/adadb/<version>/odbc/.
FloatingPointParameters=1
On Windows platforms, to add this parameter value to the ODBC DSN, open Windows Registry Editor and select the key 'HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<ODBC DSN Name>'. Right-click and select 'New > String Value'. Provide the name of the new string parameter as FloatingPointParameters. Double click on this parameter and provide the value as 1.
Note 1: On 64-bit Windows platforms the above key would be 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\<ODBC DSN Name>'.
Note 2: If you are using Oracle Client driver and experience the same problem, please check the option of 'Bind NUMBER as FLOAT' that is provided in the ODBC DSN options under Workaround tab.
Copied below is the information that we have received from DataDirect in this regard.
2. In the Adapter ae schemas, if the ae type of the primary keys in the parent table or join keys in the child table is r8, change it to string. Thus the driver will handover the number as string to database and the database will by default convert it to number and there by using the index to scan the table.
3. Alternatively, you can add an additional functional index for the key columns of the parent table and join columns of the child tables (for eg. CREATE UNIQUE INDEX <index name> ON <child table name> to_binary_double(join col1), to_binary_double(join col2), (join col3),...). This will ensure that the even with ae type as r8, the table scan will be index range.
Note:
This problem will not faced when the Adapter is configured using ADB 6.0 in Eclipse UI (i.e. using TIBCO Business Studio for designing ActiveMatrix Projects), wherein the NUMBER data type is defaulted to STRING ae type. However if necessary, the user can modify the ae type to r8 by deselecting the option 'convert to string'.
Issue/Introduction
Why does ADB Adapter perform full table scan on Oracle tables when the where clause of the queries have fields of NUMBER data type with their corresponding AE Type as r8?
Feedback
Yes
No
Powered by
