RE: [U2] Double Indexes
Definitely yes. A, D, I, S and SQL index types are all supported by UniVerse. You only need to look at the LIST.INDEX report to see that. - Original Message - From: "Marc Harbeson" <[EMAIL PROTECTED]> To: u2-users@listserver.u2ug.org Subject: RE: [U2] Double Indexes Date: Fri, 21 Oct 2005 17:31:51 -0400 > > What about an index on an I-DESC? (Does universe do that?) --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Double Indexes
> From: Marc Harbeson > What about an index on an I-DESC? (Does universe do that?) Yes. Virtual attribute is another name for I-descriptor, UniData's term mostly. See Scott Ballinger's earlier post on thist thread & here is a repost of part of my own answer: > From: Stevenson, Charles > Sent: Thursday, October 20, 2005 10:21 PM > > > Does U2 support indexing > > virtual fields, ie DATE.CUST? I don't think D3 can. > > Absolutely! Virtual fields & correlatives, too. > > The only caveat is do NOT index on any calculation where the > answer depends on anything other than the exact record being > updated. The archtypical bad example would be TRANS() & > T-correlatives. The system allows it, but you are asking > for corruption. > > cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Double Indexes
Sorry, are you talking about U2 or D3? I don't think I-Types work to well on D3. Mark: I have done a lot of indexing on I-Type items. I have had no problems with the indexing, it just works, solid. The thing you have to be careful of is indexing on translates. Once you think about it the problem becomes obvious. The index update mechanism is triggered by a write to the indexed file. If you have a translate, the indexed file does not get a write when the indexed value is updated in the other file so the index becomes stale when the indexed value is updated. It can work if the other file is a static code file and you remember to re-index when ever you update that file, but it is dangerous. I have never tried a correlative-based indexed field. I stay away from correlatives as much as possible. Tom Dodds [EMAIL PROTECTED] 630-235-2975 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Double Indexes
Mark: I have done a lot of indexing on I-Type items. I have had no problems with the indexing, it just works, solid. The thing you have to be careful of is indexing on translates. Once you think about it the problem becomes obvious. The index update mechanism is triggered by a write to the indexed file. If you have a translate, the indexed file does not get a write when the indexed value is updated in the other file so the index becomes stale when the indexed value is updated. It can work if the other file is a static code file and you remember to re-index when ever you update that file, but it is dangerous. I have never tried a correlative-based indexed field. I stay away from correlatives as much as possible. Tom Dodds [EMAIL PROTECTED] 630-235-2975 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Johnson Sent: Friday, October 21, 2005 4:03 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Double Indexes I putzed around with correlative-based indexed fields and got a lot of errmsg static. I'll try your suggestions. I'm trying to use the system instead of maintain manual xref files. This system is crammed full of 2M, 3M, 6Million record files and there is a lot of NUM1:NUM2 and NUM2:NUM1 (example) cross referencing done manually but none for DATE/CUST. I thought of creating a single Date/Cust xref file that could grab the eventual 800 average keys and let the secondary process filter it down to the desired subset but again, weaving it into this scrappy application (80% TPH) would guarantee not being perfect. Thanks. - Original Message - From: "Scott Ballinger" <[EMAIL PROTECTED]> To: Sent: Friday, October 21, 2005 11:42 AM Subject: RE: [U2] Double Indexes > [snip] > >Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 > can. > > Sure, both U2 and D3 support virtual index fields. > > U2 I type: > CUST:".":DATE > > One caveat I know of is that in D3 you must use A-correlatives to define > the index, e.g. > > Cust.date: > a5:".":6 > Or > A0(call cust.date) > > Create-index myfile cust.date > > Select myfile with cust.date eq "12345.]" -> all records with cust = > 12345 > Select myfile with cust.date eq "12345.13809" -> cust 12345 and date = > 10-21-05 > > However, the fact that the date in cust.date must be specified in > internal format reduces the utility of this approach, as does the fact > that indexed dates can't be selected using greater than or less than > type logic. > > Selecting by customer and/or date is pretty common; I usually end up > creating a separate xref index file with the key = cust dot date and a > mv list of myfile ids in attribute 1. Then you can > > select xref.file with cust = "12345" and with date ge "10-1-05" and le > "10-20-05" myfile.ids > > This is usually pretty fast in itself, and you could always index cust > and date in xref.file to make it faster under some circumstances. > > /Scott Ballinger > Pareto Corporation > Edmonds WA USA > 206 713 6006 > > P.s. I did not know that that UV10 supports multiple index queries - > very cool, and good to know! > --- > 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] Double Indexes
What about an index on an I-DESC? (Does universe do that?) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Johnson Sent: Friday, October 21, 2005 5:03 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Double Indexes I putzed around with correlative-based indexed fields and got a lot of errmsg static. I'll try your suggestions. I'm trying to use the system instead of maintain manual xref files. This system is crammed full of 2M, 3M, 6Million record files and there is a lot of NUM1:NUM2 and NUM2:NUM1 (example) cross referencing done manually but none for DATE/CUST. I thought of creating a single Date/Cust xref file that could grab the eventual 800 average keys and let the secondary process filter it down to the desired subset but again, weaving it into this scrappy application (80% TPH) would guarantee not being perfect. Thanks. - Original Message - From: "Scott Ballinger" <[EMAIL PROTECTED]> To: Sent: Friday, October 21, 2005 11:42 AM Subject: RE: [U2] Double Indexes > [snip] > >Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 > can. > > Sure, both U2 and D3 support virtual index fields. > > U2 I type: > CUST:".":DATE > > One caveat I know of is that in D3 you must use A-correlatives to define > the index, e.g. > > Cust.date: > a5:".":6 > Or > A0(call cust.date) > > Create-index myfile cust.date > > Select myfile with cust.date eq "12345.]" -> all records with cust = > 12345 > Select myfile with cust.date eq "12345.13809" -> cust 12345 and date = > 10-21-05 > > However, the fact that the date in cust.date must be specified in > internal format reduces the utility of this approach, as does the fact > that indexed dates can't be selected using greater than or less than > type logic. > > Selecting by customer and/or date is pretty common; I usually end up > creating a separate xref index file with the key = cust dot date and a > mv list of myfile ids in attribute 1. Then you can > > select xref.file with cust = "12345" and with date ge "10-1-05" and le > "10-20-05" myfile.ids > > This is usually pretty fast in itself, and you could always index cust > and date in xref.file to make it faster under some circumstances. > > /Scott Ballinger > Pareto Corporation > Edmonds WA USA > 206 713 6006 > > P.s. I did not know that that UV10 supports multiple index queries - > very cool, and good to know! > --- > 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] Double Indexes
I putzed around with correlative-based indexed fields and got a lot of errmsg static. I'll try your suggestions. I'm trying to use the system instead of maintain manual xref files. This system is crammed full of 2M, 3M, 6Million record files and there is a lot of NUM1:NUM2 and NUM2:NUM1 (example) cross referencing done manually but none for DATE/CUST. I thought of creating a single Date/Cust xref file that could grab the eventual 800 average keys and let the secondary process filter it down to the desired subset but again, weaving it into this scrappy application (80% TPH) would guarantee not being perfect. Thanks. - Original Message - From: "Scott Ballinger" <[EMAIL PROTECTED]> To: Sent: Friday, October 21, 2005 11:42 AM Subject: RE: [U2] Double Indexes > [snip] > >Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 > can. > > Sure, both U2 and D3 support virtual index fields. > > U2 I type: > CUST:".":DATE > > One caveat I know of is that in D3 you must use A-correlatives to define > the index, e.g. > > Cust.date: > a5:".":6 > Or > A0(call cust.date) > > Create-index myfile cust.date > > Select myfile with cust.date eq "12345.]" -> all records with cust = > 12345 > Select myfile with cust.date eq "12345.13809" -> cust 12345 and date = > 10-21-05 > > However, the fact that the date in cust.date must be specified in > internal format reduces the utility of this approach, as does the fact > that indexed dates can't be selected using greater than or less than > type logic. > > Selecting by customer and/or date is pretty common; I usually end up > creating a separate xref index file with the key = cust dot date and a > mv list of myfile ids in attribute 1. Then you can > > select xref.file with cust = "12345" and with date ge "10-1-05" and le > "10-20-05" myfile.ids > > This is usually pretty fast in itself, and you could always index cust > and date in xref.file to make it faster under some circumstances. > > /Scott Ballinger > Pareto Corporation > Edmonds WA USA > 206 713 6006 > > P.s. I did not know that that UV10 supports multiple index queries - > very cool, and good to know! > --- > 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] Double Indexes
I'm concluding that D3 doesn't support 2 simultaneous indexes. I deleted both indexes, timed a SELECT with 2 WITH's. Took about 85 seconds. Did a single SELECT WITH and then another SELECT WITH with the results and it went down to around 40 seconds. Re-indexed the DATE field and the first SELECT WITH was immediate but the second totalled to around 40 seconds again. Tried with 2 indexes and it went back to around 85 seconds. I optimized by deleting the CUST index and kept the DATE (it produced more items for one CUST than for one DATE, both were immediate) and then readv'd within a subroutine to get the specific CUST. Thus the DATE index remained. The users seemed happy with this approach. It ended up being around 15 seconds at best. Thanks. - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Friday, October 21, 2005 2:43 AM Subject: Re: [U2] Double Indexes >UV _should_ optimise 2-up secondary index selections (Glenn put a >really good whitepaper on the list about indexes once - I can't find >my copy now), use LIST/EXPLAIN to see if your indices are in use... My >2mil record test file uses 2 indices without a struggle returning a >result in about a second or so. >>LIST SUPPORD IF REC.TYPE = "O" AND IF ORD.DATE = "11/04/04" EXPLAIN >Single-variable predicates processed in index: >F6 = 'O' AND F3 = '13251' >Optimizing query block 0 >Driver source: SUPPORD >Access method: select list 0 with 249 ids >UniVerse/SQL: Press any key to continue or 'Q' to quit >> >-- >Or try MERGE.LIST to see if that improves performance: >SELECT FILE WITH DATE = "10.18" TO 1 >SELECT FILE WITH CUST = "12345" TO 2 >MERGE.LIST 1 INTERSECT 2 >[EMAIL PROTECTED] wrote: - >>To: >>From: "Mark Johnson" >>Sent by: [EMAIL PROTECTED] >>Date: 21/10/2005 13:50 >>Subject: [U2] Double Indexes >> >>I'm wondering if a UD or UV system behaves similarly to a D3 problem >>I see. >> >>File contains 6,000,000 records. First field is DATE and 2nd field is >>CUST. >>Both are INDEXed. >> >>SELECT FILE WITH DATE = "10.18" >>quickly returns 11,000 items >>CLEARSELECT >>SELECT FILE WITH CUST = "12345" >>quickly returns 180,000 items >>CLEARSELECT >>SELECT FILE WITH DATE = "10.18" AND CUST = "12345" >>takes forever as if the indexes aren't there. >> >>I don't know if the double test in the SELECT statement ignores the >>INDEX on >>either (or the first) or is encumbered by taking the 11,000 quick >>DATE items >>and then hashingly goes through them looking for CUST. >> >>If I try >>SELECT FILE WITH DATE = "10.18" >>then >>SELECT FILE WITH CUST = "12345" >>it still takes longer than I would expect. >> >>Is a double INDEX a strange animal. Does U2 support indexing virtual >>fields, >>ie DATE.CUST? I don't think D3 can. >> >>Thanks in advance. >>Mark Johnson >>--- >>u2-users mailing list >>u2-users@listserver.u2ug.org >>To unsubscribe please visit http://listserver.u2ug.org/ >> > >** >This email message and any files transmitted with it are confidential >and intended solely for the use of addressed recipient(s). If you have >received this email in error please notify the Spotless IS Support >Centre (+61 3 9269 7555) immediately, who will advise further action. >This footnote also confirms that this email message has been scanned >for the presence of computer related viruses. >** > --- > 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] Double Indexes
[snip] >Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 can. Sure, both U2 and D3 support virtual index fields. U2 I type: CUST:".":DATE One caveat I know of is that in D3 you must use A-correlatives to define the index, e.g. Cust.date: a5:".":6 Or A0(call cust.date) Create-index myfile cust.date Select myfile with cust.date eq "12345.]" -> all records with cust = 12345 Select myfile with cust.date eq "12345.13809" -> cust 12345 and date = 10-21-05 However, the fact that the date in cust.date must be specified in internal format reduces the utility of this approach, as does the fact that indexed dates can't be selected using greater than or less than type logic. Selecting by customer and/or date is pretty common; I usually end up creating a separate xref index file with the key = cust dot date and a mv list of myfile ids in attribute 1. Then you can select xref.file with cust = "12345" and with date ge "10-1-05" and le "10-20-05" myfile.ids This is usually pretty fast in itself, and you could always index cust and date in xref.file to make it faster under some circumstances. /Scott Ballinger Pareto Corporation Edmonds WA USA 206 713 6006 P.s. I did not know that that UV10 supports multiple index queries - very cool, and good to know! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Double Indexes
UV _should_ optimise 2-up secondary index selections (Glenn put a really good whitepaper on the list about indexes once - I can't find my copy now), use LIST/EXPLAIN to see if your indices are in use... My 2mil record test file uses 2 indices without a struggle returning a result in about a second or so. >LIST SUPPORD IF REC.TYPE = "O" AND IF ORD.DATE = "11/04/04" EXPLAIN Single-variable predicates processed in index: F6 = 'O' AND F3 = '13251' Optimizing query block 0 Driver source: SUPPORD Access method: select list 0 with 249 ids UniVerse/SQL: Press any key to continue or 'Q' to quit > -- Or try MERGE.LIST to see if that improves performance: SELECT FILE WITH DATE = "10.18" TO 1 SELECT FILE WITH CUST = "12345" TO 2 MERGE.LIST 1 INTERSECT 2 [EMAIL PROTECTED] wrote: - >To: >From: "Mark Johnson" >Sent by: [EMAIL PROTECTED] >Date: 21/10/2005 13:50 >Subject: [U2] Double Indexes > >I'm wondering if a UD or UV system behaves similarly to a D3 problem >I see. > >File contains 6,000,000 records. First field is DATE and 2nd field is >CUST. >Both are INDEXed. > >SELECT FILE WITH DATE = "10.18" >quickly returns 11,000 items >CLEARSELECT >SELECT FILE WITH CUST = "12345" >quickly returns 180,000 items >CLEARSELECT >SELECT FILE WITH DATE = "10.18" AND CUST = "12345" >takes forever as if the indexes aren't there. > >I don't know if the double test in the SELECT statement ignores the >INDEX on >either (or the first) or is encumbered by taking the 11,000 quick >DATE items >and then hashingly goes through them looking for CUST. > >If I try >SELECT FILE WITH DATE = "10.18" >then >SELECT FILE WITH CUST = "12345" >it still takes longer than I would expect. > >Is a double INDEX a strange animal. Does U2 support indexing virtual >fields, >ie DATE.CUST? I don't think D3 can. > >Thanks in advance. >Mark Johnson >--- >u2-users mailing list >u2-users@listserver.u2ug.org >To unsubscribe please visit http://listserver.u2ug.org/ > ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this email in error please notify the Spotless IS Support Centre (+61 3 9269 7555) immediately, who will advise further action. This footnote also confirms that this email message has been scanned for the presence of computer related viruses. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Double Indexes
On UV I believe there were some optimizations at rel 10.0 and it will use both indexes and take the intersection. Use EXPLAIN keyword to see what's going on. Before then, and maybe still on UD(??) it is as you described. A good workaround is: SELECT FILE WITH DATE = "10.18" TO 1 SELECT FILE WITH CUST = "12345" TO 2 MERGE.LIST 1 INTERSECT 2 > Does U2 support indexing > virtual fields, ie DATE.CUST? I don't think D3 can. Absolutely! Virtual fields & correlatives, too. The only caveat is do NOT index on any calculation where the answer depends on anything other than the exact record being updated. The archtypical bad example would be TRANS() & T-correlatives. The system allows it, but you are asking for corruption. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Double Indexes
Goo'day Mark, At 23:50 20/10/05 -0400, you wrote: I'm wondering if a UD or UV system behaves similarly to a D3 problem I see. File contains 6,000,000 records. First field is DATE and 2nd field is CUST. Both are INDEXed. SELECT FILE WITH DATE = "10.18" quickly returns 11,000 items CLEARSELECT SELECT FILE WITH CUST = "12345" quickly returns 180,000 items CLEARSELECT SELECT FILE WITH DATE = "10.18" AND CUST = "12345" takes forever as if the indexes aren't there. AFAIK, all MV variants only use the first iindex. If you try to use 2 indices, none are used. Must be documented. somewhere The old story applies: Use successive SELECTs to get the benefit of multiple indices I don't know if the double test in the SELECT statement ignores the INDEX on either (or the first) or is encumbered by taking the 11,000 quick DATE items and then hashingly goes through them looking for CUST. If I try SELECT FILE WITH DATE = "10.18" then SELECT FILE WITH CUST = "12345" it still takes longer than I would expect. Is a double INDEX a strange animal. Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 can. Why not? The index is built on a DICT item. Wasn't there a discussion somewhere in the past week over indexing null items and the suggestion was to non-null them by adding something else to the key? Thanks in advance. Mark Johnson --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.1.360 / Virus Database: 267.12.4/145 - Release Date: 20/10/05 Regards, Bruce Nichol Talon Computer Services ALBURYNSW 2640 Australia http://www.taloncs.com.au Tel: +61 (0)411149636 Fax: +61 (0)260232119 If it ain't broke, fix it till it is! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.360 / Virus Database: 267.12.4/145 - Release Date: 20/10/05 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] Double Indexes
I'm wondering if a UD or UV system behaves similarly to a D3 problem I see. File contains 6,000,000 records. First field is DATE and 2nd field is CUST. Both are INDEXed. SELECT FILE WITH DATE = "10.18" quickly returns 11,000 items CLEARSELECT SELECT FILE WITH CUST = "12345" quickly returns 180,000 items CLEARSELECT SELECT FILE WITH DATE = "10.18" AND CUST = "12345" takes forever as if the indexes aren't there. I don't know if the double test in the SELECT statement ignores the INDEX on either (or the first) or is encumbered by taking the 11,000 quick DATE items and then hashingly goes through them looking for CUST. If I try SELECT FILE WITH DATE = "10.18" then SELECT FILE WITH CUST = "12345" it still takes longer than I would expect. Is a double INDEX a strange animal. Does U2 support indexing virtual fields, ie DATE.CUST? I don't think D3 can. Thanks in advance. Mark Johnson --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
