skip to main content
Schema definition and management : Schema objects : Index catalog table OA_STATISTICS
 

Index catalog table OA_STATISTICS

The OA_STATISTICS table is stored in STAT.CSV and contains the table index statistics that the OpenAccess SDK SQL engine uses to determine the best query execution plan and to support the SQLStatistics ODBC call. The OA_STATISTICS table contains one row for every column that has an index on it and additional rows for table statistics. It is accessed from ODBC with the SQLStatistics call.
Only the columns marked with an asterisk are required by the OpenAccess SDK SQL engine. Other columns may be required by the client application. For example, Microsoft Access will give an ODBC conformance error if all required columns are not set. To enable your IP to be accessed from Microsoft Access, you need all the columns indicated with * in the following table. Set the other columns to null. Please contact us for the latest information on special needs of other applications.
Note: When a table has multiple unique indexes, the Primary Index (or the best index) should be returned first. When the OpenAccess SDK SQL engine needs the primary index of a table, it picks up the first unique index from the OA_STATISTICS. Note that in the case of Dynamic Schema, the IP should return the Primary index information first in the list of schema objects.
The OpenAccess SDK SQL engine currently uses the Primary Index exposed in OA_FKEYS for a few operations, only when a unique index is not found in OA_STATISTICS. The OA_FKEYS information is primarily used for returning Primary key information to client applications (for example, ODBC API SQLPrimaryKeys).
 
Table 25: Definition of Index catalog table OA_STATISTICS  
Column Name
Type
Len
Description
TABLE_QUALIFIER*
WVARCHAR
128
The name of the database in which the table falls. It can be used to distribute tables into physically different databases. SCHEMA is normally used. The entry here is the same you use in OA_COLUMNS and OA_TABLES.
TABLE_OWNER*
WVARCHAR
128
The owner of the table. It is normally set to system or user.
SYSTEM—the table is managed by the OpenAccess SDK SQL engine.
OAUSER—the table is managed by the IP.
In future releases, this entry will be used to control access rights to the table and will represent the actual user who owns the table. The entry here and in the OA_COLUMNS must match.
TABLE_NAME*
WVARCHAR
128
Name of the table. This is how the end user will refer to it.
NON_UNIQUE*
SMALLINT
 
Indicates whether the index prohibits duplicate values:
• TRUE (1) if the index values can be non-unique.
• FALSE (0) if the index values must be unique.
• NULL (empty) if TYPE is SQL_TABLE_STAT
INDEX_QUALIFIER
WVARCHAR
128
Name of the database to which the table belongs.
INDEX_NAME*
WVARCHAR
128
Name of the index:
NULL (empty) if TYPE is SQL_TABLE_STAT
OA_TYPE*
SMALLINT
 
Type of information being returned. Should be set to 1-3 for Microsoft Access to pick up the indexes:
• SQL_TABLE_STAT (0)—indicates a statistic for the table
• SQL_INDEX_CLUSTERED(1)— indicates a clustered index
• SQL_INDEX_HASHED(2)— indicates a hashed index
• SQL_INDEX_OTHER(3)—indicates another type of index
SEQ_IN_INDEX*
SMALLINT
 
Column sequence number in index (starting with 1)
Set to NULL if TYPE is SQL_TABLE_STAT. This value is 1 if only one column makes up an index.
COLUMN_NAME*
WVARCHAR
128
Column identifier; set to NULL if TYPE is SQL_TABLE_STAT.
OA_COLLATION
WVARCHAR
1
Collation sequence:
• A for ascending
• D for descending
• NULL if TYPE is SQL_STAT_TABLE or if no collation
OA_CARDINALITY
INTEGER
 
Cardinality of the table or index. Used by the OpenAccess SDK SQL engine to pick an optimal index if this is not a unique index.
• The number of rows in the table if TYPE is SQL_TABLE_STAT.
• The number of unique values in the index if TYPE is not SQL_TABLE_STAT; high cardinality indexes are preferred for query optimization.
• NULL if the value is not available from the data source.
OA_PAGES
INTEGER
 
Reserved for future use. The number of pages used to store the index or table.
• Number of pages for the table if TYPE is SQL_TABLE_STAT
• Number of pages for the index if TYPE is not SQL_TABLE_STAT.
• NULL if the value is not available from the data source, or if not applicable to the data source
FILTER_CONDITIONS
WVARCHAR
128
Leave blank to indicate NULL value.
To enable your IP to be accessed from Microsoft Access, you need all the columns indicated with *. Set the other columns to null.