I have to optimize the physical design of several queries. I have tried several techniques such as indexes or clusters but in most of the queries the best option in terms of consistent gets is creating a materialized view. Is there any reason why not to choose materialized views for optimizing the queries? Because if we could optimize all queries using only materialized views, everything would be much easier and faster.
4
-
Add what have you already done... show efforts... sql queries. Improve your question please.MANU– MANU2020-05-19 09:59:03 +00:00Commented May 19, 2020 at 9:59
-
It seems strange that indexes don't help. They are the common method for retrieving data quickly from the database. (Of course, if you select very many rows from a table, access via an index makes no sense anymore.) You may want to show one of your queries, so we can give advice on it. Maybe you just chose inappropriate indexes.Thorsten Kettner– Thorsten Kettner2020-05-19 10:25:42 +00:00Commented May 19, 2020 at 10:25
-
SELECT * FROM ( SELECT * FROM ( SELECT director, avg(coms) meancomm FROM (SELECT title,director,count('c') coms FROM comments GROUP BY title,director) GROUP BY director ) ORDER BY meancomm DESC ) WHERE rownum=1Truancito_2000– Truancito_20002020-05-19 14:17:53 +00:00Commented May 19, 2020 at 14:17
-
This could be an exampleTruancito_2000– Truancito_20002020-05-19 14:18:16 +00:00Commented May 19, 2020 at 14:18
Add a comment
|
2 Answers
You can optimize using materialized views. In my experience, they have one major downside: timing. Materialized views are not all updated at exactly the same time.
As a result, different tables that you think might be related might be missing rows. As a trivial example, you might have a foreign key relationship from T1 to T2. However, T2 gets materialized before T1. Then when T1 is materialized, some foreign key values could be missing. I have spent a lot of time dealing with the issues that this causes.
There are ways to adjust for this. For instance, all rows could have create dates and the materialized views could restrict rows only to those where were created or updated up to the previous hour boundary.
There are other issues, in terms of performance and maintenance. For instance, your database load might shift to materializing the views. Or the views might fail due to underlying schema changes. However, once you have a process in place, you will probably find that these are quite manageable.
Comments
For some application like OLTP it does not make sense because queries don't read a lot of data.
For some application like datawarehousing applications it could be an option but there are sometimes limitation to SQL statements that can be used to create materialized views (MV).
And in general one must take into account the cost to refresh the MV: if you don't want stale data, refresh should be automatic and there is some overhead to be taken into account.
