最近，我在查询性能方面遇到了一些问题。经过长时间的努力，我终于发现带有select前缀的查询如下：
SELECT sth.* FROM Something as sth...
是慢300倍，然后以这种方式启动查询：
SELECT * FROM Something as sth..
有人可以帮我吗，为什么呢？关于此的一些外部文档将非常有用。
用于测试的表是：
SALES_UNIT 表包含一些基本的信息 自动售货机 节点，例如名称等。唯一的关联是表SALES_UNIT_TYPE，例如ManyToOne。主键是ID和字段VALID_FROM_DTTM，它是日期。
SALES_UNIT_RELATION 包含销售单位节点之间的关系PARENT- CHILD。由SALES_UNIT_PARENT_ID，SALES_UNIT_CHILD_ID和VALID_TO_DTTM / VALID_FROM_DTTM组成。没有与任何表的关联。此处的PK是..PARENT_ID，.. CHILD_ID和VALID_FROM_DTTM
我使用的实际查询是：
SELECT s.* FROM sales_unit s LEFT JOIN sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) WHERE r.sales_unit_child_id IS NULL SELECT * FROM sales_unit s LEFT JOIN sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) WHERE r.sales_unit_child_id IS NULL
相同的查询，都使用左连接，唯一的区别是选择。
当然，这是两个不同的查询。计划可以随着选择的不同而改变。即在某事上。*可能是在左侧联接表上选择了全/快速全索引扫描。第一次可能是全表扫描。
为了进一步帮助您，我们可以看看这些计划吗？最好在SQL * PLUS中执行此操作
set timing on set autotrace on traceonly select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null; select * from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null;
编辑
根据您的解释计划，您看到每一步的CARDINALITY = 1吗？表格为空时，您已经收集了统计信息！看到这个：
SQL> select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.child_sales_unit_id) where r.child_sales_unit_id is null; no rows selected Elapsed: 00:00:03.19 Execution Plan ---------------------------------------------------------- Plan hash value: 1064670292 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 48 | 27 (86)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 1 | 48 | 27 (86)| 00:00:01 | | 2 | TABLE ACCESS FULL| SALES_UNIT | 1 | 35 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | SALES_REL_IX1 | 1 | 13 | 25 (92)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("S"."SALES_UNIT_ID"="R"."CHILD_SALES_UNIT_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 200314 consistent gets 2220 physical reads 0 redo size 297 bytes sent via SQL*Net to client 339 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
因此看到它使用了200314 IO，并花费了几秒钟。还要在每一步上看到ROWS = 1（即完整扫描）。.let收集统计信息：
SQL> begin dbms_stats.gather_table_stats(user, 'SALES_UNIT', degree=>8, cascade=>true); end; 2 / PL/SQL procedure successfully completed. SQL> begin dbms_stats.gather_table_stats(user, 'SALES_UNIT_RELATION', degree=>8, cascade=>true); end; 2 / PL/SQL procedure successfully completed.
然后重新运行：SQL>从sales_unit的左选择s。*加入（s.sales_unit_id = r.child_sales_unit_id）上的sales_unit_relation r，其中r.child_sales_unit_id为null;
no rows selected Elapsed: 00:00:00.84 Execution Plan ---------------------------------------------------------- Plan hash value: 2005864719 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 912 | 18240 | | 1659 (3)| 00:00:20 | |* 1 | HASH JOIN ANTI | | 912 | 18240 | 2656K| 1659 (3)| 00:00:20 | | 2 | TABLE ACCESS FULL | SALES_UNIT | 100K| 1472K| | 88 (3)| 00:00:02 | | 3 | INDEX FAST FULL SCAN| SALES_REL_IX1 | 991K| 4841K| | 618 (3)| 00:00:08 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("S"."SALES_UNIT_ID"="R"."CHILD_SALES_UNIT_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2537 consistent gets 0 physical reads 0 redo size 297 bytes sent via SQL*Net to client 339 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL>
现在我们只使用了2537个Gets，该计划显示了正确的ROWS和HASH联接（更好地满足了我们的需求）。我的测试表可能比您的实际测试表小，这就是为什么时间更短的原因
