sql - 从 Teradata 中的大表中更快地检索结果

Question

我正在从事一个分析项目，该项目需要我从 Teradata 的一个非常大的表中提取一些数据。这是我正在使用的查询：

select TransactionNumber
from my_table
where TransactionDate between date '2017-01-01' and date '2017-12-31'
and ItemNumber in (99276);

即使我过滤了 2017 年全年的 my_table，仍然有近 9 亿行来自此查询，并且查询需要 30 多秒才能运行。由于我项目的性质，我希望它在大约 5 秒或更短的时间内运行，但考虑到表格的大小，我什至不确定它是否可能。以下是我使用“解释”时显示的内容（如果有帮助）：

1) First, we lock DBTables.my_table in view
DB.my_table for access.
2) Next, we do an all-AMPs RETRIEVE step from 365 partitions of
DBTables.my_table in view DB.my_table with a
condition of ("(DBTables.my_table in view
DB.my_table.TransactionDate <= DATE '2017-12-31') AND
((DBTables.my_table in view
DB.my_table.TransactionDate >= DATE '2017-01-01') AND
(DBTables.my_table in view
DB.my_table.ItemNumber = 99276 ))") into Spool
1 (group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with no confidence to be 617,535,066 rows (
14,203,306,518 bytes). The estimated time for this step is 2
minutes and 48 seconds.
3) 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. The total estimated time is 2 minutes and 48 seconds.

诚然，我对优化查询不太熟悉，而且我只有对数据库的读取权限，因为我不是 DBA。

标签： sqlteradata