DISTINCT optimization

When optimizing this query:

SELECT DISTINCT Order FROM OrderItem

which plans would the different RDBMS engines build and which wou开发者_C百科ld be the most efficient?

It will depend on the indexing and the database.

• In sql server you can analyze the query plan in the GUI, or the SHOWPLAN_ALL keyword.
• In mysql and pgsql you can use the EXPLAIN keyword in SQL

So go ahead and try it out on the different servers using Identical schemas and find out for yourself!

MySQL would use a loose index scan on an indexed field, and a temporary table or a filesort on an unindexed fied.

The other engines would use either a SORT GROUP BY or a HASH GROUP BY.

The former is usually more efficient on an indexed field, however, the index scanning overhead may be quite significant, so Oracle could prefer an INDEX FULL SCAN with a HASH GROUP BY over it.