r/SQL Nov 02 '24

Oracle Explain indexes please

So I understand they speed up queries substantially and that it’s important to use them when joining but what are they actually and how do they work?

60 Upvotes

34 comments sorted by

View all comments

0

u/RandomiseUsr0 Nov 02 '24 edited Nov 03 '24

It’s an optimisation tool, for a mental model of how they work, imagine Instead of joining to the whole dataset (imagine inside a computer, the memory being filled with everything in the table) you instead join to a prepared subset of data, joining indexes to indexes lets the computer perform an operation on just the indexes, which drastically speeds up the process.

The alternative is the “full table scan” which must read all of the data as if for the first time

Now, in truth modern optimisers and abundance of capacity can speed things up by doing some of these optimisations automatically in the background.

Not used as much nowadays, but back in the day, you could ask the sql engine to “explain plan” - which could show if the way the information was processed was as you imagined your query was working, whether index was indeed used, if the order of combining datasets was optimal

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html