5 Replies Latest reply on Aug 2, 2014 7:01 PM by tim fox - Enkitec

    query execution


      Hi All,


      How does exadata handles hints on a query. What I understood is that exadata removes all the indexes when we migrate a database to exadata box to use full tablescan.



      But if we have a vendor code with hints, then how does this query works in exadata as the indexes will be removed?

        • 1. Re: query execution
          Marc Fielding

          Hello user13364377,


          Exadata does not remove indexes.  On whether you, as DBA, should manually remove indexes, Arup Nanda has an excellent paper: http://www.proligence.com/pres/ioug14/2014_316_Nanda_ppr.pdf


          Since Exadata does use indexes, query hints for indexes work as well.



          • 2. Re: query execution

            Hi Marc,


            Thanks for the excellent doc. Here in our environment, we are removing all the indexes of the database that are migrated to a exadata box.

            One question that came to my mind here is  as we are removing all the indexes,  what will happen to a with hints ?(Assuming that it is a vendor code) Will exadata internally has any mechanism to handle this like disabling hints?

            • 3. Re: query execution
              Marc Fielding

              Hi user13364377,


              I'm not aware of any Exadata-specific internal mechanisms relating to disabling hints.  In general, you should expect your hints to have the same effects as if the indexes were removed in a non-Exadata system.  So is you have a /*+INDEX hint that references a specific index name that has been removed, the hint would be ignored, for example.


              Can you tell us a bit more about the types of hints that you're using and what their intent is?



              • 4. Re: query execution

                Hi Marc,


                Normally I have seen the index hints(/* + index (col) and hints for parallel query processing.

                • 5. Re: query execution
                  tim fox - Enkitec

                  You should be careful about removing ALL your indexes when migrating to Exadata.  When and what to remove really depends on your workload and whether your current indexes are helping you.  For instance, you should always retain indexes on Foreign Keys.  If you don't, your Primary to Foreign key relationships will not have an index to use.  If you are running a data warehouse-like workload, you can experiment with removing indexes that you "might" not need.  Still, if you have PK/FK relationships in the database, you want to hang on to those associated indexes.


                  Making indexes INVISIBLE is a good way to test whether having them on Exadata helps or hurts.  You have to remember that using this feature will make an index invisible for everyone so you still have to use some caution with INVISIBILity.  You can also monitor your indexes to see which ones are being used and then evaluate which ones can be removed.


                  If you really just want hints to be ignored, there is the "_OPTIMIZER_IGNORE_HINTS" parameter, but its unsupported.  Might help in your testing.


                  In general, it's pretty safe to leave your indexes on your tables when you move to Exadata and then remove them strategically.