API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 26th, 2020
94
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.85 KB | None | 0 0
raw download clone embed print report
  1. CREATE INDEX INDEX_TICKET ON TICKET(SEATNUMBER, TICKETPRICE);
  2.  
  3. EXPLAIN PLAN SET STATEMENT_ID = 'FULL TABLE SCAN' FOR
  4. select /*+ full(ticket) */ SEATNUMBER, COUNT(*) FROM TICKET WHERE TICKETPRICE > 10 GROUP BY SEATNUMBER ORDER BY SEATNUMBER;
  5. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
  6.  
  7. EXPLAIN PLAN SET STATEMENT_ID = 'INDEX SCAN' FOR
  8. select /*+ index(ticket, INDEX_TICKET) */ SEATNUMBER, COUNT(*) FROM TICKET WHERE TICKETPRICE > 10 GROUP BY SEATNUMBER ORDER BY SEATNUMBER;
  9. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
  10.  
  11. EXPLAIN PLAN SET STATEMENT_ID = 'FAST INDEX SCAN' FOR
  12. select /*+ index_ffs(ticket, INDEX_TICKET) */ SEATNUMBER, COUNT(*) FROM TICKET WHERE TICKETPRICE > 10 GROUP BY SEATNUMBER ORDER BY SEATNUMBER;
  13. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
  14.  
  15. DROP INDEX INDEX_TICKET;
  16.  
  17.  
  18.  
  19.  
  20.  
  21. Index INDEX_TICKET created.
  22.  
  23.  
  24. Explained.
  25.  
  26.  
  27. PLAN_TABLE_OUTPUT
  28. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  29. Plan hash value: 3353023813
  30.  
  31. -----------------------------------------------------------------------------
  32. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  33. -----------------------------------------------------------------------------
  34. | 0 | SELECT STATEMENT | | 37 | 222 | 14 (8)| 00:00:01 |
  35. | 1 | SORT GROUP BY | | 37 | 222 | 14 (8)| 00:00:01 |
  36. |* 2 | TABLE ACCESS FULL| TICKET | 1526 | 9156 | 13 (0)| 00:00:01 |
  37. -----------------------------------------------------------------------------
  38.  
  39. Predicate Information (identified by operation id):
  40.  
  41. PLAN_TABLE_OUTPUT
  42. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  43. ---------------------------------------------------
  44.  
  45. 2 - filter("TICKETPRICE">10)
  46.  
  47. 14 rows selected.
  48.  
  49.  
  50. Explained.
  51.  
  52.  
  53. PLAN_TABLE_OUTPUT
  54. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  55. Plan hash value: 1705747179
  56.  
  57. -------------------------------------------------------------------------------------
  58. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  59. -------------------------------------------------------------------------------------
  60. | 0 | SELECT STATEMENT | | 37 | 222 | 105 (0)| 00:00:02 |
  61. | 1 | SORT GROUP BY NOSORT| | 37 | 222 | 105 (0)| 00:00:02 |
  62. |* 2 | INDEX FULL SCAN | INDEX_TICKET | 1526 | 9156 | 105 (0)| 00:00:02 |
  63. -------------------------------------------------------------------------------------
  64.  
  65. Predicate Information (identified by operation id):
  66.  
  67. PLAN_TABLE_OUTPUT
  68. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  69. ---------------------------------------------------
  70.  
  71. 2 - access("TICKETPRICE">10)
  72. filter("TICKETPRICE">10)
  73.  
  74. 15 rows selected.
  75.  
  76.  
  77. Explained.
  78.  
  79.  
  80. PLAN_TABLE_OUTPUT
  81. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  82. Plan hash value: 153173391
  83.  
  84. --------------------------------------------------------------------------------------
  85. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  86. --------------------------------------------------------------------------------------
  87. | 0 | SELECT STATEMENT | | 37 | 222 | 23 (5)| 00:00:01 |
  88. | 1 | SORT GROUP BY | | 37 | 222 | 23 (5)| 00:00:01 |
  89. |* 2 | INDEX FAST FULL SCAN| INDEX_TICKET | 1526 | 9156 | 22 (0)| 00:00:01 |
  90. --------------------------------------------------------------------------------------
  91.  
  92. Predicate Information (identified by operation id):
  93.  
  94. PLAN_TABLE_OUTPUT
  95. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  96. ---------------------------------------------------
  97.  
  98. 2 - filter("TICKETPRICE">10)
  99.  
  100. 14 rows selected.
  101.  
  102.  
  103. Index INDEX_TICKET dropped.
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!