在IN子句中使用嵌套表的性能- Oracle

Question

我试图在PL-SQL块的IN子句内使用嵌套表。

首先，我定义了TYPE:

CREATE OR REPLACE TYPE VARCHAR_ARRAY AS TABLE OF VARCHAR2(32767);

这是我使用'BULK COLLECT INTO'的PL-SQL块:

DECLARE
  COL1 VARCHAR2(50) := '123456789';
  N_TBL                VARCHAR_ARRAY := VARCHAR_ARRAY();
  C NUMBER;
BEGIN
    -- Print timestamp
    DBMS_OUTPUT.PUT_LINE('START: ' || TO_CHAR(SYSTIMESTAMP  ,'dd-mm-yyyy hh24:mi:ss.FF'));
    SELECT    COLUMN1
      BULK COLLECT INTO N_TBL
    FROM MY_TABLE
    WHERE     COLUMN1 = COL1;

    SELECT    COUNT(COLUMN1)
      INTO C
    FROM      MY_OTHER_TABLE
    WHERE     COLUMN1 IN (SELECT column_value FROM TABLE(N_TBL));
    -- Print timestamp
    DBMS_OUTPUT.PUT_LINE('ENDED: ' || TO_CHAR(SYSTIMESTAMP  ,'dd-mm-yyyy hh24:mi:ss.FF'));
END;

输出为:

START: 01-08-2014 12:36:14.997
ENDED: 01-08-2014 12:36:17.554

需要超过2.5秒(准确地说是2.557秒)

现在，如果我用子查询替换嵌套表，像这样:

DECLARE
  COL1 VARCHAR2(50) := '123456789';
  N_TBL                VARCHAR_ARRAY := VARCHAR_ARRAY();
  C NUMBER;
BEGIN
    -- Print timestamp
    DBMS_OUTPUT.PUT_LINE('START: ' || TO_CHAR(SYSTIMESTAMP  ,'dd-mm-yyyy hh24:mi:ss.FF'));
    SELECT    COUNT(COLUMN1)
      INTO C
    FROM      MY_OTHER_TABLE
    WHERE     COLUMN1 IN (
                          -- Nested table replaced by a subquery
                          SELECT    COLUMN1
                          FROM MY_TABLE
                          WHERE     COLUMN1 = COL1
    );
    -- Print timestamp
    DBMS_OUTPUT.PUT_LINE('ENDED: ' || TO_CHAR(SYSTIMESTAMP  ,'dd-mm-yyyy hh24:mi:ss.FF'));
END;

输出为:

START: 01-08-2014 12:36:08.889
ENDED: 01-08-2014 12:36:08.903

只需要14毫秒…!

我能做些什么来增强这个PL-SQL块?是否需要任何数据库配置?

Answer 1

两个查询计划不同吗?

假设它们是，区别可能是优化器对子查询将返回的行数有合理的估计，因此能够选择最有效的计划。当你的数据在一个嵌套表中(我讨厌在类型声明中使用array这个词，因为这意味着你在使用varray时，你不是)，Oracle没有关于集合中有多少元素的信息。默认情况下，它会猜测集合的元素数量与数据块的字节数相同。所以如果你有8k块，Oracle会猜测你的集合有8192个元素。

假设您的实际查询没有返回接近8192行的任何地方，并且它实际上返回的行更多或更少，您可以潜在地使用基数提示让优化器做出更准确的猜测。例如，如果您的查询通常返回几十行，那么您可能需要像

这样的内容。

SELECT    COUNT(COLUMN1)
  INTO C
FROM      MY_OTHER_TABLE
WHERE     COLUMN1 IN (SELECT /*+ cardinality(t 50) */ column_value 
                        FROM TABLE(N_TBL) t);

您放入cardinality提示中的文字不需要特别准确，只需接近一般现实即可。如果行数完全未知，dynamic_sampling提示可以提供帮助。

如果你使用的是Oracle 11g，你也可以从基数反馈中获益，基数反馈可以帮助优化器更好地估计集合中的元素数量。