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

col_name names of the columns that constitute the index.
name the name of the index to be dropped.

To use the DROP STATISTICS statement for a table, the user must Usage Notes have the DROP TABLE privilege for the table.

DROP STATISTICS is a DDL statement. It must be entered as a single statement request, as the only statement in a macro, or as the last or only statement in a transaction enclosed by BEGIN TRANSACTION and END TRANSACTION statements.

The efficacy of statistics that are collected using COLLECT STATISTICS varies with the types of access used on a table.

DROP STATISTTICS is DDL

Teradata RDBMS for UNIX SQL Reference

8-133
Teradata SQL Syntax Guide

DROP STATISTICS

When to Use DROP (and COLLECT) STATISTICS

If performance does not seem to be improved by the statistics collected, or if the statistics are suspected of being obsolete, and, therefore, misleading, the DROP STATISTICS statement may be used to remove the statistics. Alternatively, another COLLECT STATISTICS statement may be entered to collect new statistics.

If a table has been extensively modified since statistics were last collected for the table, the outdated statistics may cause poor access optimization. Therefore, statistics should be periodically refreshed or dropped.

Once a DROP STATISTICS statement is entered, a COLLECT STATISTICS statement that specifies the COLUMN or INDEX option is required if the user wants to regather statistics for that column or index. The COLUMN or INDEX option must be specified because a COLLECT STATISTICS statement that only lists a table name compiles any statistics that were previously collected for a table.

If an index has a single column, col_name, dropping statistics for Single CoIumn Index = COLUMN col_name is the same as dropping statistics for INDEX

INDEX (col_name) (col_name).

If neither COLUMN nor INDEX is specified, the effect of the statement is to drop any statistics that exist for the table.

The following are rules for using named indexes with the COLLECT and DROP STATISTICS statements:

• You can specify an index name in the DROP STATISTICS statement.

• To COLLECT or DROP STATISTICS from an unnamed index, you must specify the complete index definition.

The following examples illustrate the use of DROP STATISTICS:

Examples

The following statement drops statistics on an index defined on the Example I EmpNo and Name columns of the Employee table:

DROP STATISTICS Employee INDEX(EmpNo,Name);

The following statement drops any statistics that exist for the Example 2 Employee tablet.

DROP STATISTICS Employee;

8-134

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP STATISTICS

Example 3: Named Index

Example 4: Defined Index

Example 5: Non-Valid

Example 6: Non-Valid

The following example drops any statistics on a named index.

DROP STATISTICS ON table_1

INDEX unique_1

The following example drops any statistics on a defined index.

DROP STATISTICS ON table_1

INDEX (field_1, field_2)

This statement is not valid because the index named does not exist for the table.

DROP STATISTICS ON table_1

INDEX no_such_index

The system returns the error message:

The specified constraint name does not exist in table.

This statement is not valid because you cannot have both an index name and an index definition in the same DROP STATISTICS statement.

DROP STATISTICS ON table_1

INDEX unique_1 (field_1, field_2)

The system returns the error message:

Only index name or index column(s) allowed in COLLECT/DROP STATISTICS.

Teradata RDBMS for UNIX SQL Reference

8-135
Teradata SQL Syntax Guide

ECHO

ECHO

The ECHO statement returns a fixed character string to the requestor.

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

Syntax

ECHO —³------------' — string —'-ã

L' — command -,-.- 'J

command -,---------

; FF07R022

where:

Syntax Element... Specifies . . .
‘string' the text to be returned to the requestor.
‘command' a BTEQ command to be returned to BTEQ. The command must be terminated with a semicolon.

The ECHO statement is used primarily in macros to execute most ECHO and Bteq BTEQ commands during macro execution.

The ECHO statement cannot be used with the following BTEQ commands:

EXPORT, IMPORT, LOGOFF, LOGON, QUIT, REPEAT, RUN, =

When the Teradata RDBMS encounters an ECHO statement, an ECHO parcel containing ’string’ is sent to the client system. When returned to BTEQ, it is interpreted by BTEQ.

If ’command is not a BTEQ format command, BTEQ logs the illegal command as an error.

The following ECHO statement could be used in a macro that Example creates a report through BTEQ. When the Teradata RDBMS

encounters the statement during macro execution, the width of a printed page is set to 72 characters.

...ECHO 'SET WIDTH 72;'...

8-136

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide
Previous << 1 .. 143 144 145 146 147 148 < 149 > 150 151 152 153 154 155 .. 241 >> Next