oracle表连接-hashjoin哈希连接 - 数据库编程

NUMBER NOT NULL, 4 n NUMBER, 5 pad VARCHAR2(4000), 6 CONSTRAINT t4_pk PRIMARY KEY(id), 7 CONSTRAINT t4_t3_fk FOREIGN KEY (t3_id) REFERENCES t3 8 ); Table created. SQL> execute dbms_random.seed(0) PL/SQL procedure successfully completed. SQL> INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a',50) FROM dual CONNECT BY level <= 10 ORDER BY dbms_random.random; 10 rows created. SQL> INSERT INTO t2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad FROM t1, t1 dummy ORDER BY dbms_random.random; 100 rows created. SQL> INSERT INTO t3 SELECT 1000+rownum, t2.id, 1000+rownum, t2.pad FROM t2, t1 dummy ORDER BY dbms_random.random; 1000 rows created. SQL> INSERT INTO t4 SELECT 10000+rownum, t3.id, 10000+rownum, t3.pad FROM t3, t1 dummy ORDER BY dbms_random.random; 10000 rows created. SQL> COMMIT; Commit complete. 比较 hash 连接, nested loops 连接, sort merge join 连接

SQL> select * from t3, t4 where t3.id = t4.t3_id;


10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1396201636


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |  1250K|    35   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 10000 |  1250K|    35   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T3   |  1000 | 63000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T4   | 10000 |   634K|    29   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - access("T3"."ID"="T4"."T3_ID")



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        779  consistent gets
          0  physical reads
          0  redo size
    1376470  bytes sent via SQL*Net to client
       7745  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed


SQL>




 select /*+ leading(t3) use_nl(t4) */* from t3, t4 where t3.id = t4.t3_id;


10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2039660043


-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 10000 |  1250K| 11007   (1)| 00:02:13 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          | 10000 |  1250K| 11007   (1)| 00:02:13 |
|   3 |    TABLE ACCESS FULL         | T3       |  1000 | 63000 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T4_T3_ID |    10 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T4       |    10 |   650 |    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   4 - access("T3"."ID"="T4"."T3_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12605  consistent gets
          0  physical reads
          0  redo size
     342258  bytes sent via SQL*Net to client
       7745  bytes recei

oracle表连接-hashjoin哈希连接(二)