|
|
| |
 |
|
Re-writing complex queries performance
Oracle Tips by Burleson Consulting |
SQL> --*********************************************
SQL> -- Using subqueries
SQL> --*********************************************
SQL>
SQL> select
2
store_name,
3
sum(quantity)
store_sales,
4
(select sum(quantity) from sales)/(select
count(*) from store) avg_sales
5
from
6
store
s,
7
sales
sl
8
where
9
s.store_key = sl.store_key
10
having
11
sum(quantity) > (select sum(quantity) from
sales)/(select count(*) from store)
12
group by
13
store_name
14
;
STORE_NAME
STORE_SALES
AVG_SALES
----------------------------------------
----------- ----------
borders
21860
11055
books for dummies
13000
11055
wee bee books
13700
11055
wild and lively books
24700
11055
eaton books
12120
11055
Execution Plan
----------------------------------------------------------
Plan hash value: 3096648791
----------------------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
31 |
4
(25)| 00:00:01 |
|
1 |
SORT AGGREGATE
|
|
1 |
4 |
|
|
|
2 |
TABLE ACCESS FULL
| SALES
| 100
|
400 |
2
(0)| 00:00:01 |
|
3 |
SORT AGGREGATE
|
|
1 |
|
|
|
|
4 |
INDEX FULL SCAN
| SYS_C003999 |
10 |
|
1
(0)| 00:00:01 |
|*
5 |
FILTER
|
|
|
|
|
|
|
6 |
HASH GROUP BY
|
|
1 |
31 |
4
(25)| 00:00:01 |
|
7 |
NESTED LOOPS
|
|
100 |
3100 |
3
(0)| 00:00:01 |
|
8 |
TABLE ACCESS FULL
| SALES
|
100 |
900 |
2
(0)| 00:00:01 |
|
9 |
TABLE ACCESS BY INDEX ROWID| STORE
|
1 |
22 |
1
(0)| 00:00:01 |
|* 10 |
INDEX UNIQUE SCAN
| SYS_C003999 |
1 |
|
0
(0)| 00:00:01 |
|
11 |
SORT AGGREGATE
|
|
1 |
4 |
|
|
|
12 |
TABLE ACCESS FULL
| SALES
|
100 |
400 |
2
(0)| 00:00:01 |
|
13 |
SORT AGGREGATE
|
|
1 |
|
|
|
|
14 |
INDEX FULL SCAN
| SYS_C003999 |
10 |
|
1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(SUM("QUANTITY")> (SELECT SUM("QUANTITY")
FROM "SALES" "SALES")/ (SELECT
COUNT(*) FROM "STORE" "STORE"))
10 - access("S"."STORE_KEY"="SL"."STORE_KEY")
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
113
consistent gets
0
physical reads
0
redo size
682
bytes sent via SQL*Net to client
384
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
5
rows processed
SQL>
SQL> --*********************************************
SQL> -- Using CTAS
SQL> --*********************************************
SQL>
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t3;
Table dropped.
SQL>
SQL> create table t1 as select sum(quantity) all_sales from sales;
Table created.
SQL> create table t2 as select count(*) nbr_stores from store;
Table created.
SQL> create table t3 as select store_name, sum(quantity) store_sales
from store natural join sales group by store_name;
Table created.
SQL>
SQL> select
2
store_name
3
from
4
t1,
5
t2,
6
t3
7
where
8
store_sales > (all_sales / nbr_stores);
STORE_NAME
----------------------------------------
borders
books for dummies
wee bee books
wild and lively books
eaton books
Execution Plan
----------------------------------------------------------
Plan hash value: 2829709172
------------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
61 |
6
(0)| 00:00:01 |
|
1 |
NESTED LOOPS
|
|
1 |
61 |
6
(0)| 00:00:01 |
|
2 |
MERGE JOIN CARTESIAN|
|
1 |
26 |
4
(0)| 00:00:01 |
|
3 |
TABLE ACCESS FULL
| T1
|
1 |
13 |
2
(0)| 00:00:01 |
|
4 |
BUFFER SORT
|
|
1 |
13 |
2
(0)| 00:00:01 |
|
5 |
TABLE ACCESS FULL | T2
|
1 |
13 |
2
(0)| 00:00:01 |
|*
6 |
TABLE ACCESS FULL
| T3
|
1 |
35 |
2
(0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("STORE_SALES">"ALL_SALES"/"NBR_STORES")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
96
recursive calls
0
db block gets
30
consistent gets
3
physical reads
0
redo size
535
bytes sent via SQL*Net to client
384
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
2
sorts (memory)
0
sorts (disk)
5
rows processed
SQL>
SQL> --*********************************************
SQL> -- Using the WITH clause
SQL> --*********************************************
SQL>
SQL> with
2
number_stores as
3
(select count(*) nbr_stores from store),
4
total_sales as
5
(select sum(quantity) all_sales from sales),
6
store_sales as
7
(select store_name, sum(quantity) sales from
store natural join sales group by store_name)
8
select
9
store_name
10
from
11
number_stores,
12
total_sales,
13
store_sales
14
where
15
sales > (all_sales / nbr_stores);
STORE_NAME
----------------------------------------
books for dummies
borders
eaton books
wee bee books
wild and lively books
Execution Plan
----------------------------------------------------------
Plan hash value: 1754281708
-----------------------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
61 |
7
(15)| 00:00:01 |
|
1 |
NESTED LOOPS
|
|
1 |
61 |
7
(15)| 00:00:01 |
|
2 |
NESTED LOOPS
|
|
1 |
26 |
3
(0)| 00:00:01 |
|
3 |
VIEW
|
|
1 |
13 |
1
(0)| 00:00:01 |
|
4 |
SORT AGGREGATE
|
|
1 |
|
|
|
|
5 |
INDEX FULL SCAN
| SYS_C003999 |
10 |
|
1
(0)| 00:00:01 |
|
6 |
VIEW
|
|
1 |
13 |
2
(0)| 00:00:01 |
|
7 |
SORT AGGREGATE
|
|
1 |
4 |
|
|
|
8 |
TABLE ACCESS FULL
| SALES
|
100 |
400 |
2
(0)| 00:00:01 |
|*
9 |
VIEW
|
|
1 |
35 |
4
(25)| 00:00:01 |
|
10 |
SORT GROUP BY
|
|
10 |
310 |
4
(25)| 00:00:01 |
|
11 |
NESTED LOOPS
|
|
100
|
3100 |
3
(0)| 00:00:01 |
|
12 |
TABLE ACCESS FULL
| SALES
|
100 |
900 |
2
(0)| 00:00:01 |
|
13 |
TABLE ACCESS BY INDEX ROWID| STORE
|
1 |
22 |
1
(0)| 00:00:01 |
|* 14 |
INDEX UNIQUE SCAN
| SYS_C003999 |
1 |
|
0
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("SALES">"ALL_SALES"/"NBR_STORES")
14 - access("STORE"."STORE_KEY"="SALES"."STORE_KEY")
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
109
consistent gets
0
physical reads
0
redo size
535
bytes sent via SQL*Net to client
384
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
1
sorts (memory)
0
sorts (disk)
5
rows processed
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
|
|
Burleson is the American
Team
BC
Remote Oracle Support
P.O. Box 511 • Kittrell, NC, 27544
Remote DBA
Copyright © 1996 -
2009 by Burleson Enterprises, Inc. All rights reserved.
Oracle® is the registered trademark of Oracle Corporation.
|
|
