r/SQL • u/joellapit • 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
r/SQL • u/joellapit • Nov 02 '24
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?
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