1. Introduction to Altibase SQL

This chapter describes the overview, classification, and structure of SQL for using Altibase.

Overview of Altibase SQL

Structured Query Language (SQL) is a language for defining data objects and managing, manipulating and searching data in a database. This section describes the characteristics of Altibase SQL.

Altibase SQL Features

Superior Query Performance

Altibase creates an optimized execution plan tree when it is preparing a SQL statement. This is because the system catalog information for most queries does not change between the preparation and execution of the statement. The execution plan tree can greatly improve execution speed when the query is repeatedly executed. This is useful for applications that frequently execute Data Manipulation Language (DML) statements but seldom execute Data Definition Language (DDL) statements.

Support for SQL-92

Altibase SQL fully supports SQL-92. Users familiar with SQL can easily use Altibase.

Powerful Subquery Support

Subqueries are usually used in expressions for SELECT statements, CREATE TABLE ~ AS SELECT statements, and expressions and IN clauses for INSERT~ AS SELECT statements as well. Subqueries that are used like this mainly return multiple records from multiple columns.

If the subquery result is a single value (a single record from a single column), it can be used as a constant. It is more efficient to use subqueries than multiple SQL statements, as subqueries run faster. Complicated applications should use subqueries.

Support for Various System Functions

Altibase supports a wide variety of system functions, apart from SQL-92.

Query Optimizer and Execution Plans

SQL statements need to be written efficiently to enhance query performance. Understanding how Altibase processes SQL statements will help users to write optimized SQL statements.

Altibase processes a SQL statement in the following two steps:

• Preparation
  This step analyzes SQL statement syntax, checks its validity, optimizes it, and then creates an execution plan tree. While doing so, Altibase accesses meta tables and reads information about tables and indexes to create an optimized access plan.
  If the client performs the preparation and execution steps separately (instead of directly executing the SQL statement), the meta information should not have been changed after preparation to use the execution plan tree that was created at preparation. For example, if an index that existed at preparation no longer exists at execution, the optimized execution plan tree becomes invalid and cannot be used.
• Execution
  This step executes the query statement according to the execution plan that was created at preparation. If the client prepares a SQL statement using host variables and repeatedly executes the statement by changing the host variable values, Altibase prepares the statement only once but sets variable values and executes the statement multiple times.

Types of SQL Statements

Altibase supports SQL statements of the following categories:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)

This chapter briefly introduces each SQL statement.

For more detailed information about their use, please refer to Chapter 3: Data Definition Language (DDL), Chapter 4: Data Manipulation Language (DML), and Chapter 5: Data Control Language (DCL).

Data Definition Language (DDL)

DDL statements define and modify database objects.

SQL Statement	Description
ALTER DATABASE	Changes a database defintion
ALTER INDEX	Changes an index definition
ALTER JOB	Changes a job
ALTER REPLICATION	Changes a replication definition
ALTER SEQUENCE	Changes a sequence definition
ALTER TABLE	Changes a table definition
ALTER TABLESPACE	Changes a tablespace definition
ALTER TRIGGER	Change a trigger definition
ALTER USER	Changes a user password
ALTER VIEW	Recompiles a view
CONJOIN TABLE	Changes non-partitioned table to partition in partitioned table
CREATE DATABASE	Creates a database
CREATE DIRECTORY	Creates a directory object for processing files in stored procedures
CREATE INDEX	Creates an index
CREATE JOB	Create a job
CREATE QUEUE	Creates a queue
CREATE REPLICATION	Creates a replication object
CREATE SEQUENCE	Creates a sequence
CREATE SYNONYM	Creates an alias for an object
CREATE TABLE	Creates a table
CREATE TABLESPACE	Creates a tablespace
CREATE TRIGGER	Creates a trigger
CREATE USER	Creates a user
CREATE VIEW	Creates a view
DISJOIN TABLE	Changes partition to non-partitioned tables
DROP DIRECTORY	Removes a directory object
DROP INDEX	Removes an index
DROP JOB	Removes a job
DROP QUEUE	Removes a queue
DROP REPLICATION	Removes a replication object
DROP SEQUENCE	Removes a sequence
DROP SYNONYM	Removes a synonym
DROP TABLE	Removes a table
DROP TABLESPACE	Remove a tablespace
DROP TRIGGER	Removes a trigger
DROP USER	Removes a user
DROP VIEW	Removes a view
FLASHBACK TABLE	Recovers a table in the recycle bin
GRANT	Grants privileges
PURGE TABLE	Drops a table in the recycle bin
RENAME	Changes a table name
REVOKE	REVOKE Revokes privileges
TRUNCATE TABLE	Removes all rows from a table

[Table 1-1 ] List of Data Definition Languages

These DDL statements change meta information. When a DDL statement is executed, existing transactions are terminated and a new transaction executes the statement, which is then terminated after executing the transaction. DDL statements are SQL statements that are executed as a single transaction. This means that even if autocommit is turned off while a DML statement is executed and the statement remains uncommitted, Altibase commits the previously executed DML statement when a DDL statement is executed. DML transactions that were executed before a DDL statement is executed cannot be rolled back after the DDL statement has been executed.

Data Manipulation Language (DML)

DML statements manipulate data. Unlike DDL statements, they are not automatically committed if autocommit is turned off. Therefore, if a transaction executes multiple DML statements and then calls the ROLLBACK function while autocommit is turned off, the transaction is rolled back.

SQL Statement	Description
DELETE	Removes data
INSERT	Inserts data
LOCK TABLE	Locks a table in a specific mode
SELECT	Retrieves data
UPDATE	Changes data
MOVE	Move data from one table to another
ENQUEUE	Inserts a message into a queue
DEQUEUE	Retrieves a message from a queue and removes it from the queue

[Table 1‑2] List of Data Manipulation Languages

Data Control Language (DCL)

DCL (Data Control Language) statements are used to control data.

Session control statements and transaction control statements only affect each session, not other sessions.

Replication Object Control Statement

SQL Statement	Descrition
ALTER REPLICATION...STOP/FLUSH	Control replication objects

[Table 1‑3] List of Replication Object Control Statement

Database Link Object Control Statement

SQL Statement	Description
ALTER DATABASE LINKER	Starts and terminates a database link

[Table 1‑4] List of Database Link Object Control Statement

System Control Statement

SQL Statement	Description
ALTER SYSTEM	Changes session properties

[Table 1‑5] List of System Control Statement

Session Control Statement

SQL Statement	Description
ALTER SESSION	Changes session properties

[Table 1‑6] List of Session Control Statement

Transaction Control Statements

SQL Statement	Description
COMMIT	Terminates a transaction normally
ROLLBACK or ROLLBACK TO SAVEPOINT savepoint_name	Rolls back an entire transaction or to the point in time specified as save_name
SAVEPOINT savepoint_name	Sets a marker in a transaction
SET TRANSACTION	Starts a read-only/read-write transaction, or changes the ISOLATION LEVEL of a transaction

[Table 1‑7] List of Transaction Control Statements

Audit Control Statements

SQL Statement	Description
AUDIT	Set the auditing conditions
DELAUDIT	Delete the auditing conditions
NOAUDIT	Stop the auditing conditions

[Table 1‑8] List of Audit Control Statements.