4 ms·
Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other
by reshlo 6y ago
Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other table match the predicate, so you’d still need to do the join, right?
- branko_d 6y agoI guess it depends on the join predicate. For a vanilla equi-join, no grouping is required. Let's say we have the following two tables and a view that joins them: CREATE TABLE PARENT ( PARENT_ID int PRIMARY KEY, PARENT_NAME varchar(255) ); CREATE TABLE CHILD ( CHILD_ID int PRIMARY KEY REFERENCES PARENT, CHILD_NAME varchar(255) ); CREATE VIEW PARENT_CHILD AS SELECT * FROM PARENT JOIN CHILD ON PARENT_ID = CHILD_ID; Now if you select any fields from PARENT, the query plan will physically access both PARENT and CHILD. For example: SELECT * FROM PARENT_CHILD; SELECT PARENT_ID, PARENT_NAME FROM PARENT_CHILD; But the following would physically access only CHILD (because it knows that for any existing CHILD row, the corresponding PARENT row must also exist): SELECT CHILD_ID, CHILD_NAME FROM PARENT_CHILD;
- reshlo 6y agoWhat I’m suggesting is that the following still has to access CHILD despite only projecting columns from PARENT. If I understand it correctly, not projecting any columns from a joined table doesn’t mean it won’t be accessed, even for vanilla equi-joins. SELECT PARENT_ID FROM PARENT_CHILD;