1

If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered? References are welcome.

  • 4
    Is this about a particular platform? – Max Vernon Feb 14 '16 at 4:30
  • 2
    No @MaxVernon, in general – Ewerton Feb 14 '16 at 17:29
3

Most database platforms have different algorithms when joining tables. How tables (or streams of data, really) are joined depends on a number of factors, including the number of rows, the ordering of the rows and the type of join (for instance, if it's a so-called "equi-join" or not).

Assuming the following example:

SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.col6
FROM table_a AS a
INNER JOIN table_b AS b ON a.x=b.x AND a.y=b.y;

If both table_a and table_b are indexed on the columns (x, y), a highly optimal strategy would be to join those two could be to merge those two datastreams (in SQL Server and other platforms known as a Merge Join). This is particularly true if you add ORDER BY a.x, a.y;, in which case you would want to maintain the ordering from the underlying table.

As a general answer: imagine if your query was two decks of cards in your hands. How would you join them? Would you lay them out in order and merge them (a merge join), perhaps go through one card at a time from one deck and look for matching cards in the other deck (a nested loop join), or maybe subdivide all the cards into 10-15 smaller heaps and join those (more like a hash join)? How would that strategy change if one or both of your decks was ordered or not?

2

Physical order of records is defined by the clustered index, if it exists. Whatever you requested in a SQL query - it will be returned to you by the server, it is just matter of how many pages in files the server will have to look through in order to find what you requested. There is no single answer on your question, because the answer might be different depending on which other indexes are there, etc. I guess you are asking about "most typical case", then you can compare two examples below.

When you have a regular index on ZipCode:

CREATE TABLE Customers (CustomerID INT PRIMARY KEY, ZipCope VARCHAR(6))
CREATE NONCLUSTERED INDEX IDX_CUSTOMERS_ZIPCODE ON Customers (ZipCope)

and when you have a clustered index on ZipCode:

CREATE TABLE Customers (CustomerID INT, ZipCope VARCHAR(6))
CREATE CLUSTERED INDEX IDX_CUSTOMERS_ZIPCODE ON Customers (ZipCope)
CREATE NONCLUSTERED INDEX IDX_CUSTOMERS_ID ON Customers (CustomerID)

Assuming if you have millions of records, and smooth enough distribution of customers across many zipcodes, then it is more likely that if you search for a single zipcode - the server will need to search through less pages, and it will be faster if you had a clustered index based on ZipCode. Keep in mind that it may slow down queries based on other columns, such as CustomerID.

Using Clustered Indexes

  • 1
    This answer is SQL Server-specific. Also, it does not describe how index ordering affects joins, which is what the OP was asking. – Daniel Hutmacher Feb 14 '16 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.