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.