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 .. 142 143 144 145 146 147 < 148 > 149 150 151 152 153 154 .. 241 >> Next


8-127
Teradata SQL Syntax Guide

DROP DATABASE, DROP USER

The following statement can be used to drop user Jones:

Example

DROP USER Jones;

8-128

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP INDEX

DROP INDEX

Function

The DROP INDEX statement deletes a secondary index on a table.

DROP INDEX is flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

DROP INDEX

where:

(-L col name L )

( — co_name—) -p ON — tname -name

FF07A053

Syntax Element... Specifies the name of the. . .
col_name columns that constitute the index.
name index to be dropped.
tname table on which the secondary index is to
be dropped.

Privileges

To drop an index on a table, the user must have the P INDEX or DROP TABLE privilege on the table.

Why You Might Drop an Index

A secondary index is normally dropped after usage patterns for a table have changed and the index is no longer used.

Another reason to DROP a UNIQUE SECONDARY INDEX is so that a MultiLoad can be performed on the table; after the MultiLoad completes, the index is recreated.

How a DROP Functions

The drop operation performs the following functions.

Stage

2

Process

Sets an exclusive lock on the table that contains the index.

Frees the disk space that was used by the index subtable.

A named index is dropped either by specifying its name or its definition (but not both).

1

Teradata RDBMS for UNIX SQL Reference

8-129
Teradata SQL Syntax Guide

DROP INDEX

If an index has been created in the form of an unnamed UNIQUE UNIQUE Constraints constraint, it may only be removed by a DROP INDEX statement

referencing the column or columns defined for the constraint.

The following examples illustrate the use of DROP INDEX:

Examples

The following statement drops a secondary index that was defined Example I on the Name column of the Employee table:

DROP INDEX (Name) ON Employee;

This statement drops the existing named index index_1 from table Example 2: Dropping table_5.

Named Indexes

DROP INDEX index_1 ON table 5

This statement drops the existing index on columns field_1 and Example 3 field_2 from table table_6.

DROP INDEX (field_1, field_2)

ON table 6

The following example demonstrates a non-valid use of dropping

Example 4: Non-Valid an index.

DROP INDEX index_1 (field_1, field_2)

ON table 8

8-130

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP MACRO, TABLE, or VIEW

Function

Syntax

Privileges

DROP Functions

DROP MACRO, TABLE, or VIEW

The DROP MACRO, TABLE, or VIEW statement removes a table and all of its rows, or a view definition, or a macro definition from a database.

DROP MACRO, TABLE or VIEW are flagged as non-ANSI, when the SQL flagger is enabled. MACRO’s are flagged as non-ANSI.

DROP

MACRO --TABLE --VIEW -

name

where: FF07A020
Syntax Element... Specifies . . .
tname the name of the table, view, or macro to be dropped.

To use the DROP MACRO, TABLE, or VIEW statement, a user must have the DROP privilege on the referenced table, view, or macro.

The drop operation performs the following functions.

Stage Process
1 Sets an exclusive lock on the table, view, or macro.
2 Frees the disk space used by the dropped table, any secondary index subtables, and any fallback copy for use by the owner database.
3 Removes any explicit access rights on the object.

If a Table or View that is referenced by existing Views or Macros is Referenced °bjects dropped, the Views or Macros continue to exist after the object has

been dropped unless they are also explicitly dropped. Attempts to use them fail.

If a Table or View of the same name is then created, these Views and Macros operate and give correct results only if the new Table or

Teradata RDBMS for UNIX SQL Reference

8-131
Teradata SQL Syntax Guide

DROP MACRO, TABLE, or VIEW

Example

View has the same structure as that dropped. Otherwise, they may fail or provide unexpected results.

If a table named Profile was created for temporary use, it could be dropped with the statement:

DROP TABLE Profile;

8-132

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP STATISTICS

DROP STATISTICS

Function

The DROP STATISTICS statement drops the statistical data that was created for one or more columns of a table by a COLLECT STATISTICS statement.

Syntax

DROP STATISTICS is flagged as non-ANSI, when the SQL flagger is enabled.

Lon-I

— COLUMN — col_name —

—INDEX -|-( -- co_name -L)-I-------------name------

TTTT

FF07A021

where:

Syntax Element... Specifies . . .
tname the name of the table on which statistics are to be dropped.
COLUMN that statistics are to be dropped for a single column.
col_name the name of the column for which statistics are to be dropped.
INDEX that the statistics to be dropped are for one or more columns that comprise an index on the table.
Previous << 1 .. 142 143 144 145 146 147 < 148 > 149 150 151 152 153 154 .. 241 >> Next