1 Reply Latest reply on Mar 15, 2007 7:39 AM by roist

    Inserting Oracle optimizer hints into queries

      I have two JBossQL queries, one dynamic, that searches a huge Oracle table. Obviously, I use indices but the Oracle SQL optimizer thinks it's better to do a full table scan than use these indices. I kid you not. Tests demonstrate it's the worst plan.

      Rather than messing around the optimizer, which will degrade other queries, it'll be better to insert an optimizer hint, such as /*+ RULES */ into the generated SQL and override the brain damage. However, I can't generate SQL, JBoss is doing that for me from the JBossQL. Is there anyway I can get something inserted into generated SQL queries?

        • 1. Re: Inserting Oracle optimizer hints into queries
          roist

          easy way out: make a view with optimizer hint already included.

          it might be that your query is not well-formed to match the indices,
          or the indices are misconfigured (doing an index for 2 columns in the wrong order can do exactly that),
          or your statistics are bad. (if you have a newer database-version, use dbms_stats instead of analyze table!)

          i'd go back to setting the db right, instead of trying to workaround on the application server - usually, its a big deal easier to set things straight instead of workaround them.