# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

**64**> 65 66 67 68 69 70 .. 241 >> Next

D 3 s s C 3 s s Graphic 3 (improper result, not reported as an error)

ss3C ss3D Character 2

dA ss2B ss3E Character 4

string_expr Data Type Result

ABC Graphic 3

DeF Character 3

The MCHARACTERS function has the following for

MCHARACTERS (string_expr )

HH01A092

The MCHARACTERS function operates exactly like the

CHARACTERS function except for the KanjiEBCDIC character set.

Under KanjiEBCDIC, this function performs as follows:

The length of string_expr is returned as the number of logical characters.

Any Shift-Out/Shift-In characters are not included in the result count.

Any trailing graphic pad characters are included in the result count.

The following table shows example results of the MCHARACTERS

function under a KanjiEBCDIC character set.

string_expr Data Type Result

ABC Graphic 3

De<MNP> Character 5

<><> Character 0

Teradata RDBMS for UNIX SQL Reference

5-109

Data Definition

Attribute Functions

OCTET_LENGTH

Function

Table 5-20

Results from OCTET_LENGTH (string-expr)

The OCTET_LENGTH function has the following form:

OCTET_LENGTH (string_expr )

HH01A093

This function accepts either character or graphic data and returns the corresponding number of bytes. The length of the string is in bytes for both mixed SBC/MBC and graphic strings.

Any Shift-Out/Shift-In and trailing graphic pad characters are included in the result count.

This function is supported in the same manner in both Teradata and ANSI modes.

Examples of output from the OCTET_LENGTH function are given in the following table4.

Character Set Data Type string_expr Result

EBCDIC Character abcdefgh 8

ASCII Character abcdefgh 8

KanjiEBCDIC Character AB<CDE>P 11

KanjiEBCDIC Graphic MNOP 8

KanjiEUC Character dA ss2B ss3E 8

KanjiShift-JIS Character DeF 5

KanjiShift-JIS Graphic ABC 6

4 < is Shift-Out, > is Shift-In, bold = double byte

5-110

Teradata RDBMS for UNIX SQL Reference

Data Definition

Hash-Related Expressions

Hash-Related Expressions

Hash-related expressions are expressions invoking hash functions as defined below. Hash functions allow you to extract some statistical properties of the current primary index, or to evaluate these properties for other columns to determine their suitability as a future primary index. These statistics also help minimize hash synonyms and enhance the uniformity of data distribution.

Hash functions are valid within a Teradata SQL statement where other functions, like SUBSTR or INDEX, can occur.

The four hash functions listed below are described in the following passages:

HASHROW

HASHBUCKET

HASHAMP

HASHBAKAMP

Several error messages are related to hash functions. See the Teradata RDBMS for UNIX Messages Reference, for these messages.

Teradata RDBMS for UNIX SQL Reference

5-111

Data Definition

HASHROW Function

HASHROW Function

The HASHROW function returns the rowhash value of a given IntrOducUon sequence of expressions in BYTE(4) data type.

The HASHROW function has the following form, where expr is any expression that can appear in the expression list of the select clause of the SELECT statement. Typically, expr list is a comma-separated list of column names that make up the index under investigation.

-HASHROW-------------(-I expression -L)-

HH01A025

If you use the HASHROW function without any arguments, Usage N°tes HASHROWO, it will return WITH FFFFFFFF. If the arguments of

HASHROW result in a NULL value, then HASHROW will return with 00000000.

There are a maximum of 4,294,967,295 hash codes available in the system, and they are in the range of 0 to FFFFFFFF. SELECT HASHROW ( ), as in the following example, returns the last hash code in the system, which is FFFFFFFF:

SELECT HASHROW ( ) ;

The following example returns the average number of rows per row Example 1 hash where columns C1 and C2 constitute the primary index of the

table T:

SELECT COUNT (*) (FLOAT) / COUNT (DISTINCT HASHROW (C^C2), FROM T:

If columns C1 and C2 qualify for a unique index, this example returns the average number of rows with the same hash synonym.

The next example evaluates the efficiency of changing the decimal Example 2 format of a numeric field to eliminate synonyms. Assume that

columns C1 and C2 are declared as DECIMAL(2,2). You can determine the effect of reformatting the columns to DECIMAL(8,6) and DECIMAL(8,4) on hash collisions by submitting these two queries:

SELECT COUNT (DISTINCT C1(DECIMAL(8,6)) || C2 (DECIMAL(8,4)) FROM T;

SELECT COUNT (DISTINCT HASHROW (C1 (DECIMAL(8,6)), C2 (DECIMAL(8,4))) FROM T;

5-112

Teradata RDBMS for UNIX SQL Reference

Data Definition

HASHBUCKET Function

HASHBUCKET Function

The HASHBUCKET function returns the bucket number that Introduction corresponds to a hashrow. The bucket number is an integer data

type.

The HASHBUCKET function has the following form:

Form

**64**> 65 66 67 68 69 70 .. 241 >> Next