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, t1.last_upd, t1.created, t1.last_upd_by, t1.created_by, t1.modification_num, t1.row_id, t1.pr_iss_id, t1.activity_id, t1.prdint_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);
|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!
- 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