Обсуждение: Under the hood of views

 developer came by and asked me an interesting question.

If he has a view with 20 columns in it, and he selects a specific column from the view
in his query. Does the engine when accessing the view return all columns? or is it
smart enough to know to just retrive the one?

example:

create view test as
select a,b,c,d,e,f,g from testtable;


select a from test;

(does the engine retrieve b-g?)

Thanks

Dave

David Kerr wrote:
>
> create view test as
> select a,b,c,d,e,f,g from testtable;
>
> select a from test;
>
> (does the engine retrieve b-g?)

Shouldn't - the query just gets rewritten macro-style. I don't think it
eliminates joins if you don't need any columns, but that's not possible
without a bit of analysis.

--
   Richard Huxton
   Archonet Ltd

On Thu, Aug 13, 2009 at 05:28:01PM +0100, Richard Huxton wrote:
- David Kerr wrote:
- >
- >create view test as
- >select a,b,c,d,e,f,g from testtable;
- >
- >select a from test;
- >
- >(does the engine retrieve b-g?)
-
- Shouldn't - the query just gets rewritten macro-style. I don't think it
- eliminates joins if you don't need any columns, but that's not possible
- without a bit of analysis.

Perfect, thanks!

Dave

On Fri, Aug 14, 2009 at 12:04 AM, David Kerr<dmk@mr-paradox.net> wrote:
> On Thu, Aug 13, 2009 at 05:28:01PM +0100, Richard Huxton wrote:
> - David Kerr wrote:
> - >
> - >create view test as
> - >select a,b,c,d,e,f,g from testtable;
> - >
> - >select a from test;
> - >
> - >(does the engine retrieve b-g?)
> -
> - Shouldn't - the query just gets rewritten macro-style. I don't think it
> - eliminates joins if you don't need any columns, but that's not possible
> - without a bit of analysis.

In the case above everything is simple enough that the planner will
certainly collapse everything and it'll be exactly as if you juts
wrote the first query. In more complex cases involving LIMIT or GROUP
BY etc that may not be true.

However there's an underlying question here, what do you mean by
"retrieve"? The database always reads the entire row from disk
anyways. In fact it reads the whole block that the row is on.

If there are large values which have been toasted they're never
retrieved unless you actually need their values either for some
operation such as a function or operator or because they're in the
final output to send to the client.

If you mean what is sent over the wire to the client then only the
columns listed in the final select list get sent to the client.

--
greg
http://mit.edu/~gsstark/resume.pdf