Re: Why is SQL not using an index I created.

-----Original Message-----
From: rob@dekko.com <rob@dekko.com>
To: RPG400-L@midrange.com <RPG400-L@midrange.com>
Date: Thursday, August 09, 2001 12:44 PM
Subject: Re: Why is SQL not using an index I created.


>
>I just ran the following:
>CREATE INDEX ROB/rob2 ON ROB/AAAKEY (LOC ASC)
>select * from rob/rob2
>ROB2 in ROB not table, view, or physical file.
>So it looks like you are right.
>
>Do you know why you can use views but not indexes?

Because in SQL a view is a way to look at the data or part of the data. An
index is ONLY a tuning mechanism. This mistreatment of indexes is caused by
the old logical file thinking from the 38 and 400.

>Or are the SQL gods
>saying that 'you do not need to worry your pretty little head about which
>index is most efficient, we will do that for you'?

That too. Again, it's just tuning.

As for the original question, there is no easy answer as to why the indexes
you built aren't being used. You would need to show us the join statement,
the table definitions and the index definitions.

The best way is still to run it under debug and check the joblog for the
messages. They will generally lead you in the right direction.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"America is the land that fought for freedom and then
  began passing laws to get rid of it."

     - Alfred E. Neuman



+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---