Take advantage of a Function based index in First_rows mode with an ORDER BY on NULLable columns

One of the things I encounter a lot in a Siebel application I support, is the fact that there are lots of queries with ORDER BY’s that take too long to return the first screen full of records OR are executed just to open a Business Component without any filtering. A typical OLTP application mostly wants the first screen full of records to return as fast as possible, so a FIRST_ROWS optimization is the default way. In Siebel, this FIRST_ROWS optimization mode is hard-coded in the DLL, so no way to even circumvent it.

However especially with the default ORDER BY defined on business components (Sort specification) or the ability for users to define their own ORDER BY, you run into situations where this ORDER BY makes the first records to return too slowly, since the whole result set will mostly be materialized, sorted and then the first rows can be returned, but…

Hello everyone

This is another blog on Oracle. For some time I wanted an easy way of sharing my knowledge, experience and findings with everyone interested. I write articles sometimes (in Dutch Oracle magazines), but those have a somewhat large interval. A blog enables me to post potentially interesting things immediately and get feedback as well. I can be right, I can be wrong and in either case I would love to know. I hope some or many people will enjoy it!