CODEWITHSUNDEEP
sqloracle-databaseview
Jan S.
Jan S.

Reputation: 331

SQL alternative to a View

I don't really know how to even ask for what i need. So i try to explain my situation.

I have a rather simple sql query that joins various tables but I need to execute this query with slightly different conditions over and over again.

The execusion time of the query is somewhere around 0.25 seconds. But all the queries i need to execute take easily 15 seconds. This is way to long.

What i need is a table or view that holds the query results for me so that i only need to select from this one table instead of joining large tables over and over again.

A view wouldn't really help because it would just execute the same query over and over again. As far as i know.

Is there a way to have something like a view which holds its data as long as its source tables doesn't change ? And will only update and execute the query if it is really necessary?

Upvotes: 0

Views: 1745

Answers (2)

Rusty
Rusty

Reputation: 2138

I think what you described very good fits to

materialized view

usage with fast refresh on commit. However your query need to be eligible for fast refresh.

Another way to use

result_cache

  • it is automatically invalidates when one of the source tables is changed. I would try both to decide which one suites better for this particular task.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would suggest table-valued functions for this purpose. Defining such a function requires coding in PL/SQL, but it is not that hard if the function is based on a single query.

You can think of such functions as a way of parameterizing views.

Here is a good place to start learning about them.

Upvotes: 1

Related Questions