0

Will primary index be utilised in Teradata if join condition on NON primary index along with primary index columns?

Suppose,

-- Table A
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    Region VARCHAR(20)
) PRIMARY INDEX (CustomerID);

-- Table B
CREATE TABLE Customers (
    CustomerID INT,
    Region VARCHAR(20),
    Name VARCHAR(50)
) PRIMARY INDEX (CustomerID);

Query:

SELECT *
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID    -- c.CustomerID is PI
AND o.Region = c.Region           -- c.Region is NOT PI
Improve this question
3
  • Should be pretty easy to test, just run an explain. Commented May 29 at 18:36
  • Tested already,both queries are using Hash row match scan. But, some documentation/chatgpt is showing PI benefit will not be available if we use PI+non PI columns in join hence, I am bit confused. Commented May 30 at 10:23
  • Assuming region is the smaller table, the optimizer probably prepares this table into a spool which is redistributed/duplicated and sorted to match the orders PI, followed by a nerge join. Depending on the actual data there may be a different preparation. Commented Jun 3 at 9:12
Related questions
0
In Teradata there get columns/fields used by join and where condition and respective table without parsing query
2
SQL query to drop null/blank values from Database (DB2 and Teradata specific)
4
Does a composite primary key also create an index for each column separately?
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.