| Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
|
|
View PDF |
| Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
|
|
View PDF |
This document discusses how SQL is used with Oracle Database Lite. Topics include:
Section 1.5, "Oracle Database Lite Database Object Naming Conventions"
Section 1.9, "Oracle Database Lite SQL Datatypes and Literals"
Section 1.12, "Tuning SQL Statement Execution Performance With the EXPLAIN PLAN"
Oracle Database Lite uses the SQL (Structured Query Language) database language to store and retrieve data. It includes the following categories of SQL statements:
DDL (Data Definition Language)
Used to create, alter, or drop database objects, such as schemas, tables, columns, views, and sequences. For example, statements that use the commands, ALTER, CREATE, DROP, GRANT, and REVOKE.
DML (Data Manipulation Language)
Used to query and manipulate data in existing schema objects. For example, statements that use the commands, SELECT, INSERT, UPDATE, and DELETE.
TCL (Transaction Control Language)
These statements manage changes made in DML statements. For example, statements that use the commands, COMMIT, ROLLBACK, and SAVEPOINT.
Clause
Subsets of commands that modify commands. Oracle Lite supports CONSTRAINT and DROP clauses.
Pseudocolumns
Values generated from commands that behave like columns of a table but are not actually stored in the table. Oracle Database Lite supports the LEVEL and ROWNUM pseudocolumns.
Functions
Operate on data to transform or aggregate it. For example, TO_DATE to transform a date column into a particular format, and SUM to total all values for a column.
This reference provides SQL statement examples. All examples are based on the default Oracle Database Lite objects.
Oracle Database Lite uses Oracle SQL as its default SQL language. Oracle SQL handles computation results and date data in a different manner than SQL-92. The differences between Oracle SQL and SQL-92 are listed in Table 1-1.
Table 1-1 Differences Between Oracle SQL and SQL-92
| Oracle SQL | SQL-92 |
|---|---|
|
Division yields a double precision result such as 3.333. For example 8/3 yields 2.666. |
Division yields datatypes of operands such as 3. For example, 8/3 yields 2. |
|
|
|
Although Oracle Database Lite uses Oracle SQL, by default it supports several SQL-92 features including:
Column datatypes: TIME, TIMESTAMP, TINYINIT, and BIT
CASE expression
CAST expression
As mentioned in the preceding section, Oracle Database Lite uses Oracle SQL by default. However, if you want to support SQL-92 by default instead of Oracle SQL, you can change the SQL compatibility parameter in the POLITE.INI file to SQL-92. To change the parameter, add the following in the POLITE.INI file.
SQLCOMPATIBILITY=SQL92
See Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information about the POLITE.INI file.
The SQL language supported by Oracle Database Lite is a subset of the SQL language supported by Oracle. Oracle Database Lite supports some additional SQL-92 database objects, functions, and commands.
The differences between database objects supported by Oracle Database Lite and those supported by Oracle are listed in Table 1-2. See Section 1.5, "Oracle Database Lite Database Object Naming Conventions" for more information:
Table 1-2 Differences Between Oracle Database Lite and Oracle-Supported Database Objects
| Supported by Oracle Database Lite | Supported by Oracle |
|---|---|
|
Tables, views, indexes, sequences, schemas, snapshots. |
All database objects. |
|
A name identifier up to 128 characters for columns, indexes, tables, and schemas. User name identifiers can be up to 30 characters. |
A name identifier up to 31 characters. |
Chapter 2, "SQL Operators", lists the operators supported by Oracle Database Lite. In general, the Oracle Database Lite supports all operators supported by Oracle.
Except for datatype-related differences, the corresponding operators always work identically.
Chapter 3, "SQL Functions" lists the functions supported by Oracle Database Lite. The functions listed in Table 1-3 produce different results in Oracle and Oracle Database Lite.
Table 1-3 Function Behavior in Oracle Database Lite and Oracle
| Function | Supported by Oracle Lite | Supported by Oracle |
|---|---|---|
|
|
16 characters long |
18 characters long |
|
|
does not accept 'nlsparams' |
accepts 'nlsparams' |
|
|
does not accept 'nlsparams' |
accepts 'nlsparams' |
|
|
does not accept 'nlsparams' |
accepts 'nlsparams' |
Some Oracle commands have a more limited functionality in Oracle Database Lite. The Oracle command parameters that are not supported by Oracle Database Lite are listed in Table 1-4.
Table 1-4 Oracle Command Parameters Not Supported by Oracle Database Lite
| Command | Element Unsupported by Oracle Lite |
|---|---|
|
|
Index clause for table and column constraints. Exceptions into clauses for table and column constraints. Physical organization clauses. Deferred options for columns and tables. |
|
|
|
|
|
|
|
|
Rename index option. Rebuild index option. |
|
|
|
|
|
Set clause containing subqueries that select more than one column. Returning clause where row IDs for updated rows are returned. |
|
|
When used to extract timestamp from date value. |
Note:
There may be differences in subqueries for Oracle and Oracle Database Lite.Oracle Database Lite does not support the following commands and clauses.
Commands related to the following database objects.
Clusters
Database links
Stored functions and procedures other than Java stored procedures
Packages
Profiles
Rollback segments
Snapshot logs
Table spaces
Physical data storage clauses such as PCTFREE.
Oracle Database Lite does not support space management, table spaces, and INITRANS.
Oracle Database Lite DDL does not commit when executed as Oracle does, but commits as part of the current transaction.
Oracle Database Lite supports more datatypes than Oracle. For results similar to those of Oracle in Oracle Database Lite, use NUMBER and specify precision and scale.
Oracle anticipates datatypes to return and their display. It may produce results automatically, where Oracle Database Lite may need a specific CAST (one_datatype AS another_datatype) in the statement. You should avoid INT, FLOAT, and DOUBLE if you want portability between machine types. Oracle Database Lite uses the native implementations of these datatypes while Oracle maps these to specific NUMBER datatypes.
Oracle Database Lite uses 32-bit LONG indicator variables integers. Oracle uses, 16-bit SHORT indicator variables integers.
Oracle databases look at the datatype on the left side of an assignment when deciding how many decimal places of a result to store into a column. Oracle Database Lite follows SQL-92 convention, and only provides the maximum number of digits of precision from the right side of the assignment.
The Oracle Database Lite data dictionary is different from the Oracle data dictionary. Oracle Database Lite provides many commonly used system views including ALL_TABLES and ALL_INDEXES.
The table system.product_privs, which contains product user profiles in an Oracle database, does not exist in the Oracle Database Lite.
Oracle Database Lite may not generate the same messages that Oracle databases generate in response to SQL commands. The error codes may also be different. Applications should not depend on a specific error code or message text to recognize that an error has occurred.
Oracle Database Lite does not support CYCLE and CACHE clauses in sequence statements. Sequence numbers are also subject to ROLLBACK under some circumstances.
Oracle Database Lite does not support PL/SQL. However, Oracle Database Lite does support stored procedures and triggers written in Java.
Oracle Database Lite does not support trigonometric functions, SOUNDEX, or bit operations.
Oracle Database Lite begins a transaction with the first use of SELECT. In some isolation levels, the use of a SELECT on one connection can lock out an UPDATE of the same table on another connection. You may need to COMMIT after a SELECT to free the lock, so the UPDATE may proceed.
When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the command. Oracle Database Lite SQL evaluates the following two statements in the same manner.
SELECT ENAME,SAL*12,MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP;
SELECT ENAME,
SAL * 12,
MONTHS_BETWEEN( HIREDATE, SYSDATE )
FROM EMP;
Reserved words, keywords, identifiers and parameters are not case-sensitive. However, text literals and quoted names are case-sensitive. See the syntax descriptions in Chapter 3, "SQL Functions" and Chapter 4, "SQL Commands".
SQL syntax definitions use the following conventions. SQL syntax definitions are always shown in monospace text.
table_name
Indicates a place holder that should be replaced by an appropriate value or expression. Any additional delimiter that the replacement value or expression requires such as single quotes is shown.
[PUBLIC] OR [MAXVALUE | NOMAXVALUE]
Indicates an optional item or clause. Multiple items or clauses are separated by vertical bars. Do not enter brackets or vertical bars.
{ENABLE | DISABLE | COMPILE}
Braces enclose two or more required alternative choices, separated by vertical bars. Do not enter braces or vertical bars.
{IDENTITY | NULL} OR [MAXVALUE integer | NOMAXVALUE]
Vertical bars separate two or more choices, either required arguments enclosed in braces { } or optional arguments enclosed in brackets [ ]. Do not enter vertical bars, braces, or brackets.
[, column] ...
Indicates that further repetitions of the argument expressed in the same format are permissible. Do not enter ellipses.
[ASC | DESC]
Indicates the default value used if you do not specify any of the options separated by vertical bars.
A database can be made up of one or more database files or catalogs in ODBC and SQL-92. The fundamental unit of storage in SQL is a table consisting of rows of data organized in columns. All database objects, including tables, views, and indexes, are owned by a user name or a schema. By default in Oracle Database Lite, tables are created as part of the user schema, the schema with the same name as the login ID.
Object names in SQL must begin with a letter and may contain numbers and the special characters "_" and "$". Names are generally not case-sensitive. Mixed case names are permitted when enclosed in double quotes (" ").
Object names may be qualified by the catalog (otherwise known as the database file name, which in Oracle Datatbase lite is the ODB file) and schema to which they belong by separating the qualifiers with a period ".". For example,
production.payroll.emp.salary
This example refers to the salary column of the emp table owned by the payroll schema in the production catalog.
The following list describes the relative precedence of SQL operators. The operators at the top of the list have the highest precedence (they are evaluated first); the operators at the bottom of the list have the lowest precedence (they are evaluated last). Operators of equal precedence are evaluated from left to right.
+ (unary), -(unary), PRIOR
*,/
+, -, ||
All comparison operators
NOT
AND
OR
You can use parentheses in an expression to override operator precedence. Expressions inside parentheses are evaluated before those outside parentheses.
The execution of SQL statements requires the existence of a SQL session. An application can establish a SQL session by performing the following.
Issuing a SQL statement that requires a SQL session (a default session is implicitly established).
Issuing SQLConnect or SQLDriverConnect ODBC calls.
A SQL session is closed when one of the following occurs.
The SQLDisconnect API in ODBC is called.
An ODBC program terminates.
SQL databases handle requests in logical units of work called transactions. A transaction is a group of related operations that must be performed successfully before any changes to the database are finalized.
A SQL transaction starts when any DDL or DML statement is executed in a session. When you are satisfied that no errors occurred during the transaction, you can end the transaction with a COMMIT command. The database then changes to reflect the operation. If an error occurs, you can abandon the changes with the ROLLBACK command.
Oracle Database Lite does not commit a DDL statement until you issue the COMMIT command. Oracle immediately commits all DDL statements.
Oracle Database Lite datatypes and object classes are interoperable with other programming languages. You can issue SQL statements to Oracle Database Lite in a host language if you connect to the database from within the application, using the appropriate ODBC or JDBC driver.
The Open Database Connectivity (ODBC) interface from Microsoft defines a call level interface to provide interoperability across different databases. ODBC specifies a set of interface functions to allow the following features.
Connections to databases by different vendors.
Preparation and execution of SQL statements in a common language.
Retrieval of query results into local program variables.
Oracle Database Lite supports the ODBC 2.0 call level interface (CLI). Oracle Database Lite SQL supports implicit type conversion from the character string type to another datatype when necessary. For example, if the datatype of a column AGE is INTEGER, and you execute the following statement.
UPDATE EMPLOYEE SET AGE = '30' WHERE NAME = 'John'
'30' is automatically converted to an INTEGER type.
There are two principal reasons to use ODBC SQL syntax rather than the SQL syntax that is specific to your database.
First, SQL statements written in ODBC syntax are easily transferred among ODBC-compliant databases. Even though ODBC SQL syntax does not include many of the keywords and arguments that invoke important functionality for a specific database, SQL statements written in ODBC syntax are fully portable among all ODBC-compliant databases.
Second, you can use ODBC SQL syntax to execute SQL statements against databases that you are not familiar with. While ODBC SQL syntax cannot invoke your database's full functionality like your database's own SQL syntax, you can use it to perform many of the most common, and important, database functions.
You can always use database-specific SQL syntax, even when connected to a database through ODBC, since ODBC passes SQL statements through to a connected database without modification.
This section lists rules for naming Oracle Database Lite database objects and their parts.
User names must be from 1 to 30 characters long. Columns, indexes, tables, and schemas can be up to 128 characters long. Oracle Database Lite has no limit on name length, but it is recommended that you limit your name length to 30 characters.
Names cannot contain quotation marks.
Names are not case sensitive.
A name must begin with an alphabetic character.
Names can contain only alphanumeric characters and the characters _,$,and #. The use of $ and # is not recommended.
A name cannot be an Oracle Database Lite reserved word.
The word DUAL should not be used as a name for an object or part.
The Oracle Database Lite SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult to read. See Appendix C, "Oracle Database Lite Keywords and Reserved Words" for a list of Oracle Lite keywords.
A name must be unique across its name space.
A name can be enclosed in double quotes. Such names can contain any combination of characters, ignoring rules 3 through 7 in this list.
Names cannot contain a dot (".") character.
Section 1.6.1, "Number Format Elements" and Section 1.6.2, "Date Format Elements" list the elements you can use to create a valid number or date format. Formats can be used as arguments to the SQL functions: TO_DATE, TO_NUMBER, TO_CHAR, and TRUNC.
Oracle Database Lite number formats are listed in Table 1-5.
Table 1-5 Oracle Database Lite Number Formats
| Element | Example | Description |
|---|---|---|
|
9 |
9999 |
The number of nines specifies the number of significant digits returned. Blanks are returned for leading zeros and for a value of zero. |
|
0 |
0000.00 |
Returns a leading zero or a value of zero as a 0, rather than as a blank. |
|
$ |
$9999 |
Prefixes value with a dollar sign. |
|
B |
B9999 |
Returns zero value as blank, regardless of zeros in the format model. |
|
MI |
9999MI |
Returns "-" after negative values. For positive values, a trailing space is returned. |
|
S |
S9999 |
Returns "+" for positive values and "-" for negative values. |
|
PR |
9999PR |
Returns negative values in <angle brackets>. For positive values, a leading and trailing space are returned. |
|
D |
99D99 |
Returns the decimal character, separating the integral and fractional parts of a number. |
|
G |
9G999 |
Returns the group separator. |
|
C |
C999 |
Returns the ISO currency symbol. |
|
L |
L999 |
Returns the local currency symbol. |
|
, (comma) |
9,999 |
Returns a comma. |
|
. (period) |
99.99 |
Returns a period, separating the integral and fractional parts of a number. |
|
EEEE |
9.999EEEE |
Returns a value in scientific notation. |
Oracle Database Lite date formats are listed in Table 1-6.
Table 1-6 Oracle Database Lite Date Formats
| Element | Description |
|---|---|
|
|
Century; "S" prefixes BC dates with "-". |
|
|
4-digit year; "S" prefixes BC dates with "-". |
|
|
4-digit year based on the ISO standard. |
|
|
Last 3, 2, or 1 digit(s) of year. |
|
|
Last 3, 2, or 1 digit(s) of the ISO year. |
|
|
Year with comma. |
|
|
Quarter of year (1, 2, 3, 4; JAN-MAR = 1) |
|
|
Month (01-12; JAN = 01) |
|
|
Name of month; padded with blanks to length of 9 characters. |
|
|
Abbreviated name of the month. |
|
|
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
|
|
Week of year (1-52 or 1-53) based on the ISO standard. |
|
|
Week of month (1-5) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
|
|
Day of year (1-366). |
|
|
Day of month (1-31). |
|
|
Day of week (1-7). |
|
|
Name of day, padded with blanks to length of 9 characters. |
|
|
Abbreviated name of day. |
|
|
Meridian indicator. |
|
|
Meridian indicator with periods. |
|
|
Hour of day (1-12). |
|
|
Hour of day (0-23). |
|
|
Minute (0-59). |
|
|
Last 2 digits of year; for years in other countries. |
|
|
Second (0-59). |
|
|
Seconds past midnight (0-86399). |
|
|
Punctuation and quoted text is reproduced in the result. |
Use one of the following syntax forms to specify a SQL condition. The syntax diagrams in this document use a variation of Backus-Nauer Form (BNF) notation. For a description of the convention used in this document, please see Section 4.2.6, "BNF Notation Conventions".
A simple comparison condition specifies a comparison with expressions or subquery results using the syntax displayed in Figure 1-1.
BNF Notation
{ expr { = | != | ^= | <> | > | < | >= | <= } { expr |"(" subquery")"}
For example,
SELECT * FROM EMP WHERE SAL > 2000;
For information on comparison operators, see Comparison Operators.
A group comparison condition specifies a comparison with any or all members in a list or subquery using the syntax displayed in Figure 1-2.
BNF Notation
{ expr { = | != | ^= | <> | > | < | >= | <= } { ANY | SOME | ALL } {"(" subquery")"}| expr_list { = | != } { ANY | SOME | ALL } { "(" subquery ")"}}
For example:
SELECT * FROM EMP WHERE ENAME = any ('SMITH', 'WARD', 'KING');
This allows the comparison of columns or expressions using a subquery that returns a multi-column result. This feature allows users to supply a row value constructor, such as a list of comma-separated expressions enclosed within parenthesis.
You may insert a subquery anywhere. An arithmetic expression or a column can appear. The subquery needs to be enclosed in parenthesis and is restricted to return a maximum of one row with one column.
For example,
Subquery in a select list. The following query is supported (assuming c1 and c2 are columns in table t1 and c1 is a primary key).
SELECT (select c1 from t1 b where a.c1 = b.c1),
c2 from t1 a where <condition>
Subquery in an expression: The following query is supported (with the same assumption as example 1).
SELECT * from t1 a where
(select c1 from t1 where c1 = 10) =
(select c1 from t1 b where a.c1 = b.c1) - 20;
A subquery can contain Group By, Union, Minus, and Intersect, but not an Order By clause.
A membership condition tests for membership in a list or subquery using the syntax displayed in Figure 1-3.
BNF Notation
expr [NOT] IN { expr_list | "("subquery ")"}
For example,
SELECT * FROM EMP WHERE ENAME not in ('SMITH', 'WARD', 'KING');
A range condition tests for inclusion in a range using the syntax displayed in Figure 1-4.
BNF Notation
expr [ NOT ] BETWEEN expr AND expr ;
For example,
SELECT * FROM EMP WHERE SAL between 2000 and 50000;
A NULL condition tests for nulls using the syntax displayed in Figure 1-5.
BNF Notation
expr IS [NOT] NULL
For example:
SELECT * FROM EMP WHERE MGR IS NOT NULL;
An EXISTS condition tests for the existence of rows in a subquery using the syntax displayed in Figure 1-6.
BNF Notation
EXISTS "("subquery")"
For example,
SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
A LIKE condition specifies a test involving pattern matching using the syntax displayed in Figure 1-7.
BNF Notation
char1 [NOT] LIKE char2 [ESCAPE "'"esc_char"'" ]
For example,
SELECT * FROM EMP WHERE NAME like 'SM%"
A COMPOUND condition specifies a combination of other conditions using the syntax displayed in Figure 1-8.
BNF Notation
{ "(" condition ")" | NOT condition | condition {AND | OR} condition};
For example,
SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;
Use one of the following syntax forms to specify a SQL expression.
A simple expression specifies column, pseudocolumn, constant, sequence number, or null using the syntax displayed in Figure 1-9.
BNF Notation
{ [schema .] { table | view } "." { column | pseudocolumn }| text | catalog "." schema "." { table| view } "." { column | pseudocolumn }| number| sequence "." { CURRVAL | NEXTVAL }| NULL}
In addition to the schema of a user, schema can also be PUBLIC (double quotation marks required), in which case it must qualify a public synonym for a table, view, or materialized view. Qualifying a public synonym with PUBLIC is supported only in Data Manipulation Language (DML) statements, not Data Definition Language (DDL) statements.
The pseudocolumn can be either LEVEL, ROWID, or ROWNUM. You can use a pseudocolumn only with a table, not with a view or materialized view.
Examples
emp-ename 'this is a text string' 10
A built-in function expression specifies a call to a single-row SQL function using the syntax displayed in Figure 1-10.
BNF Notation
function ["(" [DISTINCT | ALL] expr [, expr]...")"] ;
Some valid built-in function expressions are:
LENGTH('BLAKE')
ROUND(1234.567*43)
SYSDATE
java_function_name (expr , expr...) schema.table.java_function_name (expr , expr...)
For information on how to use Java functions, see the Oracle Database Lite Developer's Guide.
A compound expression specifies a combination of other expressions using the syntax displayed in Figure 1-11.
BNF Notation
{ "(" expr ")" | { + | - } expr | PRIOR column | expr( * | / | + | - | ||) expr };
Some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.
Examples
('CLARK' || 'SMITH')
LENGTH('MOOSE') * 57
SQRT(144) + 72
my_fun(TO_CHAR(sysdate,'DD-MM-YY'))
A DECODE expression uses the special DECODE syntax displayed in Figure 1-12.
BNF Notation
DECODE "(" expr "," search "," result [, search "," result]... [, default] ")" ;
To evaluate this expression, Oracle Database Lite compares expr to each search value one by one. If expr is equal to a search, Oracle Database Lite returns the corresponding result. If no match is found, Oracle Database Lite returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle Database Lite compares them using non-padded comparison semantics.
The search, result, and default values can be derived from expressions. Oracle Database Lite evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle Database Lite never evaluates a search if a previous search is equal to expr.
Oracle Database Lite automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle Database Lite automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle Database Lite converts the return value to the datatype VARCHAR2.
In a DECODE expression, Oracle Database Lite considers two nulls to be equivalent. If expr is null, Oracle Database Lite returns the result of the first search that is also null. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.
Example
This expression decodes the value DEPTNO. In this example, if DEPTNO is 10, the expression evaluates to 'ACCOUNTING'. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.
DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')
An EXPRESSION LIST is a series of expressions, each separated by a comma as displayed in Figure 1-13. The entire series is enclosed in parenthesis.
BNF Notation
"("[ expr [, expr]...] ")"
A VARIABLE EXPRESSION specifies a host variable with an optional indicator variable as displayed in Figure 1-14. This form of expression can appear in a programmatic programming interface.
BNF Notation
":" host_variable [[INDICATOR] ":" indicator_variable]
A CAST expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value as displayed in Figure 1-15.
BNF Notation
CAST "(" expr AS datatype_name ")"
For the operand, expr is a built-in datatype. Table 1-7 shows which built-in datatypes accept CAST conversion to another datatype. (CAST does not support LONG, LONG RAW, or any of the LOB datatypes.)
Table 1-7 Built-In Datatypes that Accept the CAST Conversion
| From/ To | Char, Varchar2 | Numeric | Date | Time | Timestamp | Raw |
|---|---|---|---|---|---|---|
|
Char, Varchar2 |
X |
X |
X |
X |
X |
X |
|
Numeric |
X |
X |
||||
|
Date |
X |
X |
X |
|||
|
Time |
X |
X |
X |
|||
|
Timestamp |
X |
X |
X |
X |
||
|
Raw |
X |
X |
The Date datatype is affected by the SQLCompatibility setting defined in the POLITE.INI file.
Date and Timestamp are equivalent if you have set: SQLCompatibility=Oracle
Date and Timestamp are not equivalent if you have set: SQLCompatibility=SQL92
See Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information about the POLITE.INI file.
The numeric category includes the following datatypes: BIGINT, BINARY, BIT, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMBER, NUMERIC, REAL, SMALLINT, and TINYINT.
Built-In Datatype Examples
SELECT CAST ('1997-10-22' AS DATE) FROM DUAL;
SELECT * FROM t1 WHERE CAST (ROWID AS CHAR(5)) = '01234';
For a complete list of Oracle Database Lite SQL datatypes, see Appendix E, "Oracle Database Lite Datatypes". For information about literals, see Appendix D, "Oracle Database Lite Literals".
Oracle Database Lite compares character string values using one of these comparison rules:
blank-padded comparison semantics
non-padded comparison semantics
The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may vary. Table 1-8 lists the results of comparing five pairs of character values using each comparison semantic. Generally, the results of blank-padded and non-padded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and non-padded comparison semantics.
Table 1-8 Comparison of Blank-Padded and Non-Padded Comparison Semantics
| Blank-Padded | Non-Padded |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
If the two values have different lengths, Oracle Database Lite first adds blanks to the end of the shorter one so that their lengths are equal. Oracle Database Lite then compares the values character by character up to the first character that differs. The value with the greater than character (>) in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle Database Lite uses blank-padded comparison semantics only when both values in the comparison are either expressions of the datatype CHAR, text literals, or values returned by the USER and DATABASE functions.
Oracle Database Lite compares two values character by character up to the first character that differs. The value with the greater than character (>) in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle Database Lite uses non-padded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2. As a result, when comparing a CHAR value with a VARCHAR2 value, Oracle Database Lite considers the character value 'a ' unequal to 'a'.
Oracle Database Lite supports the numeric, character or date-based primary key. Primary keys may consist of a single or multiple columns. You can use the foreign key constraint to enforce referential integrity between tables. If you specify the ON DELETE CASCADE clause when creating the foreign key constraint, then detail records are automatically deleted if the corresponding master record is deleted. Otherwise, you must first delete the master record before you can delete the detail records.
Oracle Database Lite can help you ensure that all values in a particular column or combination of columns is unique. Once you create a UNIQUE constraint for the particular column or columns, Oracle Database Lite automatically prevents the creation of duplicate values.
If you do not want any column to contain null values, then you can specify a NOT NULL constraint for that particular column. Insert and update operations on columns that have NOT NULL constraints automatically fail if you attempt to either insert or update a null value for that particular column.
You can associate comments with SQL statements and schema objects. Comments within SQL statements do not affect the statement execution, but they can make your application easier to read and maintain.
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using one of the following options.
Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.
A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
Example 1
SELECT * FROM EMP WHERE EMP.DEPTNO = /* The subquery matches values in EMP.DEPTNO with values in DEPT.DEPTNO */ (SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');
This statement returns the following output.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7566 JONES MANAGER 7839 1981-04-0 2975 20
7902 FORD ANALYST 7566 1981-12-0 3000 20
7369 SMITH CLERK 7902 1980-12-1 800 20
7788 SCOTT ANALYST 7566 1982-12-0 3000 20
7876 ADAMS CLERK 7788 1983-01-1 1100 20
Example 2
SELECT ENAME, -- select the employee name SAL -- and the salary FROM EMP -- from the EMP table WHERE SAL -- where the salary >= -- is greater than or equal to 3000 -- 3000 ;
This statement returns the following output:
ENAME SAL ---------- --------- KING 5000 FORD 3000 SCOTT 3000
To execute a SQL statement, Oracle might need to perform several operations. The combination of the operations Oracle uses to execute a statement is called an execution plan, which includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method. The execution plan shows you exactly how Oracle Database Lite executes your SQL statement.
The components of an execution plan include the following:
An ordering of the tables referenced by the statement.
An access method for each table mentioned in the statement.
A join method for tables affected by join operations in the statement.
Data operations, such as FILTER, SORT, UNION, and so on.
The EXPLAIN PLAN command stores the execution plan chosen by the Oracle Database Lite optimizer for SELECT, UPDATE, INSERT, and DELETE statements into the table—PLAN_TABLE. Before using the EXPLAIN PLAN statement, a user creates the plan table using an interactive query tool, such as msql.
You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the EXPLAIN PLAN statement and then query the output table.
The EXPLAIN PLAN output shows how Oracle executes SQL statements, which helps a developer or DBA understand how a query is being executed. Thus, you can identify additional indexes needed, or how best to modify the query. The query modification may involve a re-write or use of optimizer hints to change the join order.
Use the SQL script—utlxplan.sql—to create the sample output table called PLAN_TABLE in your schema. Alternatively, you can use msql to create the plan table. See Section 1.12.1, "The PLAN Table".
Include the EXPLAIN PLAN FOR clause prior to the SQL statement. The syntax is as follows:
Explain_plan_statement ::= EXPLAIN PLAN [SET STATEMENT_ID = 'text' ] [INTO [schema.] plan_table] [FOR] statement;
Where
statement is any SELECT, UPDATE, INSERT, DELETE statement
'text' is a literal provided by the user to identify all rows for the given query.
schema.plan_table is the table where you want the result to be stored. The table must conform to the layout given in the utlxplan.sql script. The default value for is PLAN_TABLE in your own schema.
After issuing the EXPLAIN PLAN statement, query the PLAN_TABLE for the output.
The EXPLAIN PLAN command is not unique to Oracle Database Lite. It is a feature of the Oracle database. However, not all SQL operations supported in the Oracle database are supported by Oracle Database Lite. This section shows the operation subset that you can use in Oracle Database Lite.
In addition, this section does not go into full details on how the EXPLAIN PLAN works. For a full description of the EXPLAIN PLAN, see Section 13.1, "Determining Performance of Client SQL Queries With Explain Plan" in the Oracle Database Lite Client Guide.
The PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. See Table 1-9 for a description of the columns in the table.
Use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema.
Table 1-9 Plan Table
| Column | Data Type | Description |
|---|---|---|
|
statement_id |
Varchar2(30) |
User specified ID |
|
Timestamp |
Date |
Date and time of creation |
|
Remarks |
Varchar2(80) |
A user-specified remarks |
|
Operation |
Varchar2(30) |
The name of operation, such as SELECT, INSERT, UPDATE, DELETE, TABLE ACCESS and so on. See the Operations table for more information. |
|
Options |
Varchar2(30) |
Qualification for the operation |
|
object_owner |
Varchar2(30) |
Owner of a table or index |
|
object_name |
Integer |
Name of the table or index |
|
Id |
Integer |
Step identification number |
|
parent_id |
Integer |
Parent step number |
|
Position |
Integer |
Order of processing among the steps that have the same parent step id |
|
Cost |
Integer |
Estimated cost in number of I/Os. |
|
Cardinality |
Integer |
The estimated number of rows produced |
|
Text |
Varchar2(4096) |
First 4096 bytes of the statement text stored with the first step of execution. For example, id=0 |
Table 1-10 lists each combination of Operation and Option produced by the EXPLAIN PLAN statement and its meaning within an execution plan.
Table 1-10 Operation and Option Values Produced by the EXPLAIN PLAN
| Operation | Options | Comments |
|---|---|---|
|
CONNECT BY |
Retrieves rows in hierarchical order for a query containing a CONNECT BY clause. |
|
|
FILTER |
None |
Operation accepting a set of rows, eliminates some of them, and returns the rest. |
|
FOR UPDATE |
None |
Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause. |
|
INDEX |
Retrieval of one or more rowids from an index. |
|
|
NESTED LOOP |
Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. |
|
|
SORT |
AGGREGATE, UNIQUE, GROUP BY, ORDER BY |
A sort is being performed for aggregation, duplicate removal, group by or order by operations respectively. |
|
TABLE ACCESS |
FULL |
All data pages of the table will be scanned. |
|
TABLE ACCESS |
BY INDEX ROWID, BY ROWID |
The table rows are accessed using rowids from an index, or provided by some other means. |
|
UNION ALL |
A UNION ALL operation is being performed. |
|
|
VIEW |
A logical or physical view is being materialized. |
|
|
CREATE TEMP TABLE |
ORDER BY, READ COMMITTED, GROUP BY, UNION, CONNECT BY, MINUS, AGGREGATE |
Option indicates the reason for creating the temporary table. |
|
INSERT |
An INSERT operation is being performed. |
|
|
UPDATE |
An UPDATE operation is being performed. |
|
|
DELETE |
A DELETE operation is being performed. |
|
|
SELECT |
A SELECT operation is being performed. |
|
|
MINUS |
A MINUS operation is being performed. |
The following examples demonstrate the EXPLAIN PLAN statement. The output for each example should only be used as a guideline. The actual output is subject to change based on the analysis of internal data structures. The examples are based on a sample schema, as follows:
Sample Schema
drop table s;
drop table sp;
drop table p;
drop table j;
drop table spj;
create table S ( S# char(3), SNAME Char(10), Status Int, City char(10));
create table P (P# char(3), PNAME Char(10), Color Char(10), Weight Int, City Char(10));
create table SP (S# char(3), P# Char(3), Qty Int);create table J ( J# char(3), JNAME Char(10), City char(10));create table SPJ (S# char(3), P# Char(3), J# Char(3), Qty Int);
insert into S values ('S1', 'Smith', 20, 'London');insert into S values ('S2', 'Jones', 10, 'Paris');
insert into S values ('S3', 'Blake', 30, 'Paris');insert into S values ('S4', 'Clark', 20, 'London');
insert into S values ('S5', 'Adams', 30, 'Athens');insert into P values ('P1', 'Nut', 'Red', 12, 'London');
insert into P values ('P2', 'Bolt', 'Green', 17, 'Paris');
insert into P values ('P3', 'Screw', 'Blue', 17, 'Rome');
insert into P values ('P4', 'Screw', 'Red', 14, 'London');
insert into P values ('P5', 'Cam', 'Blue', 12, 'Paris');
insert into P values ('P6', 'Cog', 'Red', 19, 'London');
insert into J values ('J1', 'Sorter', 'Paris');insert into J values ('J2', 'Punch', 'Rome');insert into J values ('J3', 'Reader', 'Athens');
insert into J values ('J4', 'Console', 'Athens');
insert into J values ('J5', 'Collator', 'London');
insert into J values ('J6', 'Terminal', 'Oslo');
insert into J values ('J7', 'Tape', 'London');
insert into SP values ('S1', 'P1', 300);
insert into SP values ('S1', 'P2', 200);
insert into SP values ('S1', 'P3', 400);
insert into SP values ('S1', 'P4', 200);
insert into SP values ('S1', 'P5', 100);
insert into SP values ('S1', 'P6', 100);
insert into SP values ('S2', 'P1', 300);
insert into SP values ('S2', 'P2', 400);
insert into SP values ('S3', 'P2', 200);
insert into SP values ('S4', 'P2', 200);
insert into SP values ('S4', 'P4', 300);
insert into SP values ('S4', 'P5', 400);
insert into SPJ values ('S1', 'P1', 'J1', 200);
insert into SPJ values ('S1', 'P1', 'J4', 700);
insert into SPJ values ('S2', 'P3', 'J1', 400);
insert into SPJ values ('S2', 'P3', 'J2', 200);
insert into SPJ values ('S2', 'P3', 'J3', 200);
insert into SPJ values ('S2', 'P3', 'J4', 500);
insert into SPJ values ('S2', 'P3', 'J5', 600);
insert into SPJ values ('S2', 'P3', 'J6', 400);
insert into SPJ values ('S2', 'P3', 'J7', 800);
insert into SPJ values ('S2', 'P5', 'J5', 100);
insert into SPJ values ('S3', 'P3', 'J1', 200);
insert into SPJ values ('S3', 'P4', 'J2', 500);
insert into SPJ values ('S4', 'P6', 'J3', 300);
insert into SPJ values ('S5', 'P2', 'J2', 200);
commit;
create unique index SIX1 on S ( S# );
create unique index PIX1 on P ( P# );
create unique index SPIX1 on SP ( S#, P# );
create unique index SPJIX1 on SPJ ( S#, P#, J# );
create index PCOLOR on P(Color);
The following examples demonstrate three examples of the output for the EXPLAIN PLAN for specific select statements:
Section 1.12.2.1, "Example for Select Distinct and Group By"
Section 1.12.2.3, "Example for Select Statement With Multiple Qualifiers"
The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select distinct P# from SPJ group by p#,j# having avg(qty) > 320;
The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select s.* from S, SP where s.s#=Sp.P# and status > 20 and qty > 40 union Select s.* from s,sp, p where s.s#=sp.s# and sp.p#=p.p# and p.color='Red';
Table 1-12
| ID | POSITION | PARENT_ID | OPERATION | OPTIONS | OBJNAME |
|---|---|---|---|---|---|
|
0 |
SORT |
ORDER BY |
|||
|
1 |
1 |
0 |
CREATE TEMP TABLE |
ORDER BY |
|
|
2 |
1 |
1 |
UNION ALL |
||
|
3 |
1 |
2 |
SELECT |
||
|
4 |
1 |
3 |
FILTER |
||
|
5 |
1 |
4 |
NESTED LOOP |
||
|
6 |
1 |
5 |
TABLE ACCESS |
FULL |
S |
|
7 |
2 |
5 |
TABLE ACCESS |
FULL |
SP |
|
8 |
2 |
2 |
SELECT |
||
|
9 |
1 |
8 |
FILTER |
||
|
10 |
1 |
9 |
NESTED LOOP |
||
|
11 |
1 |
10 |
NESTED LOOP |
||
|
12 |
1 |
11 |
TABLE ACCESS |
BY INDEX ROWID |
P |
|
13 |
1 |
12 |
INDEX |
PCOLOR |
|
|
14 |
2 |
11 |
TABLE ACCESS |
FULL |
SP |
|
15 |
2 |
10 |
TABLE ACCESS |
BY INDEX ROWID |
S |
|
16 |
1 |
15 |
INDEX |
SIX1 |
The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select s.* from s, sp where s.s# = sp.s# and status > 20 and city in (select city from j where j# = 'J1' or j# = 'J2');
|
 Copyright © 1997, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|