0

Suppose multiset table A in teradata has 10 (just example-there can be > 100k rows) rows. All rows are full row duplicate. I have logically deleted 9 rows with active_date as closed (current timestamp). Now table A has 10 rows but only 1 row is active with active_date (9999). If I write query to fetch only that 1 active record, will query performance degrade because of other 9 rows which are logically deleted? Please explain how.

James Z
  • 12,209
  • 10
  • 24
  • 44
Santosh Suplepatil
  • 11
  • 3
  • It depends? If you're really concerned about it, migrate the data to a set table. – Andrew Aug 20 '20 at 21:47
  • Unless the query plan uses partition elimination or an index, the database will have to constantly look at those rows to evaluate the filter condition and decide to skip or keep the row, As long as all the duplicates for a given set of values fit in the same data block, that's relatively small additional overhead. If you have so many duplicates that they occupy many data blocks, the overhead can be huge. – Fred Aug 21 '20 at 15:16

0 Answers0