1

How can I increase performance when I run Oracle Views? I have an Oracle View, it looks something like this:

SELECT ...
FROM   myview p
WHERE  p.param1 = 'x' AND p.param2 = 'y' AND p.param3 = 'z'

The code of the view is (analogously):

SELECT a.param1, b.param2, c.param3
FROM   atab a
       JOIN btab b
           ON a.id = b.blaid
       JOIN ctab c
           ON b.id = c.blaid

The running time of myview with condition is round about 13-14 secs. When I let run the query with condition:

SELECT a.param1, b.param2, c.param3
FROM   atab a
       JOIN btab b
           ON a.id = b.blaid
       JOIN ctab c
           ON b.id = c.blaid
WHERE  a.param1 = 'x' AND b.param2 = 'y' AND b.param3 = 'z'

the running time is round about 0,3 second.

I understand this so that Oracle first fetches all data of myview, and after this applies the Filter. But I'm not very sure. If I run the query directly, Oracle has the option only get the data that is actually needed (because in the execution, the condition can be considered immediately). But here I'm not very sure also.

The question is, when the view has a complex structure and its existence is justified, how can I solve the performance Problem?

I think about a solution, for example with a function with parameters. The query could be executed immediately with a where-condition.

Are there other possible solutions?

peter70
  • 1,053
  • 16
  • 31

3 Answers3

1

I understand this so that Oracle first fetches all data of myview, and after this applies the Filter

That's not correct. You can compare execution plans of two queries: direct from tables with filter and query from view with filters. It shoud be equals. You results is about cache. To increase perfomance try to use indexes and gather statistic more often. In case of data separated by periods or some another tag you can use partitioning option.

Arman
  • 71
  • 3
1

The performance of a query is driven by the execution plan. If run the same query "natively" or as a view, it's very likely that the execution plan will be the same and the performance will be the same. The reason that your query runs faster with the addition of the WHERE clause is that you are filter the data; so that less date need to be retrieved and joined.

BobC
  • 4,208
  • 1
  • 12
  • 15
  • I think so too! Question is how to solve this problem. My colleague has edited the query, so now the execution time is short in both cases. It follows that before the query was modified, Oracle (using the joins and the where clause) processed the entire statement with its optimizer. This Oracle-optimized query was so fast, and not the query on itself. In the view, Oracle could not apply its optimizer because table-joins and condition are separated from each other. This job of Oracle-optimizer has now done my colleague, by changing the query, which results in both variants being just as fast. – peter70 Mar 09 '17 at 07:37
  • 1
    @peter70 As others have pointed out, your understanding is not correct. Get the execution plans of each query and post it in your question. To get the execution plan, run your query, then run select * from table( dbms_xplan.display_cursor( null, null, 'TYPICAL' )); – BobC Mar 09 '17 at 11:39
0

As long as the data in your tables don't change too often, or if you can accept having somehow "old" data returned, you can consider using a materialized view, which is stored on disk instead of being purely logical.

Edit

Another option is to create a parametrized view, like explained in this question

Community
  • 1
  • 1
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • Sporadically this can be a solution. Whether it could be a solution in this particular case, we would have to examine. But it would be good to know how we can generally work with Oracle Views, when there are problems like this. Because such situations are certainly not a rarity – peter70 Mar 08 '17 at 10:36
  • 1
    Edited my answer with another option – Stefano Zanini Mar 08 '17 at 10:41
  • I see, These solution uses quasi globals with DBMS_SESSION.SET_CONTEXT(key, value... That is not, what I'm really searching for. Sorry! – peter70 Mar 08 '17 at 12:41