> 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/
