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

I figured out how to get it to Snap...

Here is the original query:

SELECT *
FROM INVITMPF AS A
 INNER JOIN
     SHPEXT70 AS B
 ON
     A.IMGRP  = B.OIGRP AND
     A.IMMFG  = B.OIMFG AND
     A.IMTTL  = B.OITTL AND
     A.IMMDSE = B.OIMD  AND
     A.IMBR   = B.OIBR

Here is what will make it Crackle:  one record in file A has many results
in file B

SELECT B.*
FROM INVITMPF AS A
 INNER JOIN
     SHPEXT70 AS B
 ON
     A.IMGRP  = B.OIGRP AND
     A.IMMFG  = B.OIMFG AND
     A.IMTTL  = B.OITTL AND
     A.IMMDSE = B.OIMD  AND
     A.IMBR   = B.OIBR

If I try to access any columns in file A other than the key fields, it
builds an index.

Where's the Pop?

Mark Adkins



                                                                                
                                       
                    "R. Bruce                                                   
                                       
                    Hoffman, Jr."            To:     <RPG400-L@midrange.com>    
                                       
                    <rbruceh@attgloba        cc:                                
                                       
                    l.net>                   Subject:     Re: Why is SQL not 
using an index I created.                 
                    Sent by:                                                    
                                       
                    owner-rpg400-l@mi                                           
                                       
                    drange.com                                                  
                                       
                                                                                
                                       
                                                                                
                                       
                    08/09/01 12:31 PM                                           
                                       
                    Please respond to                                           
                                       
                    RPG400-L                                                    
                                       
                                                                                
                                       
                                                                                
                                       




-----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
+---



+---
| 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
+---