Oracle® In-Memory Database Cache User's Guide Release 11.2.1 Part Number E13073-11 |
|
|
View PDF |
This chapter lists compatibility issues between TimesTen and Oracle. The list is not complete, but it indicates areas that require special attention. It includes the following topics:
Consider the following differences between TimesTen and Oracle:
TimesTen and Oracle database metadata are stored differently. See "API compatibility" for more information.
TimesTen and Oracle have different transaction isolation models. See "Transaction semantics" for more information.
TimesTen and Oracle have different connection and statement properties. For example, TimesTen does not support catalog names, scrollable cursors or updateable cursors.
Sequences are not cached and synchronized between the TimesTen database and the corresponding Oracle database. See "SQL expressions" for more information.
Side effects of Oracle triggers and stored procedures are not reflected in the TimesTen database until after an automatic or manual refresh operation.
TimesTen and Oracle transaction semantics differ as follows:
Oracle serializable transactions can fail at commit time because the transaction cannot be serialized. TimesTen uses locking to enforce serializability.
Oracle users can lock tables explicitly through SQL. This locking feature is not supported in TimesTen.
Oracle supports savepoints while TimesTen does not.
In Oracle, a transaction can be set to be read-only or read/write. This is not supported in TimesTen.
For more information about TimesTen isolation levels and transaction semantics, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.
For a complete list of the JDBC API classes and interfaces that TimesTen supports with notes on which methods have a compatibility issue, see "Key JDBC classes and interfaces" in Oracle TimesTen In-Memory Database Java Developer's Guide.
For a complete list of the ODBC API functions that TimesTen supports with notes on which functions have a compatibility issue, see "TimesTen ODBC Functions and Options" in Oracle TimesTen In-Memory Database C Developer's Guide.
For a complete list of the OCI functions for Oracle database, release 11.1.0.7, that TimesTen supports with notes on which functions have a compatibility issue, see "TimesTen Support for Oracle Call Interface" in Oracle TimesTen In-Memory Database C Developer's Guide.
This section compares TimesTen's SQL implementation with Oracle's SQL. The purpose is to provide users with a list of Oracle SQL features not supported in TimesTen or supported with different semantics.
TimesTen does not recognize some of the schema objects that are supported in Oracle. TimesTen returns a syntax error when a statement manipulates or uses these objects and passes the statement to Oracle. The unsupported objects are:
CREATE DATABASE
and DROP DATABASE
statementsTimesTen supports views and materialized views, but it cannot cache an Oracle view.
The Oracle table features that TimesTen does not support are:
ON DELETE SET NULL
Check constraints
The following Oracle data types are not supported by TimesTen:
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
UROWID
CLOB
NCLOB
BLOB
BFILE
REF
Note:
TimesTen can cacheCLOB
, NCLOB
and BLOB
data. See "Caching Oracle LOB data".
Passthrough queries that reference Oracle CLOB
, NCLOB
and BLOB
fields are supported.
The following TimesTen data types are not supported by Oracle:
TT_CHAR
TT_VARCHAR
TT_NCHAR
TT_NVARCHAR
TT_BINARY
TT_VARBINARY
TINYINT
and TT_TINYINT
TT_SMALLINT
TT_INTEGER
TT_BIGINT
TT_DECIMAL
TT_DATE
TIME
and TT_TIME
TT_TIMESTAMP
Note:
TimesTenNCHAR
and NVARCHAR2
data types are encoded as UTF-16
. Oracle NCHAR
and NVARCHAR2
data types are encoded as either UTF-16
or UTF-8
.
To cache an Oracle NCHAR
or NVARCHAR2
column, the Oracle NLS_NCHAR_CHARACTERSET
encoding must be AL16UTF16
, not AL32UTF8
.
TimesTen supports these operators and predicates that are supported by Oracle:
-
+, -, *, /
=, <, >, <=, >=, <>=
||
IS NULL
, IS NOT NULL
LIKE
(Oracle LIKE
operator ignores trailing spaces, but TimesTen does not)BETWEEN
IN
NOT IN
(list)AND
OR
+
(outer join)ANY
, SOME
ALL
(list)EXISTS
UNION
MINUS
INTERSECT
AND
operation of two bit vector expressions, TimesTen uses the ampersand character (&
) between the expressions while Oracle uses the BITAND
function with the expressions as arguments.TimesTen supports these functions that are supported by Oracle:
ABS
ADD_MONTHS
AVG
CEIL
COALESCE
CONCAT
COUNT
DECODE
EXTRACT
FLOOR
GREATEST
INSTR
LEAST
LENGTH
LPAD
LTRIM
MAX
MIN
MOD
NUMTOYMINTERVAL
NUMTODSINTERVAL
NVL
POWER
ROUND
RPAD
RTRIM
SIGN
SQRT
SUBSTR
SUM
SYS_CONTEXT
SYSDATE
TO_DATE
TO_CHAR
TO_NUMBER
TRIM
TRUNC
TimesTen and the Oracle Database interpret the literal N'\UNNNN
' differently. In TimesTen, N'\u
nnnn
'
(where nnnn
is a number) is interpreted as the national character set character with the code nnnn
. In the Oracle Database, N'\u
nnnn
'
is interpreted as 6 literal characters. The \u
is not treated as an escape. This difference causes unexpected behavior. For example, loading a cache group with a WHERE
clause that contains a literal can fail. This can also affects dynamic loading and cache grid operation. Applications should use the UNISTR
SQL function instead of literals.
TimesTen supports these expressions that are supported by Oracle:
NULL
()
CASE
expressionCAST
These TimesTen expressions are not supported by Oracle:
CURRENT_USER
GETDATE
ORA_SYSDATE
SESSION_USER
SYSTEM_USER
TT_HASH
TT_SYSDATE
TimesTen supports these subqueries that are supported by Oracle:
IN
(subquery)>,<,= ANY
(subquery)>,=,< SOME
(subquery)EXISTS
(subquery)>,=,<
(scalar subquery)WHERE
clause of DELETE
/UPDATE
FROM
clause
Note:
A nonverifiable scalar subquery is a scalar subquery whose 'single-row-result-set' property cannot be determined until execution time. TimesTen allows at most one nonverifiable scalar subquery in the entire query and the subquery cannot be specified in anOR
expression.TimesTen supports these queries that are supported by Oracle:
FOR UPDATE
ORDER BY
GROUP BY
Table alias
Column alias
Oracle supports flashback queries, which are queries against a database that is in some previous state (for example, a query on a table as of yesterday). TimesTen does not support flashback queries.
This section lists TimesTen SQL statements and built-in procedures that are not supported by Oracle. With PassThrough
=3, these statements are passed to Oracle for execution and an error is generated.
All TimesTen cache group DDL and DML statements, including CREATE CACHE GROUP
, DROP CACHE GROUP
, ALTER CACHE GROUP
, LOAD CACHE GROUP
, UNLOAD CACHE GROUP
, REFRESH CACHE GROUP
and FLUSH CACHE GROUP
.
All TimesTen replication management DDL statements, including CREATE REPLICATION
, DROP REPLICATION
, ALTER REPLICATION
, CREATE ACTIVE STANDBY PAIR
, ALTER ACTIVE STANDBY PAIR
and DROP ACTIVE STANDBY PAIR
.
FIRST
n
clause
All TimesTen built-in procedures. See "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.
TimesTen supports a subset of stored procedure constructs, functions, data types, packages and package bodies that are supported by Oracle. See Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for details.
When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle tables and choose an equivalent or compatible data type for the columns in the cache tables.
Primary and foreign key columns are distinguished from non-key columns. The data type mappings allowed for key columns in a cache table are shown in Table 11-1.
Table 11-1 Data type mappings allowed for key columns
Oracle data type | TimesTen data type |
---|---|
|
Note: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 11-2 shows the data type mappings allowed for non-key columns in a cache table.
Table 11-2 Data type mappings allowed for non-key columns
Oracle data type | TimesTen data type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: |
|
Note: |
|
|
|
|
Note: Includes |
Note: |
|
|
|
|
|
|
|
|
|
Note: 1<= |
|
Note: 1<= |
|
Note: 1<= |