> Firebird 1.5 on Windows, accessed via ODBC.
> Have a large table (some 50 million records) with the following structure:
>
> CREATE TABLE AUDIT_TRAIL
> (
> AUDIT_ID INTEGER NOT NULL,
> AUDIT_TIME DATE,
> USER_ID VARCHAR( 12) COLLATE NONE,
> TABLE_ID SMALLINT,
> ACTION SMALLINT,
> PRIMARY_KEY VARCHAR( 128) COLLATE NONE,
> DATA VARCHAR( 5000) COLLATE NONE,
> PATIENT_ID INTEGER
> );
>
> When I run this query via ODBC:
>
> select
> *
> from
> audit_trail
> where
> audit_id>= 46262765
>
> The query will hang and I need to kill the app.
>
> I have figured out that the problem is with one record with AUDIT_ID 46270901.
>
> This suspicion is based on the following:
>
> select
> *
> from
> audit_trail
> where
> (audit_id>= 46262765 and
> audit_id<= 46270900) or
> audit_id>= 46270902
>
> This will run fine.
>
> select
> *
> from
> audit_trail
> where
> (audit_id>= 46262765 and
> audit_id< 46270901) or
> audit_id> 46270901
>
> This will hang/crash.
>
> If I run these queries in Workbench it will run fine, but the record
> with AUDIT_ID 46270901 will not show.
> In this table all AUDIT_ID's are consecutive, except for this 46270901
> that is either missing or I think corrupt.
>
> Is this normal behaviour?
> Could this record be fixed?
> Thanks for any insight.

- Have you run gfix to check for corruption?
- What's happening when you fool the optimizer to not use the index on
audit_id (I guess audit_id is the primary key column) by adding +0 to
the field in the where clause.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/