Results 1 to 3 of 3

Thread: create index

  1. #1
    Join Date
    Nov 2002
    Posts
    16

    Unanswered: create index

    there are 2 tables (A, B). Inside A, there are 4 columns (a1, a2, a3, a4) and inside B, the columns are (b1, b2, b3, b4). And I got a query like the one below :

    select a1, a2, a3, a4, b1, b2, b3, b4
    from A, B
    where A.a1 = B.b1
    and A.a2 = B.b2
    and A.a3 = 'abc'

    What kind of index I should create in A & B ?

    thx!!!

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: create index

    Originally posted by ernestso
    there are 2 tables (A, B). Inside A, there are 4 columns (a1, a2, a3, a4) and inside B, the columns are (b1, b2, b3, b4). And I got a query like the one below :

    select a1, a2, a3, a4, b1, b2, b3, b4
    from A, B
    where A.a1 = B.b1
    and A.a2 = B.b2
    and A.a3 = 'abc'

    What kind of index I should create in A & B ?

    thx!!!
    Hi,
    You should create a composite index.
    nn

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

    Re: create index

    Originally posted by ernestso
    there are 2 tables (A, B). Inside A, there are 4 columns (a1, a2, a3, a4) and inside B, the columns are (b1, b2, b3, b4). And I got a query like the one below :

    select a1, a2, a3, a4, b1, b2, b3, b4
    from A, B
    where A.a1 = B.b1
    and A.a2 = B.b2
    and A.a3 = 'abc'

    What kind of index I should create in A & B ?

    thx!!!
    First question: what are the Primary Keys of A and B? It looks like the Primary Key of B might be (b1,b2). For every primary key, there is always a corresponding index anyway.

    Second question: is there a Foreign Key between A and B? Again, it appears that there may be and it is (a1,a2) references B(b1,b2) - though this might not be the case. If there is a Foreign Key then in 99% of cases there SHOULD be (may not be) a corresponding index, i.e. (a1,a2) on A.

    Based purely on what can be seen here, the ideal indexes would appear to be: (a3) on A and (b1,b2) on B. However, it depends on other factors such as the "selectivity" of A.a3, and the number of rows in the tables, as to whether such indexes would be useful - the optimiser may choose to ignore them and do full table scans.

    As a rule of thumb for beginners I would say:
    1) Declare a Primary Key (with its implict index) on every table
    2) Declare Foreign Keys between tables for referential integrity
    3) Create matching indexes for each and every foreign key unless you know a good reason not to.
    That will take care of 95% of your index requirements.

Posting Permissions

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