Avoiding unnecessary joins in an Oracle SQL query

2
votes

Suppose you have a table as follows:

Table Name:  CUSTOMER
Primary Key: CUSTOMER_ID
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
| 1           | Bill          |
| 2           | Tom           |
+-------------+---------------+

Now, suppose you have a CUSTOMER_ATTRIBUTE table that lets you tie key/value pairs to a particular CUSTOMER:

Table Name:  CUSTOMER_ATTRIBUTE
Primary Key: (CUSTOMER_ID, ATTRIBUTE_TYPE_ID)
+-------------+-------------------+-----------------+
| CUSTOMER_ID | ATTRIBUTE_TYPE_ID | ATTRIBUTE_VALUE |
+-------------+-------------------+-----------------+
| 1           | FAVORITE_FOOD     | Pizza           |
| 1           | FAVORITE_COLOR    | Blue            |
| 2           | FAVORITE_FOOD     | Taco            |
| 2           | NAME_OF_PET       | Fido            |
+-------------+-------------------+-----------------+

Now, suppose you create a view that represents a customer with some of its possible attributes:

CREATE VIEW CUSTOMER_VIEW AS
SELECT
    CUSTOMER.CUSTOMER_ID,
    CUSTOMER.CUSTOMER_NAME,
    FAVORITE_FOOD_ATTRIBUTE.ATTRIBUTE_VALUE AS FAVORITE_FOOD,
    FAVORITE_COLOR_ATTRIBUTE.ATTRIBUTE_VALUE AS FAVORITE_COLOR
FROM
    CUSTOMER

    LEFT OUTER JOIN CUSTOMER_ATTRIBUTE favorite_food_attribute
        ON customer.customer_id = favorite_food_attribute.customer_id
           AND favorite_food_attribute.attribute_type_id = FAVORITE_FOOD

    LEFT OUTER JOIN CUSTOMER_ATTRIBUTE favorite_color_attribute
        ON customer.customer_id = favorite_color_attribute.customer_id
           AND favorite_color_attribute.attribute_type_id = FAVORITE_COLOR

Now, suppose you query this view:

SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    FAVORITE_COLOR
    -- Notice: I did not ask for the FAVORITE_FOOD column
FROM
    CUSTOMER_VIEW

According to the explain plan, Oracle is still joining favorite_food_attribute, even though its value is not needed and it does not affect the query's cardinality (because it's LEFT OUTER JOINing to a table's primary key).

Is there a way to force Oracle to avoid these unnecessary joins?

Update: Example DDL

Here is some DDL to create the example schema:

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID   NUMBER NOT NULL,
    CUSTOMER_NAME VARCHAR2(100)
);

CREATE UNIQUE INDEX CUSTOMER_PK_INDEX
    ON CUSTOMER(CUSTOMER_ID);

ALTER TABLE CUSTOMER
    ADD CONSTRAINT CUSTOMER_PK
    PRIMARY KEY (CUSTOMER_ID)
    USING INDEX CUSTOMER_PK_INDEX;

CREATE TABLE CUSTOMER_ATTRIBUTE
(
    CUSTOMER_ID       NUMBER NOT NULL,
    ATTRIBUTE_TYPE_ID NUMBER NOT NULL,
    ATTRIBUTE_VALUE   VARCHAR2(1000)
);

CREATE UNIQUE INDEX CUSTOMER_ATTRIBUTE_PK_INDEX
    ON CUSTOMER_ATTRIBUTE(CUSTOMER_ID, ATTRIBUTE_TYPE_ID);

ALTER TABLE CUSTOMER_ATTRIBUTE
    ADD CONSTRAINT CUSTOMER_ATTRIBUTE_PK
    PRIMARY KEY (CUSTOMER_ID, ATTRIBUTE_TYPE_ID)
    USING INDEX CUSTOMER_ATTRIBUTE_PK_INDEX;

CREATE OR REPLACE VIEW CUSTOMER_VIEW AS
SELECT
    CUSTOMER.CUSTOMER_ID,
    CUSTOMER.CUSTOMER_NAME,
    favorite_food_attribute.attribute_value AS favorite_food,
    favorite_color_attribute.attribute_value AS favorite_color
FROM
    CUSTOMER

    LEFT OUTER JOIN CUSTOMER_ATTRIBUTE favorite_food_attribute
        ON customer.customer_id = favorite_food_attribute.customer_id
           AND favorite_food_attribute.attribute_type_id = 5

    LEFT OUTER JOIN CUSTOMER_ATTRIBUTE favorite_color_attribute
        ON customer.customer_id = favorite_color_attribute.customer_id
           AND favorite_color_attribute.attribute_type_id = 6;

Now, I run the explain plan on this query:

SELECT CUSTOMER_ID FROM HFSMMM.CUSTOMER_VIEW

The plan is:

SELECT STATEMENT, GOAL = ALL_ROWS           Cost=1  Cardinality=1   Bytes=65
 NESTED LOOPS OUTER         Cost=1  Cardinality=1   Bytes=65
  NESTED LOOPS OUTER            Cost=1  Cardinality=1   Bytes=39
   INDEX FULL SCAN  Object owner=HFSMMM Object name=CUSTOMER_PK_INDEX   Cost=1  Cardinality=1   Bytes=13
   INDEX UNIQUE SCAN    Object owner=HFSMMM Object name=CUSTOMER_ATTRIBUTE_PK_INDEX Cost=0  Cardinality=1   Bytes=26
  INDEX UNIQUE SCAN Object owner=HFSMMM Object name=CUSTOMER_ATTRIBUTE_PK_INDEX Cost=0  Cardinality=1   Bytes=26
3
sqloraclelazy-evaluation
In my experience, Oracle usually does avoid these unnecessary joins. Can you post some real code and an explain plan? – Tony Andrews
And what version of Oracle are you running? – Mark J. Bobak
@TonyAndrews: I can't post the real code, but I will try creating some dummy tables (hopefully they will convey the same problem). – Adam Paynter
@Annjawn: No, Oracle's optimizer is supposed to be able to do join elimination on tables referenced in a view, at least in 11g. Oracle optimizer blog: "a set of tables might be exposed as a view, which contains a join. The join may be necessary to retrieve all of the columns exposed by the view. But some users of the view may only access a subset of the columns, and in this case, the joined table can be eliminated." – Mike Sherrill 'Cat Recall'
@Annjawn: I understand. I was responding to your comment, where you said you thought Oracle would always use the joins spelled out in the views. It doesn't; it can eliminate joins from a view. – Mike Sherrill 'Cat Recall'

3 Answers

1
votes

You could do a scalar subquery if you're certain that there will only ever be one entry per customer Id and attribute type:

SELECT
    CUSTOMER.CUSTOMER_ID,
    CUSTOMER.CUSTOMER_NAME,
    (select ATTRIBUTE_VALUE from CUSTOMER_ATTRIBUTE where customer_id = CUSTOMER.CUSTOMER_ID
        and ATTRIBUTE_TYPE_ID='F') AS FAVORITE_FOOD
FROM
    CUSTOMER
1
votes

While this approach just moves the processing around rather than eliminating it it makes the SQL cleaner.
Create a user function

GET_TYPE(customer_id_in NUMBER, attribute_type_id IN NUMBER) RETURN VARCHAR 2
IS
/*  TO DO:  Assertions, error handling */
attribute_name VARCHAR2(300);
BEGIN
SELECT attribute_value
INTO attribute_name
FROM CUSTOMER_ATTRIBUTE
WHERE customer_id = customer_id_in
and attribute_type_id - attribute_type_in;


RETURN attribute_name;

END GET_TYPE;

and then your view is

CREATE VIEW CUSTOMER_VIEW as
SELECT  
    CUSTOMER.CUSTOMER_ID,  
    CUSTOMER.CUSTOMER_NAME,  
    GET_TYPE(1, CUSTOMER.CUSTOMER_ID) AS FOOD,
    GET_TYPE(2, CUSTOMER.CUSTOMER_ID) AS COLOR
FROM  
    CUSTOMER;

and Adam is correct in pointing out that there is overhead in switching contexts I use this everyday for views. I'd rather have the database do the work ahead of time to prepare the view and query that as opposed to having an application send multi join queries that must be constructed and cached.

1
votes

Instead of using outer joins, use a subquery for each attribute value that you want to see in the view. This is assuming your data is structured so that none of the subqueries can return multiple rows.

CREATE VIEW CUSTOMER_VIEW AS
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       (SELECT ATTRIBUTE_VALUE FROM CUSTOMER_ATTRIBUTE ca1
          WHERE ca1.CUSTOMER_ID = c.CUSTOMER_ID
          AND ATTRIBUTE_TYPE_ID = 'FAVFOOD')  FAVORITE_FOOD,
       (SELECT ATTRIBUTE_VALUE FROM CUSTOMER_ATTRIBUTE ca2
          WHERE ca2.CUSTOMER_ID = c.CUSTOMER_ID
          AND ATTRIBUTE_TYPE_ID = 'PETNAME')  PET_NAME,
       (SELECT ATTRIBUTE_VALUE FROM CUSTOMER_ATTRIBUTE ca3
          WHERE ca3.CUSTOMER_ID = c.CUSTOMER_ID
          AND ATTRIBUTE_TYPE_ID = 'FAVCOLOR') FAVORITE_COLOR
       FROM CUSTOMER c