Improving performance on an Oracle view

Question

I have a query that I would like to save as a view:

WITH subquery AS ( SELECT aaa_id, ... FROM table_aaa ... )
SELECT subquery.aaa_id, ... FROM table_bbb JOIN subquery USING ( ... )
;

[NOTE: In real-life, my query is much more complicated than this, with several WITH sub-queries, many performing JOINS, all being JOINed together. But I'm looking for general guidance that I can use to work into my problem.]

The execution for this query include full table scans. This makes sense since there are no criteria included in a WHERE clause. However, I can eliminate most of the full table scans by including such a clause:

WITH subquery AS ( SELECT aaa_id, ... FROM table_aaa ... WHERE aaa_id = :id)
SELECT subquery.aaa_id, ... FROM table_bbb JOIN subquery USING ( ... )
;

However, it doesn't seem that I have the option of putting the WHERE condition in the right place when I create the view:

CREATE OR REPLACE VIEW vw_my_view AS
WITH subquery AS ( SELECT aaa_id, ... FROM table_aaa ... )
SELECT subquery.aaa_id, ... FROM table_bbb JOIN subquery USING ( ... )
;

SELECT ... FROM vw_my_view WHERE aaa_id = :id
;

In this case, the execute plan still contains the full table scans. Is there a way for me to hint that the WHERE clause can actually be inserted into the WITH sub-query?

What do you mean when you say you don't have the option of specifying a WHERE condition in the subquery inside the WITH clause? — Darius X., Feb 12 '13 at 18:04
@Darius - It is true that I can specify a `WHERE` condition in my view, if I want to limit my view to a certain subset of data. That is not what I want. — Paul Hanbury, Feb 12 '13 at 18:38
@Darius - What I said was that if the query against the view, adding a `WHERE` condition to that query doesn't put it in the right place in the execution plan. (Notice that the `WHERE` condition inside of the `WITH` sub-query was how I fixed the execution plan outside of a view). — Paul Hanbury, Feb 12 '13 at 18:38
Thanks for clarifying. Have you tried using global hints on the final SQL, by naming the view there? http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#6298 — Darius X., Feb 12 '13 at 18:58
Thanks, Darius (+1). This may be exactly what I was looking for, but hard to find without knowing the phrase "global hints". — Paul Hanbury, Feb 12 '13 at 20:44

ivanatpr · Accepted Answer · 2013-02-13T22:51:31.473

I've had similar experiences and while I have no general solution, I would suggest the following:

run "SELECT * FROM v$parameter2;" and make sure that _complex_view_merging is on. There was a nasty bug in one of the early 10g releases that was related to it so some dbas turned it off and might have forgotten to turn it back on once it was fixed.
Leave all consideration about hints as a last measure. In my experience, they're rarely useful for preventing full table scans because the optimizer is already doing everything it can to avoid them.
If you have a base table whose primary key is what you're ultimately going to be filtering the view on, try to set things up so that your view's main query starts with that and then joins to your complicated with clause queries, even if that join is completely redundant (i.e. give oracle a chance to do easy filtering on that base table before joining to the complicated bits). Make sure that the columns that the view is going to be filtered on are selected directly from that base table and not the complicated_query. So something like

.

     with (complicated_query)
     select base_table.key1, complicated_query.*
     from base_table
     join complicated_query on base_table.key1 = complicated_query.key1;

If you have filters that use an uncorrelated subquery, try switching them to a correlated equivalent (and vice versa).
Play around with the order of your join statements and/or which table you start out with in your FROM clause, even if logically it will not make a difference to the outcome. This is a bit of a desperate gambit, but I've definitely had execution plans change for the better by doing this. Optimizing Oracle queries is not always a rational process.

Thank you very much, Ivan. Your tips have proved to be very helpful, especially points 3 and 5 (if they had been numbered). — Paul Hanbury, Feb 15 '13 at 16:25

score 0 · Answer 2 · edited May 23 '17 at 11:50

You could use context parameters like described here: creating parameterized views in oracle11g or http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:906341500346611919 ("Parameterized views -vs- views with where conditions" in case they change URLs again)

This way you can put a WHERE aaa_id = SYS_CONTEXT ('my_namespace', 'aaa_id') deep in the view definition and use it like:

CREATE OR REPLACE VIEW vw_my_view AS
WITH subquery AS ( SELECT aaa_id, ... FROM table_aaa ...
   WHERE aaa_id = SYS_CONTEXT ('my_namespace', 'aaa_id'))
SELECT subquery.aaa_id, ... FROM table_bbb JOIN subquery USING ( ... )
;

DBMS_SESSION.SET_CONTEXT('my_namespace', 'aaa_id', TO_CHAR(:id));
SELECT ... FROM vw_my_view  /* this is not needed any more: WHERE aaa_id = :id */

Google for more examples and explanations (search term "paramet(e)rized view in oracle") ...

Improving performance on an Oracle view

2 Answers2