mike@bohra.cpg.oz (Mike Crooks) (01/15/90)
I have a problem accessing an INFORMIX/SQL database which I hope you Informix
guru's out there might be able to shed some light on...
The situation:
A large (4 Mb) table which is used continuously on-line is subject to a
user-driven sequential update. For the update to proceed, the records in
the table must be read in a specified sequence (the primary index sequence),
locking only the currently active record. Locks are released when the
user proceeds to the next record. The primary key is composed of two
non-contiguous fields.
The problem:
How do I issue an SQL query which retrieves the records in the primary
index order without INFORMIX sorting the entire table !! User response
is too slow with a sort - not to mention the megabytes of temporary disk
consumption.
The question:
I suppose what I'm really asking is a way to encourage the INFORMIX/SQL
optimizer to use the pre-built indexes - Britton Lee's Intelligent
Database Machine (IDM) actually had a language extension to support this.
In addition, it would be useful to learn of some of the rules used by
INFORMIX/SQL for retrievals - this plays a major role for database
optimization. The ORACLE manuals actually include a chapter on this
topic.
If I use the query:
select * from table order by keypart1, keypart2
INFORMIX/SQL's optimizer fails to use the primary index to retrieve the
data, preferring to sort the table. This means a second query must be used
to establish the record lock.
This same query works fine for ORACLE - it won't do a sort when the indexes
can be used to satisfy the retrieval sequence.
If I use the query:
select * from table where keypart1 >= "" and keypart2 >= ""
this works IF there is only ONE keypart - ie for this type of query the
optimizer appears to only scan the index for single part keys.
The use of a clustered index is not considered a solution since it cannot
guarantee the retrieval order (in a multi-update environment) and it does
not address the wider view of the problem.
Any ideas ?
Thanks in advance to all those who take the time to respond.
Regards,
--
Mike Crooks (mike@bohra.cpg.oz)