tsql -V "select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"
Yields the following:
Texis Version 05.00.1090358162(20040720) Copyright (c) 1988-2004 Thunderstone EPI
200 Setting pred PATN_WKU= 7301016 on table PATN in the function settablepred
PATN_WKU
------------+
The following command:
tsql -V "select PATN_WKU FROM PATN WHERE PATN_TTL LIKE 'Human transferase members and uses thereof';"
Yields the following:
Texis Version 05.00.1090358162(20040720) Copyright (c) 1988-2004 Thunderstone EPI
200 Setting pred PATN_TTL LIKE 'Human transferase members and uses thereof' on table PATN in the function settablepred
200 Opening index /opt/data1/texisdb/camp/PATN_PATN_TTL_MI in the function ixfmmindex
PATN_WKU
------------+
7301016
Does this mean that no index is being used at all by the first query?
tsql -V "set bubble=0; select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"
Yields this:
root@Inspherion2> tsql -V "set bubble=0; select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"
Texis Version 05.00.1090358162(20040720) Copyright (c) 1988-2004 Thunderstone EPI
200 Setting pred PATN_WKU= 7301016 on table PATN in the function settablepred
200 Opening index /opt/data1/texisdb/camp/PATN_WKU_ISD_I in the function ixbtindex
200 Expect to read 0% of the index in the function ixbtindex
PATN_WKU
------------+
I've been so fixated on the index PATN_WKU_I that I didn't even notice that the index PATN_WKU_ISD_I was there. The fields of index PATN_WKU_ISD_I are PATN_WKU PATN_ISD. That makes no sense to me, since values of PATN_WKU are unique. (PATN_WKU is essentially a key of the table, although the actual PK is id, which is a counter field.) PATN_ISD is a string representation of a date, in the format yyymmdd. I'm asking myself What's the point of a secondary sort on a unique value?!?
Does this output indicate a problem with this index? In any case, it makes sense to me to drop the index.
Here's a general question... If there are two indexes on the same field, what determines which one Texis uses?
I was assuming that PATN_WKU_I was being used, but PATN_WKU_ISD_I was actually used instead. These are essentially the same (and the later may be flawed.)
Just curious to know before I get rid of the bogus index.
If there are two indexes that can answer the query equally well, i.e. have the column first which one it will use is indeterminate. It sounds like that index may be the flawed one, and could be dropped.
I dropped the index on PATN_WKU + PATN_ISD and, low and behold, the record now *is* returned when PATN_WKU criteria is used (i.e., PATN_WKU='7301016') However, it turns out that the record is somehow flawed (which may have been the reason the now-deleted index was corrupted - don't know). Data appears to be in the wrong fields, a lot of what's there looks like garbage, etc., etc. I want to delete it and reload with clean data. So I tried to delete it (from the TSQL> prompt) using
DELETE FROM PATN WHERE PATN_WKU='7301016'
but this generated an ABEND and a message to the effect that it can't update index PATN_id_I (or something to that effect.) (The column id, BTW, is type counter, and the index PATN_id_I is a regular index on that.)
So, I dropped index PATN_id_I and tried again without success. The record is still there, or at least it's still in the PATN_WKU index and is returned when PATN_WKU='7301016' criteria is used. Then I rebuilt index PATN_id_I and tried again. Now when I issue the delete statement from the TSQL> prompt I get immediately kicked out of tsql back to the linux command prompt.
This one record is turning into a thorn in my side... Would love to get rid of it so I can reload it correctly.