VALUE ORDERED PRIMARY INDEX TD13.10

Database

VALUE ORDERED PRIMARY INDEX TD13.10

I'm a little confused by some of your documentation. Note here my system is actually 13.00 and I'm reading 13.10 notes but Database Desgn 1094.pdf mentions frequently the consept of a value ordered primary index for both tables and join indexes. However the following statements fail with a ERROR 3706: 

CREATE JOIN INDEX jidx_e AS
SELECT
employee.department_number
, employee.job_code
FROM CUSTOMER_SERVICE.employee
PRIMARY INDEX (department_number, job_code)
ORDER BY VALUES (job_code);
--EXPLAIN Failed 3706: Syntax Error: Order by is not applicable to PRIMARY INDEX
CREATE TABLE PhoneList
(

Calls INTEGER TITLE 'Calls Made'
,Take CHAR(1) TITLE 'Important'
)
UNIQUE PRIMARY INDEX( Calls ) ORDER BY VALUES (Calls) ;
--EXPLAIN Failed 3706: Syntax Error: Order by is not applicable to PRIMARY INDEX

I am pretty sure you DONT mean PPI but a value ordered NPPI seems to not exist as a possible answer.

7 REPLIES

Re: VALUE ORDERED PRIMARY INDEX TD13.10

Addendum: The statements also exist in the TD12.00 Documentation "Database Design" 1094.pdf

Re: VALUE ORDERED PRIMARY INDEX TD13.10

Teradata does not support a valued-ordered primary index.  A value-ordered index can be defined only as a secondary index on either a base table or a join index.  A primary index is always hashed.  The SQL documentation seems pretty clear on this point.  Any statement to the contrary in the Database Design publication is incorrect.

Re: VALUE ORDERED PRIMARY INDEX TD13.10

Agreed: Therefore the following pages in the above document seems incorrectly worded...

P611:"Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value-Ordered NUSI


Partitioned primary indexes and value-ordered NUSIs are both designed to optimize the performance of range queries. Because you cannot define both a PPI and a value-ordered primary index on the same join index,7 you must determine which is more likely to enhance the performance of a given query workload for those situations that exclude using a join index with a value-ordered primary index.

In general, a value-ordered primary index is the preferred choice if it meets the restriction of a 4-byte maximum column size. Note that this cannot be used for an MLPPI because MLPPIs do not support value-ordered NUSIs by definition.

You might want to consider creating a MLPPI on the base table as an alternative if the usage would be roughly equivalent to a value-ordered NUSI on a join index plus a nonvalue-ordered NUSI.

In nearly all cases, a join index with a value-ordered primary index is the preferred choice over a value-ordered NUSI."

Re: VALUE ORDERED PRIMARY INDEX TD13.10

Also the following code successfully demonstrates NUSI's on both parts of the JOIN INDEX's PPI, being the hashed column and the ordered value column: 

CREATE SET TABLE db.table 
,NO FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
,CHECKSUM = DEFAULT
(
Party_Name VARCHAR(300) CHARACTER SET LATIN NOT CASESPECIFIC,
Party_Accnt_Role_Name VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
Accnt_Name VARCHAR(300) CHARACTER SET LATIN NOT CASESPECIFIC,
Party_Id INTEGER,
Accnt_Num VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Sbscn_Id INTEGER,
Prdct_Line_Id INTEGER,
Prdct_Instc_Id INTEGER,
Prdct_Name VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,
Prdct_Id INTEGER
)
PRIMARY INDEX ( Accnt_Num );

CREATE JOIN INDEX db.ji AS
SELECT
ROWID
, Party_Name
, Party_Accnt_Role_Name
, Accnt_Name
, Party_Id
, Accnt_Num
, Sbscn_Id
, Prdct_Line_Id
, Prdct_Instc_Id
, Prdct_Name
, Prdct_Id
FROM db.table
PRIMARY INDEX ppi_sbn_pty (Sbscn_Id)
PARTITION BY (
RANGE_N(
Party_Id
BETWEEN 0 AND 6550000
EACH 100
, NO RANGE
, UNKNOWN
)
)
INDEX nusi_sbn (Sbscn_Id) ORDER BY VALUES (Sbscn_Id)
INDEX nusi_pty (Party_Id) ORDER BY VALUES (Party_Id);

Re: VALUE ORDERED PRIMARY INDEX TD13.10

After consulting a more expert person, I have learned that it is indeed possible to create a value-ordered join index.  Here is an example:

create table t1 (a1 int, b1 int);

create join index t1_ji as select b1, rowid from t1 order by b1 primary index(b1);

Note that in this case the ordering is specified in the select clause rather than the index clause.

Re: VALUE ORDERED PRIMARY INDEX TD13.10

That's incredibly help full, thankyou, as I've not found anything in the documentation (yet) that states that fact clearly.

After running some tests:

CREATE JOIN INDEX ji2
AS SELECT b1, ROWID
FROM t1
ORDER BY b1
PRIMARY INDEX(b1);

EXPLAIN
SELECT TOP 20 b1, HASHAMP(HASHBUCKET(HASHROW(b1 ))) AS amp_num
FROM t1


  1) First, we lock a distinct "pseudo table" for

     read on a RowHash to prevent global deadlock for

     ji2. 

  2) Next, we lock ji2 for read. 

  3) We do an all-AMPs STAT FUNCTION step from

     ji2 by way of an all-rows scan

     with no residual conditions into Spool 5, which is redistributed

     by hash code to all AMPs.  The result rows are put into Spool 1

     (group_amps), which is built locally on the AMPs.  This step is

     used to retrieve the TOP 20 rows.  One AMP is randomly selected to

     retrieve 20 rows. If this step retrieves less than 20 rows, then

     execute step 4.  The size is estimated with high confidence to be

     20 rows (580 bytes). 

  4) We do an all-AMPs STAT FUNCTION step from

     ji2 by way of an all-rows scan

     with no residual conditions into Spool 5 (Last Use), which is

     redistributed by hash code to all AMPs.  The result rows are put

     into Spool 1 (group_amps), which is built locally on the AMPs. 

     This step is used to retrieve the TOP 20 rows.  The size is

     estimated with high confidence to be 20 rows (580 bytes). 

  5) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1. 

b1 amp_num

2,992,285 435

3,344,270 435

3,394,169 435

3,501,391 435

3,627,579 435

3,760,514 435

3,807,650 435

4,112,907 435

4,438,321 435

4,438,321 435

4,438,321 435

4,438,321 435

4,438,321 435

4,438,321 435

4,476,104 435

1,409,226 369

1,522,118 369

1,842,472 369

2,165,936 369

3,064,013 369

After running this, a number of time consistantly the rows come back ordered by value, returned amp by amp by amp. Hence it does indeed exist valued orded on the primary index within the Join Index.

Again, thankyou for your clarification Jim.

Teradata Employee

Re: VALUE ORDERED PRIMARY INDEX TD13.10

Thanks Jim & Rupert.

I came to this forum post reading the same Page 611 and I found the answers also... Wonderful to be part of such a forum.