Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Index on Large Table

    Hi all,

    I need some help on indexing a table around 10 million rows.
    Basically, the table contains

    Table: Account
    unique_number
    account_type (There are about 10 account types)
    account_number
    account_name
    SSN
    account_amount

    This table is used both for query and reports.

    At first, I create an index on account_type, account_number and
    account_amount. It works great except when I try to combine this table
    with the address table, and I want to select the address for everyone
    with a specific type. Since the query has account_type, it used this
    index which made the query very slow.

    I then drop and create a new index just on account_number and
    account_amount, but why is the query slow when I do the following?

    select unique_number
    from account
    where account_type = 'a'
    and account_number like '999'
    and account_amount = 100

    What is the best way to tackle and create the appropriate index.
    Should I just make the index account_number, acocunt_type,
    account_amount?

    Thanks for the help!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It would help if you posted the actual DDL of the Account Table and the Adress Table, and describe the values in each column and how the tables are used in the applciation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Index on Large Table

    Well, Account table has all th information regarding the account. The address table has the address information for each unique number.

    I think I should take a step back and ask the most basic question.

    I know it's not reasonable to create an index on the account_type by itself since there is only 10 account_type, but on the other hand account type is used in many other aggregate functions where I would be account_type and sum(account_amount).

    In this basic query

    select unique_number
    from account
    where account_type = 'a'
    and account_number like '999'
    and account_amount = 100


    Will there a difference if I create an index on account_type, account_number, account_amount vs. account_number, account_type, account_amount?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure what you mean by:

    account_number like '999'

    do you mean:

    account_number like '999%'
    account_number like ‘%999%’
    account_number = '999'
    or something else?

    Check out the syntax for ‘like’ in the SQL Reference manual.

    An index on account_number by itself is probably OK, but an index on account_number and account_amount together might be beneficial if account number (or the 3 digits of account number that are included in the ‘like’ predicate) has many duplicate values.

    But if account_amount is updated frequently, including it in the index will hurt update performance since the index row will probably need to be moved each time account_amount is changed.

    Your explanation of the table DDL and the application is really not sufficient to give a complete answer.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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