optimizing a view-driven query

optimizing a view-driven query

Post by will trilli » Fri, 30 Mar 2001 08:07:50

if i've got a view that joins three or four tables, is there a
way to SELECT on that view to bypass any of the joins if they're
not needed in the result?

        create view course as
                        _course.name as course,
                        _topic.name  as topic,
                        _school.name as school,
                        _state.name  as state,
                        _school.zip  as zip
                        _course.topic = _topic.id
                        _topic.school = _school.id
                        _state.abbr   = _school.state

        select * from course ;
        -- shows all fields via all tables

        select topic from course where course like '%comput%' ;
        -- not asking for state, we don't need to join the state table

here the optimizer might know we don't need to join the static
lookup '_state' table. can this be made to happen?

or is it just best to have "one view, one purpose"?

It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
                -- Isaac Asimov, 'The Genetic Code'

http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


optimizing a view-driven query

Post by Tom La » Fri, 30 Mar 2001 08:31:57

>    select topic from course where course like '%comput%' ;
>    -- not asking for state, we don't need to join the state table

But you still do need to join, because the join affects which rows
will be returned.  The fact that you don't happen to use any values
out of one of the joined tables in your SELECT list is not very

In this example, you might happen to know (or think you know) that there
will be one and only one state row matching any possible row from the
subjoin of the other tables, so in the end it wouldn't affect the number
of rows output.  This is not an assumption the planner is prepared to
make, however.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?



1. Optimizing Queries, Views vs Tables - Info Needed


I'm going to be starting creation of some training materials soon and would
like to know if there are any resources on-line the explain how to optimizie
queries - yunno standard stuff like what selections to run first.  I would
also like to know if there is any text out there explaining how and why and
views are slow -- I know the answers but I tend to not dumb stuff down
enough sometimes and would like a basic outline to start working from.

Any ideas ?


2. VB vs Developer 2000: Career Choice

3. optimizing a query on a view

4. starting agents from command prompt

5. Which is more optimized for a select query view or stored procedure

6. How to copy from fields in a DBF file to a DB file

7. optimize: view of views

8. SQL Server Web DSN Set Up

9. ASA6: Optimize view of a view?????

10. Partitioned view optimized incorrectly

11. optimize View on Tables on different databases

12. To VFP Gugus: Really OPTIMIZING local views

13. anyway to optimize a union in a view?