0

I have a statement (in simple we could see it like this: )

Select * from VIEW where View.target='alpha'

The view is using multiple tables - table X, Y, Z for example, and the target will be in X. The view returns a dataset of 2 billion rows and with this query the database will load all of them and search where target equals 'alpha'. Now is there any possibility to make this query faster?

Maybe there is a possibility to make the view from which I load a little bit smaller (?) I think when I could make the 'target='alpha' statement within the view and the view would then be smaller this would really help... but I think when I do this statement within the view the problem would be the same because then the view would do the same (Am I right?)

At the end it would be better when I could have the view as it is and do the work within the new statement but if someone would have an idea that would work when I change the view this could be done also.

Thank you!

APC
  • 144,005
  • 19
  • 170
  • 281
Pascal Homberg
  • 83
  • 1
  • 7
  • The optimization of the query execution is one of the reason why the DBAs exist. Your question is too general. Without to know all tables behind, their statistics, indexes and index statistics, the type of the queries issued against them, how they are filled, changed or partitioned and so on, you cannot optimize. Ask your DBA. He has the knowledge to do it. If you want to learn how to do it, you can begin here https://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#PFGRF94083 – Mottor Jul 06 '16 at 07:36
  • http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html –  Jul 06 '16 at 07:48

1 Answers1

3

A view is a stored SQL statement, and to make a view faster you will have to make the stored SQL statement faster.

The SQL statement depends on tables and to speed up queries against tables you use index. The benefits with index are plenty, read this great answer to a SO question here. It explains what index is, how it works and why its needed.

So to prevent a full table scan you add indexes to make the query faster. In your case you need to identify the columns in the SQL statement that make a good fit for an index. Usually columns that are commonly used in WHERE, ORDER BY, JOIN and GROUP BY clauses make a good fit to be included in an index. In your case, start looking at the table where the target column exists. Start by adding an index there and then continue with its relations to other tables in the query. This will eventually result in a faster response time when using your view.

To create index on your Oracle table. Read the Oracle docs here

Community
  • 1
  • 1
Marcus Höglund
  • 16,172
  • 11
  • 47
  • 69
  • Can I use an index on a View? The view is using multiple Tables to be created,, (table x, y, z for example) the target will be in x would it help when I use the index on table x target? or would this have no affect? Thank you in advance! – Pascal Homberg Jul 06 '16 at 06:49
  • A view is a stored SQL statement, you can not create an index on that. Create the index on the underlying tables in the view to improve the performance of view – Marcus Höglund Jul 06 '16 at 06:52
  • If you join many tables in the view, look at the joining columns between the tables and create indexes that includes those columns on each table. Then the performance of that view will be faster as the joins will using indexes. – Marcus Höglund Jul 06 '16 at 06:54
  • Tuning a view is different from tuning a query on a single table. You need to rewrite your answer to address the OP's actual situation. – APC Jul 06 '16 at 07:34
  • @APC You are right, Thanks for pointing it out. I've change my answer to make a better fit to the actual situation. – Marcus Höglund Jul 06 '16 at 08:10