CODEWITHSUNDEEP
sqloracle-databaseview
Pascal Homberg
Pascal Homberg

Reputation: 83

Oracle SQL - Avoid full table scan (where)

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!

Upvotes: 0

Views: 3093

Answers (1)

Marcus Höglund
Marcus Höglund

Reputation: 16801

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

Upvotes: 3

Related Questions