Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 120 121 122 123 124 125 < 126 > 127 128 129 130 131 132 .. 241 >> Next

Teradata SQL Syntax Guide

CREATE DATABASE

The CREATE DATABASE statement does not include the PASSWORD and STARTUP clauses or the DEFAULT DATABASE and COLLATION options. Because these clauses and options affect the session environment (and only a user can establish a session), they do not apply to a database.

8-58

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE INDEX

CREATE INDEX

The CREATE INDEX statement creates a new secondary index on an existing table. The index can be optionally be given a name.

CREATE INDEX is an extension to ANSI. It is flagged if the SQL flagger is enabled.

Syntax

CREATE

INDEX-|--------p

—I I—name-I

¦ UNIQUE

-( — col_name --)^-ON — tname

UT

FF07A011

where:

Syntax Element... Description
UNIQUE Specifies that no two rows in the table can have the same value or combination of values in the indexed fields.
name name is optional, and specifies the name you assign to the UNIQUE or non-UNIQUE secondary INDEX (30 characters maximum).
col_name Names one or more columns whose field values are to be indexed. If more than one column is named, the new index is based on the combined values of each column. A maximum of 16 columns can be defined for one index.
tname Identifies the table for which an index is to be created.

To create new secondary indexes via CREATE INDEX, the user Privileges must have the INDEX or DROP TABLE privilege on the named

table.

When CREATE INDEX is executed, an EXCLUSIVE lock is placed Locks on the table that contains the index column(s). The time required

depends on the number of rows in the table.

A maximum of 32 secondary indexes can be defined for one table Maximum Number of and in one CREATE INDEX statement. This permits all the index

Indexes operations for one table to be treated as a single transaction; that is,

Teradata RDBMS for UNIX SQL Reference

8-59
Teradata SQL Syntax Guide

CREATE INDEX

Secondary Indexes Require Subtable Creation, Which Takes Space

Secondary Indexes Can Shorten Search Time

UNIQUE Versus NonUNIQUE

EXPLAIN Helps To Analyze Each Secondary Index

Named Indexes

all the indexes are created, or none of them are created. Up to 16 columns can be defined for each index.

Alternatively, secondary indexes can be created as part of the CREATE TABLE statement.

Secondary indexes require the creation of subtables in which to store index values and associated row IDs. Each secondary index subtable requires additional disk space, and insert, delete, and update operations require more processing time when secondary index subtables also need to be changed.

Secondary index values can shorten table search time. Each row in a secondary index subtable is made up of the index value and one or more row IDs that point to the data row or rows containing that value. Therefore, when secondary index values are used as conditional expressions in SQL statements, only the row or rows that contain the specified value are accessed.

The UNIQUE option prevents the occurrence of duplicate index values. Only one row can have a value that corresponds to an entry in the subtable of a unique secondary index. The subtable of a unique secondary index is a hashed table, which is very efficient for retrieving a single row.

When the values of non-unique secondary indexes are used to apply complex conditional expressions to very large tables, efficient retrieval is achieved by bit mapping the subtable row IDs.

Before a request is processed, the Teradata RDBMS estimates comparative costs to determine whether the use of a secondary index offers savings or is plausible. If not, a full-table search is used. It is advisable, therefore, to test the performance of a secondary index before implementing it.

The EXPLAIN modifier can be used to obtain a description of the processing sequence and thus determine whether a secondary index should be retained.

The index name must conform to the usual rules for a Teradata SQL identifier and be unique in the table.

See also “DROP INDEX”.

Refer also to the examples below.

8-60

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE INDEX

Examples

Example 1

Example 2: Named Indexes

Example 3: Creating a Named Unique Secondary Index

Example 4: Creating a Named Non-Unique Secondary Index

Example 5: Creating an Unnamed Unique Secondary Index Example 6: Creating an Unnamed Non-Unique Secondary index Example 7: Non-Valid Use of CREATE INDEX

The following examples illustrate the use of CREATE INDEX.

The following statement can be used to create a secondary index on the DeptNo column of the Employee table:

CREATE INDEX (DeptNo) ON Employee;

The UNIQUE option is not used in this statement because multiple rows will contain the same department number. The index should be useful, however, because department numbers are often used for conditional retrievals and for ordering employee listings.
Previous << 1 .. 120 121 122 123 124 125 < 126 > 127 128 129 130 131 132 .. 241 >> Next