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 »

This is what I get:

$
id: 486e530f0$
{+ PATN_WKU+ }: {7301016}$
$
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 »

Odd. Does it find it this way:
select PATN_WKU FROM PATN WHERE PATN_WKU like '7301016';
or this way:
select PATN_WKU FROM PATN WHERE PATN_WKU like '/7301016';

What kind of index do you have on PATN_WKU?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

We have a regular index on PATN_WKU, created with this command:

CREATE INDEX PATN_WKU_I on PATN(PATN_WKU);

I've tried to run the first of those two queries before, but I was never patient enough to wait for the return :-) (I guess a query like that uses either a metamorph index or does a table scan, and there are about three million rows in the table.) I'm running the second query now (it's been running for about 10 minutes), but it has not returned yet.
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 »

Yes, those are linear queries in the absence of a metamorph index on the field.

When you recreated the PATN_WRU_I index did you drop it first?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

Yes, I dropped the index first.
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 »

If the database is not changing you can try dbverify

dbverify -v -i PATN_WRU_I

If you don't have dbverify you can create it as a symlink to monitor

cd INSTALLDIR/bin
ln -s monitor dbverify
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

Please explain briefly how to use dbverify. I don't know what to do with it or what I expect to see from it.

Here's what I did: I ran it, and it returned

200 Looking at /opt/data1/texisdb/camp/PATN_WKU_I on PATN

and then basically showed nothing. From another command prompt I ran the other queries that I've been trying all day, and the dbverify window still showed nothing.

Also, is CTRL+C a proper way to exit from this?
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 »

Not much to it. Use dbverify -h for options help.
I gave the exact syntax you need above. You just have to wait for it to verify the files. It will report problems as it finds them and a summary at the end.
Example:

% dbverify -v -i xoptname
200 Looking at /usr/local/morph3/texis/testdb/xoptname on options
200 Counted 712 rows in options
200 Counted 712 rows in /usr/local/morph3/texis/testdb/xoptname
200 There were 0 differences
%
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Problem with index or data or both - mystery

Post by barry.marcus »

I ran dbverify on the index for PATN_WKU. Here is the output:

200 Looking at /opt/data1/texisdb/camp/PATN_WKU_I on PATN
200 Counted 2965710 rows in PATN
200 Counted 2965710 rows in /opt/data1/texisdb/camp/PATN_WKU_I
200 There were 0 differences

I also ran it on the metamorph index for PATN_TTL. Here is that output:

200 Looking at /opt/data1/texisdb/camp/PATN_PATN_TTL_MI on PATN
200 Reading new list from index PATN_PATN_TTL_MI
200 Reading delete list
200 Reading recids from table PATN
200 Comparing token recids to table

Is it significant that the verification of the PATN_WKU index explicitly showed 0 differences, while the verification of the PATN_TTL index did not?
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. The check on metamorph indexes does not explicitly report 0 differences.

Is PATN_WKU_I the only index on WKU? If you do the query with -V option to tsql it should show the query it will do and the index it will use, e.g.

tsql -V "select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"
John Turnbull
Thunderstone Software
Post Reply