C:\>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 화 4월 12 01:17:10 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> set linesize 130
SQL> set pagesize 0
SQL> DROP TABLE t1 PURGE;

테이블이 삭제되었습니다.

SQL> DROP TABLE t2 PURGE;

테이블이 삭제되었습니다.

SQL> DROP TABLE t3 PURGE;

테이블이 삭제되었습니다.

SQL> CREATE TABLE t1(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE TABLE t2(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE TABLE t3(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE INDEX t1_n1 ON t1(c1);

인덱스가 생성되었습니다.

SQL> CREATE INDEX t2_n1 ON t2(c1);

인덱스가 생성되었습니다.

SQL> CREATE INDEX t3_n1 ON t3(c1);

인덱스가 생성되었습니다.

SQL> ALTER SESSION SET "_optimizer_cost_based_transformation" = off;

세션이 변경되었습니다.

SQL> ALTER SESSION SET "_optimizer_push_pred_cost_based" = false;

세션이 변경되었습니다.

SQL> INSERT INTO t1
  2  SELECT level              c1    -- 유일값(1~10000)
  3       , 'dummy'            c2    -- 동일값('dummy')
  4       , MOD(level, 10) + 1 c3    -- 10개값(1~10)
  5    FROM dual
  6   CONNECT BY level <= 10000
  7  ;

10000 개의 행이 만들어졌습니다.

SQL> INSERT INTO t2
  2  SELECT level
  3       , 'dummy'
  4       , MOD(level, 10) + 1
  5    FROM dual
  6   CONNECT BY level <= 1000
  7  ;

1000 개의 행이 만들어졌습니다.

SQL> INSERT INTO t3
  2  SELECT level
  3       , 'dummy'
  4       , MOD(level, 10) + 1
  5    FROM dual
  6   CONNECT BY level <= 100
  7  ;

100 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T1');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T2');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T3');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT COUNT(*)
  2    FROM (SELECT /*+ gather_plan_statistics */
  3                 t1.c1, t1.c2
  4            FROM t1
  5           WHERE t1.c1 IN (SELECT /*+ NO_UNNEST */ t2.c1 FROM t2)
  6          )
  7  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  9bm19ht6vrpg5, child number 0
-------------------------------------
SELECT COUNT(*)   FROM (SELECT /*+ gather_plan_statistics */                t1.c1, t1.c2
       FROM t1          WHERE t1.c1 IN (SELECT /*+ NO_UNNEST */ t2.c1 FROM t2)         )

Plan hash value: 645528743

----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |       |      1 |      1 |            |      1 |00:00:00.04 |   20038 |
|*  2 |   FILTER            |       |      1 |        |            |   1000 |00:00:00.01 |   20038 |
|   3 |    TABLE ACCESS FULL| T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      38 |
|*  4 |    INDEX RANGE SCAN | T2_N1 |  10000 |      1 |     1   (0)|   1000 |00:00:00.03 |   20000 |
----------------------------------------------------------------------------------------------------

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

   2 - filter( IS NOT NULL)
   4 - access("T2"."C1"=:B1)


22 개의 행이 선택되었습니다.

SQL> SELECT COUNT(*)
  2    FROM (SELECT /*+ gather_plan_statistics */
  3                 t1.c1, t1.c2
  4            FROM t1
  5           WHERE t1.c1 IN (SELECT t2.c1 FROM t2)
  6          )
  7  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  9g821jv5f7kgv, child number 0
-------------------------------------
SELECT COUNT(*)   FROM (SELECT /*+ gather_plan_statistics */                t1.c1, t1.c2           FROM t1          WHERE
t1.c1 IN (SELECT t2.c1 FROM t2)         )

Plan hash value: 911817144

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.01 |      31 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI |       |      1 |    999 |    10  (10)|   1000 |00:00:00.01 |      31 |  1066K|  1066K| 1174K (0)|
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      24 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="T2"."C1")


21 개의 행이 선택되었습니다.

SQL>

SELECT COUNT(*)
  FROM (SELECT *
          FROM t1 SEMI JOIN t2
         WHERE t1.c1 = t2.c1
        )
;