Federated column options

You can define federated column options that the query optimizer uses for developing access plans.

The column options tell the wrapper to handle the data in a column differently than it normally would handle it. The SQL complier and query optimizer use the metadata to develop better plans for accessing the data. The federated database treats the object that a nickname references as if it is a table. As a result, you can set column options for any data source object that you create a nickname for.

The ALTER NICKNAME statement can be used to add or change column options for nicknames. There are two column options:
  • NUMERIC_STRING. This column option applies to character type columns (CHAR and VARCHAR). Suppose that a data source has a collating sequence that differs from the federated database collating sequence. The federated server would not sort any columns that contain character data at the data source. It would return the data to the federated database and perform the sort locally. However, suppose that the column is a character data type and contains only numeric characters ('0','1',...,'9'). You can indicate this by assigning a value of 'Y' to the NUMERIC_STRING column option. This gives the query optimizer the option of performing the sort at the data source because numerics, even when represented as character strings, always sort the same, regardless of collating sequence. If the sort is performed remotely, you can avoid the overhead of porting the data to the federated server and performing the sort locally.
  • VARCHAR_NO_TRAILING_BLANKS. Unlike the server option with the same name, this column option can be used to identify specific Oracle columns that contain no trailing blanks. The SQL compiler pushdown analysis step will then take this information into account when checking all operations performed on columns which have this setting. Based on the VARCHAR_NO_TRAILING_BLANKS setting, the SQL compiler can generate a different but semantically equivalent form of a predicate that is used in the remote SQL statement sent to the data source. A value of 'Y' is likely to enable the use of remote indexes (if available) which can improve query performance.