驱动表测试

为了进行此次测试，需要加hints来指定驱动表，如下解释：

ORDERED
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use

the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such

information lets you choose an inner and outer table better than the optimizer could.

ordered_hint::=

Text description of the illustration ordered_hint.gif

The following query is an example of the use of the ORDERED hint:

SELECT  /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
  FROM customers c, order_items l, orders o
 WHERE c.cust_last_name = :b1
   AND o.customer_id = c.customer_id
   AND o.order_id = l.order_id;

1.创建三个表，其中在表a的列x上和表c的x列上创建索引，b上没有索引。

SQL> select count(*) from a;
 
  COUNT(*)
----------
     32768
 
SQL> select count(*) from b;
 
  COUNT(*)
----------
         4
 
SQL> select count(*) from c;
 
  COUNT(*)
----------
     32768
 
SQL> desc a;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER
 B                                                  VARCHAR2(10)
 
SQL> desc b;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Y                                                  NUMBER
 C                                                  VARCHAR2(10)
 
SQL> desc c;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER
 B                                                  VARCHAR2(10)

2. 首先按照a,b,c顺序，此时oracle选择a为驱动表。

SQL> select /*+ ordered */ a.*,b.y,b.c,c.x from a,b,c where a.x=b.y and b.y=c.x and b.c is not null
 
         X B                   Y C                   X
---------- ---------- ---------- ---------- ----------
         1 asdfas              1 asdf                1
         2 asdfas              2 asdf                2
         3 asdfas              3 asdf                3
         4 asdfas              4 asdf                4

Elapsed: 00:00:00.20
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34 Card=4 Bytes=96)
   1    0   NESTED LOOPS (Cost=34 Card=4 Bytes=96)
   2    1     HASH JOIN (Cost=30 Card=4 Bytes=76)
   3    2       TABLE ACCESS (FULL) OF 'A' (Cost=9 Card=32768 Bytes=36
          0448)
 
   4    2       TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=4 Bytes=32)
   5    1     INDEX (RANGE SCAN) OF 'IDX_C' (NON-UNIQUE) (Cost=1 Card=
          1 Bytes=5)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
         35  physical reads
          0  redo size
        655  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
 
3. 再指定b为驱动表

SQL> select /*+ ordered */ a.*,b.y,b.c,c.x from b,c,a where a.x=b.y and b.y=c.x and b.c is not null;
 
         X B                   Y C                   X
---------- ---------- ---------- ---------- ----------
         1 asdfas              1 asdf                1
         2 asdfas              2 asdf                2
         3 asdfas              3 asdf                3
         4 asdfas              4 asdf                4
 
Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=4 Bytes=96)
   1    0   NESTED LOOPS (Cost=10 Card=4 Bytes=96)
   2    1     NESTED LOOPS (Cost=6 Card=4 Bytes=52)
   3    2       TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=4 Bytes=32)
   4    2       INDEX (RANGE SCAN) OF 'IDX_C' (NON-UNIQUE) (Cost=1 Car
          d=1 Bytes=5)
 
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'A' (Cost=1 Card=1 Byte
          s=11)
 
   6    5       INDEX (UNIQUE SCAN) OF 'IDX_A' (UNIQUE)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        655  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
 
SQL> l
  1* select /*+ ordered */ a.*,b.y,b.c,c.x from b,c,a where a.x=b.y and b.y=c.x and b.c is not null

4.再删除c上的索引，选择c为驱动表。

SQL> drop index idx_c;
 
Index dropped.
 
SQL> select /*+ ordered */ a.*,b.y,b.c,c.x from c,a,b where a.x=b.y and b.y=c.x and b.c is not null;
         X B                   Y C                   X
---------- ---------- ---------- ---------- ----------
         1 asdfas              1 asdf                1
         2 asdfas              2 asdf                2
         3 asdfas              3 asdf                3
         4 asdfas              4 asdf                4
 
Elapsed: 00:07:04.62

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=307200 Card=4 Bytes=
          96)
 
   1    0   HASH JOIN (Cost=307200 Card=4 Bytes=96)
   2    1     TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=4 Bytes=32)
   3    1     MERGE JOIN (CARTESIAN) (Cost=294921Card=1073741824Byte
          s=17179869184)
 
   4    3       TABLE ACCESS (FULL) OF 'C' (Cost=9Card=32768Bytes=16
          3840)
 
   5    3       BUFFER (SORT) (Cost=294912Card=32768Bytes=360448)
   6    5         TABLE ACCESS (FULL) OF 'A' (Cost=9 Card=32768 Bytes=
          360448)
 

 
 
 
Statistics
----------------------------------------------------------
        104  recursive calls
          0  db block gets
        171  consistent gets
          0  physical reads
          0  redo size
        655  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          4  rows processed

注意红色字体为巨大的结果集

5.总结
   很明显，在做连接时，选择不同的驱动表，执行效率差异巨大，对于本试验中的三个表a，b，c。驱动表的记录数越少，效率就越高。