BIGINT on Single table join index

Teradata Employee

BIGINT on Single table join index


I created a single TABLE join index ON my table_1 WITH the same PI AS the PI OF table_2 so that I can avoid the redistribution.

table_1: 1.5 billion ROWS

table_2: 4 million ROWS

I could NOT CREATE a STJI ON the smaller TABLE because it does NOT have the UPI COLUMN OF the larger TABLE.

Scenario 1: IF I include a BIGINT COLUMN ON my STJI THEN look AT the EXPLAIN plan, the STJI IS NOT used.

Scenario 2: IF I REPLACE the BIGINT COLUMN WITH another COLUMN (SMALLINT OR VARCHAR OR CHAR datatypes), the STJI IS used AS I see it IN the EXPLAIN plan.

My Question IS, why does the optimizer choose NOT TO USE the STJI ON scenario 1?

STJI: The join index fully covers the SQL statement.

CREATE join index test_db.STJI_table1



       , col2

       , col3

       , col4

       , col5

       , col6 --bigint, if I replace this with other columns with smaller data type and replace the same column on the SQL statement then the STJI will be used by the optimizer

FROM test_db.table1 a