3 ms·
This was just explained to me by our DBA for a data warehouse (technically a 'data mart' not a data warehouse if you want to pedantic) that we're building. Say
by pyre 15y ago
This was just explained to me by our DBA for a data warehouse (technically a 'data mart' not a data warehouse if you want to pedantic) that we're building. Say you've got two tables:
table a (
id integer primary key,
data text
)
table b (
id integer primary key,
postal_code text
)
In reality, postal_code is a natural key, so there's no need to the surrogate key b.id but if you insist on using it that way you'll end up with this:
select *
from a inner join b on a.id = b.id
where b.postal_code = '97000'
The query plan for this will end up something like:
NESTED LOOP
TABLE B
WHERE postal_code = '97000'
TABLE A
That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. There are a couple of ways to mitigate this:
1. Use postal_code as the key.
2. Perform two queries:
select * from b where postal_code = '97000';
and use the result of that to filter table a
select * from a where id in (*); -- or similar
So now it only does a single pass table scan of table a
3. Use Oracle which has something called a 'star join' which basically does option #2 automatically for you.
Obviously this is the simple case. Things get more complex the more tables you add in.
- j-kidd 15y ago> That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. The query plan would be either: NESTED LOOP TABLE B WHERE postal_code = '97000' TABLE A (index scans) or: TABLE A (1 full table scan) TABLE B WHERE postal_code = '97000' What database do you use that would perform multiple full table scans?