2 Replies Latest reply: Apr 2, 2014 5:08 AM by Dom Brooks RSS

    What is the purpose of "Materialized Hint"

    gsrinivas.srini

      What is the purpose of "Materialized Hint"

      I have a select query with 2 tables(let us say Table x and Table y) joined with a equi-join and the query is performing very well.

      Later I have added an exists clause to the initial query and then the query performance degraded.

      Execution time went up to 10 minutes from the initial time of 3 seconds.

      The table (Table z ) on which the exists condition is added has only 10000 records but there is a drastic degradation in the performance.

      Based on some recomondations I have used the materialized hint with the "with" clause and I could see that my query is performing well with 3 seconds.

      Can some please explain what does this Materialize hint does.