oracle大表在前还是小表在前,Where语句大表放前和小表放前的区别？

連線到:

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production

With the Partitioning option

JServer Release 8.1.7.0.1 - Production

SQL> set autot on;

SQL> select b.* from bsempms a,pdwrkms b

2  where a.emp_no=b.emp_no and a.emp_no='s1895'

3  /

沒有任何資料列被選取

執行計畫

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1452 Card=2107 Bytes

=168560)

1    0   NESTED LOOPS (Cost=1452 Card=2107 Bytes=168560)

2    1     INDEX (UNIQUE SCAN) OF 'PK_BSEMPMS' (UNIQUE) (Cost=1 Car

d=1 Bytes=6)

3    1     TABLE ACCESS (BY INDEX ROWID) OF 'PDWRKMS' (Cost=1451 Ca

rd=2107 Bytes=155918)

4    3       INDEX (RANGE SCAN) OF 'Srch' (NON-UNIQUE) (Cost=11 Car

d=2107)

統計值

----------------------------------------------------------

170  recursive calls

0  db block gets

26  consistent gets

1  physical reads

0  redo size

927  bytes sent via SQL*Net to client

237  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

0  rows processed

SQL> ed

已將 檔案 afiedt.buf 寫入

1  select a.* from pdwrkms a,bsempms b

2* where a.emp_no=b.emp_no and a.emp_no='s1895'

SQL> /

沒有任何資料列被選取

執行計畫

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1452 Card=2107 Bytes

=168560)

1    0   NESTED LOOPS (Cost=1452 Card=2107 Bytes=168560)

2    1     INDEX (UNIQUE SCAN) OF 'PK_BSEMPMS' (UNIQUE) (Cost=1 Car

d=1 Bytes=6)

3    1     TABLE ACCESS (BY INDEX ROWID) OF 'PDWRKMS' (Cost=1451 Ca

rd=2107 Bytes=155918)

4    3       INDEX (RANGE SCAN) OF 'Srch' (NON-UNIQUE) (Cost=11 Car

d=2107)

統計值

----------------------------------------------------------

0  recursive calls

0  db block gets

2  consistent gets

0  physical reads

0  redo size

927  bytes sent via SQL*Net to client

237  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

0  rows processed

SQL>