Tibero
		Next

Tibero SQL Reference Guide

Tibero 6

Copyright © 2020 TmaxData Co., Ltd. All Rights Reserved.

Restricted Rights Legend

All TmaxData Software (Tibero®) and documents are protected by copyright laws and international convention. TmaxData software and documents are made available under the terms of the TmaxData License Agreement and this document may only be distributed or copied in accordance with the terms of this agreement. No part of this document may be transmitted, copied, deployed, or reproduced in any form or by any means, electronic, mechanical, or optical, without the prior written consent of TmaxData Co., Ltd. Nothing in this software document and agreement constitutes a transfer of intellectual property rights regardless of whether or not such rights are registered) or any rights to TmaxData trademarks, logos, or any other brand features.

This document is for information purposes only. The company assumes no direct or indirect responsibilities for the contents of this document, and does not guarantee that the information contained in this document satisfies certain legal or commercial conditions. The information contained in this document is subject to change without prior notice due to product upgrades or updates. The company assumes no liability for any errors in this document.

Trademarks

Tibero® is a registered trademark of TmaxData Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies.

Open Source Software Notice

Some modules or files of this product are subject to the terms of the following licenses. : OpenSSL, RSA Data Security, Inc., Apache Foundation, Jean-loup Gailly and Mark Adler, Paul Hsieh's hash

Detailed Information related to the license can be found in the following directory : ${INSTALL_PATH}/license/oss_licenses

Document Information

Title: Tibero SQL Reference Guide

Publication Date: 2020-03-13

Software Version: Tibero 6 (FixSet07)

Edition: v2.1.7.4

Table of Contents

About This Document

1. Introduction to SQL

1.1. Overview

1.2. SQL Standard

1.3. SQL Statement Types

1.3.1. Data Definition Language
1.3.2. Data Manipulation Language
1.3.3. Transaction and Session Management Language

2. SQL Elements

2.1. Data Types

2.1.1. String
2.1.2. Number
2.1.3. Datetime
2.1.4. Interval
2.1.5. Large Object
2.1.6. Embedded
2.1.7. User-Defined

2.2. Data Type Conversions

2.2.1. Explicit Type Conversion
2.2.2. Implicit Type Conversion

2.3. Literals

2.3.1. String
2.3.2. Numeric
2.3.3. Datetime
2.3.4. Interval

2.4. Format Strings

2.4.1. NUMBER Types
2.4.2. Datetime Types
2.4.3. Format Modifiers

2.5. Pseudo Columns

2.5.1. ROWID
2.5.2. ROWNUM
2.5.3. LEVEL
2.5.4. CONNECT_BY_ISLEAF
2.5.5. CONNECT_BY_ISCYCLE

2.6.1. NULL in SQL Functions
2.6.2. Comparison Condition for NULL

2.7. Comments

2.8.1. Query Transformations
2.8.2. Optimizer Modes
2.8.3. Access Modes
2.8.4. Join Orders
2.8.5. Join Modes
2.8.6. Parallel Processing
2.8.7. Materialized Views
2.8.8. Others

2.9. Schema Objects

2.9.1. Tables
2.9.2. Indexes
2.9.3. Views
2.9.4. Sequences
2.9.5. Synonyms
2.9.6. Schema Object Names
2.9.7. Syntax for Schema Objects

3. SQL Operations

3.1. Overview

3.2. Operators

3.2.1. General Operators
3.2.2. Operators Used in Conditional Expressions

3.3. Expressions

3.3.1. Expression Conversion
3.3.2. Simple Expressions
3.3.3. Compound Expressions
3.3.4. CASE Expressions
3.3.5. Functions
3.3.6. Subquery Expressions
3.3.7. Variables
3.3.8. List

3.4. Conditional Expressions

3.4.1. Simple Conditions
3.4.2. Group Conditions
3.4.3. Compound Conditions
3.4.4. BETWEEN Condition
3.4.5. EXISTS Condition
3.4.6. IN Condition
3.4.7. IS NULL Condition
3.4.8. LIKE Condition
3.4.9. REGEXP_LIKE Condition

4. Functions

4.1. Overview

4.1.1. Single Row Functions
4.1.2. Aggregate Functions
4.1.3. Analytic Functions

4.2. Function List

5. SQL Queries

5.1. SELECT

5.2.1. Join Conditions
5.2.2. Equi Joins
5.2.3. Self Joins
5.2.4. Inner Joins
5.2.5. Outer Joins
5.2.6. Anti-joins
5.2.7. Semi-joins

5.3. Subqueries

5.4. Set Operators

5.5. Hierarchical Queries

5.5.1. Hierarchical Query Operators
5.5.2. Conditional Expressions in a Hierarchical Query
5.5.3. How to Execute a Hierarchical Query

5.6. Parallel Query

5.7. Dual Table

6. Materialized Views

6.1. Refresh

6.1.1. Complete Refresh
6.1.2. Fast Refresh

6.2. Query Rewrite

6.2.1. Enabling Query Rewrite
6.2.2. Rewrite Methods
6.2.3. Cost-Based Optimization

6.3. Materialized View with Remote Storage

6.3.1. Tasks before Creating a Materialized View
6.3.2. Creating a Materialized View
6.3.3. Refreshing and Querying a Storage Table
6.3.4. Constraints

7. Data Definition Language

7.1. Common DDL Syntax Elements

7.1.1. Constraints
7.1.2. Constraint_state
7.1.3. Deferrable_option
7.1.4. Sgmt_attr
7.1.5. Storage_clause

7.2. ALTER DATABASE

7.3. ALTER DISKSPACE

7.4. ALTER FUNCTION

7.5. ALTER INDEX

7.6. ALTER MATERIALIZED VIEW

7.7. ALTER MATERIALIZED VIEW LOG

7.8. ALTER PACKAGE

7.9. ALTER PROCEDURE

7.10. ALTER PROFILE

7.11. ALTER ROLE

7.12. ALTER ROLLBACK SEGMENT

7.13. ALTER SEQUENCE

7.14. ALTER TABLE

7.15. ALTER TABLESPACE

7.16. ALTER TRIGGER

7.17. ALTER TYPE

7.18. ALTER USER

7.19. ALTER VIEW

7.20. AUDIT

7.21. COMMENT

7.22. CREATE CONTEXT

7.23. CREATE CONTROLFILE

7.24. CREATE DATABASE

7.25. CREATE DATABASE LINK

7.26. CREATE DIRECTORY

7.27. CREATE DISKSPACE

7.28. CREATE FUNCTION

7.29. CREATE INDEX

7.30. CREATE MATERIALIZED VIEW

7.31. CREATE MATERIALIZED VIEW LOG

7.32. CREATE OUTLINE

7.33. CREATE PACKAGE

7.34. CREATE PACKAGE BODY

7.35. CREATE PROCEDURE

7.36. CREATE PROFILE

7.37. CREATE ROLE

7.38. CREATE SEQUENCE

7.39. CREATE SYNONYM

7.40. CREATE TABLE

7.41. CREATE TABLESPACE

7.42. CREATE TYPE

7.43. CREATE TYPE BODY

7.44. CREATE TRIGGER

7.45. CREATE USER

7.46. CREATE VIEW

7.47. DROP DATABASE LINK

7.48. DROP DIRECTORY

7.49. DROP DISKSPACE

7.50. DROP FUNCTION

7.51. DROP INDEX

7.52. DROP MATERIALIZED VIEW

7.53. DROP MATERIALIZED VIEW LOG

7.54. DROP OUTLINE

7.55. DROP PACKAGE

7.56. DROP PROCEDURE

7.57. DROP PROFILE

7.58. DROP ROLE

7.59. DROP SEQUENCE

7.60. DROP SYNONYM

7.61. DROP TABLE

7.62. DROP TABLESPACE

7.63. DROP TRIGGER

7.64. DROP TYPE

7.65. DROP TYPE BODY

7.66. DROP USER

7.67. DROP VIEW

7.68. EXPLAIN PLAN

7.69. FLASHBACK TABLE

7.70. GRANT

7.71. LOCK TABLE

7.72. NOAUDIT

7.73. PURGE

7.74. RENAME

7.75. REVOKE

7.76. TRUNCATE TABLE

8. Data Manipulation Language

8.1. INSERT

8.2. UPDATE

8.3. DELETE

8.6. Parallel DML

8.6.1. Execution Methods
8.6.2. Constraints

9. Transaction and Session Management Language

9.1. ALTER SESSION
9.2. ALTER SYSTEM
9.3. COMMIT
9.4. ROLLBACK
9.5. SAVEPOINT
9.6. SET ROLE
9.7. SET TRANSACTION

A. Reserved Words

A.1. A
A.2. B
A.3. C
A.4. D
A.5. E
A.6. F
A.7. G
A.8. H
A.9. I
A.10. L
A.11. M
A.12. N
A.13. O
A.14. P
A.15. R
A.16. S
A.17. T
A.18. U
A.19. V
A.20. W

List of Figures

[Figure 2.1] ROWID Structure
[Figure 3.1] Example of a Syntax Diagram for an Expression
[Figure 5.1] EMP2 Table Hierarchy

List of Tables

[Table 2.1] Explicit Type Conversion (1)
[Table 2.2] Explicit Type Conversion (2)
[Table 2.3] Explicit Type Conversion (3)
[Table 2.4] Implicit Type Conversion (1)
[Table 2.5] Implicit Type Conversion (2)
[Table 2.6] Implicit Type Conversion (3)
[Table 2.7] Type Comparison (1)
[Table 2.8] Type Comparison (2)
[Table 2.9] Type Comparison (3)
[Table 2.10] Type Comparison (4)
[Table 2.11] Type Comparison (5)

List of Examples

[Example 2.1] EMP Table
[Example 2.2] DEPT Table

		Next
		About This Document

Copyright © 2020 TmaxData Co., Ltd. All Rights Reserved.