8 Replies Latest reply: Feb 7, 2011 4:42 AM by Jonathan Lewis RSS

    When To Create an Index

    778931
      Hi,
      First pardon my generic question, but I am posting this after much frustration. When you create an index on a table, which thought process you follow? I mean instead of blindly throwing index on every column in where clause, how do you decide as whether to create index or not, type of index, composite index or not etc. Please do the favor and describe in simple words.

      Thanks a million.
        • 1. Re: When To Create an Index
          Umesh Gupta
          Panicked DBA wrote:
          Hi,
          First pardon my generic question, but I am posting this after much frustration. When you create an index on a table, which thought process you follow?
          Indexes are created for faster retrieval of data.

          So you basically create index for the columns which are used in where condition.
          I mean instead of blindly throwing index on every column in where clause, how do you decide as whether to create index or not, type of index, composite index or not etc.
          So see which columns are generally used in your queries in where condition,you should create index on them,not on all the columns

          for more index concepts go through the link..

          http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg06idx.htm


          Thanks

          Umi
          • 2. Re: When To Create an Index
            Jonathan Lewis
            Panicked DBA wrote:
            Hi,
            First pardon my generic question, but I am posting this after much frustration. When you create an index on a table, which thought process you follow? I mean instead of blindly throwing index on every column in where clause, how do you decide as whether to create index or not, type of index, composite index or not etc. Please do the favor and describe in simple words.
            The best generic answer to help you understand the theory and strategy of indexing is probably the book by Tapio Lahdenmaki and Mike Leach - Relational Database Index Design and the Optimizers.

            Here's a link on Amazon UK: http://www.amazon.co.uk/Relational-Database-Index-Design-Optimizers/dp/0471719994

            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            http://www.jlcomp.demon.co.uk

            A general reminder about "Forum Etiquette / Reward Points": http://forums.oracle.com/forums/ann.jspa?annID=718

            If you never mark your questions as answered people will eventually decide that it's not worth trying to answer you because they will never know whether or not their answer has been of any use, or whether you even bothered to read it.

            It is also important to mark answers that you thought helpful - again it lets other people know that you appreciate their help, but it also acts as a pointer for other people when they are researching the same question, moreover it means that when you mark a bad or wrong answer as helpful someone may be prompted to tell you (and the rest of the forum) what's so bad or wrong about the answer you found helpful.
            • 3. Re: When To Create an Index
              837236
              Even after creating the required indexes based on analysis of the where clause, further monitoring of index usage can be achieved by issuing the following command for example

              alter index SCHEMANAME.INDEXNAME monitoring usage;

              Then the application can be run for a certain period of time that may represent a workload.
              A query then can be run to get the necessary information as follows

              select used
              from v$object_usage
              where table_name = 'TABLENAME' and INDEX_NAME='INDEXNAME';

              A "Yes" would mean that the index has been used and vice versa.

              In this way, even if indexes have been created initially based on where clauses, useless indexes can be dropped later as found.

              I hope this helps...
              • 4. Re: When To Create an Index
                618702
                Dear Panicked DBA,

                There are advisors in the Oracle database which shows you recommendations where to create an index, even with it's type. Please review more about SQL Access Advisor and SQL Tuning Advisor. Since you are asking about indexing check the Access Advisory.

                The only indicator is not only the where condition but also the columns in the SELECT statement. What do you want from Oracle on which conditions?

                I can suggest you to use the Enterprise Manager for more managebility about SQL queries and indexing.

                By the way, if you want to have more information about indexes and index types, read articles of Richard Foote.

                Regards.

                Ogan
                • 5. Re: When To Create an Index
                  Antonio Navarro
                  Usually, a index is better where it return the same data more faster than a full scan

                  HTH
                  • 6. Re: When To Create an Index
                    EdStevens
                    Antonio Navarro wrote:
                    Usually, a index is better where it return the same data more faster than a full scan

                    HTH
                    "All else being equal"

                    But all else is never equal.

                    don't forget that every index you create causes additional overhead for every insert/update/delete statement on that table. So among other considerations you need to balance the negative impact of the index on DML statements vs. the sometimes positive impact on queries.
                    • 7. Re: When To Create an Index
                      mseberg
                      Listen to Ed. There's no shortage of people in my shop who will knee jerk an index on a table. I always start with an explain plan and the position prove the index or the hint for that matter will improve performance.


                      All to EdStevens

                      Nice summary!
                      • 8. Re: When To Create an Index
                        Jonathan Lewis
                        EdStevens wrote:
                        Antonio Navarro wrote:
                        Usually, a index is better where it return the same data more faster than a full scan

                        HTH
                        "All else being equal"

                        But all else is never equal.

                        don't forget that every index you create causes additional overhead for every insert/update/delete statement on that table. So among other considerations you need to balance the negative impact of the index on DML statements vs. the sometimes positive impact on queries.
                        Which means the only simple answer to the generic question on when to create an index is that there are no simple answers: http://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk


                        A general reminder about "Forum Etiquette / Reward Points": http://forums.oracle.com/forums/ann.jspa?annID=718

                        If you never mark your questions as answered people will eventually decide that it's not worth trying to answer you because they will never know whether or not their answer has been of any use, or whether you even bothered to read it.

                        It is also important to mark answers that you thought helpful - again it lets other people know that you appreciate their help, but it also acts as a pointer for other people when they are researching the same question, moreover it means that when you mark a bad or wrong answer as helpful someone may be prompted to tell you (and the rest of the forum) what's so bad or wrong about the answer you found helpful.