4 Replies Latest reply: Aug 5, 2009 11:19 AM by Greybird-Oracle RSS

    Difference between foreign key index and secondary index

    713359
      Hi,

      Let's assume we have two primary databases,

      Employee(id, name, company_id)

      Company(id, name)

      (where company:employee is one to many, and I am using the collections API)

      If we want to make a join between the Employee and the Company based on the Company ID . I want to know the difference between the following two options:

      1- Building a secondary index on Employee(company_id). let us call this index index1. Then we will use the following code:

      For each company c
      index1.get(c.id)

      2- Building a foreign key index on Employee(company_id) where the foreign key database is Company. let us call this index index2. Then we will use the following code:

      For each company c
      index2.get(c.id)

      I have two questions:

      1- What is the difference between these two options in terms of performance?

      2- I know one advantage of foreign key is enforcing integrity constraints (CASCADE, NULLIFY, etc on delete). Does declaring a foreign key gives me any advantage when I want to make a join? ( for example a fast method, or a single statement for doing a join)

      Thanks,
      Walaa.
        • 1. Re: Difference between foreign key index and secondary index
          713359
          I just wanted to mention that the code above is pseudo code, but the actual implementation is in BDB Java Collections API.
          • 2. Re: Difference between foreign key index and secondary index
            Greybird-Oracle
            Hi,

            A foreign key index is a specialized secondary index. The only additional benefit of a foreign key index is that integrity constraints are enforced (these are listed in the javadoc).

            I'm not sure I understand your join example. If you want to join from Employee to Company, then you don't need a secondary index. You would do:
            Company c = companies.get(employee.company_id);
            A secondary index on Employee.company_id is used to find all Employees in a given company, and (if you wish) to enforce foreign key constraints.

            --mark                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: Difference between foreign key index and secondary index
              713359
              Thanks for the reply. Probably the example was poor. How about this one:

              Emp (id, name)

              Comp (id, name)

              WorksFor(emp_id, comp_id)

              where WorksFor is many to many.

              Now option1 is building and index on WorksFor(emp_id) to access companies an employee works for.

              and option2 is making a foreign key from WorksFor(emp_id) to Emp(id).

              When we make a join between Emp and WorksFor on employee id, will there be an advantage from using one option over the other?

              Thanks,
              Walaa.
              • 4. Re: Difference between foreign key index and secondary index
                Greybird-Oracle
                No matter what the example, the only advantage of a foreign key index (above the advantages of a secondary index) is that it enforces foreign key constraints. There is no other advantage to a foreign key index.

                --mark