Can using VIEW operations for SELECT improve performance?

Question

When working to improve the performance of our decision center, one of the bottlenecks that we identify is the database.

So, I wonder if the oracle compiles the execution plan for it?

Suppose suppose I have a specific request that is used 10000once during the request. The query looks something like this:

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 get the query result above with an additional filtering parameter, for example: WHERE aTbl.flag1 = <<NUMBER>>

Now I have 2 options:

• Create a prepared statement using the above SQL, and then reuse the object.
• Put above select (aTbl, bTbl, cTbl)in VIEW, and then create a prepared statement on this view, thereby taking advantage of the execution plan pre-compiled by Oracle.

What would you suggest?

Answer 1

Oracle can put a predicate in a view if he believes that he will improve the plan.

If you want to avoid this, you can use one of the following actions:

• Add a tooltip /*+ NO_MERGE */or /*+ NO_PUSH_PRED */to a view definition
• Add a tooltip /*+ NO_MERGE (view) */or /*+ NO_PUSH_PRED (view) */to a query that uses a view.

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

, ( MATERIALIZED VIEW, ) (, ).

Oracle , SQL.

:

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

:

• ( Oracle , );
  • ;
  • .

Answer 2

.

, ; .

- , , , . , atbl btbl, " " btbl ctbl.. , .

!

Answer 3

. . , ; . Oracle :

. , - , , .

, , , . , . . , - , .

Answer 4

, , , , , , . . . ( OLTP).

Oracle .

Answer 5

Oracle " " : , ( , ).

, , " 10000 ". - ?

Answer 6

: .

oracle . , "" . ( ) .

create view as-is .

.

SELECT * FROM VIEW

SELECT * FROM VIEW WHERE A=1

- /.

, , SELECT. usuall

, , , .

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;

, java, , ?? stmt.bind()

,