Can using a VIEW for SELECT operations improve performance?

Question

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);
   • Be compiled when first called;
   • Be reused when called again.

Answer 2

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!