![]() |
SQL-J Language Reference
|
Reference Manual |
CREATE STATEMENT statementThe CREATE STATEMENT statement creates stored prepared statements, which are SQL-J statements (usually pre-compiled) stored in the database for multiple use. Storing statements allows you to avoid compilation time and to share compiled statement plans between sessions and connections. A stored prepared statement is prepared, tagged with an identifier, and stored in a system table. The prepared statement can then be invoked directly, using its identifier, without compiling the statement again. Stored prepared statements provide an additional performance benefit in multi-user systems, because they optimize memory use. See Storing Prepared Statements to Improve Performance in Tuning Cloudscape for more information. The compiled statement and reference information about the statement are stored in the SYS.SYSSTATEMENTS table, and information about each parameter is stored in the SYS.SYSCOLUMNS table. Statements cannot be created in the SYS schema. You cannot create stored prepared statements for CREATE STATEMENT and EXECUTE STATEMENT statements. Syntax
CREATE STATEMENT StatementName [ NOCOMPILE ] About the USING ClauseThe USING clause, which is optional, allows you to provide sample values for parameters in the CREATE STATEMENT statement by providing a SingleRowResultSet. A SingleRowResultSet is a query that returns a single row, usually a VALUES expression or a SELECT. This gives the optimizer representative data so that it can choose a plan that will be suitable for the real values that will be used by the statement. Without these values, the optimizer makes guesses as to the selectivity of the parameters. These values are used for compiling the statement, not for executing the statement. (You provide values for executing the statement when you actually execute it with the EXECUTE STATEMENT statement.) Providing sample values (with the USING clause) is particularly useful if the application designer has a good idea how the statement will be used in the future. For example, imagine an indexed column that is used to store ages. Ages typically range from 0 to 100. If your query typically looks for one specific age, the index will prove useful. If your query typically looks for values under a specific value, the index may or may not prove useful. For example, if the query looks for all values less than 5, an index will prove useful, because Cloudscape will avoid scanning 95% of the data pages. However, if the query looks for all values less than 90, it would probably make more sense for Cloudscape to go straight to the data pages and filter out those unneeded rows later. Providing a realistic sample value when creating the statement allows Cloudscape to devise a useful access plan. CREATE STATEMENT Examples
CREATE STATEMENT getFullFlightInfo
CREATE STATEMENT getDirectFlights AS SELECT orig_flights.flight_id
CREATE STATEMENT INSERTFlightObject
CREATE STATEMENT UpdateCursor NOCOMPILE AS Dependency SystemA stored prepared statement depends on all the objects that are referenced by the statement. A stored prepared statement can be dependent on the following types of objects:
If an object is referenced by a stored prepared statement, the object cannot be dropped or altered in such a way that the statement is invalidated if the prepared statement is currently in use. For example, if a stored prepared statement uses the orig_index on the Flights table to access its data, that index cannot be dropped when that stored prepared statement is in use (that is, if a ResultSet is open on the statement). This is true of any active prepared statement (stored or not). If the statement is not in use, a referenced object can be dropped and the stored prepared statement is invalidated. The invalidation is recorded by setting the VALID field of SYS.SYSSTATEMENTS to FALSE. The next time the statement is executed, it is recompiled before execution. If an object on which it depends does not exist at the time it is recompiled, the execution request results in an error. If a new object is created with the same name, the statement will be compiled against the new object. In the example in the preceding paragraph, if a new index on the Flights table is created, the stored prepared statement will be compiled with that new index being considered as a possible access path. This is the same behavior as that of a prepared statement that is not stored in Cloudscape. You can also force recompilation. See ALTER STATEMENT statement. Publications depend on stored prepared statements if the statements are part of the publications. Adding or dropping publications may invalidate stored prepared statements. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |