同样的SQL语句在两台数据库服务器上执行(两个数据库的结构都是一样的),执行计划和响应时间却是相差很大,请问这是怎么回事:
sql语句: select count(*) from table1 a,table1 b,table c
where c.parentid=b.id and b.parentid=a.id and a.parentid=1 and c.type=5 and b.type=6
/// 执行计划1//
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=76 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=76 Card=7 Bytes=168)
3 2 TABLE ACCESS (FULL) OF 'Table1' (Cost=25 Card=511
Bytes=5110)
4 2 MERGE JOIN (CARTESIAN) (Cost=50 Card=3009 Bytes=42126)
5 4 TABLE ACCESS (FULL) OF 'Table1' (Cost=25 Card=1
Bytes=10)
6 4 BUFFER (SORT) (Cost=25 Card=21 Bytes=84)
7 6 TABLE ACCESS (FULL) OF 'Table1' (Cost=25 Card
=21 Bytes=84)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
744 consistent gets
660 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
//执行计划2///
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=198 Card=7 Bytes=161)
3 2 NESTED LOOPS (Cost=132 Card=1 Bytes=20)
4 3 TABLE ACCESS (FULL) OF 'Table1' (Cost=66 Card=1
Bytes=10)
5 3 TABLE ACCESS (FULL) OF 'Table1' (Cost=66 Card=1
Bytes=10)
6 2 TABLE ACCESS (FULL) OF 'Table1' (Cost=66 Card=21
Bytes=63)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6450 consistent gets
4444 physical reads
0 redo size
377 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)
1 rows processed