Re: Indexes on Views

All modern SQL database systems that I can think of (Microsoft, Sybase, Oracle, IBM) process normal views by merging the definition of the view into the current query in some form. It then goes through query optimization and will use the indexes defined on the base tables as if they were explicitly listed in the query. I think some older database systems (old DB2?) would execute the view query at run-time and put that in a temporary table for use by the rest of the query.

At least Oracle (I forget which version) and the upcoming Microsoft SQL 2000 support some sort of materialized view which allows access to the view data by using an "index" instead of accessing the base tables. You can check out their web sites and their TPC-D and TPC-H results (which all use some flavor of materialized views). I don't know if Adaptive Server supports this feature or when Sybase plans to implement it.

<simanonok_at_my-deja.com> wrote in message news:8dijjl$he5$1_at_nnrp1.deja.com...
> This thread has had some considerable prosyletizing and chest-thumping
 going
> on in it, and I would like to ask a theoretical question here regarding
> Sybase's implementation of Views:
>
> I have a client, using a Sybase database in an existing application, who
> needs some functionality added to it. They use many Views, to the extent
> that they even have Views constructed from other Views! It has been my
> understanding, and I glean from most of those posting to this thread, that
> Views are generally not indexed (and they are not in my client's
> application). Some seem to indicate that Views CAN be indexed but it is
> stupid to do so. However, I once directly asked a Microsoft SQL Server
> support person if Views CAN be indexed, and he indicated NO, they cannot
 be
> indexed in the first place. I understand that Sybase's database (which
 they
> have renamed to 'Adaptive Server Enterprise') and Microsoft SQL Server (at
> least version 6.5) have a common heritage, so perhaps they behave the same
> regarding views.
>
> My client's application appears to have some performance problems with
 many
> queries on these Views. I have written Sybase technical support and sales
> numerous times and searched their online documentation without success
> (including chasing many broken links) without yet finding an answer to
 this
> simple question:
>
> ====================== Do QUERIES on VIEWS in Adaptive Server Enterprise
 use
> INDEXES, either on the Views themselves or the underlying Tables the Views
> are formed from? ======================
>
> You'd think that this would be a simple question, or at least one that
> someone at Sybase could respond to intelligently. After several weeks of
> trying intermittently to find an intelligent person at Sybase, however, I
 am
> still at a loss. The possibilities seem to be:
>
> 1) Nobody at Sybase knows the answer to that simple question. 2) Nobody at
> Sybase cares about their customers enough to pass my question along to
> someone who might know the answer to it.
>
> After such pathetic experience with Sybase technical support, I am
> recommending to my client that they convert their entire application to
> Microsoft SQL Server and abandon Sybase entirely. It's now plainly clear
 why
> Sybase has so little market share, and I will never recommend to any other
> clients that they use Sybase or continue to use it if they already are.
>
> However, I am still very curious from a theoretical perspective just what
> happens when a View is queried. Does a query on a View use the indexes on
> the underlying tables from which the View is constructed, or not? If
 queries
> on Views do not use the indexes of the underlying tables then the only
 value
> of Views is for rather small recordsets or in cases when the every record
> (perhaps not every field, though) is returned by the query. Otherwise
 you'd
> be better off querying the tables directly because their indexes will
 speed
> the process greatly.
>
> Since I'll be adapting Microsoft SQL Server for my client, I'd appreciate
 it
> if my question (do QUERIES on VIEWS use INDEXES on the underlying TABLES
 the
> VIEWS are formed from?) could be answered specifically for that platform
 as
> well as theoretically (I would think that theoretically, the answer would
 be
> 'YES', but that's just a logical perspective). If anyone happens to know
 how
> Sybase behaves in this regard too, I'd be interested in learning that just
> for curiosity's sake, but I am definitely abandoning that dinosaur
> completely.
>
> Regards,
>
> Karl Simanonok
> karl_at_advantasolutions.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Apr 18 2000 - 00:00:00 CEST