Re: Indexes on Views

From: Ray DiMarcello <rdimarcello_at_ingva.com>
Date: 2000/04/20
Message-ID: <8dnecb$17i$1_at_bob.news.rcn.net>

For Sybase, the answer is yes, views can and will use indexes on the underlying tables. Views wouldn't be much use if they didn't This is subject to the rules governing whether any query can or will use a given index, however. For all of that information, see the Sybase documentation. There's no such concept as putting an index on a view.

1> sp_helptext AccountHist
2> go
 # Lines of Text

---

               2

(1 row affected)

         text

---

---

---

---

          CREATE VIEW AccountHist AS SELECT
          iIdAccountHist,
          iIdTran,
          iIdSubType,
          iIdAccountDivision,
          iIdBreakage,
          rDollars,
          dCycleDate,
          cIdAccountCode,
          bDebit,
          bLOB,
          bSeparateAccount,
          iUnDone
          from accounting..AccountHist

(2 rows affected)
(return status = 0)

1> use accounting
2> go

1> sp_helpindex AccountHist
2> go

 index_name           index_description
         index_keys

         index_max_rows_per_page
 -------------------- ------------------------------------------------------
--
        --------------------------------------------------------------------
-------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
        -----------------------
 XAK1AccountHist      clustered, unique located on default
          dCycleDate, iIdAccountHist

                               0
 XIE1AccountHist      nonclustered located on default
          iIdTran

                               0

(2 rows affected)
(return status = 0)

1> use admin_prod
2> go

1> set showplan on
2> go

1> select 1 from AccountHist where iIdTran = 123456 2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            accounting..AccountHist
        Nested iteration.
        Index : XIE1AccountHist
        Ascending scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            iIdTran
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

---

(0 rows affected)

1>

simanonok_at_my-deja.com wrote in message <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 Thu Apr 20 2000 - 00:00:00 CEST