Re: using index

paulkcng_at_news.netvigator.com (Ng K C Paul) writes:

> I want some standards or guidelines in designing database and programming
> in SQL. I think there should be some standard mechanisms in
> forming the execution path of the SQL query. When writing program spec.,
> I don't have the database in mind, maybe it could be IBM DB2, CA-Ingres,
> or Oracle Rdb.

I don't think there are any _standardised_ ways (which would work with all the database brands) to persuade the optimizers to follow some specific execution plan.

A trick which may work across many relational db systems is to explicitly add 0 (or concatenate the empty string for character columns) to those columns(s) which have an index you don't want to use.

For example, let us have two tables A and B, and

  A.a_id = A's Primary key
  B.b_id = B's Primary key
  B.b_a_id = foreign key from B to A

Assume all these columns are indexed.

Case 1, let the optimizer choose whatever it thinks is best:

  select A.*, B.*
    from A as a, B as b
   where a.a_id = b.b_a_id

Case 2, don't use any indexes:

  select A.*, B.*
    from A as a, B as b
   where a.a_id+0 = b.b_a_id+0

  Optimizers (probably) won't consider using indexes on a.a_id or   b.b_a_id, because the column expressions are modified by +0.

Case 3, force the join from table A to B:

  select A.*, B.*
    from A as a, B as b
   where a.a_id+0 = b.b_a_id

  Index on a.a_id is not applicable, but index on b.b_a_id is, so   the join is driven from table A to B.

Case 4, force the join from table B to A:

  select A.*, B.*
    from A as a, B as b
   where a.a_id = b.b_a_id+0

  Likewise, but from table B to A.

Of course, this trick (+0 or +'') can be used on any indexed column or columns whose indexes you don't want to use on some specific occasion.

Experiment with your specific queries when you have problems. Remember also, that the optimisation you make today may be invalid two years from now, when your data mass grows and/or the distribution of your data changes, or when you install the next version of the db engine.

Regards,

Tapio Luukkanen Received on Wed Oct 14 1998 - 00:00:00 CDT