in and exists

最新推荐文章于 2025-01-20 16:34:01 发布

原创最新推荐文章于 2025-01-20 16:34:01 发布 · 588 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #statistics #disk

SQL 专栏收录该内容

48 篇文章

订阅专栏

本文通过两个不同大小的表T1和T2进行SQL查询统计，并对比了内表小于外表与内表大于外表时的查询性能差异。结果显示两者间的性能区别并不明显，可能与数据量较小有关。

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

SQL> select count(1) from t1;

COUNT(1)
----------
2337184

SQL> select count(2) from t2;

COUNT(2)
----------

100

SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                          NUMBER
OBJECT_NAME                                        VARCHAR2(128)

SQL> desc t2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                          NUMBER
OBJECT_NAME                                        VARCHAR2(128)

111->内表小于外表

Statistics(exists)
----------------------------------------------------------
         15 recursive calls
         12 db block gets
         53 consistent gets
          0 physical reads
          0 redo size
       1460 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         15 rows processed
222->内表大于外表
Statistics
----------------------------------------------------------
         15 recursive calls
         12 db block gets
         53 consistent gets
          0 physical reads
          0 redo size
       1444 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         15 rows processed

111->内表小于外表
Statistics(in)
----------------------------------------------------------
         15 recursive calls
         12 db block gets
         55 consistent gets
          0 physical reads
          0 redo size
       1450 bytes sent via SQL*Net to client
        524 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         15 rows processed

222->内表大于外表
Statistics
----------------------------------------------------------
         15 recursive calls
         12 db block gets
         53 consistent gets
          0 physical reads
          0 redo size
       1434 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         15 rows processed