Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    10

    Unanswered: Optimization in retrieval using indexes

    I am posting this question here because of the fact that it has more to do with database theory than a particular product.

    Supposing i have a table TABLE1(ID,ID2,field1,field2,....,fieldn)

    The primary key is ID.
    There is a secondary index on ID2. ID2 is a candidate key.
    These are the only indices.

    1)Do modern database systems like Oracle,DB2,SQL Server provide a way to execute the following query

    SELECT * FROM TABLE1 ORDER BY ID


    in approximately the same time taken for the foll. query

    SELECT * FROM TABLE1


    Particularly if the primary index (or file organisation) can be organised in a way (maybe i choose the file/index orgn. offered by the DB) so that the above is achieved.

    Any help is appreciated.
    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Posts
    40

    Re: Optimization in retrieval using indexes

    For Oracle (any version > V7), your PK must have an index and
    your other key, you say, is indexed so if you order by either of
    these columns that are indexed, the query will not take very much
    longer that your #2 statement.

    You mention "approximately" so.... this'll really have to do with
    how many records are in the database. If all you have is a four (4)
    column table with, lets say, < 5,000 rows, querying that table
    and accessing index columns (in either the "where" or "Order by"
    clause, will be very fast.

    For exact results and complete details as to what the query will
    do, you'd need to "anaylze" the query.

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    10
    One more doubt please.
    With ref. to the first post,

    1)SELECT * FROM TABLE1 WHERE ID>M AND ID<N


    AND

    2)SELECT * FROM TABLE1 WHERE ID2>M AND ID2<N


    Won't 1) execute very much faster coz of using a primary index that matches the file organisation leading to virtually sequential retrieval?

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    It also largly depends on your optimizer mode.
    If you are using COST Based Optimisation, then the oracle database internally calculates the load and decide on wether to use the index or not.

Posting Permissions

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