Thanks for the question, Preeti.

Asked: August 29, 2001 - 10:28 pm UTC

Last updated: May 25, 2017 - 1:18 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

We are displaying several complicated reports in our web application. These reports use joins in many Orcale tables. Tables may have 1000s of records. Do we gain any performance by making Views that perform complicated SQL. And then in our application just code SQL as
SELECT * FROM view_name.

and Tom said...

No, a view is simply a stored query. When you submit "select * from view_name" we simply retrieve the view definition and rewrite the query using it.

Now, there are MATERIALIZED views which can definitely speed up reporting -- especially if you do large aggregations of data. See

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76994/mv.htm#721 <code>

for an overview of this technology.

Rating

(19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Well,

John, August 30, 2001 - 12:40 pm UTC

We create a query

Create view my_view select * from emp;

Then I issue the statement

Select * from myview;

IS SELECT * FROM EMP resolved first and then select * from myview.

Is there any difference between
Create view my_view as select * from emp; and
Create view my_view as select col1,col2,col3 ....from emp;

August 30, 2001 - 1:06 pm UTC

When you issue the statement:

select * from myview;

it is processed as:

select * from ( select * from emp );

going further, if you issue:

select * from myview where empno = 1234;

that will be:

select * from ( select * from emp ) where empno = 1234;

which will be rewritten internally as:

select * from (select * from emp where empno=1234);

allowing indexes and so on to be used (this is predicate "pushing" whereby predicates are pushed into subqueries when possible).

There is no difference between "select *" and "select c1, c2, c3, ...". In fact, select * is turned into select c1, c2, c3:

scott@ORA817DEV.US.ORACLE.COM> create or replace view v as select * from emp;
View created.

scott@ORA817DEV.US.ORACLE.COM> select text from user_views where view_name = 'V';

TEXT
--------------------------------------------------------------------------------
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp

Now, there IS a difference between select emp.* and select * however:

scott@ORA817DEV.US.ORACLE.COM> create or replace view v as select emp.* from emp;

View created.

scott@ORA817DEV.US.ORACLE.COM> select text from user_views where view_name = 'V';

TEXT
--------------------------------------------------------------------------------
select emp.* from emp

The difference is subtle and will only become apparent if you add/drop/reorder the columns in the EMP table. My advice is to NOT use correlation names in VIEWS ever. It can only lead to obscure hard to find issues later.

Well

A reader, August 30, 2001 - 1:24 pm UTC

Is select * from ( select * from emp );
more expensive than

select * from emp;

August 30, 2001 - 1:51 pm UTC

It is neglible -- the shared pool caches query plans. After the first parse -- you will not notice any difference (and even then, the first parse will not be noticeably affected).

no convinced

Steve, August 30, 2001 - 3:02 pm UTC

You should not forget that select * from myview; will be pushed out of the shared pool as it will lead to a full table scan.

So every time we issue the query we should do a select * from (select * from emp;)

In my opinion this will definitely more expensive, then just a select * from emp.

How about when we have a 1 gig table , and you create a view on that, and do a select * from from that view. Woudnt that be very costly then doing a select * from the table itself.

August 30, 2001 - 7:27 pm UTC

Huh ??? You seem to be totally mixing up concepts.

A full scan will not cause the shared pool to do anything -- the shared pool is the shared pool. The buffer cache is a separate piece of memory. Full scan -- index read -- who cares, it will NOT affect the shared pool.

Select * from myview where myview is equal to "select * from emp" will do the SAME EXACT THING as "select * from ( select * from emp )"

Views are not evil.
Views are not evil.
(just keep repeating that over and over).
Views are useful.

In my experience, based on fact -- neither of those queries will be more or less expensive then the other.

As for the comment

"How about when we have a 1 gig table , and you create a view on that, and do a
select * from from that view. Woudnt that be very costly then doing a select *
from the table itself."

Doing a select * on a 1 gig table, versus doing a select * from a view of that 1 gig table will be EXACTLY the same-- no different. As I said -- queries against views are simply rewritten to be queries against base tables. Views are just stored queries -- not stored data, not overhead, not dangerous.

Views and 'select *'

Connor, August 30, 2001 - 4:28 pm UTC

Its not good practice to use 'select *' when creating a view, because you can get all sort of nasty anomalies when you redefine things. For example, create a view as "select *" and then drop/recreate the table with columns in different order...You'll get a very different result between "select a.*" and "select *"

View Performance

Robert Eke, September 04, 2001 - 6:32 am UTC

I found the discussion of get value.
I have some large tables which I was going to place a few views over in an attempt to speed things up.

The detail and examples in this discussion have proved very useful in the use of views and their draw backs.

For some reason I was always told that selecting data from layered views is bad. It only seems bad if the sql within the combined views is not optimised.

A very useful review.

Views improving performance

Matt T, September 04, 2001 - 10:02 am UTC

I have found views very useful when using Data Querying
tools which automatically generate SQL (such as discoverer).

By pre-joining your tables into a single view you can
directly effect the performance using hints. This
was particularly useful in the days of Rule based optimiser.

Performance of Views

Sri, September 01, 2003 - 12:25 am UTC

Dear Tom,

We have a debate here regarding the performance of views compared to the underlying tables. One argument is that the query in the view is pre-compiled, so Oracle need not go through all the steps involved in Query processing when I do a 'Select * from my_view' as compared to 'Select * from my_base_table'. I am pretty much convinced from tkprof and explain plan that it is not happening that way. But, my doubt is,

1. why it is not happening that way?

I did the following in my 8i (8.1.7.0) Database: -

create table base_table
(roll varchar2(4) primary key,
name varchar2(10) not null,
mark1 number(3)
)
/
insert into base_table values ('1', 'First', NULL)
/
insert into base_table values ('2', 'Second', 10)
/
create view vw_temp as select * from base_table
/
select * from vw_temp
/
ROLL NAME MARK1
---- ---------- ----------
1 First
2 Second 10

desc vw_temp

Name Null? Type
------------------------------------- -------- ---------
ROLL NOT NULL VARCHAR2(4)
NAME NOT NULL VARCHAR2(10)
MARK1 NUMBER(3)

alter table base_table add (Mark2 Number(3))
/

desc vw_temp

ERROR:
ORA-24372: invalid object for describe

select * from vw_temp
/

ROLL NAME MARK1
---- ---------- ----------
1 First
2 Second 10

insert into base_table values ('3', 'Third', 10, 20)
/

select * from vw_temp
/

ROLL NAME MARK1
---- ---------- ----------
1 First
2 Second 10
3 Third 10

select * from (select * from base_table)
/

ROLL NAME MARK1 MARK2
---- ---------- ---------- ----------
1 First
2 Second 10
3 Third 10 20

2. Why did the desc return an error? If I do the describe after the Select, it is working. Why is this so?

Thank you.

September 01, 2003 - 8:24 am UTC

1) a view is nothing more then a stored piece of text.

when you query "select * from my_view" that is rewritten as "select * from ( YOUR_QUERY_TEXT_HERE )" in effect and then parsed and optimized.

it would not be POSSIBLE to store the compiled plan for a view -- think about it:

select * from view where x = 1;

it would be nice if X had an index -- or even better, X was the primary key -- for that to use an index. however "select * from view" should not.

every query -- without exception - is hard parsed and cached in the shared pool the first time. no query plans are stored in the data dictionary.

2) the view was invalid, you cannot describe an invalid view. the act of using the view compiled it. you can describe valid objects.

rowid's in view

Raghu, September 01, 2003 - 9:44 am UTC

Hi Tom,

I have a view based on two tables.

When i do a SELECT * FROM VIEW_NAME, the tkprof report shows me that the select statement as rowid associated with it.

I would like to know this rowid is from which table out of the two i have in my view.

Recently i had a problem.i had a view which was inturn based on other views.
when a queried from the SQL*PLUS with a where clause i got the correct results.

But when i used forms, it was giving me different count of rows.
Through tkprof i saw that the query was using rowid.
when i executed the same query from the tkprof in SQL*PLUS ,the count was wrong again as it had rowid with it.

so i removed the rowid from the query and executed in SQL*PLUS it worked right again.

Then i changed the view definition , and all the view inside the view definition were changed with the table name and it worked fine for me.

Can you please explain the behaviour of this.

September 01, 2003 - 10:45 am UTC

need a concrete example to work with.

select * from view_name will not show a rowid in a tkprof report.  you would need to actually select rowid from view_name to get that.

the rowid will come from the first key preserved table.  Here is a simple example:

ops$tkyte@ORA920> create table emp as select * from scott.emp where deptno = 10 and rownum = 1;
Table created.
 
ops$tkyte@ORA920> create table dept as select * from scott.dept where deptno = 10;
Table created.
 
ops$tkyte@ORA920> create or replace view V
  2  as
  3  select emp.ename, dept.dname
  4    from emp, dept
  5   where emp.deptno = dept.deptno;
View created.
 
ops$tkyte@ORA920> select rowid from V;
select rowid from V
                  *
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
 
 
ops$tkyte@ORA920> select column_name, insertable, updatable, deletable
  2    from user_updatable_columns
  3   where table_name = 'V';
 
COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
ENAME                          NO  NO  NO
DNAME                          NO  NO  NO

<b>In this case -- the view is not updatable -- there are NO rowids to be had here.  We need a primary/unique key in order to be updatable:</b>

 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table dept add constraint dept_pk primary key(deptno);
 
Table altered.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select rowid from V;
 
ROWID
------------------
AAANjpAAGAAAABKAAA
 
ops$tkyte@ORA920> select column_name, insertable, updatable, deletable
  2    from user_updatable_columns
  3   where table_name = 'V';
 
COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
ENAME                          YES YES YES
DNAME                          NO  NO  NO

<b>Now, the emp table is updatable in this view -- since Oracle knows that each row in EMP will join to AT MOST one row in dept -- so the update against EMP will not be "ambigous" -- a row in emp appears in the view at most once...</b>
 
ops$tkyte@ORA920> select ename from emp where rowid = (select rowid from V);
 
ENAME
----------
CLARK
 
ops$tkyte@ORA920> select dname from dept where rowid = (select rowid from V);
select dname from dept where rowid = (select rowid from V)
                  *
ERROR at line 1:
ORA-01410: invalid ROWID
 
<b>that just proves the rowid belongs to EMP, not dept...</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table emp add constraint emp_pk primary key(deptno);
 
Table altered.

<b>now, we make BOTH tables updatable...</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select rowid from V;
 
ROWID
------------------
AAANjqAAGAAAABSAAA
 
ops$tkyte@ORA920> select column_name, insertable, updatable, deletable
  2    from user_updatable_columns
  3   where table_name = 'V';
 
COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
ENAME                          YES YES YES
DNAME                          YES YES YES

<b>still have a rowid -- but from which table??</b>
 
ops$tkyte@ORA920> select ename from emp where rowid = (select rowid from V);
select ename from emp where rowid = (select rowid from V)
                  *
ERROR at line 1:
ORA-01410: invalid ROWID
 
 
ops$tkyte@ORA920> select dname from dept where rowid = (select rowid from V);
 
DNAME
--------------
ACCOUNTING

<b>dept apparently -- recreate view with tables swapped</b>
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace view V
  2  as
  3  select emp.ename, dept.dname
  4    from dept, emp
  5   where emp.deptno = dept.deptno;
 
View created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select rowid from V;
 
ROWID
------------------
AAANjpAAGAAAABKAAA
 
ops$tkyte@ORA920> select column_name, insertable, updatable, deletable
  2    from user_updatable_columns
  3   where table_name = 'V';
 
COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
ENAME                          YES YES YES
DNAME                          YES YES YES
 
ops$tkyte@ORA920> select ename from emp where rowid = (select rowid from V);
 
ENAME
----------
CLARK
 
ops$tkyte@ORA920> select dname from dept where rowid = (select rowid from V);
select dname from dept where rowid = (select rowid from V)
                  *
ERROR at line 1:
ORA-01410: invalid ROWID
 
 
<b>now it comes from emp</b> that last bit is EMPIRICAL and NOT TO BE RELIED ON.  If you have an updatable join view with more then one table in it -- the rowid is 100% unreliable, do not use it -- you don't really know which table it is for!!!

As for your "wrong answers" -- you'll really have to supply a test case!

If its just a piece of text ...

Oleg Oleander, September 01, 2003 - 10:02 am UTC

than what do you think about the idea of returning resultsets as views created on the fly. I know that DDLs are costly, but If its just a piece of text ... or does it places lockes and not scale? I originally planed to use ref cursor but its a general-use filtering type I implement and I cannot find a way to return a ref cursor.
please comment

Tnx very much

September 01, 2003 - 10:46 am UTC

why cannot you return a ref cursor?

you do NOT want to create views one the fly -- not a chance.

You CAN return a ref cursor. Tell me why you think you cannot and we'll fix that.

not good idea using select * with developer 200

juancarlosreyesp@yahoo.com, September 01, 2003 - 12:20 pm UTC

additionally
The problem of using SELECT * FROM EMP
causes if you add a new column to emp, the need to recompile de report or form in some situations.

because the number of binded variables is unknown at compile time

Oleg Oleander, September 02, 2003 - 5:03 am UTC

Tom,
We are using 9i.
I read your articles:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:139812348065

about binding list, and

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
about sys_context.

I implement a general-use FILTER_TY. The types created under this add the
table-dependent logic, but all types would use the same interface to
supply parameters and get the result set. I would like to use bind
variables and return a ref cursor to the clinet (which is c# using odp.net).
The parameters passed to the FILTER_TY are of two types.
Either a scalar value or a list. The number of parameters of each type are
unknown, which is the problem when using 'execute immediate' or 'open ... for'.
Based on the above mentioned articles I can think of two possible solutions:

1. For binding scalar values I can use sys_context with the appropriate
type conversion.
For binding lists I can use nested tables If I limit the number of
parameters of this type.
It will look something like this:

create type VARCHAR2_TAB_TY is table of VARCHAR2(255);
create type VARCHAR2_TAB_TAB_TY is table of VARCHAR2_TAB_TY;

procedure proc1 (c out sys_refcursor)
scalar_value_1 VARCHAR2(255);
scalar_value_2 VARCHAR2(255);
list_values VARCHAR2_TAB_TAB_TY; -- in real, these parameters are
-- passed to the FILTER_TY

v_sql VARCHAR2(32767) := 'select a,b,c from tab where ';
c is ref cursor;
begin
-- for each scalar value I can:
v_sql := v_sql || 'col_1 = TO_NUMBER(sys_context( ''MY_CTX'',
''scalar_value_1'' )) ';
dbms_session.set_context( 'MY_CTX', 'scalar_value_1', scalar_value_1);

-- for each list value that is set or NOT set (empty) I can:
v_sql := v_sql || 'col_2 in (select * from TABLE(:X)) ';

...

-- and then opening the cursor like:
open c for v_sql
using list_values(1), list_values(2), ..., list_values(n);

-- n is a constant, the maximum number of list_values the rutine can handle.
-- 30 should be enough.

end;

2. For binding scalar values I can use sys_context with the appropriate type
conversion.
For binding lists I can use a plsql table. In this case I have to convert
lists stored in nested tables into comma separated simple varchar2
list, and then I can bind it like scalar values thru sys_context.
This approach is not usable though, because sys_context values can
store values only up to 255 chars.

Q: Is there any better way?
In case 1, will binding of empty nested tables cause any problem?

Thank you very much Tom. Looking forward to your answer.

September 02, 2003 - 7:33 am UTC

you got the scalar part right

but for the list, I would use something like:

loop i 1 .. N over list
dbms_session.set_context( 'my_ctx', 'list_name_' || i, list_value(i) );
end loop;
v_sql := v_sql || ' col_2 in ( select value from session_context
where namespace = ''MY_CTX''
and attribute like ''LIST\_NAME\_%'' escape ''\'' ) ';

just blow all of the values into the context and use session_context to retrieve the list in the query.

Thats what I was looking for...

Oleg Oleander, September 03, 2003 - 2:57 am UTC

I knew you'd know it.
Thanks Tom, I didnt think of that.
Respect.

Pros & Cons

Sikandar Hayat, March 27, 2004 - 12:47 am UTC

I would like to know the Pros and Cons of using views and the situations where we must use views.

Like what is the advantage of view like,

create view myview as select * from emp;
I mean what are the reasons that we are not using select * from emp and creating view on it?

March 27, 2004 - 10:44 am UTC

if you wanted to be able to "rename the columns" without renaming the columns...

if you wanted to "reorder the columns" without physically reordering the columns...

if you wanted to split emp into two physical tables in the future (but not tell anyone)....

basically views are a way to protect applications from physical schema changes -- consider it a "translation layer"

I use views like subroutines. When a query gets too large/complicated to be in the code -- I would consider "viewing it"

View vs Table producing different results

denni50, April 21, 2004 - 11:47 am UTC

Tom

I have a view I've created from a base table that is used
to generate analysis reports. When I attempt to validate
the results produced from the view they are different
from results that are queried directly from the base table.
The results should be the same...see below:
(for sake of brevity I'm only showing the first and last
 lines generated from base table gift to illustrate that
 the correct dates were selected spanning back 6-9 months)

Base Table: Gift
  1  select idnumber,max(giftamount),max(giftdate) from gift
  2  where usercode1='ACLJ'
  3  and giftamount between .01 and 9.99
  4  and months_between(to_date('04-17-2004','MM-DD-YYYY'),giftdate) between 6 and 8.9999
  5  group by idnumber
  6* order by max(giftdate)
SQL> /
  IDNUMBER MAX(GIFTAMOUNT) MAX(GIFTD                                                                                                                                                                    
---------- --------------- --------- 
   567023               6 18-JUL-03                                                                                                                                                                    
   3349899               5 17-OCT-03                                                               
   
5004 rows selected.

Results from View based on same criteria:
create or replace view aclj_maxgift_maxdate
as
select idnumber,max(giftamount) as giftamount,max(giftdate) as giftdate
from gift
where usercode1='ACLJ' 
group by idnumber


SQL> select count(*) from aclj_maxgift_maxdate
  2  where giftamount between .01 and 9.99
  3  and months_between(to_date('04-17-2004','MM-DD-YYYY'),giftdate) between 6 and 8.9999
  4  /

  COUNT(*)
----------
       763


The view produces 763 records while the base table produces
5004 records. 

any clues?....thanks

never mind

denni50, April 21, 2004 - 2:02 pm UTC

figured it out..thanks

how did you figure it out

amit, July 08, 2004 - 6:43 am UTC

Hi,
how did you figure it out

Asad Khan, June 03, 2008 - 9:16 pm UTC

We are getting a push back from DBAs in using views (supposedly affects perfromance issues). They want us to create stored procedures to get the same data we would be from a View.

I have been using views and have not seen any drastic performance issues,a s long as you know how to write your queries.

Is there any justification to their thought process. Is there anything i can take back to them to see that Views are not an overhead?

Thanks Tom.

June 04, 2008 - 9:32 am UTC

... (supposedly affects
perfromance issues) ..

oh my gosh, the old "we had a bad experience with views once, views are evil" story. I actually have a slide that says that, we usually get a really good laugh out of that one.

a view is nothing more than a stored query. Period.

Problems arise when people do a 50 table join, a join that can get any bit of information, and then they use that view over and over - everywhere, even when they only needed one of the tables. They build views of views of views of views - each view being more inappropriate than the last (because the prior views select out stuff they didn't need, the layered views ultimately do the same).

A view should be considered like a subroutine - sometimes they can be general purpose, reusable but USUALLY they are very focused, sole purpose - not extremely reusable.

It is when someone tries to use a view inappropriately that we run into problems. The times I see it most - the <whatever> team, believing no one else could ever write a join, joins are hard - creates a series of views that joins everything possible and then mandates all access only via these views. Now you start using them and hitting tables you had no idea you were hitting - and didn't want/need/desire to hit. but you have no choice.

Views are just a tool

What if you had a DBA that had a bad experience with a stored procedure, where they were abused. Would you live with them saying "no stored procedures, ever, they are performance hindering devices (quite the opposite)"

Are you going to ask this DBA team when they are going to write their own tools to monitor and manage the database - because you know - all tools today use VIEWS (the, well, data dictionary). If views are evil, then all tools are evil. I hope none of their scripts touch the dba_*, all_*, user_* views - because they are views. Heck, the v$ tables are views (well, synonyms that point to views) - so hopefully they just query the x$ tables directly....

ugh, I'm not liking your DBA team.

What if the view is in a separate site?

Ricardo J. Todd, April 25, 2017 - 10:43 pm UTC

I have always believed that views work as TOM describes in this article. But now I have an issue and I would like to know why.
We have a couple of huge tables in one schema, and a view that joins these two tables.
When we use a SELECT statement from a different schema that joins local tables to this particular view, the results takes almost 2 minutes, but if we do the join to the original tables it does it in less than 10 seconds.
What am I missing?
We finally solved the issue by adding a hint to change de driver site, but the question is still there.

April 26, 2017 - 4:21 am UTC

OK, you say "different schema" and then you say "driver site".

That's two different things - one is schema, the other is database.

You need to clarify, and perhaps show us a test case (maybe as a new question)

Difference between SELECT Privilege and WITH READ ONLY

Milind, May 24, 2017 - 11:59 am UTC

Hi TOM,

What is a difference between creating view, granting SELECT privilege on view as against creating view WITH READ ONLY option.

Warm Regards.
Milind

May 25, 2017 - 1:18 am UTC

'read only' also protects the owning schema, eg

SQL> create table t as select * From scott.emp;

Table created.

SQL>
SQL> create or replace view v1 as select * from t;

View created.

SQL> create or replace view v2 as select * from t with read only;

View created.

SQL>
SQL> delete from v1;

14 rows deleted.

SQL> roll;
Rollback complete.
SQL>
SQL> delete from v2;
delete from v2
            *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Well,

Well

no convinced

Views and 'select *'

View Performance

Views improving performance

Performance of Views

rowid's in view

If its just a piece of text ...

not good idea using select * with developer 200

because the number of binded variables is unknown at compile time

Thats what I was looking for...

Pros & Cons

View vs Table producing different results

never mind

how did you figure it out

What if the view is in a separate site?

Difference between SELECT Privilege and WITH READ ONLY

More to Explore

Live SQL

Dev Gym

Views perform better?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Well,

Well

no convinced

Views and 'select *'

View Performance

Views improving performance

Performance of Views

rowid's in view

If its just a piece of text ...

not good idea using select * with developer 200

because the number of binded variables is unknown at compile time

Thats what I was looking for...

Pros & Cons

View vs Table producing different results

never mind

how did you figure it out

What if the view is in a separate site?

Difference between SELECT Privilege and WITH READ ONLY

More to Explore

Live SQL

Dev Gym