1 / 36

Top N Query: How to use STOPKEY

Top N Query: How to use STOPKEY. Presented by Dalibor Mar č eta. Overview. Producer – Consumer Common misconceptions Top N query Pagination Iteration Index range scan Multiple tables Explain plan Quick check. Producer - Consumer. Amazon has service-oriented architecture

shafferb
Download Presentation

Top N Query: How to use STOPKEY

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Top N Query: How to use STOPKEY Presented by Dalibor Marčeta Amazon Confidential

  2. Overview Producer – Consumer Common misconceptions Top N query Pagination Iteration Index range scan Multiple tables Explain plan Quick check Amazon Confidential

  3. Producer - Consumer • Amazon has service-oriented architecture • Services communicate with each other by passing data from one service to another • Identifying components • The Producer generates data and stores it into the queue for consumption by the consumer • The Consumer consumes data from the queue • The Queue is some common table used as a communication queue between the producer and consumer loop Amazon Confidential

  4. Components Amazon Confidential

  5. Most likely implementation Amazon Confidential

  6. Target implementations Amazon Confidential

  7. Scaling producers Amazon Confidential

  8. Queue table Amazon Confidential

  9. Scaling consumers Doesn’t scale: • Optimistic locking • Pessimistic locking, wait • Pessimistic locking, no wait Scales: • Iteration • Iteration skip locked Amazon Confidential

  10. Optimistic Amazon Confidential

  11. Pessimistic Amazon Confidential

  12. Pessimistic nowait Amazon Confidential

  13. Select for update nowait Amazon Confidential

  14. Select for update skip locked Amazon Confidential

  15. Top N query Teams frequently have a need to display top N or last N rows from a table or group of joined tables with some criteria applied. Engineers commonly make the following mistake when designing queries and indexes to solve the top N query problem. They only create an index matching the lead predicate. Amazon Confidential

  16. Common misconceptions • Order of the columns in index is not important • When building an index we choose columns with good selectivity used as a query predicate. • Select * from table where rownum < N order by creationDate desc - will decrease response time. • Service has been tested and it works until customer with many records logs in. Amazon Confidential

  17. How to identify problem • Latency • High user CPU • High I/O If Top N is not implemented or if it is not implemented properly query can cause problems for application and for database. It usually can be recognized by the following manifestations: Amazon Confidential

  18. Implementation • Incorrect implementation • Correct inefficient • Correct efficient • Pagination • Iteration Amazon Confidential

  19. Incorrect implementation Amazon Confidential

  20. Correct inefficient Amazon Confidential

  21. Correct efficient Amazon Confidential

  22. Pagination incorrect Amazon Confidential

  23. Pagination correct Amazon Confidential

  24. Iteration I Amazon Confidential

  25. Iteration II Amazon Confidential

  26. Range scan I before Amazon Confidential

  27. Range scan I after Amazon Confidential

  28. Range scan II Amazon Confidential

  29. Range scan II Amazon Confidential

  30. Multiple tables Amazon Confidential

  31. Multiple tables Amazon Confidential

  32. Bad Execution Plan Amazon Confidential

  33. Good Execution Plan Amazon Confidential

  34. Rule Amazon Confidential

  35. Quick check Amazon Confidential

  36. Questions? Amazon Confidential

More Related