SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Rochester
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How use an exiting index in a view

    Hi,

    Need help again. I am creating a view. It is taking too long. so I want to use the Index.


    I am using only one table. It has 5 or 6 indexes. Lets say I have temp5_ind which was created when database tables were created. I want to use this temp5_ind in my view. Need some help on that..


    Thanks,
    jani....

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    views do use an index

    it is just as though you were running the underlying query

    a view id really only a definition of a query

    the query will either use the index or it won't

    do an EXPLAIN on the query to find out what the optimizer is doing

    rudy
    http://r937.com/

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Rochester
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,
    Thanks for your reply. One month back I posted one question using subqueries. Somebody gave me an answer. If I create a small table with data -it is working fine. But if I use the regular table it is taking long time. So I thought of using an index.

    This is the query I have in my view
    -----------
    create unique index #temp_ind on
    case_table(csm_projno)

    SELECT case_type, csm_projno
    FROM case_table
    WHERE case_type = 'BLD'
    UNION
    SELECT case_type, csm_projno
    FROM case_table
    WHERE case_type IN ('MEC','ELE','PLM') AND
    csm_projno NOT IN (SELECT csm_projno FROM case_table
    WHERE case_type='BLD')

    1.I am selecting BLD cases only - if csm_projno exits for cases like 'MEC','ELE','PLM'
    2. selecting cases like 'MEC','ELE','PLM' - if csm_projno does not exits for "BLD'
    3. if only "BLD' cases exits then I am selecting those.

    I am not sure it is using the index or not...
    May be I am not making any sense...I am not at all good at this....

    Thanks,
    jani...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jani
    create unique index #temp_ind on case_table(csm_projno)
    well, no, it wouldn't use that index, because your WHERE clause has conditions on case_type, not csm_projno

    you can also speed up your query by changing UNION to UNION ALL, because there can be no duplicates across the subqueries

    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •