Here’s a good reason to ensure that you always output (and read) the predicate information of the execution plan at the Oracle level and not just at the SAP level.
Inside the ST01 SQL Trace screen (in R/3 4.7 anyway), it doesn’t show how the predicates are accessed/filtered.
But in the Oracle view of the execution plan (I used autotrace, or DBMS_XPLAN) you can clearly see the “Predicate Information” section:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 281 | 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| BSAS | 1 | 281 | 10 (0)|
|* 2 | INDEX RANGE SCAN | BSAS~Z2 | 1 | | 9 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MANDT"=:A0 AND "BUKRS"=:A1 AND "HKONT"=:A2 AND
"XBLNR"=:A4 AND "BUDAT"<=:A3)
filter("XBLNR"=:A4)
This allowed me to see that the index BSAS~Z2 was not being accessed correctly due to a “feature” in the Oracle 10g CBO (see note: 176754, question #5) which SAP has documented as:
“If you specify indexed columns with LIKE, >, <, >=, <=, or BETWEEN, the system can no longer use the columns that follow to restrict the index range scan.”
After creating a new index BSAS~Z3 which contains exactly the same columns, but changing the order of the last two (BUDAT and XBLNR), I was able to get the execution plan to look like this:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 281 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| BSAS | 1 | 281 | 1 (0)|
|* 2 | INDEX RANGE SCAN | BSAS~Z3 | 1 | | 1 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MANDT"=:A0 AND "BUKRS"=:A1 AND "HKONT"=:A2 AND
"XBLNR"=:A4 AND "BUDAT"<=:A3)
What’s the difference I hear you ask. Just look at the COST column.
In a table with 100 million records, the difference is about 20 seconds.
Filter predicates are not as efficient as access predicates (just ask Tom).
This example was fairly specific to transaction FBL3n performance, since I noted that whenever this transaction was used to query open items, it always adds a predicate of “BUDAT < 99991231” before any of the dynamic selection predicates. Bad SAP!
