API tools faq
paste
Advertisement
grahn

Untitled

grahn
Jun 8th, 2011
320
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 8.12 KB | None | 0 0
raw download clone embed print report
  1.  
  2. SQL> CREATE TABLE foo (c1 NUMBER, c2 VARCHAR2(10));
  3.  
  4. TABLE created.
  5.  
  6. SQL> INSERT INTO foo SELECT  3, '003' FROM dual CONNECT BY LEVEL <= 1000000;
  7.  
  8. 1000000 ROWS created.
  9.  
  10. SQL> INSERT INTO foo SELECT 10, '010' FROM dual CONNECT BY LEVEL <= 1000000;
  11.  
  12. 1000000 ROWS created.
  13.  
  14. SQL> COMMIT;
  15.  
  16. COMMIT complete.
  17.  
  18. SQL> EXEC dbms_stats.gather_table_stats(USER,'foo');
  19.  
  20. PL/SQL PROCEDURE successfully completed.
  21.  
  22. SQL> EXPLAIN PLAN FOR
  23.   2  SELECT COUNT(*) FROM foo WHERE c2=003;
  24.  
  25. Explained.
  26.  
  27. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  28.  
  29. PLAN_TABLE_OUTPUT
  30. -------------------------------------------------------------------------------
  31. PLAN hash VALUE: 1342139204
  32.  
  33. ---------------------------------------------------------------------------
  34. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  35. ---------------------------------------------------------------------------
  36. |   0 | SELECT STATEMENT   |      |     1 |     4 |   952   (4)| 00:00:12 |
  37. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  38. |*  2 |   TABLE ACCESS FULL| FOO  |  1000K|  3906K|   952   (4)| 00:00:12 |
  39. ---------------------------------------------------------------------------
  40.  
  41. Predicate Information (IDENTIFIED BY operation id):
  42. ---------------------------------------------------
  43.  
  44.    2 - filter(TO_NUMBER("C2")=003)
  45.  
  46. 14 ROWS selected.
  47.  
  48. SQL> EXPLAIN PLAN FOR
  49.   2  SELECT COUNT(*) FROM foo WHERE c2=010;
  50.  
  51. Explained.
  52.  
  53. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  54.  
  55. PLAN_TABLE_OUTPUT
  56. -------------------------------------------------------------------------------
  57. PLAN hash VALUE: 1342139204
  58.  
  59. ---------------------------------------------------------------------------
  60. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  61. ---------------------------------------------------------------------------
  62. |   0 | SELECT STATEMENT   |      |     1 |     4 |   952   (4)| 00:00:12 |
  63. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  64. |*  2 |   TABLE ACCESS FULL| FOO  |   571K|  2232K|   952   (4)| 00:00:12 |
  65. ---------------------------------------------------------------------------
  66.  
  67. Predicate Information (IDENTIFIED BY operation id):
  68. ---------------------------------------------------
  69.  
  70.    2 - filter(TO_NUMBER("C2")=010)
  71.  
  72. 14 ROWS selected.
  73.  
  74. SQL> EXPLAIN PLAN FOR
  75.   2  SELECT COUNT(*) FROM foo WHERE c2='003';
  76.  
  77. Explained.
  78.  
  79. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  80.  
  81. PLAN_TABLE_OUTPUT
  82. -------------------------------------------------------------------------------
  83. PLAN hash VALUE: 1342139204
  84.  
  85. ---------------------------------------------------------------------------
  86. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  87. ---------------------------------------------------------------------------
  88. |   0 | SELECT STATEMENT   |      |     1 |     4 |   942   (3)| 00:00:12 |
  89. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  90. |*  2 |   TABLE ACCESS FULL| FOO  |  1000K|  3906K|   942   (3)| 00:00:12 |
  91. ---------------------------------------------------------------------------
  92.  
  93. Predicate Information (IDENTIFIED BY operation id):
  94. ---------------------------------------------------
  95.  
  96.    2 - filter("C2"='003')
  97.  
  98. 14 ROWS selected.
  99.  
  100. SQL> EXPLAIN PLAN FOR
  101.   2  SELECT COUNT(*) FROM foo WHERE c2='010';
  102.  
  103. Explained.
  104.  
  105. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  106.  
  107. PLAN_TABLE_OUTPUT
  108. -------------------------------------------------------------------------------
  109. PLAN hash VALUE: 1342139204
  110.  
  111. ---------------------------------------------------------------------------
  112. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  113. ---------------------------------------------------------------------------
  114. |   0 | SELECT STATEMENT   |      |     1 |     4 |   942   (3)| 00:00:12 |
  115. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  116. |*  2 |   TABLE ACCESS FULL| FOO  |  1000K|  3906K|   942   (3)| 00:00:12 |
  117. ---------------------------------------------------------------------------
  118.  
  119. Predicate Information (IDENTIFIED BY operation id):
  120. ---------------------------------------------------
  121.  
  122.    2 - filter("C2"='010')
  123.  
  124. 14 ROWS selected.
  125.  
  126. SQL> -- regather to make histogram
  127. SQL> EXEC dbms_stats.gather_table_stats(USER,'foo');
  128.  
  129. PL/SQL PROCEDURE successfully completed.
  130.  
  131. SQL> EXPLAIN PLAN FOR
  132.   2  SELECT COUNT(*) FROM foo WHERE c2=003;
  133.  
  134. Explained.
  135.  
  136. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  137.  
  138. PLAN_TABLE_OUTPUT
  139. -------------------------------------------------------------------------------
  140. PLAN hash VALUE: 1342139204
  141.  
  142. ---------------------------------------------------------------------------
  143. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  144. ---------------------------------------------------------------------------
  145. |   0 | SELECT STATEMENT   |      |     1 |     4 |   952   (4)| 00:00:12 |
  146. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  147. |*  2 |   TABLE ACCESS FULL| FOO  |     1 |     4 |   952   (4)| 00:00:12 |
  148. ---------------------------------------------------------------------------
  149.  
  150. Predicate Information (IDENTIFIED BY operation id):
  151. ---------------------------------------------------
  152.  
  153.    2 - filter(TO_NUMBER("C2")=003)
  154.  
  155. 14 ROWS selected.
  156.  
  157. SQL> EXPLAIN PLAN FOR
  158.   2  SELECT COUNT(*) FROM foo WHERE c2=010;
  159.  
  160. Explained.
  161.  
  162. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  163.  
  164. PLAN_TABLE_OUTPUT
  165. -------------------------------------------------------------------------------
  166. PLAN hash VALUE: 1342139204
  167.  
  168. ---------------------------------------------------------------------------
  169. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  170. ---------------------------------------------------------------------------
  171. |   0 | SELECT STATEMENT   |      |     1 |     4 |   952   (4)| 00:00:12 |
  172. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  173. |*  2 |   TABLE ACCESS FULL| FOO  |     1 |     4 |   952   (4)| 00:00:12 |
  174. ---------------------------------------------------------------------------
  175.  
  176. Predicate Information (IDENTIFIED BY operation id):
  177. ---------------------------------------------------
  178.  
  179.    2 - filter(TO_NUMBER("C2")=010)
  180.  
  181. 14 ROWS selected.
  182.  
  183. SQL> EXPLAIN PLAN FOR
  184.   2  SELECT COUNT(*) FROM foo WHERE c2='003';
  185.  
  186. Explained.
  187.  
  188. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  189.  
  190. PLAN_TABLE_OUTPUT
  191. -------------------------------------------------------------------------------
  192. PLAN hash VALUE: 1342139204
  193.  
  194. ---------------------------------------------------------------------------
  195. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  196. ---------------------------------------------------------------------------
  197. |   0 | SELECT STATEMENT   |      |     1 |     4 |   942   (3)| 00:00:12 |
  198. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  199. |*  2 |   TABLE ACCESS FULL| FOO  |  1000K|  3906K|   942   (3)| 00:00:12 |
  200. ---------------------------------------------------------------------------
  201.  
  202. Predicate Information (IDENTIFIED BY operation id):
  203. ---------------------------------------------------
  204.  
  205.    2 - filter("C2"='003')
  206.  
  207. 14 ROWS selected.
  208.  
  209. SQL> EXPLAIN PLAN FOR
  210.   2  SELECT COUNT(*) FROM foo WHERE c2='010';
  211.  
  212. Explained.
  213.  
  214. SQL> SELECT * FROM TABLE(dbms_xplan.display());
  215.  
  216. PLAN_TABLE_OUTPUT
  217. -------------------------------------------------------------------------------
  218. PLAN hash VALUE: 1342139204
  219.  
  220. ---------------------------------------------------------------------------
  221. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  222. ---------------------------------------------------------------------------
  223. |   0 | SELECT STATEMENT   |      |     1 |     4 |   942   (3)| 00:00:12 |
  224. |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
  225. |*  2 |   TABLE ACCESS FULL| FOO  |   999K|  3904K|   942   (3)| 00:00:12 |
  226. ---------------------------------------------------------------------------
  227.  
  228. Predicate Information (IDENTIFIED BY operation id):
  229. ---------------------------------------------------
  230.  
  231.    2 - filter("C2"='010')
  232.  
  233. 14 ROWS selected.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!