Question to SAP Support "Why is partition pruning ...

lbreddemann · ‎04-15-2011

Welcome to the problem

As usual understanding the meaning and the scope of the question is key for the answer to this question, recently filed by a customer.

So, what is the customer complaining about? What does he mean with "partition pruning"?

It's a feature of the query optimizer to figure out, which partitions of a table or an index it can be safely left aside, when running the query.
It reduces work and thereby makes queries faster.

Easy example for this

Think of a table containing sales information together with the date of the recorded sales transactions.
Now this table is partitioned by the month (say, column "0CALMONTH") and you want to see the sales data of the the first quarter of 2011.
You would probably come up with a SQL statement like this:

SELECT 0CALMONTH as MONTH, SUM(0AMOUNT) as SUM_AMOUNT
FROM SALES
WHERE 0CALMONTH is between 201101 and 201103
GROUP BY 0CALMONTH;

With the information given, the optimizer knows that all relevant data can be found in the partitions that contain the data for 201101, 201102 and 201103 - all other partitions can be ignored.

Nice, isn't it?

So, how can we know that such stuff is happening in our database?
The explain plan contains this information:

SQL> explain plan for
  2  SELECT "0CALMONTH" as MONTH, SUM("0AMOUNT") as SUM_AMOUNT
  3  FROM SALES
  4  WHERE   "0CALMONTH"  between 201101 and 201103
  5  GROUP BY "0CALMONTH";
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3524420984
--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    11 |     4  (25)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    11 |     4  (25)| 00:00:01 |     8 |    10 |
|   2 |   HASH GROUP BY          |       |     1 |    11 |     4  (25)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL     | SALES |  3056 | 33616 |     3   (0)| 00:00:01 |     8 |    10 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("0CALMONTH"<=201103)

15 rows selected.

The Pstart/Pstop columns tell us precisely, which partitions are used for the single steps - and we see, that only 3 of them (8,9,10) are actually used.

Back to the customer

Ok, so my customer was telling me, that exactly THIS was NOT happening on his system.
Since we use partitioning in BW on Oracle for a long time very successfully, I asked for a reproduction example, which the customer swiftly provided.
Based on what the customer told me I should run a BW query in RSRT and activate the checkbox to display the execution plans for the SQL statements (interestingly this one is called "Display Runschedule" in BW 7.0 onwards...).

A first mistake (or "to know your checkboxes and save everybody's time")

When I first ran the query, this was the SQL against the E-fact table which I assumed to be the problematic one (I removed some uninteresting stuff to make reading a bit easier):

====> QUERY 1 

SELECT "DT"."SID_0FISCVARNT"      AS "S____034",
       "DT"."SID_0CALDAY"         AS "S____003",
       "D1"."SID_0PLANT"          AS "S____066",
       "X1"."S__0MATL_GROUP"      AS "S____170",
       "DU"."SID_0LOC_CURRCY"     AS "S____239",
       SUM ("F"."ISSVS_VAL")      AS "Z____565",
       SUM ("F"."RECVS_VAL")      AS "Z____571",
       COUNT(*)                   AS "Z____149",
   [ ... ]
FROM   "/BIC/EZICSTKBAL" "F"                     // <<-- our partitioned E-fact table
   [ ... ]
WHERE  (( ((     "DT"."SID_0CALDAY" = 20100101
             AND "DT"."SID_0CALMONTH" = 201001
             AND "F"."SID_0CALMONTH" = 201001 )) // <<-- a selection criteria on the partitioning column
          AND (( "DP"."SID_0CHNGID" = 0 ))
          AND (( "DT"."SID_0FISCVARNT" = 14 ))
          AND (( "X1"."S__0MATL_GROUP" = 28 ))
          AND (( "D1"."SID_0PLANT" = 14 ))      
          AND (( "DP"."SID_0RECORDTP" = 0 ))     // <<-- we select a certain kind of data here
          AND (( "DP"."SID_0REQUID" <= 72505 )) ))
       AND "X1"."OBJVERS" = 'A'

GROUP  BY "DT"."SID_0FISCVARNT",
          "DT"."SID_0CALDAY",
          "D1"."SID_0PLANT",
          "X1"."S__0MATL_GROUP",
          "DU"."SID_0LOC_CURRCY"

Execution Plan

---------------------------------------------------------------------------------------------------------------------------------
| Id  |Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                    |     1 |   166 |    15   (7)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                             |                    |     1 |   166 |    15   (7)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID              | /BIC/DZICSTKBALP   |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                            |                    |     1 |   166 |    14   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                           |                    |     1 |   157 |    13   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                          |                    |     1 |   151 |    12   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                         |                    |     1 |   141 |    11   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                        |                    |     1 |   132 |    10   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS                       |                    |     1 |   125 |     9   (0)| 00:00:01 |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID       | /BIC/DZICSTKBALT   |     1 |    18 |     0   (0)| 00:00:01 |       |       |
|* 10 |           INDEX RANGE SCAN                 | /BIC/DZICSTKBALT01 |     1 |       |     0   (0)| 00:00:01 |       |       |
|  11 |          PARTITION RANGE SINGLE            |                    |    54 |  6156 |     9   (0)| 00:00:01 |    54 |    54 |
|* 12 |           TABLE ACCESS BY LOCAL INDEX ROWID| /BIC/EZICSTKBAL    |    54 |  6156 |     9   (0)| 00:00:01 |    54 |    54 |
|  13 |            BITMAP CONVERSION TO ROWIDS     |                    |       |       |            |          |       |       |
|* 14 |             BITMAP INDEX SINGLE VALUE      | /BIC/EZICSTKBAL~02 |       |       |            |          |    54 |    54 |
|* 15 |         TABLE ACCESS BY INDEX ROWID        | /BIC/DZICSTKBAL1   |     1 |     7 |     1   (0)| 00:00:01 |       |       |
|* 16 |          INDEX UNIQUE SCAN                 | /BIC/DZICSTKBAL1~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS BY INDEX ROWID         | /BIC/DZICSTKBAL2   |     1 |   141 |     1   (0)| 00:00:01 |       |       |
|* 18 |         INDEX UNIQUE SCAN                  | /BIC/DZICSTKBAL2~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 19 |       TABLE ACCESS BY INDEX ROWID          | /BI0/XMATERIAL     |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|* 20 |        INDEX UNIQUE SCAN                   | /BI0/XMATERIAL~0   |     1 |       |     0   (0)| 00:00:01 |       |       |
|  21 |      TABLE ACCESS BY INDEX ROWID           | /BIC/DZICSTKBALU   |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|* 22 |       INDEX UNIQUE SCAN                    | /BIC/DZICSTKBALU~0 |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 23 |     INDEX RANGE SCAN                       | /BIC/DZICSTKBALP02 |     1 |       |     0   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------

As I saw (and as you see now) partition pruning was fully operational on this query (check the bold line and the PSTART/PSTOP columns!) - so what was the customer talking about?

Well, the tricky part with this BW query started via RSRT is, that the OLAP layer does run multiple SQL statements in parallel to reduce the total runtim of queries.
That was the reason why the customer initially told me to check the "No parallel processing" box in the Execute+Debug dialog of RSRT.
A hint that I simply overlooked at first.

Et voilá, the problem!

Once I re-ran the query with parallel execution disabled, I was able to see the execution plan the customer was complaining about:

===> QUERY 2

SELECT "DT"."SID_0FISCVARNT"      AS "S____034",
       "DP"."SID_0RECORDTP"       AS "S____011",
       "DT"."SID_0CALDAY"         AS "S____003",
       "D1"."SID_0PLANT"          AS "S____066",
       "X2"."S__0MATL_GROUP"      AS "S____170",
       "DU"."SID_0LOC_CURRCY"     AS "S____239",
       "DU"."SID_Z_UOM_EA"        AS "S____157",
       "DU"."SID_Z_UOM_FT"        AS "S____158",
       "DU"."SID_Z_UOM_LB"        AS "S____159",
       'ZICSTKBAL'                AS "K____002",
       188                        AS "S____002",
       SUM ("F"."ISSVS_VAL")      AS "Z____565",
   [ ... ]

FROM   "/BIC/EZICSTKBAL" "F"
   [ ... ]

WHERE  (( (( "DT"."SID_0FISCVARNT" = 14 ))          // <<-- no selection criteria on the partitioning column anymore
          AND (( "X2"."S__0MATL_GROUP" = 28 ))
          AND (( "D1"."SID_0PLANT" = 14 ))
          AND (( "DP"."SID_0REQUID" <= 72505 )) ))
       AND (((( "DP"."SID_0RECORDTP" = 1            // <<-- we also select some other type of data here...
                 OR "DP"."SID_0REQUID" > 0 ))))
       AND "X2"."OBJVERS" = 'A'
GROUP  BY "DT"."SID_0FISCVARNT",
          "DP"."SID_0RECORDTP",
          "DT"."SID_0CALDAY",
          "D1"."SID_0PLANT",
          "X2"."S__0MATL_GROUP",
          "DU"."SID_0LOC_CURRCY",
          "DU"."SID_Z_UOM_EA",
          "DU"."SID_Z_UOM_FT",
          "DU"."SID_Z_UOM_LB"


Execution Plan
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                             |   440 | 84040 |   128K  (1)| 00:25:39 |       |       |

   [ ... ]

|* 21 |        HASH JOIN                          |                             |   440 | 64680 |   127K  (1)| 00:25:28 |       |       |
|* 22 |         TABLE ACCESS FULL                 | /BIC/DZICSTKBALT            |  3309 | 59562 |     7   (0)| 00:00:01 |       |       |
|  23 |         PARTITION RANGE ALL               |                             | 48550 |  6116K|   127K  (1)| 00:25:28 |     1 |   90  |
|  24 |          TABLE ACCESS BY LOCAL INDEX ROWID| /BIC/EZICSTKBAL             | 48550 |  6116K|   127K  (1)| 00:25:28 |     1 |   90  |
|  25 |           BITMAP CONVERSION TO ROWIDS     |                             |       |       |            |          |       |       |
|  26 |            BITMAP AND                     |                             |       |       |            |          |       |       |
|  27 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  28 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  29 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|* 30 |                VIEW                       | index$_join$_025            |     3 |    21 |     3  (34)| 00:00:01 |       |       |
|* 31 |                 HASH JOIN                 |                             |       |       |            |          |       |       |
|* 32 |                  INDEX RANGE SCAN         | /BIC/DZICSTKBAL101          |     3 |    21 |     2  (50)| 00:00:01 |       |       |
|  33 |                  INDEX FAST FULL SCAN     | /BIC/DZICSTKBAL1~0          |     3 |    21 |     1   (0)| 00:00:01 |       |       |
|* 34 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~04          |       |       |            |          |     1 |   90  |
|  35 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  36 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  37 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|* 38 |                TABLE ACCESS BY INDEX ROWID| /BIC/DZICSTKBALP            |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|* 39 |                 INDEX RANGE SCAN          | /BIC/DZICSTKBALP03          |     2 |       |     1   (0)| 00:00:01 |       |       |
|* 40 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~01          |       |       |            |          |     1 |   90  |
|  41 |             BITMAP MERGE                  |                             |       |       |            |          |       |       |
|  42 |              BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |
|  43 |               BUFFER SORT                 |                             |       |       |            |          |       |       |
|  44 |                TABLE ACCESS FULL          | SYS_TEMP_0FD9DDDB8_15EC3B48 |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|* 45 |               BITMAP INDEX RANGE SCAN     | /BIC/EZICSTKBAL~05          |       |       |            |          |     1 |   90  |
|  46 |     TABLE ACCESS FULL                     | SYS_TEMP_0FD9DDDB8_15EC3B48 | 10839 | 86712 |     7   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------

Here we can see what the customer was complaining about: Oracle decides to read through ALL partitions, although we've seen that the BW query clearly contained the information about the time dimension.

So why is this happening?

Oracle does nothing wrong here!
If you look closely, then you see that the second statement didn't contain a selection criteria on the partitioning column.

Do we have a bug in the BW OLAP code here?

Actually not!
Would these be BW queries on standard infocubes we would have a problem here.
Especially since we have TWO queries against the SAME table!!!

Fact is: this is not a query on a standard infocube.
This is a query against a so called inventory or non-cummulative cube.

Inventory Cubes?

Inventory Infocubes are a special data design implemented in SAP BW that allow to analyze stock information. Since you cannot just add up the amount of stock over time, such information required special handling by the OLAP processor.

So, what is done in this case is to store reference stock information ("markers") and in addition to these the relative stock movements. That way OLAP can take the marker numbers and calculate backwards the amount of stock that was present for the given reference point in time.

This is of course far too short as an explanation, so make sure to check out sap note #1548125 for a far better one.

The consequence of this data design is that the OLAP engine needs to read the data in two steps and it does this indeed.
That's why wee see four SQL queries in this case (instead of the usual two SELECT statements against F- and E-facttable)

1. Read of movement data from F- and E-facttable

and

2. Read of "marker" data from F- and E-facttable

Due to the nature of the "marker" data, OLAP needs to catch up ALL of them, regardless of the time restriction.
Therefore, no WHERE condition for the time dimension is created in the second kind of statements.

Since the E-facttable in this case was partitioned by the time dimension, partition pruning would require to have such a restricting WHERE condition.
But as this is missing, no partition pruning can be done - which is correct in this case.

Let's have a closer look to the statements and the execution plans.
Review query 1 above. This is one of the queries issued by OLAP for gathering all the movement data.

Please note the restrictions on the time dimensions, especially the explicit condition on "F"."SID_0CALMONTH" which allows for the partition pruning to take place.
Also see, that OLAP here is reading the movement data, denoted by the
condition "DP"."SID_0RECORDTP" = 0.

As we see, in the execution plan above, ORACLE uses the restriction
on the partition key and only reads a single partition.

Now, let's check the query 2 again.

With this query OLAP reads the "marker" data (or "reference points") and we can see this by the predicate "DP"."SID_0RECORDTP" = 1.

Important here is to see, that there aren't any restrictions on the
time dimension left (except the fiscal variant, for rather formal
reasons). So, there is no way for Oracle to avoid any partition of the
E-facttable.

Room for improvement!?

Ok, so far we've already learned a lot about the way OLAP gets data from the database when dealing with NCUM cubes.
But, as you may have already guessed, there's still more to it.

The marker information always represent the most current state of data. No movement is done after the marker information. So basically, if a query should return the stock amount from a day in the past, OLAP takes the marker and calculates backwards.
Another way to put this is to say: there cannot be any information more recent or with a 'higher' value in the time dimension. And in fact, marker information are always stored with the maximum/infinitive date 31.12.9999.

Now, since the E-fact table can only be partitioned by the time dimension, it's assured that the marker data will always end up being stored in the MAXVALUE partition.

So, in theory it would be possible to create a statement that delivers the very same data to OLAP and still allow for the partition pruning to take place. For that it would 'just' be necessary to add the somewhat artificical selection predicate "F"."SID_0CALMONTH" = 99991231 to the query.

I write "in theory" because this feature is not present in the OLAP-DBIF layer right now. Of course we're discussing this internally, but whether or not this is going to be realized is completely open (for many good and valid reasons).

Once again, a rather lengthly blog post about some rather not so simple topics, but I still hope that you've enjoyed reading it and that you've learned a bit or byte.