Transcript of SQLチューニング総合診療Oracle CloudWorld出張所
- 1. 1
- 2. 2
- 3. 3
- 4. 4
- 5. 5 select count(1) from foober;
- 6. 6 select count(1) from foober;
- 7. 7 select count(1) from foober;
- 8. 8 select count(1) from foober;
- 9. 9 select count(1) from foober;
- 10. 10 select count(1) from foober;
- 11. 11 select count(1) from foober;
- 12. 12 select count(1) from foober;
- 13. 13 select count(1) from foober;
- 14. 14 select count(1) from foober;
- 15. 15
- 16. 16
- 17. 17 select count(1) from foober;
- 18. 18 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset
5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '00' 8* AND ROWNUM r 1
SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1
AND 10 7* AND status = '00' COUNT(1) ---------- 10000 SCOTT> r 1
SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1
AND 10 7 AND status = '00' 8* AND ROWNUM r 1 SELECT 2 COUNT(1) 3
FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status =
'01' COUNT(1) ---------- 0
- 19. 19 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset
5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1)
---------- 10000 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4
count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8*
AND ROWNUM r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6
id BETWEEN 1 AND 10 7* AND status = 01' COUNT(1) ---------- 0
- 20. 20 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset
5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1)
---------- 10000 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4
count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8*
AND ROWNUM r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6
id BETWEEN 1 AND 10 7* AND status = 01' AND ROWNUM r 1 SELECT 2
COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7*
AND status = :3 COUNT(1) ---------- 10000 SCOTT> r 1 SELECT 2
COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7
AND status = :3 8* AND ROWNUM desc count4emptyset NULL?
----------------------------------------- --------
---------------------------- ID NOT NULL NUMBER BRANCH# NOT NULL
NUMBER STATUS NOT NULL CHAR(2) DATA CHAR(500) INDEX_NAME
COLUMN_NAME DESC ------------------------------
------------------------------ ---- PK_COUNT4EMPTYSET ID ASC
BRANCH# ASC COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------ ------------ ----------
--------------- ID 1000 0 NONE BRANCH# 1999 0 NONE STATUS 1 0
FREQUENCY DATA 1000 0 NONE INDEX_NAME DISTINCT_KEYS NUM_ROWS
CLUSTERING_FACTOR ------------------------------ -------------
---------- ----------------- PK_COUNT4EMPTYSET 1000000 1000000
987729
- 25. 25 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset
5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1)
---------- 10000
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |..| 9931 (1)|..| | 1 | SORT AGGREGATE
| | 1 |..| |..| |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED|
COUNT4EMPTYSET | 10008 |..| 9931 (1)|..| |* 3 | INDEX RANGE SCAN |
PK_COUNT4EMPTYSET | 10009 |..| 42 (0)|..|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 -
filter("STATUS"='00') 3 - access("ID">=1 AND "ID" r 1 SELECT 2
COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7*
AND status = '00' COUNT(1) ---------- 10000
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |..| 9931 (1)|..| | 1 | SORT AGGREGATE
| | 1 |..| |..| |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED|
COUNT4EMPTYSET | 10008 |..| 9931 (1)|..| |* 3 | INDEX RANGE SCAN |
PK_COUNT4EMPTYSET | 10009 |..| 42 (0)|..|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 -
filter("STATUS"='00') 3 - access("ID">=1 AND "ID" r 1 SELECT 2
COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7*
AND status = '00' COUNT(1) ---------- 10000
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |..| 9931 (1)|..| | 1 | SORT AGGREGATE
| | 1 |..| |..| |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED|
COUNT4EMPTYSET | 10008 |..| 9931 (1)|..| |* 3 | INDEX RANGE SCAN |
PK_COUNT4EMPTYSET | 10009 |..| 42 (0)|..|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 -
filter("STATUS"='00') 3 - access("ID">=1 AND "ID"=1 AND "ID" r 1
SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1
AND 10 7* AND status = '00' COUNT(1) ---------- 10000
----------------------------- 9893 consistent gets 0 physical reads
SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6
id BETWEEN 1 AND 10 7* AND status = '01' COUNT(1) ---------- 0
----------------------------- 9893 consistent gets 0 physical
reads
- 30. 30
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |..| 9931 (1)|..| | 1 | SORT AGGREGATE
| | 1 |..| |..| |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED|
COUNT4EMPTYSET | 10008 |..| 9931 (1)|..| |* 3 | INDEX RANGE SCAN |
PK_COUNT4EMPTYSET | 10009 |..| 42 (0)|..|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 -
filter("STATUS"='00') 3 - access("ID">=1 AND "ID"=1 AND
"STATUS"='00' AND "ID" r 1 SELECT 2 COUNT(1) 3 FROM 4
count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '00' 8*
AND ROWNUM