RE: table indexes

Birgitta,
Apparently exclusive use of SQL tends to create more access paths than LF and I've yet to see a cost/benefit analysis.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, July 10, 2014 10:06 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: table indexes

Just as an aside: DDS described logical files will share access path if there is an other logical file or SQL index with the same key fields or more key fields in the same sequence.
SQL indexes instead will only share access path with other SQL indexes if they are an exact match, i.e. the same number of key fields in the same sequence.

May be the second index was requested, because it is needed in a program with native I/O, in which two different indexes/logical files with the same key fields must be accessed.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Campin
Gesendet: Thursday, 10.7 2014 21:28
An: Midrange Systems Technical Discussion
Betreff: Re: table indexes

You can create the index but if you look at the logical file that gets created as a result of creating the index you will see that it says that it is being shared with the original index so in effect the only thing you are doing is creating a name that SQL will never use because it has the original index. The only thing being stored is the format object. Unless you tell it no, OS400 will always share an existing index if it can. No reason to keep and maintain two.

That is why I always look at indexes when I go to work for a company. At one company I found somewhere upwards 300B bytes of indexes that were not neeed if you just deleted and recreated the indexes(logical files) and it looked and said I already have an index I can use.


On Thu, Jul 10, 2014 at 12:45 PM, Briggs, Trevor (TBriggs2) < TBriggs2@xxxxxxxxxxx> wrote:

Surely that depends upon who or what is doing the telling? If it's the
SQL optimizer telling you then it might have a good reason. If it's
your CEO, then the correct response is, "Yes ma'am,

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Richard
Sent: Thursday, July 10, 2014 2:24 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: table indexes

I would not think so . the data will already be presented in the
desired order.

/dick


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Dave
Sent: Thursday, July 10, 2014 1:17 PM
To: Midrange Systems Technical Discussion
Subject: table indexes

Hi,

I have a table with an index already on Columns 1, 2, 3 and 4.

Now I've been told we need another one on Columns 1, 2 and 3.

Is that necessary? I don't see why.

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


**********************************************************************
**********************************************************************
****************************************************************
This message originates from Lincare Holdings Inc. It contains
information which may be confidential or privileged and is intended
only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use
the contents of this message.
All personal messages express views solely of the sender, which are
not to be attributed to Lincare Holdings Inc., and may not be copied
or distributed without this disclaimer.
If you received this message in error, please notify us immediately at
MailAdmin@xxxxxxxxxxx or (800) 284-2006.

**********************************************************************
**********************************************************************
****************************************************************

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.