I'm posting here because the question is trying to focus on how DB2 works and is not supposed to be specific to my situation/query.
My particular situation for some context:
I have a query which is used in a search/filter/order type program with many potential WHERE clause parameters based on user input. I've noticed when I specify an order by clause it is drastically slower than without. While looking at the execution plan I noticed it takes milliseconds up until the fetch stage. After a bit of reading I've come to realize this is because it has to create a temporary table in the specified order before it can even begin to return the first record ('Materialized Cursor' is what I believe this is called). Now from what I've read, it only has to do this step if it can't use an existing index on that field in the specified order (desc/asc). I've been testing with 1 particular column as parts of the ORDER BY and there exists an index over that field in the correct order. Now this query is run against 2 views each on top of other tables and views. The top level views JOIN on hard coded conditions (IE: ...ON t1.col1 = 'Hard Coded Value') while the few underlying views contain their own WHERE clauses as well. I'm assuming because of the nature of views on top of views with conditional joins and where clauses is preventing the index from being used.
My questions:
A view which is just a replica of a table which only uses scalar functions such as TRIM or UPPER on the columns should be able to make use of indexes?
While a view which involves multiple tables or even other views and WHERE clauses will likely not?
Many Thanks in Advance,
Dan
My particular situation for some context:
I have a query which is used in a search/filter/order type program with many potential WHERE clause parameters based on user input. I've noticed when I specify an order by clause it is drastically slower than without. While looking at the execution plan I noticed it takes milliseconds up until the fetch stage. After a bit of reading I've come to realize this is because it has to create a temporary table in the specified order before it can even begin to return the first record ('Materialized Cursor' is what I believe this is called). Now from what I've read, it only has to do this step if it can't use an existing index on that field in the specified order (desc/asc). I've been testing with 1 particular column as parts of the ORDER BY and there exists an index over that field in the correct order. Now this query is run against 2 views each on top of other tables and views. The top level views JOIN on hard coded conditions (IE: ...ON t1.col1 = 'Hard Coded Value') while the few underlying views contain their own WHERE clauses as well. I'm assuming because of the nature of views on top of views with conditional joins and where clauses is preventing the index from being used.
My questions:
A view which is just a replica of a table which only uses scalar functions such as TRIM or UPPER on the columns should be able to make use of indexes?
While a view which involves multiple tables or even other views and WHERE clauses will likely not?
Many Thanks in Advance,
Dan
Comment