String types store strings. CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, NVARCHAR2, RAW, LONG, and LONG RAW types are classified as string types.

CHAR

The CHAR type stores fixed-length character strings.

CHAR(size[BYTE|CHAR])

The CHAR type has the following characteristics:

The following example illustrates the use of the CHAR type:

PRODUCT_NAME CHAR(10)

In the above example, the PRODUCT_NAME column contains 10 byte character strings. If the length of an entered string is shorter than the declared type, the remainder will be blank-padded. For example, if 'Tibero' is entered, four blank spaces will be padded and 'Tibero____' will be stored.

VARCHAR

The VARCHAR type stores variable-length character strings.

VARCHAR(size[BYTE|CHAR])

The VARCHAR type has the following characteristics:

The following example illustrates the use of the VARCHAR type:

EMP_NAME VARCHAR(10)

In the above example, the EMP_NAME column has 10 byte character strings. For example, if 'Peter' is entered, 'Peter' will be stored without any change. Therefore, the actual stored string length is 5 bytes while the EMP_NAME column is declared as 10 bytes.

Like this, the VARCHAR type has variable-length strings up to a maximum of the declared length.

VARCHAR2

The VARCHAR2 type is exactly the same as the VARCHAR type.

NCHAR

The NCHAR type stores fixed-length Unicode character strings.

NCHAR(size)

The NCHAR type has the following characteristics:

NVARCHAR

The NVARCHAR type stores variable-length Unicode character strings.

NVARCHAR(size)

The NVARCHAR type has the following characteristics:

NVARCHAR2

The NVARCHAR2 type is the same as the NVARCHAR type.

RAW

The RAW type stores variable-length binary data.

The RAW type can have NULL characters ('\0') in the middle of a string unlike the CHAR and VARCHAR types. This type always stores length information because a NULL character cannot indicate the end of the data.

RAW(size)

The RAW type has the following characteristics:

LONG

The LONG type is an expanded version of the VARCHAR type. This type stores character strings in the same way as the VARCHAR type.

LONG

The LONG type has the following characteristics:

LONG RAW

The LONG RAW type is an expanded version of the RAW type. This type stores binary data in the same way as the RAW type.

LONG RAW

The LONG RAW type has the following characteristics:

Number types store integers and real numbers. NUMBER, INTEGER, FLOAT, and BINARY_DOUBLE types are classified as number types.

Tibero supports the SQL standard number types specified in ANSI. Therefore, when a column is declared as INTEGER or FLOAT, the column type is changed to the NUMBER type after precision and scale are set internally.

NUMBER

The NUMBER type stores integers and real numbers.

The NUMBER type stores a number of up to 38 digits. The absolute value is between 1.0×10^-130 and 1.0×10¹²⁶. It can also store the values of positive infinity, negative infinity, and zero.

The NUMBER type can be declared with precision and scale.

NUMBER[(precision[,scale])]

The following table shows how input data would be stored based on the precision and the scale values.

BINARY_FLOAT

The BINARY_FLOAT type stores real numbers and integers. It is a single-precision 32-bit floating point data type.

A BINARY_FLOAT variable can be set to positive infinity, negative infinity, and zero. The absolute value range is 1.17549E-38 to 3.40282E+38.

The BINARY_FLOAT type has the following characteristics:

BINARY_DOUBLE

The BINARY_DOUBLE typestores real numbers and integers. It is a double-precision 64-bit floating point data type.

A BINARY_DOUBLE variable can be set to positive infinity, negative infinity, and zero. The absolute value range is 1.79769313486231E+308 to 2.22507485850720E-308.

The BINARY_DOUBLE type has the following characteristics:

Priority of NUMBER TYPE

Tibero converts numbers to different data types according to the priority. The order of priority from highest to lowest: BINARY_DOUBLE, BINARY_FLOAT, then NUMBER.

BINARY_DOUBLE, BINARY_FLOAT, and NUMBER have lower priority than the datetime or interval types, but they have higher priority than other types.

Datetime types store times and dates. DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE types are classified as datetime types.

DATE

The DATE type stores specific times ranging from seconds to years.

DATE

The DATE type has the following characteristics:

TIME

The TIME type stores specific times in seconds with up to nine decimal places.

TIME [(fractional_seconds_precision)]

The TIME type has the following characteristics:

TIMESTAMP

The TIMESTAMP type stores specific dates and times in seconds down to nine decimal places.

TIMESTAMP [(fractional_seconds_precision)]

The TIMESTAMP type has the following characteristics:

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE type stores specific time zones as well as dates and times.

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE type has the following characteristics:

TIMESTAMP WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE type stores different times depending on the time zone of a particular session.

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE type has the following characteristics:

Interval types store a difference between times or dates. INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND types are classified as interval types.

INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH type stores time intervals with years and months.

INTERVAL YEAR [(year_precision)] TO MONTH

INTERVAL DAY TO SECOND

The INTERVAL DAY TO SECOND type stores time intervals with days, hours, minutes, and seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

Large object types store large objects. CLOB, BLOB, and XMLTYPE types are classified as large object types.

CLOB

The CLOB type is an expanded version of the LONG type.

The CLOB type has the following characteristics:

BLOB

The BLOB type is an expanded version of the LONG RAW type.

The BLOB type has the following characteristics:

XMLTYPE

XML (Extensible Markup Language), a standard introduced by the W3C (World Wide Web Consortium), expresses any kind of data, regardless of structure. To store XML data, Tibero provides the XMLTYPE type that is internally stored as the CLOB type.

The XMLTYPE type has the following characteristics:

Embedded types are automatically inserted in every row by Tibero. The ROWID type is classified as an embedded type.

ROWID

The ROWID type is inserted in every row by Tibero to identify each row in a database. This type stores the physical location of each row.

ROWID

A user-defined type can be used as a tbPSM type and can be stored in Tibero. User-defined types include arrays and nested tables. For more information, refer to "Tibero tbPSM Guide".

Array

Array is a collection of data with the same type. The type and length of the array are defined by the user.

Nested Table

Nested table is a collection of data with the same type. The type is defined by the user, but there is no length.

The user can directly use SQL conversion functions to convert data types.

The following is a list of type conversion functions. (Rows are types before conversion, and columns are types after conversion)

Implicit conversion is performed automatically as needed without user specifying explicit conversion.

Implicit type conversion is needed in the following cases:

The following is an implicit type conversion matrix. (Rows are types before conversion, and columns are types after conversion).

Type Comparison

The following matrix shows the type with higher precedence when comparing two data types.

A string literal represents strings. A string literal is enclosed with single quotes(' ') to distinguish it from other schema objects.

A string literal has the following characteristics:

A detailed description of a string literal follows:

A numeric literal represents integers and real numbers.

The numeric literal has the following characteristics:

A detailed description of a numeric literal follows:

The datetime literal specifies date and time information. DATE, TIME, TIMESTAMP, and TIMESTAMP WITH TIME ZONE literals are classified as datetime literals.

DATE

The DATE literal specifies date and time information.

The DATE literal has the following characteristics:

TIME

A TIME literal specifies time information.

The TIME literal has the following characteristics:

TIMESTAMP

The TIMESTAMP literal is an expanded version of the DATE literal.

The TIMESTAMP literal has the following characteristics:

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE literal is an expanded version of the TIMESTAMP literal.

The TIMESTAMP WITH TIME ZONE literal has the following characteristics:

TIMESTAMP WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE literal has the same format as that of the TIMESTAMP literal.

An interval literal specifies the interval between two points in time. An interval can be specified with two formats: a combination of year and month, or with the date, hour, minute, and second.

The interval literal is provided in Tibero with two types:

Each type consists of the first field, which is mandatory, and the second field, which is optional. The first field represents a default unit of date or time to be expressed, and the second field represents the smallest interval of the default unit. Interval of the same type can be added to and subtracted from each other.

YEAR TO MONTH

The YEAR TO MONTH type specifies a time interval with a year and a month.

A detailed description of the YEAR TO MONTH type follows:

DAY TO SECOND

The DAY TO SECOND type specifies a time interval with a date, an hour, a minute, and a second.

A detailed description of the DAY TO SECOND type follows:

NUMBER type format strings can be used as parameters for the TO_CHAR and TO_NUMBER functions.

NUMBER type format strings have the following characteristics:

The following table shows format elements which may be included in NUMBER type format strings:

The following table shows how a NUMBER type value is output according to each NUMBER format string when the TO_NUMBER function is used.

Datetime type format strings can be used as parameters for the TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.

Datetime type format strings have the following characteristics:

The following are the format elements that can be used in datetime type.

In the above table, the RR format element is similar to the YY format element but can specify and store a year from another century more easily.

The rules for the RR format element follows:

Datetime type format elements can have the following suffixes.

A format modifier can be specified several times in a format string. Whenever a format modifier is used, it can activate disabled features or inactivate enabled features.

The fill mode (FM) format modifier can change the method for adding spaces, and the format exact (FX) format modifier determines if the input string is identical to the format string.

FM

Tibero adds blank spaces to strings output by a format element to make the length of all the strings identical to the longest string. For example, because the longest string is 'SEPTEMBER' in the MONTH format element, other strings that are months are padded with blank spaces to make their lengths equal to nine characters to match September.

If FM is specified, the method for filling blank spaces is changed to the following:

FX

Tibero examines if input strings are identical to the format strings. If any string is not the same an error will occur.

If FM is specified, the following restrictions apply:

The ROWID is an identifier that refers to a row in the entire database. A ROWID contains the physical location of a row, and this value is not changed unless the row is removed.

Tibero uses a multilevel disk structure for storing databases. To search a particular row on a disk using a ROWID, it needs to reflect the disk structure of the ROWID.

ROWIDs in Tibero have the same structure as [Figure 2.1].

[Figure 2.1] ROWID Structure

ROWID consists of 12 bytes, including a segment, data file, data block, and row number which are 4, 2, 4, and 2 bytes respectively.

The BASE64 encoding is used for expressing a ROWID value. The encoding expresses a 6-bit number with an 8-bit character by replacing a number between 0 and 63 by A through Z, a through z, 0 through 9, a plus sign (+), or slash (/).

If ROWID is encoded in BASE64, a segment# (6 bytes), data file# (3 bytes), data block# (6 bytes), and row# (3 bytes) are represented in "SSSSSSFFFBBBBBBRRR" format. For example, when a segment#, data file#, data block#, row# are 100, 20, 250, and 0 respectively, the ROWID is "AAAABkAAUAAAAD6AAA".

The ROWNUM is the number of a row when a set of rows is created as a result of a SELECT statement. The first row returned as a result of a query has a value of 1, the second row has 2, the third row has 3, etc.

The following is the method for assigning a ROWNUM in Tibero:

ROWNUM can be used to restrict the number of result rows. The following example restricts the returned rows to 10:

SELECT * FROM EMP WHERE ROWNUM <= 10;

As a ROWNUM value is assigned near the end of processing a query, even the same SELECT statement can have different results depending on the internal steps of processing the query. For example, a decision by the query optimizer of whether to use an index may have a different result.

The ORDER BY clause can be used to get same results from any query that includes the ROWNUM. However, all subqueries including the WHERE clause are executed, and then the ORDER BY clause is executed in Tibero. Therefore, the ORDER BY clause can also bring different results.

For example, the following query gets different results every time it is executed:

SELECT * FROM EMP WHERE ROWNUM <= 10 ORDER BY EMPNO;

If the above query is changed to the following, the results are always identical because the ORDER BY clause is executed first:

SELECT * FROM (SELECT * FROM EMP ORDER BY EMPNO)
WHERE ROWNUM <= 10;

The following SELECT statement returns no rows:

SELECT * FROM EMP WHERE ROWNUM > 1;

The reason the above statement returns no rows is that the conditional expression for ROWNUM is executed before the ROWNUM value is fixed. The conditional expression is not satisfied because the ROWNUM of the first row is 1, therefore the first row is not returned. Since the conditional expression is not satisfied, the value of the ROWNUM counter is not incremented. Therefore, the ROWNUM of the second row is also 1 and the second row will not be returned. In the end, no results are returned.

LEVEL is the column type for outputting hierarchies of each row in a tree after executing a hierarchical query. The LEVEL value of the top row is 1, and this value is increased by one for each lower row. Output of hierarchical queries and LEVEL column values are described in “5.5. Hierarchical Queries”.

The CONNECT_BY_ISLEAF pseudo column returns "1" if a current row is a leaf of a tree defined by the CONNECT BY condition or "0" if it is not. This information determines whether the row can be extended to show its hierarchy.

The following example illustrates the use of the CONNECT_BY_ISLEAF pseudo column.

SQL> SELECT ENAME, CONNECT_BY_ISLEAF, LEVEL, SYS_CONNECT_BY_PATH(ENAME,'-') "PATH"
     FROM EMP2
       START WITH ENAME = 'Clark'
       CONNECT BY PRIOR EMPNO = MGRNO
       ORDER BY ENAME;

ENAME           CONNECT_BY_ISLEAF LEVEL      PATH
--------------- ----------------- ---------- -----------------------
Alicia                          1          3 -Clark-Martin-Alicia
Allen                           1          3 -Clark-Ramesh-Allen
Clark                           0          1 -Clark
James                           1          3 -Clark-Martin-James
John                            0          3 -Clark-Ramesh-John
Martin                          0          2 -Clark-Martin
Ramesh                          0          2 -Clark-Ramesh
Ward                            1          4 -Clark-Ramesh-John-Ward

The CONNECT_BY_ISCYCLE pseudo column is used in a hierarchical query. It determines if the row has a child node, and also if the child node can become a parent node of the row. After determining whether or not a loop between the parent node and its child node is possible, the column returns "1" if the row has such a child node or "0" if it does not.

This pseudo column can be used only when a NOCYCLE statement is specified in the CONNECT BY condition. If the NOCYCLE statement is specified, an error will not occur even when a loop occurs.

The following example illustrates the use of the CONNECT_BY_ISCYCLE pseudo column.

SQL> SELECT ENAME, CONNECT_BY_ISCYCLE, LEVEL FROM EMP
     START WITH ENAME = 'Alice'
     CONNECT BY NOCYCLE PRIOR EMPNO = MGRNO
     ORDER BY ENAME;

ENAME           CONNECT_BY_ISCYCLE LEVEL
--------------- ------------------ ----------
Alice                            0          1
Smith                            1          2
Micheal                          0          3
Viki                             0          2
Jane                             1          2
Jacob                            0          4

If an argument of any constant function except for REPLACE, NVL, and CONCAT is NULL, the return value is also NULL. If the NVL function is used, a non-NULL value can be returned. If a column value is NULL, 0 will be returned, and if the value is non-NULL, the column value will be returned. Most aggregate functions ignore NULL.

The following example illustrates the use of the AVG function with data including NULL:

DATA = {1000, 500, NULL, NULL, 1500}
AVG(DATA) = (1000 + 500 + 1500) /3 = 1000 

There are two comparison conditions, IS NULL and IS NOT NULL, which can check NULL. Because NULL means that there is no data, NULL cannot be compared with NULL or other values. However, two NULLs can be compared with the DECODE function.

SQL> SELECT DECODE(NULL, NULL, 1) FROM DUAL;

DECODE(NULL,NULL,1)
-------------------
                  1

In the above example, NULL is compared with NULL using the DECODE function. The result is 1, which means the two values are the same.

If other comparison conditions are used for NULL, the result will be UNKNOWN. In the most cases, UNKNOWN is handled as FALSE. For example, if a WHERE clause in a SELECT statement has a condition judged as UNKNOWN, no row is returned. In the case of UNKNOWN and FALSE, although another operator is used with the UNKNOWN condition, the result will be always UNKNOWN.

The following shows the difference between results of using the NOT operator for FALSE and for UNKNOWN.

NOT FALES = TRUE
NOT UNKNOWN = UNKNOWN

Hints about query transformations influence the query transformation method of Tibero. Only the NO_MERGE hint can currently be used.

NO_QUERY_TRANSFORMATION

Instructs the query transformer to avoid all query changes. Tibero

NO_MERGE

Instructs the query transformer not to merge specific views. Views are merged by default in Tibero. The merged view forms a single query block by combining with an upper query block. However, the NO_MERGE hint prevents views from being merged.

UNNEST

Instructs the query transformer to unnest specific subqueries. Subqueries are unnested by default in Tibero. To unnest only a specific query, disable the unnesting function with the initialization parameter and use the UNNEST hint by specifying it in the desired subquery block.

NO_UNNEST

Instructs the query transformer not to unnest specific subqueries. Subqueries are unnested by default in Tibero. If subqueries can be unnested, the subqueries will be transformed by joining. At this time, the NO_UNNEST hint prevents subqueries from being unnested. This hint is specified in a subquery block.

NO_JOIN_ELIMINATION

Instructs the query transformer not to eliminate unnecessary joins. Joins that are needed to generate query results are eliminated by default in Tibero . This can be prevented by using the NO_JOIN_ELIMINATION hint.

The handling process and result output can be optimized using optimizer mode hints. If a query uses an optimizer mode hint, the query will be handled as if it has no statistics information or values for the optimizer mode for initialization parameters.

ALL_ROWS

The ALL_ROWS mode instructs the optimizer to optimize the method for the handling process to maximize result throughput with minimum resources.

FIRST_ROWS

The FIRST_ROWS mode instructs the query optimizer to optimize the method to provide the fastest response for the output result for the first n rows, where n is provided via a parameter.

Hints about the access mode instruct the query optimizer to use a specific access mode if possible. If the mode is not possible to use, the optimizer ignores the hint.

The table name specified in the hint must be the same as the one used in the SQL statement. If an alias is used for a table, the table alias must be used instead of the table name. Even if a table name in an SQL statement includes a schema name, only the table name is necessary in the hint.

FULL

The FULL hint instructs the query optimizer to perform a full table scan when a given table is scanned. Even if there is an index which meets the conditional expression specified in a WHERE clause, a full table scan is executed.

INDEX

The INDEX hint instructs the query optimizer to perform an index scan using a specified index when a given table is scanned.

NO_INDEX

The NO_INDEX hint instructs the optimizer not to perform an index scan when a given table is scanned. If NO_INDEX and INDEX, INDEX_ASC, or INDEX_DESC specify the same index, the query optimizer ignores both hints.

INDEX_ASC

The INDEX_ASC hint instructs the query optimizer to perform an index scan using a specified index when a given table is scanned. If an index scan is used, the index is scanned in ascending order. As the default order for index scans in Tibero is already by ascending order, INDEX_ASC returns the same result as INDEX.

INDEX_DESC

The INDEX_DESC hint instructs the query optimizer to perform an index scan using a specified index when a given table is scanned. If an index scan is used, the index is scanned in descending order. In the case of a partitioned index, each partition is scanned in descending order.

INDEX_FFS

The INDEX_FFS hint instructs the query optimizer to perform a fast full index scan using a specified index for a specified table.

NO_INDEX_FFS

The NO_INDEX_FFS hint instructs the query optimizer not to perform a fast full index scan using a specified index for a specified table.

INDEX_RS

Instructs the query optimizer to perform a range index scan using a specified index.

NO_INDEX_RS

Instructs the query optimizer not to perform a range index scan using a specified index.

INDEX_SS

Instructs the query optimizer to perform an index skip scan using a specified index.

NO_INDEX_SS

Instructs the query optimizer not to perform an index skip scan using a specified index.

The hints about join order, LEADING and ORDERED, determine the join order. Because the LEADING hint gives more options to the query optimizer than the ORDERED hint does, using the LEADING hint rather than the ORDERED hint is recommended.

LEADING

The LEADING hint Instructs the query optimizer to use a specified set of tables that must be joined first. If the LEADING hint includes a table that cannot be joined first, the hint is ignored. If two or more LEADING hints conflict, all LEADING and ORDERED hints are ignored. If the ORDERED hint is used, all LEADING hints are ignored.

ORDERED

The ORDERED hint instructs the query optimizer to join tables in the order specified in a FROM clause. The query optimizer has information about the size of a result set of joins. It is recommended to use this hint only when a user clearly understands the join order from information provided by the query optimizer.

Hints about the join mode change the mode for joining a table. These hints are only available when the specified table is used as the inner table for joining. If the specified table is used as an outer table, the hints are ignored.

USE_NL

The USE_NL hint instructs the query optimizer to use a nested loop join when a specified table is joined with other tables.

NO_USE_NL

Instructs the query optimizer not to use a nested loop join when a specified table is joined with other tables. However, in certain circumstances, the query optimizer can still create a plan that uses nested loop joins.

USE_NL_WITH_INDEX

Instructs the query optimizer to use a nested loop join when a specified table is joined with another table. The specified table should be accessed using a specified index and join conditions for the two tables. If the index cannot be used, the hint is ignored.

USE_MERGE

Instructs the query optimizer to use a merge join when a specified table is joined with another table.

NO_USE_MERGE

Instructs the query optimizer not to use a merge join when a specified table is joined with another table.

USE_HASH

Instructs the query optimizer to use a hash join when a specified table is joined with another table.

NO_USE_HASH

Instructs the query optimizer not to use a hash join when the specified table is joined with another table.

MERGE_SJ

Instructs the query optimizer to use merge semi-join when unnesting a subquery.

MERGE_AJ

Instructs the query optimizer to use merge anti-join when unnesting a subquery.

NL_SJ

Instructs the query optimizer to use nested rule semi-join when unnesting a subquery.

NL_AJ

Instructs the query optimizer to use nested rule anti-join when unnesting a subquery.

PARALLEL

Instructs the query optimizer to use a specified number of threads to execute queries in parallel.

NO_PARALLEL

Instructs the query optimizer not to execute queries in parallel.

PQ_DISTRIBUTE

Instructs the query optimizer to use a specific distribution method for rows to be joined during parallel processing of queries. The methods are HASH-HASH, BROADCAST-NONE, NONE-BROADCAST, and NONE-NONE. Selecting a specific distribution method can enhance the performance of a join during parallel processing.

REWRITE

Instructs the query optimizer to rewrite a query using a materialized view in the query block without comparing costs. The optimizer compares the result of rewriting the query block with the REWRITE hint with the result of rewriting all blocks and selects the better option. When a list of materialized views is specified, a query rewrite is only attempted on the materialized views in the list.

NO_REWRITE

Instructs the query optimizer not to rewrite a query in the query block.

APPEND

Enables Direct-Path, which is a method that inserts into a data file directly from a DML statement. Unlike other insert methods, Direct-Path inserts data by always being allocated a new data block and inserts data without using a buffer cache, which enhances performance.

APPEND_VALUES

Instructs the query optimizer to execute the direct-path method, which writes directly to a data file in DML statements. Unlike general insert methods, the Direct-Path method always inserts data with newly allocated data blocks and directly inserts data files without using the buffer cache. This method has a performance advantage when used for batch inserts.

NOAPPEND

Instructs the query optimizer not to use the Direct-Path method for DML statements.

IGNORE_ROW_ON_DUPKEY_INDEX

This hint is only available for single table insert statements. If an insert violates a unique constraint, the row is rolled back and an insert is performed starting in the next row without generating an error. An error is generated when an index is not defined, multiple indexes are defined, or a defined index does not have unique property. If a hint is defined, the APPEND and PARALLEL hints are ignored.

CARD

Instructs the optimizer to use cardinality for optimizing a query.

MONITOR

Instructs the optimizer to collect query execution information.

NO_MONITOR

Instructs the optimizer not to collect query execution information.

USE_CONCAT

Forces to create an OR expansion plan by concatenating combined OR conditions in a clause to a compound query with UNION ALL.

NO_EXPAND

Forces to create a plan with an OR condition by preventing the optimizer from performing OR expansion.

A Table is the basic storage unit of a relational database. All other schema objects are defined with tables as the basic unit. A table has the form of a two-level matrix. A table consists of one or more columns, and each column has its own data type. A table includes zero or more rows, and each row has a value for each column.

The following example shows a table that has information about employees:

     EMPNO ENAME        ADDR                 SALARY     DEPTNO
---------- ------------ ---------------- ---------- ----------
        35 John         Houston               30000          5
        54 Alicia       Castle                25000          4
        27 Ramesh       Humble                38000          5
        69 James        Houston               35000          4

The above table consists of five columns (EMPNO, ENAME, ADDR, SALARY, and DEPTNO) and four rows. Column information that defines a table is rarely changed, but the number of rows included in a table can be changed at any time.

For some column data types, like the string type, the maximum length needs to be set, and for some column types, like the NUMBER type, precision and scale need to be set. A default value can be set for some columns. For more information about data types, refer to “2.1. Data Types”.

Integrity constraints can be declared for an entire table or for individual columns. All rows inserted in a table must satisfy the integrity constraints. For example, the following constraint can be declared for the column SALARY in the table EMP:

SALARY >= 0

As a salary of an employee cannot be less than 0, the above condition prevents incorrect data from being entered. Conditional expressions to declare integrity constraints are described in “3.4. Conditional Expressions”.

An integrity constraint can be declared for one table, and a referential integrity constraint can be declared between two tables.

The following example shows the DEPT table that contains information about departments:

    DEPTNO DNAME        LOC
---------- ------------ ----------------
         1 Accounting   Houston
         4 Research     Spring
         5 Sales        Houston

If each employee must be a member of a particular department, the values of the column DEPTNO in the table EMP in [Example 2.1] must match one of the values of the column DEPTNO in the table DEPT in [Example 2.2].

An index is the data structure that enables searching a particular column in a table rapidly by using a separate storage space from the table. A table owner can create one or more indexes for a particular column.

Descriptions of indexes are as follows:

The purpose of a view is to represent frequently executed queries in a way that is similar to a table. A view is defined using a table or another view and can be used like a general table within a SQL statement. The table where views are defined is called a base table.

As a table can be accessed by multiple users, views can be defined to secure information by hiding some parts of the table information from particular users.

Descriptions of views follow:

A sequence is a schema object that generates unique sequential values. The value is generally used for a primary key or a unique key. In a sequence name, a pseudo column name is always attached.

SQL statements read the sequence value through the following pseudo columns:

The following example illustrates the use of a sequence with pseudo columns:

seq1.currval
seq2.nextval

The following describes the availability of sequence pseudo columns according to the location in an SQL statement:

When a sequence is created, its initial value, incremental value, and decremental value are defined. When a sequence is accessed for the first time through the pseudo column NEXTVAL, the sequence returns the initial value. Whenever NEXTVAL is used, the value of the sequence is increased by the incremental value and the newly increased value is returned. The pseudo column CURRVAL always returns the current value of the sequence, and this value is the same as the value returned by the last use of NEXTVAL.

To use the pseudo column CURRVAL, initialize this column using NEXTVAL at least one time. When the pseudo column NEXTVAL is used in a SQL statement, it increases the sequence value by the number of rows handled by the SQL statement.

Rows which increase sequence values are:

If more than one NEXTVAL appears in the same row, the sequence value is increased only once in the first location, and the value will be used in the following locations.

If NEXTVAL and CURRVAL appear in the same row, the sequence value is increased once, and the value returned by NEXTVAL will be used in CURRVAL.

A synonym is an alias defined for a particular schema object. A synonym is usually defined for a schema object which has a particular purpose or has a long name.

Descriptions of synonyms follow:

A user has to give a name to each schema object or to the whole schema objects by the objects such as table, table column, index, integrity constraint, table partition, table subpartition, index partition, index subpartition, package, and functions and procedures in the package.

To specify the name of a schema object in SQL, use an identifier with double quotes or an identifier without double quotes.

Any identifier can be used to name a schema object. An identifier with double quotes is case sensitive while an identifier without double quotes is not case sensitive and treats all letters as capital letters.

For example, the following are all different identifiers:

department
"department"
"Department"

The following are all the same identifier:

department
DEPARTMENT
"DEPARTMENT"

Identifiers must comply with the following rules:

This section describes how to specify a schema object and components of the object using SQL statements.

Detailed descriptions of the syntax that specifies schema objects follow:

How to search for a schema object

The process to search for a schema object specified in an SQL statement is as follows:

The following illustrates the above process:

SELECT * FROM employees;

The above SQL statement is entered. The following numbers represent each step of the above search process.

How to search an object in a remote database

To specify an object in a remote database, specify the object name with the at symbol (@) and a database link. A database link is a schema object that enables access to a remote database. For more information about how to create a database link, refer to “7.25. CREATE DATABASE LINK”.

The process to search for an object when a database link is used in a SQL statement is as follows: