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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 145 146 147 148 149 150 < 151 > 152 153 154 155 156 157 .. 241 >> Next

The EXPLAIN modifier is flagged as non-ANSI, when the SQL flagger is enabled.

EXPLAIN details what indexes, if any, would be used to process the request, identifies any intermediate spool files that would be generated, shows whether the statements in a transaction would be dispatched in parallel, and so on.

EXPLAIN aids users in evaluating a complex query and in developing an alternative processing strategy that may be more efficient.



- request -



Secondary Indexes

References to secondary indexes are based on the following:

• Each secondary index is stored as a subtable.

• Each row in a secondary index subtable is made up of an index

value and one or more row IDs.

The subtable row IDs identify the data rows that contain the index value.

The subtable for a unique secondary index is a hashed table.

Bit Mapping

References to bit mapping may appear when complex conditional expressions involving non-unique secondary indexes are applied to a very large table.

Such expressions may be resolved by mapping each subtable row ID to a number in the range 0-32767. This number is used as an index into a bit map, in which the bit for each qualifying data row is turned on.

The Teradata RDBMS is better able to determine whether the table is a candidate for bit mapping when statistics exist for the table; see also the COLLECT STATISTICS statement.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

EXPLAIN Modifier

Only the first 255 characters of a conditional expression are displayed in an EXPLAIN. The entire conditional expression is enclosed in quotation marks.

Although the times shown in the EXPLAIN output are indicated as seconds, they are actually arbitrary units of time measure; their value is that they allow you to compare alternate methods of operation.

Make the results of EXPLAINs an integral part of your review process; they may indicate inefficiencies in the query structure.

Keep the EXPLAIN results with your system documentation; they may be of value when reevaluating the system.

In 2PC mode, if EXPLAIN is used with a request, and if that request 2PC Mode is followed by a 2PC function, the SQL portion of the request is

explained, but the 2PC function is not.

For example, if the following request was submitted:


the INSERT and SELECT would be explained; the VOTE would not be explained. The VOTE is, however, sent to the AMPs. VOTE is not specified by users. It is specified by the system administrator.

For further information about the EXPLAIN modifier, see the Teradata RDBMS for UNIX Database Design and Administration, Chapter 17, “Designing the Database for Optimum Performance.”

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide

EXPLAIN Modifier


Terms used in EXPLAIN phrases are described in the following list:

Term Explanation
All-AMPs JOIN step by way of an all-rows scan...
On each AMP on which they reside, spooled rows and/or primary table rows are searched row by row; rows that satisfy the join condition are joined.
All-AMPs JOIN step by way of a RowHash match scan...
1 The first row is retrieved from the first table; that row’s hash code is used to locate a row from the second table. 2 Each row hash match is located and processed as follows: 1 The row hashes are compared; if not equal, the larger row hash is used to read rows from the other table until a row hash match is found, or until the table is exhausted. 2 If match is found, each pair of rows with the same hash code is accessed (one at a time) from the two tables. For each such pair, if the join condition is satisfied, a join result row is produced. 3 After all rows with the same row hash are processed from both tables, one more row is read from each table; the row hashes from these two rows are compared, restarting the compare process.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

EXPLAIN Modifier

Term Explanation
All-AMPs RETRIEVE step by way of an all-rows scan...
All rows of a table are selected row by row on all AMPs on which the table is stored. BMSMS (bit map set manipulation step); intersects the following row id bit maps: 1) The bit map built for ... by way of index # n... 2) The bit map built for ... by way of index # n... The resulting bit map is placed in Spool n... BMSMS... Indicates that two or more bit maps are intersected by ANDing them to form one large bit map. index # n... Identifies, in the order in which they are ANDed, each non-unique secondary index used in the intersection. resulting bit map is placed in Spool n... Identifies the temporary file in which the large bit map produced by the BMSMS is stored to make it available for use in producing the final result
By way of index # n and the bit map in Spool n...
The data row associated with the row ID is accessed only if the associated bit is turned on in the bit map (see Usage Notes).
Previous << 1 .. 145 146 147 148 149 150 < 151 > 152 153 154 155 156 157 .. 241 >> Next