Optimising an open-ended range query

G'day all.

Apologies if this is off-topic. I'm new to Oracle and I'm using a problem which I have to solve as motivation to learn more about how Oracle ticks with respect to query optimisation.

The application in question is a system of applying updates to a large number of clients. When the clients log onto our system, we consult the table of updates to see which apply to them, and apply them. We're using Oracle 8.1.5.

The table looks something like this:         

	Table:  UPDATE
	Column Name                    Null?    Type
	------------------------------ -------- ----
	ID                             NOT NULL NUMBER(9)
	TIMESTAMP                      NOT NULL DATE
	RULE                                    VARCHAR2(4000)

When a client logs on, we find out when that client last updated, and then perform this query:

	select	rule
	from	update
	where	timestamp > to_date('something', 'something')
	order by timestamp asc

Now, we expect the number of updates to become quite large, and we also expect that in the general case, clients will update often. We have put an index on the timestamp column, and there are two cases we would like to optimise for:

1. It would be fantastic if the case where there are no updates to be performed (i.e. the timestamp we are supplying to test against is greater than all the timestamps in the table) is performed in constant time, or at most time proportional to that required to look up something in the index.
2. It is highly desirable for cases which don't fall into the previous case to run in time proportional to the number of elements to be retrieved plus time proportional to one index lookup. Obviously this is a generalisation of the previous case, but may be harder for Oracle to optimise.

For intellectual exercise, it would be interesting to know how to get as much performance as possible on these cases, and thus gain more insight in the inner workings of Oracle.

Going through Dejanews on this newsgroup, I get impression that we should be bypassing the cost-based optimiser because its behaviour is somewhat less than deterministic. Based on my not-very-wide reading, I have a few thoughts and I was hoping someone could comment on whether or not I'm on the right track.

First off, it seems to me that we need an index scan, so adding an INDEX hint would be a good start. However, would it be more efficient if we used INDEX_DESC, since it's the latter parts of the index that we're trying to find?

Secondly, would creating a partitioned table (say, partitioned on some small part of the timestamp, such as the year/month combination) help in any way? How would it affect the performance of simple queries (e.g. the case where the set returned is the null set)? What about queries where entries must be retrieved from several partitions? How about if we used this in combination with INDEX/INDEX_DESC? What about clustered tables/indexes?

Lastly, is bypassing the cost-based optimiser a good idea in the first place? Or doesn't it make any difference since the query doesn't use any joins? If I should be using it, what should I analyse?

Thanks in advance etc.

Cheers,
Andrew Bromage Received on Sun Aug 08 1999 - 22:39:22 CDT