Problem with index or data or both - mystery

barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

The following command:

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?
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Problem with index or data or both - mystery

Post by John »

No, it's an older version of Texis that doesn't report indexes in all cases. Try:

tsql -V "set bubble=0; select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

What the...?!?

The command:

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.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

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.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Problem with index or data or both - mystery

Post by John »

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.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

The saga continues...

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.

Thanks for your ongoing help.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Problem with index or data or both - mystery

Post by mark »

You may need to run kdbfchk on the table to fix corruption. Then drop and rebuild the indices.

Has the machine ever lost power or otherwise been shutdown suddenly or a texis process killed while possibly in the middle of writing to the table?
Post Reply