SQL改写案例
oradebug setospid 45744416oradebug unlimit
oradebug event 10053 trace name context forever,level 1
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM
sh.sales s,sh.times t,sh.customers c,sh.channels ch
WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND
s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND
ch.channel_desc IN ('Internet','Catalog') AND
t.calendar_quarter_desc IN ('2002-01','2002-02')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
alter session set events '10053 trace name context forever, level 1';
1. 特别简单又容易忽视的场景：
准备环境：
SQL> create table t3( avarchar2(20)) ;
Table created.
SQL> begin
2 for i in 1..100000
3 loop
4 insert into t3 values(to_char(i));
5 end loop;
6 commit;
7end;
8/
PL/SQL procedure successfully completed.
SQL> create index i3 on t3(a) ;
Index created.
测试执行计划1：
SQL> set autot on
SQL> select * from t3 where a=999;
A
--------------------
999
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 3 | 36 | 69 (2)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T3 | 3 | 36 | 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("A")=999)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
8recursive calls
5db block gets
243consistent gets
0physical reads
1008redo size
544bytes sent via SQL*Net to client
390bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
测试执行计划2：
SQL> select * from t3 where a='999';
A
--------------------
999
Execution Plan
----------------------------------------------------------
Plan hash value: 2553414617
-------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|*1 |INDEX RANGE SCAN| I3 | 1 | 12 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"='999')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
9recursive calls
0db block gets
52consistent gets
1physical reads
0redo size
544bytes sent via SQL*Net to client
392bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
2. 关联子查询改写（顺便研究sql 10046 trace）：
LISTPRICE_RELATE的trace：
SQL> conn sh/oracle_4U@rcat
Connected.
SQL> alter session set tracefile_identifier='LISTPRICE_RELATE';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select /*LISTJOIN_RELATE*/ count(*) from products p
wherep.prod_list_price < 1.15*(selectavg(unit_cost) avg from costs c
where c.prod_id=p.prod_id);2 3
COUNT(*)
----------
46
SQL>alter session set sql_trace=false;
Session altered.
LISTPRICE_JOIN的trace：
SQL> conn sh/oracle_4U@rcat
Connected.
SQL> alter session set tracefile_identifier='LISTPRICE_JOIN';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select /*LISTPRICE_JOIN*/ count(*)from products p,
(selectprod_id,avg(unit_cost) avg from costsgroup by prod_id) a
where p.prod_id=a.prod_id and
p.prod_list_price < 1.15* a.avg;2 3 4
COUNT(*)
----------
46
SQL>alter session set sql_trace=false;
Session altered.
/u01/app/oracle/diag/rdbms/rcat/rcat/trace：
$ ls *LISTPRICE*
rcat_ora_1519515_LISTPRICE_RELATE.trcrcat_ora_1536602_LISTPRICE_JOIN.trc
rcat_ora_1519515_LISTPRICE_RELATE.trmrcat_ora_1536602_LISTPRICE_JOIN.trm
trcsess合并：
$ trcsess output=/home/oracle/LISTPRICE.trc service=rcat.example.com rcat_ora_1519515_LISTPRICE_RELATE.trc rcat_ora_1536602_LISTPRICE_JOIN.trc
tkprof解读：
$ tkprof /home/oracle/LISTPRICE.trc /home/oracle/report.txt explain=sh/oracle_4U@rcat
********************************************************************************
select /*LISTPRICE_JOIN*/ count(*)from products p,
(selectprod_id,avg(unit_cost) avg from costsgroup by prod_id) a
where p.prod_id=a.prod_id and
p.prod_list_price < 1.15* a.avg
call count cpu elapsed disk query current rows
------- -------------- ---------- ---------- ---------- --------------------
Parse 1 0.03 0.03 0 2778 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 19 0 1
------- -------------- ---------- ---------- ---------- --------------------
total 4 0.04 0.04 0 2797 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105(SH)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)Row Source Operation
---------- ---------- -------------------------------------------------------------
1 1 1SORT AGGREGATE (cr=68 pr=0 pw=0 time=11765 us starts=1)
46 46 46 HASH JOIN(cr=68 pr=0 pw=0 time=11773 us starts=1 cost=17 size=140 card=4)
72 72 72 TABLE ACCESS FULL PRODUCTS (cr=3 pr=0 pw=0 time=81 us starts=1 cost=3 size=648 card=72)
72 72 72 VIEW(cr=16 pr=0 pw=0 time=6535 us starts=1 cost=14 size=1872 card=72)
72 72 72 HASH GROUP BY (cr=16 pr=0 pw=0 time=6521 us starts=1 cost=14 size=648 card=72)
1068 1068 1068 PARTITION RANGE ALL PARTITION: 1 28 (cr=16 pr=0 pw=0 time=7629 us starts=1 cost=13 size=739008 card=82112)
1068 1068 1068 TABLE ACCESS INMEMORY FULL COSTS PARTITION: 1 28 (cr=16 pr=0 pw=0 time=5671 us starts=28 cost=13 size=739008 card=82112)
Rows Execution Plan
----------------------------------------------------------
0SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
46 HASH JOIN
72 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PRODUCTS' (TABLE)
72 VIEW
72 HASH (GROUP BY)
1068 PARTITION RANGE (ALL) PARTITION: START=1 STOP=28
1068 TABLE ACCESS MODE: ANALYZED (INMEMORY FULL) OF
'COSTS' (TABLE) PARTITION: START=1 STOP=28
********************************************************************************
select /*LISTJOIN_RELATE*/ count(*) from products p
wherep.prod_list_price < 1.15*(selectavg(unit_cost) avg from costs c
where c.prod_id=p.prod_id)
call count cpu elapsed disk query current rows
------- -------------- ---------- ---------- ---------- --------------------
Parse 1 0.01 0.01 0 58 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 19 0 1
------- -------------- ---------- ---------- ---------- --------------------
total 4 0.02 0.02 0 77 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105(SH)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)Row Source Operation
---------- ---------- -------------------------------------------------------------
1 1 1SORT AGGREGATE (cr=19 pr=0 pw=0 time=7879 us starts=1)
46 46 46 HASH JOIN(cr=19 pr=0 pw=0 time=7882 us starts=1 cost=17 size=140 card=4)
72 72 72 NESTED LOOPS(cr=16 pr=0 pw=0 time=7567 us starts=1 cost=17 size=140 card=4)
72 72 72 NESTED LOOPS(cr=16 pr=0 pw=0 time=7493 us starts=1)
72 72 72 STATISTICS COLLECTOR(cr=16 pr=0 pw=0 time=7490 us starts=1)
72 72 72 VIEWVW_SQ_1 (cr=16 pr=0 pw=0 time=7381 us starts=1 cost=14 size=1872 card=72)
72 72 72 HASH GROUP BY (cr=16 pr=0 pw=0 time=7362 us starts=1 cost=14 size=648 card=72)
1068 1068 1068 PARTITION RANGE ALL PARTITION: 1 28 (cr=16 pr=0 pw=0 time=8594 us starts=1 cost=13 size=739008 card=82112)
1068 1068 1068 TABLE ACCESS INMEMORY FULL COSTS PARTITION: 1 28 (cr=16 pr=0 pw=0 time=6363 us starts=28 cost=13 size=739008 card=82112)
0 0 0 INDEX UNIQUE SCAN PRODUCTS_PK (cr=0 pr=0 pw=0 time=0 us starts=0)(object id 92749)
0 0 0 TABLE ACCESS BY INDEX ROWID PRODUCTS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=9 card=1)
72 72 72 TABLE ACCESS FULL PRODUCTS (cr=3 pr=0 pw=0 time=46 us starts=1 cost=3 size=648 card=72)
Rows Execution Plan
----------------------------------------------------------
0SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
46 HASH JOIN
72 NESTED LOOPS
72 NESTED LOOPS
72 STATISTICS COLLECTOR
72 VIEW OF 'VW_SQ_1' (VIEW)
72 HASH (GROUP BY)
1068 PARTITION RANGE (ALL) PARTITION: START=1 STOP=28
1068 TABLE ACCESS MODE: ANALYZED (INMEMORY FULL) OF
'COSTS' (TABLE) PARTITION: START=1 STOP=28
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PRODUCTS_PK'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PRODUCTS' (TABLE)
72 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PRODUCTS' (TABLE)
********************************************************************************
SQL>select 'OK' from dual where exists (select a from t3 where rownum=1);
'O
--
OK
Execution Plan
----------------------------------------------------------
Plan hash value: 1170111551
--------------------------------------------------------------------
| Id| Operation | Name | Rows| Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
|*1 |FILTER | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
|*3 | COUNT STOPKEY | | | | |
| 4 | TABLE ACCESS FULL| T3 | 81798 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "T3" "T3" WHERE ROWNUM=1))
3 - filter(ROWNUM=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
7recursive calls
0db block gets
8consistent gets
0physical reads
0redo size
546bytes sent via SQL*Net to client
431bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
SQL> select 'OK' from dual where (selectcount(*) from t3) >=1 ;
'O
--
OK
Execution Plan
----------------------------------------------------------
Plan hash value: 1926481175
--------------------------------------------------------------------
| Id| Operation | Name | Rows| Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 (0)| 00:00:01 |
|*1 |FILTER | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | | |
| 4 | TABLE ACCESS FULL| T3 | 81798 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT COUNT(*) FROM "T3" "T3")>=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
4recursive calls
0db block gets
193consistent gets
0physical reads
0redo size
546bytes sent via SQL*Net to client
421bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
3 . with clause：
SQL> set linesize 10000
SQL> set autot traceonly
SQL> select prod_id, amount_sold from sales
2 where amount_sold= (select max(amount_sold) from sales)
3union all
4select prod_id, amount_sold from sales
5 where amount_sold= (select min(amount_sold) from sales)
6union all
7select prod_id, amount_sold from sales
8 where amount_sold= (select avg(amount_sold) from sales)
9;
292 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3943716753
-------------------------------------------------------------------------------------------------
| Id| Operation | Name| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1536 | 13824 | 26734 (1)| 00:00:02 | | |
| 1 |UNION-ALL | | | | | | | |
| 2 | PARTITION RANGE ALL | | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
|*3 | TABLE ACCESS FULL | SALES | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
| 4 | SORT AGGREGATE | | 1 | 5 | | | | |
| 5 | PARTITION RANGE ALL| |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
| 7 | PARTITION RANGE ALL | | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
|*8 | TABLE ACCESS FULL | SALES | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
| 9 | SORT AGGREGATE | | 1 | 5 | | | | |
|10 | PARTITION RANGE ALL| |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
|11 | TABLE ACCESS FULL | SALES |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
|12 | PARTITION RANGE ALL | | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
|* 13 | TABLE ACCESS FULL | SALES | 512 |4608 |4457 (1)| 00:00:01 | 1 | 28 |
|14 | SORT AGGREGATE | | 1 | 5 | | | | |
|15 | PARTITION RANGE ALL| |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
|16 | TABLE ACCESS FULL | SALES |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AMOUNT_SOLD"= (SELECT MAX("AMOUNT_SOLD") FROM "SALES" "SALES"))
8 - filter("AMOUNT_SOLD"= (SELECT MIN("AMOUNT_SOLD") FROM "SALES" "SALES"))
13 - filter("AMOUNT_SOLD"= (SELECT AVG("AMOUNT_SOLD") FROM "SALES" "SALES"))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
39680consistent gets
0physical reads
0redo size
5765bytes sent via SQL*Net to client
761bytes received via SQL*Net from client
21SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
292rows processedSQL> with s as ( selectmax(amount_sold) max,
2 min(amount_sold) min,
3 avg(amount_sold) avg
4 from sales)
5select prod_id, amount_sold from sales
6 where amount_sold in (selectmax from s union all selectmin from s union all select avg from s )
7;
292 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2588622809
------------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1537 | 33814 |8920 (1)| 00:00:01 | | |
| 1 |TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D663D_1F26D4 | | | | | | |
| 3 | SORT AGGREGATE | | 1 | 5 | | | | |
| 4 | PARTITION RANGE ALL | |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES |1837K|8973K|4454 (1)| 00:00:01 | 1 | 28 |
|*6 | HASH JOIN | |1537 | 33814 |4465 (1)| 00:00:01 | | |
| 7 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 | | |
| 8 | HASH UNIQUE | | 3 | 39 | 6 (0)| 00:00:01 | | |
| 9 | UNION-ALL | | | | | | | |
|10 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | |
|11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_1F26D4 | 1 | 5 | 2 (0)| 00:00:01 | | |
|12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | |
|13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_1F26D4 | 1 | 5 | 2 (0)| 00:00:01 | | |
|14 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | |
|15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_1F26D4 | 1 | 5 | 2 (0)| 00:00:01 | | |
|16 | PARTITION RANGE ALL | |1837K| 15M|4454 (1)| 00:00:01 | 1 | 28 |
|17 | TABLE ACCESS FULL | SALES |1837K| 15M|4454 (1)| 00:00:01 | 1 | 28 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AMOUNT_SOLD"="MAX")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
2recursive calls
8db block gets
13251consistent gets
1physical reads
624redo size
6707bytes sent via SQL*Net to client
761bytes received via SQL*Net from client
21SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
292rows processed
4. merge:
SQL> create table t1 (a varchar2(20), b number);
Table created.
SQL> create table t2 (a varchar2(20), b number);
Table created.
SQL> insert into t1 values('a', 1) ;
1 row created.
SQL> insert into t1 values('b', 2);
1 row created.
SQL> insert into t1 values('c', 3);
1 row created.
SQL>insert into t1 values('xxx',100);
1 row created.
SQL> insert into t1 values('yyy',200);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t2 values ('a',10);
1 row created.
SQL> insert into t2 values ('b',20);
1 row created.
SQL> insert into t2 values ('xxx',30);
1 row created.
SQL> insert into t2 values ('yyy',40);
1 row created.
SQL> commit;
Commit complete.
merge into t2
using t1
on (t1.a=t2.a)
when matched then
update
set t2.b=t1.b*2
merge into t2
using t1
on (t1.a=t2.a)
when matched then
update
set t2.b=t1.b*2
where t1.a='xxx'
merge into t2
using t1
on (t1.a=t2.a)
when matched then
update
set t2.b=t1.b*2
delete where t2.a='yyy'
merge into t2
using t1
on (1<>1)
when not matched then
insert
values (t1.a, t1.b)
页:
[1]