[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

DE | FR | ES | JA ]

by Markus Winand.

Sorting and Grouping


Sorting is a very resource intensive operation. It needs a fair amount of CPU time, but the main problem is that the database must temporarily buffer the results. After all, a sort operation must read the complete input before it can produce the first output. Sort operations cannot be executed in a pipelined manner—this can become a problem for large data sets.

An index provides an ordered representation of the indexed data: this principle was already described in Chapter 1. We could also say that an index stores the data in a presorted fashion. The index is, in fact, sorted just like when using the index definition in an order by clause. It is therefore no surprise that we can use indexes to avoid the sort operation to satisfy an order by clause.

Ironically, an INDEX RANGE SCAN also becomes inefficient for large data sets—especially when followed by a table access. This can nullify the savings from avoiding the sort operation. A FULL TABLE SCAN with an explicit sort operation might be even faster in this case. Again, it is the optimizer’s job to evaluate the different execution plans and select the best one.

If you like this page, you might also like …

… to subscribe my mailing lists, get free stickers, buy my book or join a training.

An indexed order by execution not only saves the sorting effort, however; it is also able to return the first results without processing all input data. The order by is thus executed in a pipelined manner. Chapter 7, “Partial Results, explains how to exploit the pipelined execution to implement efficient pagination queries. This makes the pipelined order by so important that I refer to it as the third power of indexing.

Note

The B-Tree traversal is the first power of indexing.

Clustering is the second power of indexing.

Pipelined order by is the third power of indexing.

This chapter explains how to use an index for a pipelined order by execution. To this end we have to pay special attention to the interactions with the where clause and also to ASC and DESC modifiers. The chapter concludes by applying these techniques to group by clauses as well.

Contents

  1. Indexed Order Bywhere clause interactions

  2. ASC/DESC and NULL FIRST/LAST — changing index order

  3. Indexed Group By — Pipelining group by

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2010-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR