More index woes

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

More index woes

Post by barry.marcus »

This may or may not be related to the problem, currently unresolved, discussed in the thread named "Problem with index or data or both - mystery", but here goes...

I am running the following query, and (correctly) get no hits:

SELECT PATN_WKU, PATN_ISD, PATN_TTL FROM PATN WHERE (PATN_TTL LIKE '+battery @0 supply* w/2') AND (PATN_ISD = '20081209');

However, when I change to a *range* for PATN_ISD, as in the following, I do (incorrectly) get a hit:

SELECT PATN_WKU, PATN_ISD, PATN_TTL FROM PATN WHERE (PATN_TTL LIKE '+battery @0 supply* w/2') AND (PATN_ISD > '20081208') AND (PATN_ISD < '20081210');

This is what is returned:

PATN_WKU PATN_ISD PATN_TTL
------------+------------+------------+
7463008 20081209
Power supply apparatus with transistor control for constant current between series-connected battery blocks

As far as I can tell, that PATN_TTL value does not satisfy the MM criteria although the value of PATN_ISD *is* in the range specified in the criteria. As a sanity check, I issued the following mminfo query which, correctly, does NOT return any hits:

SELECT mminfo('+battery @0 power* w/2',PATN_TTL,0,0,3) FROM PATN WHERE PATN_WKU='7463008';

In light of the problem described in the "Problem with index or data or both - mystery" thread, I'm even more convinced that there is a problem with index on PATN_TTL. I've rebuilt it twice using the following statements:

DROP INDEX PATN_PATN_TTL_MI;
set delexp=0;
set addexp = '\alnum{1,99}';
set keepnoise=1;
set indexmeter=1;
create METAMORPH INVERTED index PATN_PATN_TTL_MI on PATN(PATN_TTL,PATN_ISD);

Hopefully I'm just misunderstanding how this is supposed to work. My worry is that there are errors in the index, some I'm not yet aware of.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

More index woes

Post by John »

Is PATN_ISD a string? How is it declared in the table? Normally strings wouldn't be a compound field in a metamorph index.

What if you parenthize the query:

SELECT PATN_WKU, PATN_ISD, PATN_TTL FROM PATN WHERE (PATN_TTL LIKE '+battery @0 supply* w/2') AND ((PATN_ISD > '20081208') AND (PATN_ISD < '20081210'));
John Turnbull
Thunderstone Software
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

More index woes

Post by Kai »

Following up via tech support to get more details.
Post Reply