- 论坛徽章:
- 0
|
有分析函数的sql和没有分析函数的sql执行顺序不同后得到的统计信息不一样？
为什么？
--先执行 没有分析函数的sql 再 执行有分析函数的sql 后的结果：
SQL> set autot traceonly ;
SQL> select a.OrderID,a.ServiceID,a.CompleteDate,b.SusType,nvl(b.OldStatus,0)
2 from
3 tabA a, tabB b where a.OrderID in ( select max(a.orderID)
4 from tabA a,tabB b where a.serviceID>=1 and
5 a.serviceID<10000000000 and a.CompleteDate<TO_DATE ('2007-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and a.OrderType=
6 '4' and a.OrderID=b.OrderID group by a.ServiceID,b.SusType ) and a.orderID=
7 b.OrderID order by a.serviceID,a.OrderID;
177 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 VIEW OF 'VW_NSO_1'
6 5 SORT (UNIQUE)
7 6 SORT (GROUP BY)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
9 8 NESTED LOOPS
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
11 10 INDEX (RANGE SCAN) OF 'TABA_IDX_SERVICEID' (NON-UNIQUE)
12 9 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
13 4 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
14 13 INDEX (UNIQUE SCAN) OF 'PK_TABA' (UNIQUE)
15 3 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19044 consistent gets
213 physical reads
68 redo size
6780 bytes sent via SQL*Net to client
773 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
177 rows processed
SQL> SELECT orderid, serviceid, completedate, sustype, oldstatus
2 FROM (SELECT a.orderid, a.serviceid, a.completedate, b.sustype sustype,NVL (b.oldstatus, 0) oldstatus,
3 ROW_NUMBER () OVER (PARTITION BY a.serviceid, b.sustype ORDER BY a.orderid DESC) as pos
4 FROM serviceorder a, suspensionorder b
5 WHERE a.serviceid >= 1
6 AND a.serviceid < 10000000000
7 AND a.completedate < TO_DATE ('2007-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
8 AND a.ordertype = '4'
9 AND a.orderid = b.orderid)
10 WHERE pos = 1
11 ORDER BY serviceid, orderid;
177 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 VIEW
3 2 WINDOW (SORT PUSHED RANK)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
5 4 NESTED LOOPS
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
7 6 INDEX (RANGE SCAN) OF 'TABA_IDX_SERVICEID' (NON-UNIQUE)
8 5 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18680 consistent gets
0 physical reads
0 redo size
6771 bytes sent via SQL*Net to client
773 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
177 rows processed
--先执行 有分析函数的sql 再执行 没有有分析函数的sql 后的结果：
SQL> set autot traceonly;
SQL> SELECT orderid, serviceid, completedate, sustype, oldstatus
2 FROM (SELECT a.orderid, a.serviceid, a.completedate, b.sustype sustype,NVL (b.oldstatus, 0) oldstatus,
3 ROW_NUMBER () OVER (PARTITION BY a.serviceid, b.sustype ORDER BY a.orderid DESC) as pos
4 FROM serviceorder a, suspensionorder b
5 WHERE a.serviceid >= 1
6 AND a.serviceid < 10000000
7 AND a.completedate < TO_DATE ('2007-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
8 AND a.ordertype = '4'
9 AND a.orderid = b.orderid)
10 WHERE pos = 1
11 ORDER BY serviceid, orderid;
177 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 VIEW
3 2 WINDOW (SORT PUSHED RANK)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
5 4 NESTED LOOPS
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
7 6 INDEX (RANGE SCAN) OF 'TABA_IDX_SERVICEID' (NON-UNIQUE)
8 5 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18677 consistent gets
0 physical reads
0 redo size
6771 bytes sent via SQL*Net to client
773 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
177 rows processed
SQL> select a.OrderID,a.ServiceID,a.CompleteDate,b.SusType,nvl(b.OldStatus,0)
2 from
3 tabA a, tabB b where a.OrderID in ( select max(a.orderID)
4 from tabA a,tabB b where a.serviceID>=1 and
5 a.serviceID<10000000 and a.CompleteDate<TO_DATE ('2007-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and a.OrderType=
6 '4' and a.OrderID=b.OrderID group by a.ServiceID,b.SusType ) and a.orderID=
7 b.OrderID order by a.serviceID,a.OrderID;
177 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 VIEW OF 'VW_NSO_1'
6 5 SORT (UNIQUE)
7 6 SORT (GROUP BY)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TABB'
9 8 NESTED LOOPS
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
11 10 INDEX (RANGE SCAN) OF 'TABA_IDX_SERVICEID' (NON-UNIQUE)
12 9 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
13 4 TABLE ACCESS (BY INDEX ROWID) OF 'TABA'
14 13 INDEX (UNIQUE SCAN) OF 'PK_TABA' (UNIQUE)
15 3 INDEX (RANGE SCAN) OF 'SUSPENSION_IDX_ORDID' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19034 consistent gets
0 physical reads
0 redo size
6780 bytes sent via SQL*Net to client
773 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
177 rows processed
SQL> |
|
免费注册
发表于 2007-03-15 11:50
