性能问题：select s。*与select *之间的区别

Question

最近，我在查询性能方面遇到了一些问题。经过长时间的努力，我终于发现带有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

相同的查询，都使用左连接，唯一的区别是选择。

Answer 1

当然，这是两个不同的查询。计划可以随着选择的不同而改变。即在某事上。*可能是在左侧联接表上选择了全/快速全索引扫描。第一次可能是全表扫描。

为了进一步帮助您，我们可以看看这些计划吗？最好在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联接（更好地满足了我们的需求）。我的测试表可能比您的实际测试表小，这就是为什么时间更短的原因