I have a Btree index on a Dict. field "VIO_CODE"… been there for years.

I run a report that uses a "reduce" and "select" statement , selecting

on "VIO_CODE=791" and get 12 records listed.

I know there is over 60 of these records… so I goto the "Database Manager" and both update and rebuild the btree. I run the report again and get the same 12 records…. I go back to "Database Manager" and delete the btree index for "VIO_CODE" , run the report and get a list of all 76 records. I go back to "Database Manager", "ADD" a new btree index for this "VIO_CODE", rebuild and update it, run my report and NO records are listed…..

Should I just not use BTREE index.. I thought it would improve the speed of selects… but if its not bringing back all current records I probably need to delete ALL of my BTREE's ….yes??

Steve C.

Steve,

I run a report that uses a "reduce" and "select" statement , selecting on "VIO_CODE=791"

Is it Reduce or Select that's got the "VIO_CODE=791" criterion? Or do both have criteria? In case what's the other criterion?

(Reduce needs 'EQ' and can't use '=.)

Many people have had problems with Reduce and multiple criteria.

Can you do it without Reduce? With RList?

- Oystein -

Steve,

More likely there is something wrong with your database. Check for the usual issues:

1. Records with delimiters in the key ID.

2. Make sure the dictionary entry for indexed fields are set to be 1 to 2 characters in length longer than the actual longest data element being stored.

3. Make sure the key ID dictionary entries are set to be 1 to 2 characters in length longer than the actual longest data element being stored.

4. Try removing your indexes manually and then rebuild them.

There are more things that could be checked but these are some of the most common problems.

[email protected]

SRP Computer Solutions, Inc.

Donald and Oystein,

Oystein,

actually I do "swap" all of my "=, , ]..etc"

with the equivalent "EQ, NOT, GT" before executing the REDUCE.

Donald,

1. NONE

2. plenty, 9 characters DICT length, actual codes only 3 digits.

3. Plenty of lengths.

4. I will try to manually removing it.

Thanks for the suggestions.

Steve C.

Steve,

actually I do "swap" all of my "=, , ]..etc" with the equivalent "EQ, NOT, GT" before executing the REDUCE.

All… …so you do have a complex REDUCE. I don't trust them any more. I had a problem just recently with a REDUCE not more complicated than EQ-AND-EQ. Could have been my own fault of course.

Hope the manual index removal helps.

- Oystein -

Steve,

I have been following postings on BTREE indexing problems quite alot lately. If at all possible, you might want to try, as a test, to create a symbloic of your VIO_CODE, prefixing it with an alpha character. That way it now becomes a "non" number.

Just make the symbolic… @ANS=A':{VIO_CODE} then BTREE index that symbolic and see if you start getting accurate record counts.

If the symbolic works then I suggest that it has to do with your index values being numeric.

Or the possibility that your dictionary defines them as numeric (Integer, or whatever) and one of the values is non-numeric (including a leading/trailing space imbedded).

Don M.

I have also found weird things happen when selecting off of Btree's and non Btree's in the same rlist. Sometimes I would get all records in the file (and no error). I resolved this by splitting out the selects into Indexed then if @list.active Non-Indexed.

Also, something else to keep in mind OI select and AREV select are not equivelent especially if you combine AND and OR. OI does not make the same assumptions as AREV.

HTH,

John

Would a cross reference index on a field that is only a single digit

or 3 digit number speed up a select using "Reduce" ???

Thanks…

Steve C.

View this thread on the Works forum...