避免在Oracle SQL查询中进行不必要的连接

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:

现在,假设您有一个CUSTOMER_ATTRIBUTE表,可以将键/值对绑定到特定的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).

根据解释计划,Oracle仍然加入favorite_food_attribute,即使它不需要它的值并且它不会影响查询的基数(因为它是LEFT OUTER JOIN到表的主键)。

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

有没有办法强制Oracle避免这些不必要的连接?

Update: Example DDL

更新:示例DDL

Here is some DDL to create the example schema:

这是一些用于创建示例模式的DDL:

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 个解决方案

#1

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

如果您确定每个客户ID和属性类型只有一个条目,则可以执行标量子查询:

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

#2

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

虽然这种方法只是移动处理而不是消除它,但它使SQL更清洁。创建用户功能

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.

并且Adam指出在切换上下文中存在开销是正确的,我每天都使用它来查看视图。我宁愿让数据库提前做好准备视图和查询的工作,而不是让应用程序发送必须构建和缓存的多连接查询。

#3

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