Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Unanswered: Trouble fetching from table with about 1.1 million records

    Hi,
    I am trying to fetch a list of records from a table that has about 1.1 million records. Generally the query fetches about 200 records. In such a case the query takes about 45-90 seconds to complete.

    This table has 7 fields, there is a unque index on a field which has a running sequence. There is no other index on the table. The query mentioned above is NOT on the unique index that I have mentioned. Is it possible that the query is takeing so much time only because there is no index that the query is using, or can there be any other reason than just indexes?

    Thanks,
    Sree Kumar

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trouble fetching from table with about 1.1 million records

    You want 200 records from a 1100000 row table, i.e. a TINY proportion. This is exactly what indexes are for! Without a usable index, Oracle will simply have to trawl through all 1100000 records and inspect to see if they match the criteria (i.e. a full table scan).

    Add an index, and the response time should come down to under a second, I'd guess.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: Trouble fetching from table with about 1.1 million records

    I would suggest you check the explain plan to determine exactly what Oracle is doing for your query. If you are getting a full table scan of 1.1 million records then you should follow Andrews advice and consider putting an index on the table.

    It's generally a good idea to check each of your queries as you're developing.

    Explain plan for Select ..... from .... where;

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Do explain plan on your query to find out what is going on.

    Keep in mind that, long inlists can cause CBO to spend long time to to determine the cost for the expanded statement. Especially when the inlist is expanded into a large number of UNION ALLed statements.

    For ore information on this check this note on Metalink


    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

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