sql - 嵌套 where exists 执行时间过长

Question

我有以下查询运行良好并且不需要太多时间

我们称之为查询 1：

select t3.date2
      ,t4.date1
      ,case
          when t4.date1 is null then
           t3.date2
          else
           t4.date1
       end check_date
      ,t3.dimension2
  from (select max(date2) date2
              ,dimension2
          from view1 t4
         where measure2 > 0
         group by dimension2) t3
  left join (select max(t1.date1) date1
                   ,t1.dimension1
               from table1 t1
              where dimension2 = 'some value'
                and exists (select 1
                       from (select max(date2) date2
                                   ,dimension2
                               from view1
                              where measure2 > 0
                              group by dimension2) t2
                      where t1.date1 > t2.date2
                        and t1.dimension1 = t2.dimension2)
              group by t1.dimension1) t4
    on t3.dimension1 = t4.dimension1;

EXPLAIN PLAN FOR ABOVE QUERY:
-----------------------------------------------------------------------
| Id  | Operation                           | Name                    |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |
|   1 |  HASH JOIN RIGHT OUTER              |                         |
|   2 |   VIEW                              |                         |
|   3 |    HASH GROUP BY                    |                         |
|   4 |     HASH JOIN RIGHT SEMI            |                         |
|   5 |      VIEW                           |                         |
|   6 |       HASH GROUP BY                 |                         |
|   7 |        HASH JOIN RIGHT OUTER        |                         |
|   8 |         INDEX STORAGE FAST FULL SCAN| TABLEB                  |
|   9 |         HASH JOIN RIGHT OUTER       |                         |
|  10 |          TABLE ACCESS STORAGE FULL  | TABLEA                  |
|  11 |          TABLE ACCESS STORAGE FULL  | VIEW1                   |
|  12 |      TABLE ACCESS STORAGE FULL      | TABLE1                  |
|  13 |   VIEW                              |                         |
|  14 |    HASH GROUP BY                    |                         |
|  15 |     HASH JOIN RIGHT OUTER           |                         |
|  16 |      INDEX STORAGE FAST FULL SCAN   | TABLEB                  |
|  17 |      HASH JOIN RIGHT OUTER          |                         |
|  18 |       TABLE ACCESS STORAGE FULL     | TABLEA                  |
|  19 |       TABLE ACCESS STORAGE FULL     | VIEW1                   |
-----------------------------------------------------------------------

现在通过使用之前的“查询 1”，我正在创建一个新查询。此查询不会引发任何错误，但需要永远运行

select a
      ,b
      ,date1
      ,dimension
  from table1 t5
 where some_condition = 'some condition'
   and some_column is null
   and exists (select 1
          from ('query 1 here') t6
         where t5.dimension = t6.dimension2
           and t5.date1 >= t6.check_date);

EXPLAIN PLAN FOR ABOVE QUERY:
-----------------------------------------------------------------------------
| Id  | Operation                                 | Name                    |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |
|   1 |  FILTER                                   |                         |
|   2 |   INLIST ITERATOR                         |                         |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED    | TABLE1                  |
|   4 |     INDEX RANGE SCAN                      | TABLEC                  |
|   5 |   FILTER                                  |                         |
|   6 |    NESTED LOOPS OUTER                     |                         |
|   7 |     VIEW                                  |                         |
|   8 |      SORT GROUP BY                        |                         |
|   9 |       HASH JOIN OUTER                     |                         |
|  10 |        HASH JOIN OUTER                    |                         |
|  11 |         TABLE ACCESS STORAGE FULL         | VIEW1                   |
|  12 |         TABLE ACCESS STORAGE FULL         | TABLEA                  |
|  13 |        INDEX STORAGE FAST FULL SCAN       | TABLEB                  |
|  14 |     VIEW                                  |                         |
|  15 |      SORT GROUP BY                        |                         |
|  16 |       HASH JOIN SEMI                      |                         |
|  17 |        TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1                  |
|  18 |         INDEX RANGE SCAN                  | TABLE1_N5               |
|  19 |        VIEW                               |                         |
|  20 |         SORT GROUP BY                     |                         |
|  21 |          HASH JOIN OUTER                  |                         |
|  22 |           HASH JOIN OUTER                 |                         |
|  23 |            TABLE ACCESS STORAGE FULL      | VIEW1                   |
|  24 |            TABLE ACCESS STORAGE FULL      | TABLEA                  |
|  25 |           INDEX STORAGE FAST FULL SCAN    | TABLEB                  |
-----------------------------------------------------------------------------

我也有与上面类似的查询，但代替“此处的查询 1”部分，我有一个非常简单的查询，例如“select max（某个日期），dim from t group by dim”，它返回更多数据（我确定它），但它运行得很快并给了我一个结果，但是当在上面的查询中结合使用时，它会一直运行，我必须手动终止它

** 我不是oracle/sql开发人员，我只是使用select查询来获取数据，有时当返回的数据太大时，我会尝试直接在查询中进行转换以避免后处理

标签： sqloracleexists