Performance Tuning :: Analytics And Predicate Pushing?

The following sequence of simple statements shows me how Analytics and Predicate Pushing appear to work. But when I go from constant tests to a join with the same data as a row instead of a constant value, the Predicate Pushing stops.

I have a table with 9 million rows in it. It takes about 90 seconds to scan.
13:25:11 SQL>
13:25:11 SQL> select count(*) thecount from lv_pln_usge_fact ;
THECOUNT
----------
8681388

1 row selected.

Elapsed: 00:00:32.28

If I run an analytic that counts all rows in the table I can see that easy enough. This exampe as I understand it, scans the table (I know this because if for no other reason, it take 90 seconds to get an answer), then after scanning all rows, counts them and adds the count to each row. There are in fact 9500 or so rows with the values of lv_rqst shown. Instead of seeing all 9500 rows, I distinct it to get only one. See how the count shows all rows in the table.

From this we see that the predicate is not pushed into the inner query to filter rows. If it had, the analytic would have produced a number like 9500 not 9 million. I have no issue with this as this is how Analytics are documented work.

13:25:43 SQL> select distinct *
13:25:43 2 from (
13:25:43 3 select LV_RQST
13:25:43 4 ,count(*) over () thecount

[code]..

We can see how filter does happen with an analytic. We push the predicate into the inner query and all of a sudden we get a count of only those rows for the specific request. Thus we see the basics of how analytics work, particularly with respect to predicate pushing. There is not real rocket science here. The filter removed rows before the analytic counted them. This too is how Analytics are documented to work.

13:27:02 SQL> select distinct *
13:27:02 2 from (
13:27:02 3 select lv_rqst,count(*) over () thecount
13:27:02 4 from lv_pln_usge_fact
13:27:02 5 where lv_rqst = '746780192'

[code]...

So far we have been doing "all rows" analytics. Now we use a PARTITION clause to group the data. Notice the rowcount. It is the count for just the lv_rqst. Do not be fooled. This is because the PARTITION column says to synchronize the analytic count to the data for its associated row. Thus the counts will be grouped by LV_RQST. Whether we had the predicate on the outside of the query or not, for this specific lv_rqst shown we would still get this count. This is not proof that we did any filtering with the predicate lv_rqst = '746780192'.

However, notice the speed of the query. It gets done so fast, that there is no way it is scanning the table. I know there is an index that starts with LV_RQST so I conclude that the index was used which I believe means the predicate was in fact PUSHED into the inner query. Thus I think we did in fact filter the rows to just this single LV_RQST value and we also accessed the table using the predicate as well which became an index range scan instead of full table scan. My biggest point is we were able to use the index to get the data we want, not scan the table and this was made so because we added the LV_RQST column to the OVER clause as part of the PARTITION BY expression.

13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
13:27:03 4 ,count(*) over (partition by lv_rqst) thecount

[code]...

Now I add an additional column to the inner query, and an additional predicate against that column to the outer query. We know that 9539 is the count of all rows where LV_RQST='746780192' as we have seen that before above. I also know the data and know about half say Y and half say N for this indicator column. This query gives the right answer. I just put it here are additional demonstration of the way analytics work. It demonstrates that the new predicate is not pushed into the innser query to filter rows. Again this is how Analytics are documented to work.

It used the index to range scan only the rows where LV_RQST='746780192'. So only those predicates that have their columns in the anlytics OVER clause are allowed to be pushed into the query for filtering and accessing purposes.

13:27:03 SQL> col LV_PLN_USGE_DEL_IND format a20 trunc
13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST

[code]...

This next query shows a little more clarity. If we add the indicator column to our OVER clause then the rowcount changes to be the number of rows where LV_RQST='746780192' AND lv_pln_usge_del_ind = 'N'. So by putting the column into the OVER expression, Oracle decides to push the predicate down into the inner query and filter the data before the analytic count is done. Again this just demonstrates for clarity how it works. I think I am describing it right anyway.

And once again the speed clearly indicates that an index range scan was done using LV_RQST='746780192'. Recall I said there is an index that starts with this column.

13:27:03 SQL> select distinct *
13:27:03 2 from (
13:27:03 3 select LV_RQST
13:27:03 4 ,LV_PLN_USGE_DEL_IND

[code]...

NOW WE COME TO MY PROBLEM.Instead of using the constant value '746780192' we are going to create a one column one row table that has this value in it. We are then going to join to the the analytic subquery instead of doing a contant test against it.

13:27:03 SQL> create table kevt1
13:27:03 2 (
13:27:03 3 lv_rqst varchar2(10) not null
13:27:03 4 )
13:27:03 5 /

Table created.
Elapsed: 00:00:00.06
13:27:03 SQL>
13:27:03 SQL> insert into kevt1 values ('746780192')
13:27:03 2 /

1 row created.
Elapsed: 00:00:00.00
13:27:03 SQL>
13:27:03 SQL> commit
13:27:03 2 /

Commit complete.
Elapsed: 00:00:00.00
This query is in my mind the same query we did before but we loose the use of the index and go back to doing a FULL TABLE SCAN.
13:27:03 SQL> select distinct x.*
13:27:03 2 from (
13:27:03 3 select LV_RQST

[code]...

For a little bit more clarity, two more queries. Pay attention to how long it takes, and to how the additional joins affect things. Notice, particularly with the last statement, that the join criteria is being pushed into the inner query with the analytics. Otherwise how did it get that count?

14:55:21 SQL> select distinct x.*
14:56:16 2 from (
14:56:16 3 select LV_RQST
14:56:16 4 ,LV_PLN_USGE_DEL_IND

[code]...

So after looking at all this, here is my question: How do I get Oracle the use the index and nested loop join to the table lv_pln_usge_fact. We know Oracle pushes the predicates down when the columns are referenced in the OVER expression because we see that in several places. We also know the CBO can do a nested loop join with index access on LV_RQST because it does it when we use a constant test. But it won't use the index and nested loop when we do a join to a table with the same data, no matter how much rewriting or hinting I do.

I tested this in 9i/10g/11g and got same behavior in all three places.