Hi, 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 AS SELECT col1 , 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 PRIMARY INDEX (col1);