 Library |
 Product |
|
This section describes known problems and restrictions for the SQL
interface for Version 7.0.
2.2.1 Behavior of Journaling Using IMPORT
The IMPORT statement does not let you restore original journal settings. Because IMPORT disables journaling, FAST COMMIT is disabled. Thus, the resulting database is not in its original state.
Oracle Rdb does not let you enable after-image journaling with the CREATE DATABASE statement. Because the IMPORT statement shares a common code path with the CREATE DATABASE statement, the after-image journal attributes cannot be imported. Therefore, the after-image journal attributes are disabled after IMPORT, inherently implying that FAST COMMIT is also disabled.
This restriction will be investigated for a future release.
You can use the following workaround:
Before exporting the database, use the following RMU Extract command to generate a script of the after-image journal definition. After the database is exported and imported, run the script to re-create the original after-image journal settings:
$ RMU/EXTRACT/ITEM=ALTER_DATABASE/OUTPUT=ADD_AIJ.SQL yourdatabase
You cannot alter a storage map that is vertically partitioned; you cannot modify a storage map that is not partitioned vertically to one that is partitioned vertically.
The following example shows the error message that Oracle Rdb returns:
SQL> ALTER STORAGE MAP T_MAP ENABLE COMPRESSION; %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-WISH_LIST, feature not implemented yet -RDMS-E-VRPINVALID, invalid operation for storage map "T_MAP"
This restriction may be lifted in a future version of Oracle Rdb.
Currently, the recommended method of altering the storage map is to
unload and load the table.
2.2.3 SQL Does Not Display Storage Map Definition After Cascading Delete of Storage Area
When you drop a storage area using the CASCADE keyword and that storage area is not the only area to which the storeage map refers, the SHOW STORAGE MAP statement no longer shows the placement definition for that storage map.
The following example demonstrates this restriction:
SQL> SHOW STORAGE MAP DEGREES_MAP1
DEGREES_MAP1
For Table: DEGREES1
Compression is: ENABLED
Partitioning is: NOT UPDATABLE
Store clause: STORE USING (EMPLOYEE_ID)
IN DEG_AREA WITH LIMIT OF ('00250')
OTHERWISE IN DEG_AREA2
SQL> DISCONNECT DEFAULT;
SQL> -- Drop the storage area, using the CASCADE keyword.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> DROP STORAGE AREA DEG_AREA CASCADE;
SQL> --
SQL> -- Display the storage map definition.
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> SHOW STORAGE MAP DEGREES_MAP1
DEGREES_MAP1
For Table: DEGREES1
Compression is: ENABLED
Partitioning is: NOT UPDATABLE
SQL>
The other storage area, DEG_AREA2, still exists, even though the SHOW STORAGE MAP statement does not display it.
A workaround is to use the RMU Extract command with the
Items=Storage_Map qualifier to see the mapping.
2.2.4 ARITH_EXCEPT or Incorrect Results Using LIKE IGNORE CASE
When you use LIKE...IGNORE CASE, programs linked underOracle Rdb V4.2 and V5.1, but run under higher versions of Oracle Rdb, may result in incorrect results or %RDB-E-ARITH_EXCEPT exceptions.
To work around the problem, avoid using IGNORE CASE with LIKE or
recompile and relink under a higher version (V6.0 or higher.)
2.2.5 Different Methods of Limiting Returned Rows From Queries
You can establish the query governor for rows returned from a query by using either the SQL SET QUERY LIMIT statement or a logical name or configuration parameter. This note describes the differences between the two mechanism.
$ DEFINE RDMS$BIND_QG_REC_LIMIT 10 $ SQL$ SQL> ATTACH 'FILENAME MF_PERSONNEL'; SQL> SELECT EMPLOYEE_ID FROM EMPLOYEES; %RDB-F-EXQUOTA, Oracle Rdb runtime quota exceeded -RDMS-E-MAXRECLIM, query governor maximum limit of rows has been reached
SQL> ATTACH 'FILENAME MF_PERSONNEL'; SQL> SET QUERY LIMIT ROWS 10; SQL> SELECT EMPLOYEE_ID FROM EMPLOYEES; EMPLOYEE_ID 00164 00165 . . . 00173 %RDB-E-EXQUOTA, Oracle Rdb runtime quota exceeded -RDMS-E-MAXRECLIM, query governor maximum limit of rows has been reached
Keep the differences in mind when limiting returned rows using the
logical name RDMS$BIND_QG_REC_LIMIT or the configuration parameter
RDB_BIND_QG_REC_LIMIT. They may limit more queries than are obvious.
This is important when using 4GL tools, the SQL precompiler, the SQL
module processor, and other interfaces that read the Oracle Rdb
system tables as part of query processing.
2.2.6 Suggestions for Optimal Usage of SHARED DATA DEFINITION Clause for Parallel Index Creation
The CREATE INDEX process involves the following steps:
Step 6 is the point of conflict with other index definers because the system table and indexes are locked like any other updated table.
Multiple users can create indexes on the same table by using the RESERVING table_name FOR SHARED DATA DEFINITION clause of the SET TRANSACTION statement. For optimal usage of this capability, Oracle Rdb suggests the following guidelines:
The following table displays the elapsed time benefit when creating multiple indexes in parallel with the SHARED DATA DEFINITION clause. The table shows the elapsed time for ten parallel process index creations (Index1, Index2,...Index10) and one process with ten sequential index creations (All10). In this example, global buffers are enabled and the number of buffers is 500. The longest time for a parallel index creation is Index7 with an elapsed time of 00:02:34.64, compared to creating ten indexes sequentially with an elapsed time of 00:03:26.66. The longest single parallel create index elapsed time is shorter than the elapsed time of creating all ten of the indexes serially.
| Index Create Job | Elapsed Time |
|---|---|
| Index1 | 00:02:22.50 |
| Index2 | 00:01:57.94 |
| Index3 | 00:02:06.27 |
| Index4 | 00:01:34.53 |
| Index5 | 00:01:51.96 |
| Index6 | 00:01:27.57 |
| Index7 | 00:02:34.64 |
| Index8 | 00:01:40.56 |
| Index9 | 00:01:34.43 |
| Index10 | 00:01:47.44 |
| All10 | 00:03:26.66 |
During concurrent index creation, Oracle Rdb validates only the first 27 bytes of the index name. Therefore, these bytes must be unique. After the index creation is complete, Oracle Rdb uses the full index name. This restriction happens only when multiple index definitions are in progress.
To permit concurrent index definitions, Oracle Rdb takes out a special lock on the provided index name. The lock manager allows 31 bytes for lock names, however Oracle Rdb needs 4 bytes for database context, thus leaving only 27 bytes to keep the index names unique. Therefore, index names must be unique to 27 bytes when you create indexes concurrently.
The following example demonstrates this problem:
Session 1: SQL> CREATE INDEX EMP_DAILY_SALES_SUMRY_SORTED_2 cont> ON EMPLOYEES (ADDRESS_DATA_2); Session 2: SQL> CREATE INDEX EMP_DAILY_SALES_SUMRY_SORTED_1 cont> ON EMPLOYEES (ADDRESS_DATA_1); %SQL-F-IND_EXISTS, Index EMP_DAILY_SALES_SUMRY_SORTED_1 already exists in this database or schema
When calling a stored routine, you must not use the same routine to calculate argument values by a stored function. For example, if the routine being called is also called by a stored function during the calculation of an argument value, passed arguments to the routine may be incorrect.
The following example shows a stored procedure P being called during the calculation of the arguments for another invocation of the stored procedure P:
SQL> create module M cont> lang SQL cont> cont> procedure P (in :a integer, in :b integer, out :c integer); cont> begin cont> set :c = :a + :b; cont> end; cont> cont> function F () returns integer cont> comment is 'expect F to always return 2'; cont> begin cont> declare :b integer; cont> call P (1, 1, :b); cont> trace 'returning ', :b; cont> return :b; cont> end; cont> end module; SQL> SQL> set flags 'TRACE'; SQL> begin cont> declare :cc integer; cont> call P (2, F(), :cc); cont> trace 'Expected 4, got ', :cc; cont> end; ~Xt: returning 2 ~Xt: Expected 4, got 3
The result as shown above is incorrect. The routine argument values are written to the called routine's parameter area before complex expression values are calculated. These calculations may (as in the example) overwrite previously copied data.
The workaround is to assign the argument expression (in this example calling the stored function F) to a temporary variable and pass this variable as the input for the routine. The following example shows the workaround:
SQL> begin cont> declare :bb, :cc integer; cont> set :bb = F(); cont> call P (2, :bb, :cc); cont> trace 'Expected 4, got ', :cc; cont> end; ~Xt: returning 2 ~Xt: Expected 4, got 4
This problem will be corrected in a future version of Oracle Rdb.
2.2.9 Incorrect Processing of Subquery When Nested in FOR Cursor Loop
A subquery may return incorrect results when it appears in a SET statement nested within a FOR cursor loop and this subquery refers to local variables initialized inside the FOR cursor loop.
This problem is due to an optimization which pulls the subquery evaluation into the FOR cursor loop's own query, thereby evaluating it before the local variables (or parameters) have been initialized.
The following example shows the problem:
SQL> set flags 'TRACE'; SQL> SQL> begin cont> declare :id char(5); cont> declare :sal integer(2); cont> cont> for :emp as cont> select last_name, employee_id cont> from employees cont> where employee_id = '00164' cont> do cont> set :id = :emp.employee_id; cont> set :sal = (select salary_amount cont> from salary_history cont> where employee_id = :id cont> and salary_end is null); cont> trace 'Employee: ', :id, ', Salary: ', :sal; cont> end for; cont> end; ~Xt: Employee: 00164, Salary: 0.00
The salary should not be zero. This incorrect value is returned because the subquery requires the local variable ID, which is assigned a value within the FOR loop prior to the subquery. However, this assignment to ID is performed after the subquery has been evaluated.
A workaround is to reference the FOR loop columns directly using the cursors handle, rather than taking copies before the subquery is executed.
SQL> begin cont> declare :id char(5); cont> declare :sal integer(2); cont> cont> for :emp as cont> select last_name, employee_id cont> from employees cont> where employee_id = '00164' cont> do cont> set :id = :emp.employee_id; cont> set :sal = (select salary_amount cont> from salary_history cont> where employee_id = :emp.employee_id cont> and salary_end is null); cont> trace 'Employee: ', :id, ', Salary: ', :sal; cont> end for; cont> end; ~Xt: Employee: 00164, Salary: 51712.00
The correct result is returned when you use the FOR loop handle and a direct column reference.
This problem will be corrected in a future version of Oracle Rdb.
2.2.10 Nested Correlated Subquery Outer References Incorrect
Outer references from aggregation subqueries contained within nested queries could receive incorrect values, causing the overall query to return incorrect results. The general symptom for an outer query that returned rows 1 to n was that the inner aggregation query would operate with the nth - 1 row data (usually NULL for row 1) when it should have been using the nth row data.
This problem has existed in various forms for all previous versions of Oracle Rdb, but only appears in V6.1 and later when the inner of the nested queries contains an UPDATE statement.
The following example demonstrates the problem:
SQL> attach 'filename shipping';
SQL> select * from manifest where voyage_num = 4904 or
cont> voyage_num = 4909;
VOYAGE_NUM EXP_NUM MATERIAL TONNAGE
4904 311 CEDAR 1200
4904 311 FIR 690
4909 291 IRON ORE 3000
4909 350 BAUXITE 1100
4909 350 COPPER 1200
4909 355 MANGANESE 550
4909 355 TIN 500
7 rows selected
SQL> begin
cont> for :a as each row of
cont> select * from voyage v where v.ship_name = 'SANDRA C.' or
cont> v.ship_name = 'DAFFODIL' do
cont> for :b as each row of table cursor modcur1 for
cont> select * from manifest m where m.voyage_num = :a.voyage_num do
cont> update manifest
cont> set tonnage = (select (avg (m1.exp_num) *3) from manifest m1
cont> where m1.voyage_num = :a.voyage_num)
cont> where current of modcur1;
cont> end for;
cont> end for;
cont> end;
SQL> select * from manifest where voyage_num = 4904 or
cont> voyage_num = 4909;
VOYAGE_NUM EXP_NUM MATERIAL TONNAGE
4904 311 CEDAR NULL
4904 311 FIR NULL
4909 291 IRON ORE 933
4909 350 BAUXITE 933
4909 350 COPPER 933
4909 355 MANGANESE 933
4909 355 TIN 933
7 rows selected
The correct value for TONNAGE on both rows for VOYAGE_NUM 4904 (outer query row 1) is: AVG (311 + 311) *3 = 933. However, Oracle Rdb calculates it as: AVG (NULL + NULL) *3 = NULL. In addition, the TONNAGE value for VOYAGE_NUM 4909 (outer query row 2) is actually the TONNAGE value for outer query row 1.
A workaround is to declare a variable of the same type as the outer reference data item, assign the outer reference data into the variable before the inner query that contains the correlated aggregation subquery, and reference the variable in the aggregation subquery. Keep in mind the restriction on the use of local variables in FOR cursor loops described by Section 2.2.9.
For example:
SQL> declare :vn integer;
SQL> begin
cont> for :a as each row of
cont> select * from voyage v where v.ship_name = 'SANDRA C.' do
cont> set :vn = :a.voyage_num;
cont> for :b as each row of table cursor modcur1 for
cont> select * from manifest m where m.voyage_num = :a.voyage_num do
cont> update manifest
cont> set tonnage = (select (avg (m1.exp_num) *3) from manifest m1
cont> where m1.voyage_num = :vn)
cont> where current of modcur1;
cont> end for;
cont> end for;
cont> end;
SQL> select * from manifest where voyage_num = 4904;
VOYAGE_NUM EXP_NUM MATERIAL TONNAGE
4904 311 CEDAR 933
4904 311 FIR 933
This problem will be corrected in a future release of Oracle Rdb.
2.2.11 Additional Usage Notes for Holdable Cursors
If your applications use holdable cursors, be aware that after a COMMIT or ROLLBACK statement is executed, the result set selected by the cursor may not remain stable. That is, rows may be inserted, updated, and deleted by other users because no locks are held on the rows selected by the holdable cursor after a commit or rollback occurs. Moreover, depending on the access strategy, rows not yet fetched may change before Oracle Rdb actually fetches them.
As a result, you may see the following anomalies when using holdable cursors in a concurrent user environment:
Holdable cursors are a very powerful feature for read-only or predominantly read-only environments. However, in concurrent update environments, the instability of the cursor may not be acceptable. The stability of holdable cursors for update environments will be addressed in future versions of Oracle Rdb.
You can define the logical name RDMS$BIND_HOLD_CURSOR_SNAP or
configuration parameter RDB_BIND_HOLD_CURSOR_SNAP to the value 1 to
force all hold cursors to fetch the result set into a cached data area.
(The cached data area appears as a "Temporary Relation" in
the optimizer strategy displayed by the SET FLAGS 'STRATEGY' statement
or the RDMS$DEBUG_FLAGS "S" flag.) This logical name or configuration
parameter helps to stabilize the cursor to some degree.
2.2.12 Compiling Programs Containing
DISCONNECT DEFAULT
If your application programs use the DISCONNECT DEFAULT or FINISH statement, you may need to compile them using the SQL precompiler switch -s "conn" or the SQL module switch -conn. <>
|
Copyright ©1996, Oracle Corporation All rights reserved.