Re: [U2][UD] Index for @ID (IBM documentation)
Hi, If you have access to a UV system, add EXPLAIN to the command line to see what RetrieVe does. Works fine for me, using the index. Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 On Thu, Jan 29, 2009 at 12:18 PM, Kevin King wrote: Martin, are you sure? I would expect the query optimizer to see the <= and cancel the use of the index. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
If you have access to a UV system, add EXPLAIN to the command line to see what RetrieVe does. On Thu, Jan 29, 2009 at 12:18 PM, Kevin King wrote: > Martin, are you sure? I would expect the query optimizer to see the <= and > cancel the use of the index. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UD] Index for @ID (IBM documentation)
If I indexed @ID, I would always include nulls in the index. Two reasons: 1) There should never be a null @ID 2) The reason you shouldn't index nulls is because you may have many records with a null value, so you will end up with a huge record in your index. Bad... But with @ID you'll only have one value so the argument against indexing nulls is invalid. Cheers, Wol -Original Message- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Martin Phillips Sent: 30 January 2009 10:27 To: u2-users@listserver.u2ug.org Subject: Re: [U2][UD] Index for @ID (IBM documentation) Hi, > Martin, are you sure? I would expect the query optimizer to see > the <= and cancel the use of the index. Unless I mis-typed my posting (I didn't keep it), it is fine because the overall condition is not satisfied by a null item. Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
Hi, Martin, are you sure? I would expect the query optimizer to see the <= and cancel the use of the index. Unless I mis-typed my posting (I didn't keep it), it is fine because the overall condition is not satisfied by a null item. Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UD] Index for @ID (IBM documentation)
IMO, if the index has been created with NO.NULLS, then the <= would require the file to be scanned as there is a possibility of a "" in the data that would not be index'ed. -Original Message- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Friday, 30 January 2009 7:18 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2][UD] Index for @ID (IBM documentation) Martin, are you sure? I would expect the query optimizer to see the <= and cancel the use of the index. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
Martin, are you sure? I would expect the query optimizer to see the <= and cancel the use of the index. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
Hi, The most common use of an index on @ID is where the ids are a sparse set of values and you want to find those in a specific range. For example, if I have a file where the id is the date but not all dates are present, I might want to do LIST MYFILE WITH DATE >= "1 Jan 08" AND <= "31 Dec 08" With an index on @ID, this only reads the records that exist. With no index, it must read the entire file. Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
> It's not going to speed everything up, just certain select statements. And > quite honestly, it's going to speed up certain select statements that are > not really kosher. > > For example, if your application is full of select statements like SELECT > FILE WITH @ID = "ABCDEF", then you should probably re-write the statement to > something like SELECT FILE "ABCDEF". What about this? SELECT FILE WITH @ID LIKE ABCDEF... or, in Pick style SELECT FILE EQ 'ABCDEF]' Tim Snyder Consulting I/T Specialist U2 Lab Services Information Management, IBM Software Group 717-545-6403 tsnyd...@us.ibm.com --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
It's not going to speed everything up, just certain select statements. And quite honestly, it's going to speed up certain select statements that are not really kosher. For example, if your application is full of select statements like SELECT FILE WITH @ID = "ABCDEF", then you should probably re-write the statement to something like SELECT FILE "ABCDEF". If you can't do that, because you don't have the source, don't have the time or whatever, then creating the index might help. I first heard of this a few years ago and yes, it does work, but I've never actually used it in a live application. It's just not a real good use of an index file. - Original Message - From: "jjuser ud2" To: Sent: Wednesday, January 28, 2009 1:06 PM Subject: [U2][UD] Index for @ID (IBM documentation) > Hi everybody, > > In the IBM publication "Developing UniBasic Applications", Version > 7.1, December 2006, it says on page 4-16 (page 93 in my PDF file): > > Tip: Create an index for @ID to speed access to data > records. > > Seriously? Will that honestly speed things up in a file that has > eight million records? > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ [demime 1.01d removed an attachment of type application/pgp-signature which had a name of PGP.sig] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UD] Index for @ID (IBM documentation)
Okay, cool :) I thought I was going crazy when I read that and that my knowledge of indexes was more bunk than it already is. Thanks Tom!!! --JJ On Wed, Jan 28, 2009 at 1:52 PM, Tom Whitmore wrote: > No, you don't want to index on the ID because it is unique. That would be a > very inefficient index. If you created an i-descriptor that broke the ID > into several multi-valued parts, then it MAY help, but I don't think it is > worth the overhead. > > Tom > > -Original Message- > From: owner-u2-us...@listserver.u2ug.org > [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of jjuser ud2 > Sent: Wednesday, January 28, 2009 1:06 PM > To: u2-users@listserver.u2ug.org > Subject: [U2][UD] Index for @ID (IBM documentation) > > Hi everybody, > > In the IBM publication "Developing UniBasic Applications", Version > 7.1, December 2006, it says on page 4-16 (page 93 in my PDF file): > > Tip: Create an index for @ID to speed access to data > records. > > Seriously? Will that honestly speed things up in a file that has > eight million records? > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UD] Index for @ID (IBM documentation)
No, you don't want to index on the ID because it is unique. That would be a very inefficient index. If you created an i-descriptor that broke the ID into several multi-valued parts, then it MAY help, but I don't think it is worth the overhead. Tom -Original Message- From: owner-u2-us...@listserver.u2ug.org [mailto:owner-u2-us...@listserver.u2ug.org] On Behalf Of jjuser ud2 Sent: Wednesday, January 28, 2009 1:06 PM To: u2-users@listserver.u2ug.org Subject: [U2][UD] Index for @ID (IBM documentation) Hi everybody, In the IBM publication "Developing UniBasic Applications", Version 7.1, December 2006, it says on page 4-16 (page 93 in my PDF file): Tip: Create an index for @ID to speed access to data records. Seriously? Will that honestly speed things up in a file that has eight million records? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2][UD] Index for @ID (IBM documentation)
Hi everybody, In the IBM publication "Developing UniBasic Applications", Version 7.1, December 2006, it says on page 4-16 (page 93 in my PDF file): Tip: Create an index for @ID to speed access to data records. Seriously? Will that honestly speed things up in a file that has eight million records? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
