SQL optimization improve efficiency | filter data on non primary key column

Question

I have a SQL Teradata table with 4 columns. Cust_id & Act_num are indexed columns, and Date is not indexed.

I need to filter the data on the DATE column.

Select 
    Cust_id,
    Act_num,
    Date,
    Max(score) Max_Score
from
    table_name
where 
    date >= 1240601
group by 
    1, 2, 3

The query takes a long time to run, as it is filtering on the non-indexed column, and when I run that query to retrieve data for more than 6 months, it shows error insufficient spool space.

I have read only access and limited spool space which cannot be changed.

Please suggest ways to optimize the query. I already tried self join and exists

You can try retrieve data as batches like where date >=1240601 and date<1240901, next as where date >=1240901 and date<1241201 and so on. Your query takes filtered rows where date >=1240601 then spool to sort on(Cust_id,Act_num,Date) and group by ... — ValNik
– ValNik, Commented Dec 28, 2024 at 10:42
Work with your DBAs. First suggestion is to ensure stats are collected and current for the column you are trying to filter on. BTW, are you trying to use dates in teradata's native format? There is no benefit to doing so, and it's much more readable to use date '2024-06-01' — Andrew
– Andrew, Commented Dec 31, 2024 at 15:48
If the data is static after it is written, consider building and maintaining a Summary table. — Rick James
– Rick James, Commented Jan 23 at 3:26

Nikhil · Accepted Answer · 2024-12-31 06:50:18Z

0

Create a a Btree index on the date columnn :

create index idx_date_column_name on tablename(date_columnname);

Btree indexes are optimised for range values and ordering and range value filter where date >= 1240601 is present in your query

answered Dec 31, 2024 at 6:50

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL optimization improve efficiency | filter data on non primary key column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related