4 ms·

It's not jut about large columns. When fetching from views, selecting unneeded columns can trigger unneeded JOINs. Assuming no JOINed column is mentioned in t

It's not jut about large columns. When fetching from views, selecting unneeded columns can trigger unneeded JOINs. Assuming no JOINed column is mentioned in the select list (either explicitly or through *), the query optimizer can eliminate the OUTER JOIN, or INNER JOIN when it is known that the referenced row exists (because there is a FOREIGN KEY). This is mentioned in the article as "Oracle’s join elimination transformation", but (most?) other databases can do it too.

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 ago
  I 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 ago
    What 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;

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 ago
I 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 ago
  What 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;