Alters an outline definition.
1 – Environment
You can use the ALTER OUTLINE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER OUTLINE <outline-name> ----+
+--------------------------------+
+-+-+-> COMMENT IS --+-------> '<string>' ---------+--+-+->
| | +------------ / <--------------+ | |
| +-> COMPILE ---------------------------------------+ |
| +-> MOVE TO <new-schema-name> -----------------+ |
| +-> RENAME TO <new-outline-name> -----------------+ |
+-------------------------- <--------------------------+
3 – Arguments
3.1 – COMMENT IS string
The COMMENT IS clause can be used to modify the comment stored
with the query outline. The COMMENT ON statement is identical in
function to the ALTER OUTLINE . . . COMMENT IS clause.
This clause is equivalent to the COMMENT ON procedure.
3.2 – COMPILE
The COMPILE option can be applied to query outlines that have
been made invalid by DROP TABLE or DROP INDEX. If the tables and
indices have been recreated then the query outline will be made
valid again. For example, once re-validated the optimizer will
try to use that query outline.
NOTE
There is a possibility that the query outline although
marked valid will not be used because of changes in the
index definition. There is too little information stored
with the query outline to perform a complete consistency
check. If possible, queries using this outline should be run
to verify correct index and table usage.
If the query outline is currently valid then this clause is
ignored by Oracle Rdb.
3.3 – MOVE_TO
MOVE TO is valid only for multischema databases. You must be
attached explicitly or implicitly with the MULTISCHEMA IS ON
clause. The MOVE TO clause can be used to move the query outline
to a different catalog and schema. An error will be raised if
this clause is specified in a non-multischema environment.
The target catalog and schema must exist in this database.
3.4 – RENAME_TO
The RENAME TO clause can be used to change the name of the
outline. The new name must not already exist in the database.
If RENAME TO is used in a multischema database, attached with
MULTISCHEMA IS ON, then only the multischema name is modified
not the STORED NAME of the object. To change the STORED NAME of
the query outline you must attach to the database explicitly with
the MULTISCHEMA IS OFF clause (see the example below). Please
note that the STORED NAME for the query outline may have been
generated by Oracle Rdb.
NOTE
Any queries using the OPTIMIZE USING clause will also need
to be changed to reference this new outline name.
4 – Examples
Example 1: Changing the comment on a query outline
SQL> alter outline show_tables
cont> comment is 'show the tables query'
cont> / 'derived from a stored procedure';
SQL> show outline show_tables
SHOW_TABLES
Comment: show the tables query
derived from a stored procedure
Source:
-- Rdb Generated Outline : 8-FEB-2002 16:17
create outline SHOW_TABLES
id '4D5B5CC5B46C6DD21B0E1999C0EB8BF3'
mode 0
as (
query (
-- For loop
subquery (
RDB$RELATIONS 0 access path index RDB$REL_REL_NAME_NDX
)
)
)
compliance optional ;
Example 2: Using the alternate COMMENT ON syntax to change the
comment
SQL> comment on outline show_tables
cont> is 'show the tables query'
cont> / 'derived from the stored procedure'
cont> / 'SHOW_TABLES';
Example 3: Changing the name of a query outline
SQL> alter outline show_tables
cont> rename to show_the_tables;
SQL> show outline show_the_tables
SHOW_THE_TABLES
Comment: show the tables query
derived from the stored procedure
testing new COMMENT ON OUTLINE
Source:
-- Rdb Generated Outline : 8-FEB-2002 16:17
create outline SHOW_THE_TABLES
id '4D5B5CC5B46C6DD21B0E1999C0EB8BF3'
mode 0
as (
query (
-- For loop
subquery (
RDB$RELATIONS 0 access path index RDB$REL_REL_NAME_NDX
)
)
)
compliance optional ;
Example 4: This example shows setting a query outline valid after
a DROP INDEX
First, our stored procedure is executed with the STRATEGY flag
defined so we can see that it is using a query outline named MY_
OUTLINE.
SQL> set flags 'strategy';
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" used
Aggregate Conjunct Index only retrieval of relation MY_TABLE
Index name MY_INDEX [1:1]
Now the index that was used by the query (and referenced by the
query outline) is dropped. This causes the query outline to be
set invalid (as shown by using the WARN_INVALID flag). The query
now uses sequential access strategy when the stored procedure is
executed.
SQL> set flags 'warn_invalid';
SQL> drop index my_index;
~Xw: Outline "MY_OUTLINE" marked invalid (index "MY_INDEX" dropped)
SQL>
SQL> set flags 'strategy';
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" is invalid and can not be used
Aggregate Conjunct Get
Retrieval sequentially of relation MY_TABLE
SQL> show outline my_outline
MY_OUTLINE
Outline has been marked invalid
.
.
.
The ALTER OUTLINE ... COMPILE clause is now used to make the
outline valid. The first attempt reports that the index is
missing. After the index is recreated the COMPILE succeeds.
Calling the stored procedure now uses this query outline.
SQL> alter outline my_outline compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-INDNOTEXI, index MY_INDEX does not exist in this database
SQL> -- must redefine the index
SQL> create index my_index on my_table (b desc);
SQL> alter outline my_outline compile;
SQL> call my_procedure();
~S: Outline "MY_OUTLINE" used
Aggregate Conjunct Index only retrieval of relation MY_TABLE
Index name MY_INDEX [1:1]
SQL>
Example 5: Changing the STORED NAME of a query outline in a
multischema database
This example shows how to change the STORED NAME of a multischema
outline. Here we explicitly provide the STORED NAME, however,
the same technique can be used when SQL generates a unique STORED
NAME for the outline.
SQL> attach 'filename mschema';
SQL> create outline SHOW_TABLE
cont> stored name SHOW_TABLE_01
cont> on procedure name SHOW_TABLES;
SQL> commit;
SQL> disconnect all;
SQL> attach 'filename mschema MULTISCHEMA IS OFF';
SQL> alter outline SHOW_TABLE_01
cont> rename to SHOW_THE_TABLES;
SQL> commit;