Skip to Main Content
  • Questions
  • Need to create index for every table before I can execute any query

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, JW.

Asked: November 29, 2018 - 12:46 pm UTC

Last updated: November 29, 2018 - 2:31 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

I'm running an Oracle 12c database with 32gb ram and ~ 1 mln rows by ~ 30 columns. My queries take forever to run if I try to do a join or where without an index. If I create the indexes it finishes in a few seconds.

Could this have something to do with the configuration of the database?

Regards,
Jan Willem

and Chris said...

Could this have something to do with the configuration of the database?

Unlikely. If you have a table with 1 million+ rows and no indexes, the database has to scan all the rows to find those matching your where clause. If your query only returns a few rows, this is a huge waste.

An index enables the database to find the location of the few rows that match your search criteria. So you only read these rows from the table. Making your query faster.

There's a lot more to indexes than that though ;)

I suggest you read up on how these work. The following resources will help:

My introduction to indexing video series: https://www.youtube.com/watch?v=Rlgb7LwOiHk&list=PL78V83xV2fYklRnurEpbRYh12nBTFNmC-
Use the Index, Luke! A site and book dedicated to explaining indexes by Markus Winand: https://use-the-index-luke.com/

My posts on how to create indexes and get execution plans to find out if the database is using them:

https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database
https://blogs.oracle.com/sql/how-to-create-an-execution-plan

When you get more advanced, I recommend reading Richard Foote's blog, the best resource on indexes in Oracle Database:

https://richardfoote.wordpress.com/

Good luck!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.