| index on view [message #20756] |
Tue, 18 June 2002 08:49  |
Wendy Frederico
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
If I join 2 views together, are the indexes from the underlying tables used, or is a full scan done?
Are there any disadvantages of putting views over all of my tables, and running an application from the views?
Thanks in advance,
Wendy
|
|
|
|
| Re: index on view [message #20757 is a reply to message #20756] |
Tue, 18 June 2002 09:37  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
A view is just a piece of sql. When you query the view (or views), Oracle tries to re-write the query to push your "where clause" deeper into the SQL to avoid the views returning data which will be filtered out anyway.
Consider view V_emp = select * from emp;
Now if you query
select * from v_emp where emp_no = 10;
You could re-write the query as:
select * from (select * from emp) where emp_no=10;
Oracle effectively re-writes it to push the where clause deeper as:
select * from (select * from emp where emp_no=10);
If your view includes "distinct", "group by", "max" etc then Oracle generally can't push your where clause deeper.
Oracle's data dictionary is full of view - user_tables, etc etc. are all views. Make sure that your views are efficient and you should be fine. Sometimes you can rewrite a query not involving views more effieciently than by simply querying and views with other views or other tables - so sometimes you can do better.
So the answer to your question is that yes, indexes qould be used if it makes sense for oracle to use them.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
