中间表--简例说明

最新推荐文章于 2025-05-14 11:17:18 发布

TBNoO

最新推荐文章于 2025-05-14 11:17:18 发布

阅读量2.6k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle数据库开发

本文链接：https://blog.youkuaiyun.com/badyflf/article/details/51211530

Oracle数据库开发专栏收录该内容

48 篇文章

订阅专栏

本文通过具体案例展示了如何通过创建中间表来优化复杂SQL查询的执行效率，并对比了不同查询方式的执行计划及性能表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> select t.sid, t.sname
  2    from t_student t
  3   where t.sid not in
  4         (select distinct t.sid
  5            from t_score t
  6           where t.cid in
  7                 (select t.cid
  8                    from t_course t
  9                   where t.tid =
 10                         (select t.tid from t_teacher t where t.tname = '叶平')));

       SID SNAME
---------- --------------------
   2005006 kdj
   2005007 bsk
   2005008 hbl
   2005009 hqb
   2005010 dm

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 668890525

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     9 |    90 |    51   (6)| 00:00:01 |
|*  1 |  FILTER              |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T_STUDENT |    10 |   100 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |           |     1 |    20 |     7  (15)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | T_COURSE  |     1 |    10 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T_TEACHER |     1 |    11 |     3   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | T_SCORE   |     2 |    20 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_SCORE" "T","T_COURSE"
              "T" WHERE "T"."TID"= (SELECT /*+ */ "T"."TID" FROM "T_TEACHER" "T" WHERE
              "T"."TNAME"='叶平') AND "T"."CID"="T"."CID" AND LNNVL("T"."SID"<>:B1)))
   3 - access("T"."CID"="T"."CID")
   4 - filter("T"."TID"= (SELECT /*+ */ "T"."TID" FROM "T_TEACHER" "T"
              WHERE "T"."TNAME"='叶平'))
   5 - filter("T"."TNAME"='叶平')
   6 - filter(LNNVL("T"."SID"<>:B1))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        140  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> select sid, sname
  2    from t_student
  3   where sid not in (select distinct t1.sid
  4                       from t_score t1, t_course t2, t_teacher t3
  5                      where t1.cid = t2.cid
  6                        and t3.tid = t2.tid
  7                        and t3.tname = '叶平');

       SID SNAME
---------- --------------------
   2005006 kdj
   2005007 bsk
   2005008 hbl
   2005009 hqb
   2005010 dm

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 1085620063

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     9 |    90 |    51   (6)| 00:00:01 |
|*  1 |  FILTER                |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL    | T_STUDENT |    10 |   100 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN            |           |     1 |    31 |    10  (10)| 00:00:01 |
|   4 |    MERGE JOIN CARTESIAN|           |     2 |    42 |     6   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL  | T_TEACHER |     1 |    11 |     3   (0)| 00:00:01 |
|   6 |     BUFFER SORT        |           |     2 |    20 |     3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL | T_SCORE   |     2 |    20 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL   | T_COURSE  |     7 |    70 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TEACHER" "T3","T_COURSE"
              "T2","T_SCORE" "T1" WHERE LNNVL("T1"."SID"<>:B1) AND "T3"."TID"="T2"."TID"
              AND "T1"."CID"="T2"."CID" AND "T3"."TNAME"='叶平'))
   3 - access("T1"."CID"="T2"."CID" AND "T3"."TID"="T2"."TID")
   5 - filter("T3"."TNAME"='叶平')
   7 - filter(LNNVL("T1"."SID"<>:B1))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        178  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          5  rows processed

创建中间表进行性能的缓冲

create table t_sc_cs_tc
as
select distinct t1.sid  
                     from t_score t1, t_course t2, t_teacher t3  
                    where t1.cid = t2.cid  
                      and t3.tid = t2.tid  
                      and t3.tname = '叶平'

优化后的执行计划：

SQL> select t.sid, t.sname
  2    from t_student t
  3   where t.sid not in (select t2.sid from t_sc_cs_tc t2);

       SID SNAME
---------- --------------------
   2005006 kdj
   2005007 bsk
   2005008 hbl
   2005009 hqb
   2005010 dm

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 54380670

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     9 |    90 |    13   (0)| 00:00:01 |
|*  1 |  FILTER            |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_STUDENT  |    10 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T_SC_CS_TC |     5 |    65 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_SC_CS_TC" "T2" WHERE
              LNNVL("T2"."SID"<>:B1)))
   3 - filter(LNNVL("T2"."SID"<>:B1))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed