Skip to Main Content
  • Questions
  • Materialize Hint used in almost all queries


Question and Answer

Connor McDonald

Thanks for the question, jj.

Asked: January 24, 2023 - 9:33 pm UTC

Last updated: January 31, 2023 - 6:47 am UTC

Version: 13

Viewed 1000+ times

You Asked


Thanks to this forum a lot of my answers were answered.
I noticed that most of the queries in an application that am working on is using the MATERIALIZE hint in the subqueries.
Our queries heavily use CTEs and relies on multiple joins.
Our query performance is not great.

In OEM, i noticed all these queries have high user I/O and am guessing, its because all the queries that query tables of 100s million rows are materialized.
Is that true? Is it better to keep it there rather than remove it?
When should i REALLY be using materialized hint?

The queries also use
Also uses
Select NVL(this, NVL(that,99)), column, column from table;
Does the above impact performance? If so how can i improve it?

I also noticed use of "use_concat materialize" hint. For some of the queries, when i execute it without "use_concat" the speed is almost 300% faster.
Under what condition should this hint be used?

What would help with performance

and Connor said...

1) My stance is MATERIALIZE should only be used as a tuning activity, never as a default.

If we want to be *really* strict about it, MATERIALIZE is not even documented so officially you should never use it, but obviously its usage out in the real world is fairly commonplace and I don't envision it going away any time soon.

With *every* query in an application I try work along the following guide:

- no hints at all
- if I hit a problem, make sure its not lack/wrong stats
- if normal stats not good enough, see if dynamic sampling is an option (for analytical not oltp style queries)
- if after all of that, then look at hints to get a good plan
- if that helps, then lock plan in with SPM and remove the hints

2) Select NVL(this, NVL(that,99)), column, column from table;

is not an issue because its applied only AFTER the records you want are found

3) "where column1 = CASE WHEN this THEN 1 WHEN THAT THEN 2 WHEN OTHER_THAT THEN 3"

*might* be an issue if "this", "that" are columns not static values, because it might disable the use of indexes on 'column1'


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Thank you

jj, January 26, 2023 - 3:59 am UTC

Thank you Connor for your advice.

Another question i had was, suppose my big_table joins another big_table on multiple columns and multiple small tables on single column. Is there any index or performance strategy i could use?
Connor McDonald
January 31, 2023 - 6:47 am UTC

As long as you have sufficient pga sized, then large joins should not be an issue assuming you're achieving HASH joins. If you're seeing MERGE joins, then its time to look at the SQL to see why we had to fall back to that.

Common tuning techniques for large table joins:

1) partitioning
2) parallelism

More to Explore


Get all the information about database performance in the Database Performance guide.