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…

…you can take advantage of indexes to return the first rows instantly.  This does not mean you can skip the ORDER BY clause, though, you cannot rely on an index, a GROUP BY statement or another implicit order to return the records in sorted order!

This is probably not new to most people. But what happens if the ORDER BY clause is on a column or group of columns that are NULLable (that is: do not have the NOT NULL constraint defined on them)? Then Oracle might not use the index.  See the following query I encountered today:

SELECT t1.conflict_id,
  FROM s_act_prod_appl t1
ORDER BY t1.priority_num;

This is a dreadfull query executed by Siebel for some reason. It is just opening a business component (BC) on S_ACT_PROD_APPL and this applies the Sort specification defined on it as well. And indeed we checked: the Sort specification is “Priority”. This however makes this simple opening of the Business Component a performance problem: on average this takes about 100 seconds!

  • One may argue that this sort specification may be removed. We need to check if it is really necessary
  • It is not that the application will indeed retrieve the 14 million rows returned from it, it is just opening the BC

So I thought: I will create an index on priority_num and Oracle will use this index to return the first records as soon as possible. But it did not…

I even hinted the query and still Oracle would not take it. This is one of those moments you think you understand something and get disappointed in the end. But wait, I attended Tom Kyte’s seminar yesterday and the day before and suddenly remembered: Oracle cannot take this index to drive the query: priority_num appeared to be a NULLable column. And entirely NULL index key values are never indexed (partially NULL values in a compound index are, but an entire index key containing NULL values are not). This I knew, but I did not link it to the index not being used in cases like this. Tom luckily also showed how a FUNCTION BASED INDEX (FBI) can solve this problem. With FBI’s you can take advantage of the fact that NULL values are not indexed. The following index does the trick:

CREATE INDEX idx ON s_act_prod_appl (priority_num,0);

Query plan:

SELECT STATEMENT FIRST_ROWS Cost: 1 Bytes: 91 Cardinality: 1
  2 TABLE ACCESS BY INDEX ROWID TABLE S_ACT_PROD_APPL Cost: 1 Bytes: 1.305.474.807 Cardinality: 14.345.877
    1 INDEX FULL SCAN INDEX IDX Cost: 1 Cardinality: 1

In other words: create an index on priority_num and the fixed value “0“. Since Oracle now has to index ALL records in the table, even the ones that have the NULL value for priority_num, the index will contain all records in the table and the query can be satisfied by scanning the index and fetching the first rows immediately! A superb trick. Thanks Tom Kyte!

Some remarks:

  • You should check whether the absence of the NOT NULL constraint in this case is correct or incorrect. If the column indeed should not contain NULL values, it is best to create the NOT NULL constraint (If Siebel would allow it): then the index does not need to use the trick of the fixed value
  • In general: constraints help the optimizer a lot! This is not a well known fact, but it is. The more you ‘tell’ the optimizer about the data integrity rules in the database using constraints and (UNIQUE) indexes, the better it works. This example shows that even a simple NOT NULL constraint makes a huge difference!
  • But if you do not have the choice and cannot create constraints or the constraint does not apply, the FBI trick can be very valuable

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

  1. Hi Toine,

    just want to add one small note:

    CREATE INDEX idx ON s_act_prod_appl (priority_num,0);

    yes this is a solution for the problem.
    But it is better to make the index :

    CREATE INDEX idx ON s_act_prod_appl (priority_num,row_id);

    this for the following reasons:
    – function based indexes aren’t supported by siebel. (the normal query is)
    – you can’t create the function based indexes in the siebel repos (so when they do a DDL-sync, the index will be gone, so you have additional administration)
    – there is a “small” change that you have a query somewhere that does: “select rowid from s_act_prod_appl where priority_num=…”



    • Hi Kurt, great addition to the post. That is why I miss you at this customer: to check some thoughts. Your comments seem entirely valid. If I remember clearly: the index indeed had to be included in our ‘post DDLsync’ script because the repository could not hold it. And indeed, row_id itself is NOT NULL also, so every record would be in the index. And it would be beneficial for any query that also uses row_id as you say.

      Thanks, I am going back to the drawing board and check this one. Regards from the Tanel Poder seminar room in Utrecht (a live comment). Great stuff so far!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s