Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    35

    Wink Unanswered: y index need for select statement

    hi all,
    I need the index is useful for regular select statment?
    If i put select count(*) without index it takes more , but if i have one index in that, it fetches the rows very quickly and both statements does not have any where clause.
    so pls tell me db2 needs indexes for regular select statment.
    My coworker is saying index is doesn't matter for simple select statement.
    Pls explain how this index work for select statement.
    thanks in advance,
    muthu

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Whether or not an index improves performance depends on many factors, some of which include: which column(s) make up the index, what the SQL query looks like, how many unique values in the index column(s), how many rows there are in the table, etc.. There are many times other factors also.

    When you write a simple SQL statement without a where clause like:

    Select count(*) from table_name;

    DB2 just needs to figure out how many rows there are in the table. To do that it can read all the data rows and count them. But if there is at least one index defined, DB2 can read the index and count up the index entries (there is always one index entry for each data row). Since the index is usually smaller than the data in the table, it will usually just be faster to read the all index entries instead of all the data rows.

  3. #3
    Join Date
    Mar 2003
    Posts
    35

    Wink what is other way for union(or to avoid union)

    Thanks a lot for your response.
    whether index will also be useful for simple select * from emp ?
    In one of our procedure takes 20 mts to run and it contains views and union, if i execute the query seperately without union it runs fast and when i joined the query with union with another query it takes long time.is there any other alternate way is there to avoid union?
    thanks,
    muthu



    Originally posted by Marcus_A
    Whether or not an index improves performance depends on many factors, some of which include: which column(s) make up the index, what the SQL query looks like, how many unique values in the index column(s), how many rows there are in the table, etc.. There are many times other factors also.

    When you write a simple SQL statement without a where clause like:

    Select count(*) from table_name;

    DB2 just needs to figure out how many rows there are in the table. To do that it can read all the data rows and count them. But if there is at least one index defined, DB2 can read the index and count up the index entries (there is always one index entry for each data row). Since the index is usually smaller than the data in the table, it will usually just be faster to read the all index entries instead of all the data rows.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, an index will not help with: Select * from employee;

    This assumes you are selecting all the columns (indicated by an *). If you selected only certain columns, and all columns selected happened to be in one index, DB2 could read the entire index instead of the entire table, similar to the count(*) example. This is called "Index Only Access" with Matching Columns = 0 (indicating that the entire index was scanned).

    In order to answer your other question, I would need to see the exact definition of the table(s), index(es), view(s), and the entire SQL statement (with and without UNION).

Posting Permissions

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