Forum Stats

  • 3,824,847 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Joins to utilizes outer tables min/max values of join columns

user3897193
user3897193 Member Posts: 16 Blue Ribbon
edited Aug 5, 2019 8:34AM in Database Ideas - Ideas

If one has select statement of type

select

T1.C,

T2.B

from

T1

inner join T2 on T1.A = T2.A

where

T1.B = 'something'

;

The table T1 typically full scanned (T1.B does not have index) and table T2 is full scanned or accessed by index.

If table T2 is big, then join operation using full scan can take a quite a lot of time; temporary areas used for joining can be large.

If during scanning of table T1 the max and min values of T1.A where stored, and when accessing table T2 this information would be utilized,

there might be possibility to utilize partition pruning or discarding of not applicable rows for temporary areas.

The max and min values of T1.A could be gotten easily during scanning of rows, no extra sorting would be required.

Then joining of these tables might utilize less resources.

0 votes

Active · Last Updated

Comments