Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: Views not using Indexes

    Hi all,

    just wondered if anyone would be able to shed any light on an issue we are currently having.

    We have 2 tables which we union using a view. Selecting information from this view is fine, and indexes on the underlying tables is used correctly.

    For example:

    Select v.*
    From View v
    Where v.id = x

    However, if we need to join with another table table, the optimizer seems to be creating a temporary table and then sequentially scanning.

    For example:

    Select v.*, o.*
    From View v, Other o
    Where v.id = o.id
    And o.id = x

    This is causing our queries to take a long time to complete due to the size of the table being created by the view (3 million records).

    Any help appreciated!

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hi Lemm,

    Are you sure there are statistics available on the table under the view. If this isn't solving the problem please sen din info like exact version and both access paths.

    Hope ths helps,

    Rob

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,
    Please, take the SQL that generates the view and runs this with set explain on.

    Gustavo.

Posting Permissions

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