主页 / dba / 问题 / 29021
Accepted
a1ex07
Asked: 2012-11-21 16:27:24 +0800 CST

Oracle 10g 使用 sql 集合

  • 772

我有一个收集对象的存储过程。( TABLE OF MyCustomType)。在过程中，我试图将此参数与真实表连接起来。

例如，像

create or replace TYPE MYTYPE AS OBJECT (....);  
CREATE OR REPLACE TYPE LIST_OF_MYTYPE AS TABLE OF MYTYPE;

CREATE PROCEDURE FOO(my_table LIST_OF_MYTYPE ,....) AS
  CURSOR cur_read_data IS 
  SELECT a.col1, b.col2, b.col3
  FROM 
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id
  ;

BEGIN
     FOR record_info in cur_read_data 
     LOOP 
        ......
     END LOOP;
END;

它有效，但我有一个性能问题。传递给过程的集合参数没有很多元素；即使它只有一个元素，执行计划也会涉及对existing_table.

在 1 个元素的情况下，更改INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)为INNER JOIN existing_table b ON (b.existing_table_id =FOO.my_table(1).existing_table_id )会产生巨大的差异 - 查询使用“INDEX UNIQUE SCAN”，正如我在初始查询中所期望的那样。我什至尝试过没有结果的查询提示（有序，领先）......

即使我的应用程序中集合中的元素数量在 1 到 5 之间，并且可以编写一个过程的 5 个不同版本，我想知道是否可以使其按预期工作。

为了测试，我也做了

CURSOR cur_read_data IS 
  SELECT a.col1, b.col2, b.col3
  FROM 
   ( 
     SELECT 1 as existing_table_id, 'test 1' as col1 FROM DUAL
      UNION
     SELECT 2 as existing_table_id, 'test 2' as col1 FROM DUAL
      UNION
     SELECT 3 as existing_table_id, 'test 3' as col1 FROM DUAL
   )
  a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

这样它也可以按预期工作，INDEX_UNIQUE_SCAN，而不是全扫描......

感谢您的回答。

更新 相当令人惊讶，但将其重写为

CURSOR cur_read_data IS 
  WITH CTE1 AS (SELECT * FROM TABLE(FOO.my_table))
  SELECT a.col1, b.col2, b.col3
  FROM 
  CTE1 a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id
  AND b.existing_table_id IN (SELECT existing_table_id FROM CTE1))      
  ORDER BY a.existing_table_id

成本降低了大约 30 倍（1200 原始版本与 38 版本WITH）这是可以接受的，但我仍然不知道为什么这有帮助......

另一个更新

分析V$SQLSTATS表明，对于游标的第一个版本，它不惜一切代价避免 DISK READS (0)，DIRECT WRITES 也是 0；使用WITH以某种方式更改执行计划导致 CPU TIME 的巨大改进超过增加的 DISK READS (1)...

performance optimization

1 个回答

  1. Best Answer

    在 SQL 中使用集合的问题之一是优化器无法猜测集合有多少元素。它默认假设集合有几千个元素（我想说 4k 元素，但我不会下注）。如果这比实际集合中的元素多大约 1,000 倍，那肯定会导致优化器对查询计划做出错误的选择。

    缓解该问题的最佳方法是使用CARDINALITY提示。

    SELECT /*+ cardinality(a 4) */ a.col1, b.col2, b.col3
  FROM 
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

    告诉优化器假设别名为的集合a只有 4 个元素，这应该导致它选择更合适的查询计划。

    • 1

相关问题