SELECT is not sophisticated enough for the type of complex selection you are doing. You are best off breaking this up into 2 processes - the first process creates a select-list from the index (consider using KEY-SELECT for this) and the second process uses the list created by the first process to narrow down the result. This should perform adequately enough.
Dan On Wed, Jan 27, 2010 at 2:02 AM, [Xze] <[email protected]> wrote: > Dear Pawel, > I have installed the latest jbase version i could get, 5.0.22, and this > issue is still there as well! > As for files, we are using INDEX only on FBNK.ACCOUNT and > FBNK.LD.LOANS.AND.DEPOSITS: > > -->LIST-INDEX FBNK.ACCOUNT > INDEX definitions for file FBNK.ACCOUNT at 08:44:40 27 JAN > 2010 PAGE 1 > > INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX > DEFINITION................... > > ACCOUNT.OF en_US AR BY-AR 11 > FICER > CATEGORY en_US AR BY-AR 2 > CURRENCY en_US AL BY-AL 8 > CUSTOMER en_US AR BY-AR 1 > SYN.CODE en_US AR BY-AR > ITYPE(\LOCAL.REF<1,7>\) > > LIST-INDEX FBNK.LD.LOANS.AND.DEPOSITS > INDEX definitions for file FBNK.LD.LOANS.AND.DEPOSITS at 08:44:54 27 JAN > 2010 PAGE 1 > > INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX > DEFINITION................... > > CATEGORY en_US AR BY-AR 11 > CUSTOMER.I en_US AR BY-AR 1 > D > > 2 Records Listed > > Same thing for both files - inaccurate select results > > Also, we do not use the jlogdup > > Regarding [offtopic], we came to the same conclusion as you did, but if we > do not use INDEX at all, then the performance is totally bad. > > > > > > >> >> Hi, >> >> There were few problems in jBASE 4.1 release. Most of them are already >> fixed I belive. Not sure about 5.0 release, but I can imagine that early >> releases could suffer from similar problems. (shared code?) >> >> One of the problems discovered by us happened on files were more than 2 >> indexes were defined. Only second, third, ... index could become invalid in >> some scenario - never 1st index. Unfortunately I can not recall exact >> scenario at home, but we were able to recreate problem. >> Did you try to upgrade jBASE? Does it happen only on files that have more >> than 1 index? >> >> There was also some other problem reported by us. It took a long time to >> find out that polish characters in indexed data were causing problems (we >> are running regular, non UTF8 jBASE mode). Are your indexed data having some >> specific characters? Are you running jBASE in UTF mode? >> >> By the way: indexes get out of sync [by default?] on hot-standby server >> where jlogdup is running and replicating database updates. Again not sure >> but I guess there is some option to force index updates, is not it? I would >> be very gratefull if somebody could give some light on that subject. >> >> [Offtopic] Why do you use secondary indexes? Check reasons of using them >> carefully. We came to the conclusion that they affect negtively our >> performance during online and most of them was rarelly needed. We are still >> using indexes, but we have lowered their number. >> >> Kind regards >> Pawel >> > > ============ > > No, Jim, there was nothing wiered like kill -9 or sudden crash done to the > system. Moreover, re-creating the INDEX did not fix the issue as i already > wrote: > > -->LIST-INDEX FBNK.LD.LOANS.AND.DEPOSITS > INDEX definitions for file FBNK.LD.LOANS.AND.DEPOSITS at 08:33:04 27 JAN > 2010 PAGE 1 > > INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX > DEFINITION................... > > CATEGORY en_US AR BY-AR 11 > CUSTOMER.I en_US AR BY-AR 1 > D > > 2 Records Listed > > ~ -->DELETE-INDEX -a FBNK.LD.LOANS.AND.DEPOSITS > Deleting index records for definition 'CUSTOMER.ID' in file > '../bnk.data/ld/FBNK.LD.L001' ... > Deleting index records for definition 'CATEGORY' in file > '../bnk.data/ld/FBNK.LD.L001' ... > ~ -->CREATE-INDEX -vd FBNK.LD.LOANS.AND.DEPOSITS CUSTOMER.ID BY > CUSTOMER.ID > Notice: Command converted to 'CREATE-INDEX -vd FBNK.LD.LOANS.AND.DEPOSITS > CUSTOMER.ID BY-AR 1' > Index definition 'CUSTOMER.ID' created successfully > Deleting index records for definition 'CUSTOMER.ID' in file > '../bnk.data/ld/FBNK.LD.L001' ... > Building index records in file '../bnk.data/ld/FBNK.LD.L001' ... > .................................................. > ................ > ~ -->CREATE-INDEX -vd FBNK.LD.LOANS.AND.DEPOSITS CATEGORY BY CATEGORY > Notice: Command converted to 'CREATE-INDEX -vd FBNK.LD.LOANS.AND.DEPOSITS > CATEGORY BY-AR 11' > Index definition 'CATEGORY' created successfully > Deleting index records for definition 'CATEGORY' in file > '../bnk.data/ld/FBNK.LD.L001' ... > Building index records in file '../bnk.data/ld/FBNK.LD.L001' ... > .................................. > ................................ > ~ --> > ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 > > 1 Records selected > >>CT > ** Error [ 200 ] ** > What is the file name ?? > ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 AND > ((CATEGORY >= 21050 AND CATEGORY <=210 > 59) OR CATEGORY EQ 21095 OR CATEGORY EQ 21096) BY CATEGORY > > 210 Records selected > >>CT > ** Error [ 200 ] ** > What is the file name ?? > ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 AND > ((CATEGORY >= 21050 AND CATEGORY <=210 > 59) OR CATEGORY EQ 21095 OR CATEGORY EQ 21096) BY CATEGORY NO.INDEX > > 1 Records selected > >>CT > ** Error [ 200 ] ** > What is the file name ?? > ~ --> > > > [ Xze ] > > > On Tue, Jan 26, 2010 at 22:08, Jim Idle <[email protected]> wrote: >> >> The index should never be out of date unless you did something wired to >> the file, or the file is corrupt, or the index is corrupt. Did you use kill >> -9 on your system, or have a machine crash or something? >> >> >> >> You should probably use DELETE-INDEX to remove all indexes on the file, >> then re-create the indexes using the script you wrote to create them >> originally. >> >> >> >> JIm >> >> >> >> From: [email protected] [mailto:[email protected]] On Behalf Of >> [Xze] >> Sent: Monday, January 25, 2010 2:10 AM >> To: [email protected] >> Subject: Inaccurate SELECT results on INDEX fields >> >> >> >> Dear all, >> >> We are facing the issue described below: >> >> When performing SELECT on INDEX fields the results are inaccurate >> >> ~ -->LIST-INDEX FBNK.LD.LOANS.AND.DEPOSITS >> INDEX definitions for file FBNK.LD.LOANS.AND.DEPOSITS at 11:01:20 25 JAN >> 2010 PAGE 1 >> INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX >> DEFINITION................... >> >> CATEGORY en_US AR BY-AR 11 >> CUSTOMER.I en_US AR BY-AR 1 >> D >> >> 2 Records Listed >> >> =============== >> >> ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 >> 1 Records selected >> >> ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 AND >> ((CATEGORY >= 21050 AND CATEGORY <=210 >> 59) OR CATEGORY EQ 21095 OR CATEGORY EQ 21096) BY CATEGORY >> >> 210 Records selected >> ~ -->SELECT FBNK.LD.LOANS.AND.DEPOSITS WITH CUSTOMER.ID EQ 369403 AND >> ((CATEGORY >= 21050 AND CATEGORY <=210 >> 59) OR CATEGORY EQ 21095 OR CATEGORY EQ 21096) BY CATEGORY NO.INDEX >> >> 1 Records selected >> >> ======== >> >> File 'FBNK.LD.LOANS.AND.DEPOSITS' is not a distributed file >> >> Rebuilding the INDEX did not fix the issue >> >> ======== >> >> ~ -->jdiag >> jdiag - jBASE diagnostic '$Revision: 1.15 $' >> >> System Information >> ================== >> >> System : AIX jbsec 3.5 00CED1BC4C00 >> OS Release : 5.3.9.0 >> UNIX User : cobusr (uid 209, euid 209) >> Tty name : /dev/pts/23 >> Time : Mon Jan 25 11:04:03 2010 >> >> Environment >> =========== >> >> JBCPORTNO : Not Set >> JBCRELEASEDIR : '/opt/jbase5' >> JBCGLOBALDIR : '/opt/jbase5' >> WARNING: JBCDATADIR is not set, Default '/opt/jbase5/jbase_data' >> WARNING: JBCDATADIR is subdirectory of JBCGLOBALDIR >> HOME : '/cob/cob/bnk.run' >> JEDIFILEPATH : '/cob/cob/bnk.run' >> JEDIFILENAME_MD : 'VOC' >> JEDIFILENAME_SYSTEM : '/opt/jbase5/src/SYSTEM' >> SYSTEM File is (DICT) : '/opt/jbase5/src/SYSTEM]D' >> RELEASE Information : Major 5.0 , Minor 20 , Patch 0364 (Change >> 85159) >> Spooler dir (JBCSPOOLERDIR) : '/var/spool/jbase' >> JBCEMULATE : 'prime' >> bnk.run/globuspatchbin', error 2 >> Object path (JBCOBJECTLIST) : >> '/cob/cob/bnk.run/globuspatchlib:/cob/cob/bnk.run/lib:/cob/cob/bnk.run/globuslib:/cob/cob/bnk.run/fixlib' >> WARNING: Cannot access Object path '/cob/cob/bnk.run/globuspatchlib', >> error 2 >> WARNING: Cannot access Object path '/cob/cob/bnk.run/fixlib', error 2 >> jBASE Compiler Run-time : '/opt/jbase5/config/system.properties' >> Program dir (JBCDEV_BIN) : '/cob/cob/bnk.run/bin' >> Subroutine dir (JBCDEV_LIB) : '/cob/cob/bnk.run/lib' >> Max open files : 65534 >> >> >> Can anybody explain why is this happening? >> >> Regards, >> [ Xze ] >> >> >> >> -- >> >> Please read the posting guidelines at: >> http://groups.google.com/group/jBASE/web/Posting%20Guidelines >> >> IMPORTANT: Type T24: at the start of the subject line for questions >> specific to Globus/T24 >> >> To post, send email to [email protected] >> To unsubscribe, send email to [email protected] >> For more options, visit this group at >> http://groups.google.com/group/jBASE?hl=en >> >> -- >> Please read the posting guidelines at: >> http://groups.google.com/group/jBASE/web/Posting%20Guidelines >> >> IMPORTANT: Type T24: at the start of the subject line for questions >> specific to Globus/T24 >> >> To post, send email to [email protected] >> To unsubscribe, send email to [email protected] >> For more options, visit this group at >> http://groups.google.com/group/jBASE?hl=en > > -- > Please read the posting guidelines at: > http://groups.google.com/group/jBASE/web/Posting%20Guidelines > > IMPORTANT: Type T24: at the start of the subject line for questions specific > to Globus/T24 > > To post, send email to [email protected] > To unsubscribe, send email to [email protected] > For more options, visit this group at > http://groups.google.com/group/jBASE?hl=en > -- Please read the posting guidelines at: http://groups.google.com/group/jBASE/web/Posting%20Guidelines IMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24 To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en
