Can using a VIEW for SELECT operations improve performance?

Question

Hello everyone,

Working on improving performance of our decision center, one of the bottlenecks we identify is the DB.

So I wonder, does oracle compiles an Execution Plan for it's views?

Lets hypothetically assume I have a defined query being used 10000 times during a request. The query looks something like :

select A, B, C 
from aTbl, bTbl left join cTbl on bTbl.cTblID = cTbl.objectkey
where aTbl.objectkey = bTbl.parentkey

In the code I would like to fetch the result of the query above with additional filtering parameter, for example: WHERE aTbl.flag1 = <<NUMBER>>

Now I have 2 options:

1. Creating a prepared statement using the above SQL, then reusing the object.
2. Putting the above select (aTbl, bTbl, cTbl) into a VIEW, then creating a prepared statement on this view, thus benefiting the from execution plan precompiled b Oracle.

What would you suggest?

Answer 1

Oracle may push predicate into a view if it thinks it will improve the plan.

If you want to avoid this, you may use either of the following:

1. Add a /*+ NO_MERGE */ or /*+ NO_PUSH_PRED */ hint into the view definition
2. Add a /*+ NO_MERGE (view) */ or /*+ NO_PUSH_PRED (view) */ hint into the query that uses the view.

If you want to force this, use their couterparts /*+ PUSH_PRED */ and /*+ MERGE */

As long as performance is concerned, there is no difference between using a defined view (if it's not a MATERIALIZED VIEW of course) or an inline view (i. e. subquery).

Oracle compiles plans not for views, but for exact SQL texts.

That is, for the following statements:

SELECT A, B, C 
FROM   aTbl, bTbl
LEFT JOIN cTbl ON 
  bTbl.cTblID = cTbl.objectkey
WHERE aTbl.objectkey = bTbl.parentkey
  AND aTbl.flag1 = :NUMBER

SELECT *
FROM
  (
  SELECT A, B, C, flag1
  FROM   aTbl, bTbl
  LEFT JOIN cTbl ON 
    bTbl.cTblID = cTbl.objectkey
  WHERE aTbl.objectkey = bTbl.parentkey
  )
WHERE flag1 = :NUMBER

/*
CREATE VIEW v_abc AS
SELECT A, B, C, flag1 
FROM   aTbl, bTbl
LEFT JOIN cTbl ON 
  bTbl.cTblID = cTbl.objectkey
WHERE aTbl.objectkey = bTbl.parentkey
*/

SELECT A, B, C
FROM v_abc
WHERE flag1 = :NUMBER

the plan will:

1. Be the same (if Oracle will choose to push the predicate, which is more than probable);
2. Be compiled when first called;
3. Be reused when called again.

Answer 2

There is no "pre-compilation" of views in Oracle: selecting from a defined view is no different performance-wise than selecting from the table directly (assuming all joins and conditions are the same).

I am puzzled when you say your query is "being used 10000 times during a request". That doesn't sound optimal - why is that?

Answer 3

A view isn't really what you want here.

What you want to do is use bind variables for your flag condition; that way you can compile the statement once, and execute multiple times for different flags.

Just as a stylistic note -- you should decide how you specify joins, and go with that, for consistency and readability. As is, you have the explicit join condition for atbl and btbl, and the "left join" syntax for btbl and ctbl.. doesn't really matter in the scheme of things, but looks and reads a little strange.

Good luck!

Answer 4

the answer here is YES and NO.

oracle will only parse a single statement once. the key being that it has to be 'identical' not just similar. this includes spacing (or tabs) and comments.

creating a view with create view allows you to fix a statement as-is and allow for reuse.

however.

SELECT * FROM VIEW
and
SELECT * FROM VIEW WHERE A=1

are 2 different statement/queries.

to get maximum statment reuse you HAVE TO USE bind variables, and not just concatenate conditions onto your SELECT. and that usuall means prepared statments

you have not mentioned which host language if so say i'll update with a specifica example if i can.

in PL/SQL

DECLARE
  cursor c (cpflag aTbl.flag1%TYPE )is
   select A, B, C 
     from aTbl, bTbl, cTbl
    where aTbl.objectkey = bTbl.parentkey
      and bTbl.cTblID = cTbl.objectkey
      and aTbl.flag1 = cp_flag ;

vtype c%rowtype;
BEGIN

open c(100);
fetch c into vtype;
close c;

open c(200);
fetch c into vtype;
close c;
END;

in things like java prepares statments you put "?" in as placholders and use stmt.bind() calls

hope that helps

Answer 5

Views will not cause performance improvement. Sometimes they may degrade preformance. I don't mean you should avoid views; just know the possible impact before using them. Oracle documentation says:

Views can speed up and simplify application design. A simple view definition can mask data model complexity from the programmers whose priorities are to retrieve, display, collect, and store data.

However, while views provide clean programming interfaces, they can cause sub-optimal, resource-intensive queries. The worst type of view use is when a view references other views, and when they are joined in queries. In many cases, developers can satisfy the query directly from the table without using a view. Usually, because of their inherent properties, views make it difficult for the optimizer to generate the optimal execution plan.

Answer 6

I don't think this is what you are looking for, but if the under lying tables do not change often, like only daily or hourly, you could use a materialized view. That is basically a stored result set. You can also add indexes on them. They can be refreshed on a schedule or when the under lying tables change (not really for OLTP).

Here is Oracle's docs on using materialized views.