How should i design my tables for concurrent table scan access?

I need to hold multiple pairs of 70,000 rows and perform a comparison difference like operation between them using a minus operator. At any time there could be comparisons (table scans).

I currently have one table with this sort of design:

• primary key (sequenced)
• foreign key to identify set
• key to identify set #1 or set #2
• then the data here i need to minus against

The data would look something like this

| PK | FK | Key   | Data      |
| 1  | 1  | Left  | Some data |
| 1  | 1  | Left  | Diff data |
| 1  | 1  | Right | Some data |

My query would be:

SELECT data
FROM diffTable
WHERE pk = 1
AND fk = 1
AND key = 'Left'

MINUS

SELECT data
FROM diffTable
WHERE pk = 1
AND fk = 1
AND key = 'Right'

I am fearing the full table scans will monopolise resources and subsequent inserts and minus' will suffer.

How should I d开发者_运维知识库esign my tables and why?

create index PK_FK on diff_table
   (PK, FK, Key);

The query you posted in your question would run very fast with this index.

Btw, the PK column is not, by itself, the primary key. See the other comments. Perhaps you want:

alter table diff_table
  add constraint PK_FK primary key (PK, FK, Key);

maybe pick a better name...