Oracle 查询性能大SQL

嗨，汤姆，

感谢您抽出时间阅读我的问题。我有一个连接大约36个表的查询。驾驶表有大约4300万条记录。5其他表有大约900万条记录。其余的表都很小。它的内部和左外部连接的组合我通过连接这36个表来选择950列。当我对这个查询进行计数 (*) 时，我在大约10-12分钟内得到4300万的计数。如果我用运行约5小时的950列替换count(*)，以给出前几行。在两种情况下，“来自” 之后的所有代码都是相同的。

您能帮我为什么选择950列时需要更长的时间吗？我认为count(*) 将需要更多时间，因为它必须获取所有记录？

另外，您是否认为有更好的方法或更好的方法来做到这一点，而不是加入36个表并选择950列？

再次感谢您的帮助

更改所选列当然是对查询的更改，因此优化器可能会提出其他方法来运行它，例如

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> create index ix on t ( object_id );

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select created
  2  from   t
  3  where  object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 78256 |   993K|   424   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 78256 |   993K|   424   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">0)

SQL>
SQL> select count(*)
  2  from   t
  3  where  object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 110194766

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     5 |    50   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IX   | 78256 |   382K|    50   (2)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">0)
复制

现在我假设你想使用count(*) 来避免你的4300万行回来。您可以聚合您的列，例如

SQL> select created,
  2         owner,
  3         object_name
  4  from   t
  5  where  object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 78256 |  4050K|   424   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 78256 |  4050K|   424   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">0)

SQL>
SQL> select max(created),
  2         max(owner),
  3         max(object_name)
  4  from   t
  5  where  object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    53 |   424   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    53 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 78256 |  4050K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">0)
复制

这并不是获得相同计划的保证，它正在努力获取您将需要进行真实查询的所有列，因此可以使您对所涉及的工作有一个好主意。或者，您可以查看获取现有查询的计划，并嵌套到were查询中，例如

SQL> explain plan for
  2  select created,
  3         owner,
  4         object_name
  5  from   t
  6  where  object_id > 0 ;

Explained.

SQL>
SQL> SELECT * from table(dbms_xplan.display(format=>'typical +outline'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 78256 |  4050K|   424   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 78256 |  4050K|   424   (1)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


SQL> set autotrace traceonly explain
SQL> with x as
  2  (
  3  select
  4    /*+
  5        BEGIN_OUTLINE_DATA
  6        FULL(@"SEL$1" "T"@"SEL$1")
  7        OUTLINE_LEAF(@"SEL$1")
  8        ALL_ROWS
  9        DB_VERSION('12.2.0.1')
 10        OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
 11        IGNORE_OPTIM_EMBEDDED_HINTS
 12        END_OUTLINE_DATA
 13    */
 14         created,
 15         owner,
 16         object_name
 17  from   t
 18  where  object_id > 0
 19  )
 20  select count(*)
 21  from  x;

Execution Plan
----------------------------------------------------------
Plan hash value: 3715821533

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |       |   424   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |       |            |          |
|   2 |   VIEW              |      | 78256 |       |   424   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T    | 78256 |   382K|   424   (1)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("OBJECT_ID">0)

复制

但是这里有一些明显的问题 ....

1) 一个36表连接音...良好的...相当复杂
2) 950列声音...良好的...很多
3) 4300万行

除非你正在加载另一个表... 我想不出任何需要4300万x 950列结果的要求。