X

Enabling Real-Time Analytics With Database In-Memory

Push-Down: Part 2

Maria Colgan
Master Product Manager

In our previous post we introduced the concept of push down. The ability to push predicates, aggregations and group-bys down into the scan of a column or columns, allowing Oracle to take full advantage of all the scan performance features of the In-Memory column store. We also illustrated how you could monitor the benefits of push down via session level statistics (v$mystat). What you might not have realized is that we can also see what where clause predicates get push via the execution plan.

Under a typical execution plan there is information on how and when the where clause predicates get evaluated. The predicates are divided into two groups: ACCESS and FILTER predicates. ACCESS predicates are applied as part of the search for the data or the scan, while FILTER predicates are evaluated after the data has been fetched.

Let’s look at a very simple example:

SELECT Count(*)
FROM lineorder
WHERE lo_partkey = 210876;

COUNT(*)

----------

   101 

Without an index on the LO_PARTKEY column the where clause predicate LO_PARTKEY=210876 is applied as a FILTER after the scan of the LINEORDER table.

But if we place the LINEORDER table in the In-Memory column store, the where clause predicate LO_PARTKEY=210876 can be pushed into the scan of the LO_PARTKEY column. You can tell if a predicate has been pushed by looking for the keyword inmemory in the predicate information under the plan. The keyword inmemory replaces the traditional ACCESS keyword, when the predicate is applied as part of a scan in the In-Memory column store.

SELECT Count(*)
FROM lineorder
WHERE lo_partkey = 210876;

COUNT(*)

----------

101

We can also confirm the predicate is pushed by checking the session statistics. The LINEORDER table has 23996604 rows and is made up of 44 IMCUs.

SELECT name, value
FROM v$statname sn,
v$mystat ms
WHERE ms.value != 0
AND sn.statistic# = ms.statistic#
AND ( sn.name LIKE 'IM %' )
ORDER BY name;

Remember, the stat IM scan rows projected indicates the number of rows being returned from the In-Memory column store. In this case 101 rows are returned, which is the actual number of rows in the LINEORDER table where LO_PARTKEY=210876.

What’s really great about this technique is we don’t just push the predicates you provide in the query; we also push implied predicates.

Implied predicates are predicates that the Optimizer can derive from the predicates specify in the query. Pushing these additional predicates as part of a scan allows for more data to be filtered out sooner. For example if a query has the following where clause predicate:

WHERE col1 = 2

       OR
( col1 = 1 AND col2 > 1 );

Then the Optimizer can determine the additional implied predicate col1 IN (1,2). This implied predicate on col1 can be pushed into the scan of col1 and reduce the number of rows being return from the In-Memory column store. Without the implied predicate our where clause predicate would have to be applied as a filter after the scan. Implied predicate are also visible in the predicate information under the execution plan.

Let’s look at an example where an Implied predicate can be generated.

SELECT Count(*)
FROM lineorder
WHERE lo_quantity > 30
OR ( lo_quantity < 10
AND lo_partkey = 210876 );

COUNT(*)

----------

9602103

If the LINEORDER table is not populated into the In-Memory column store you get the following execution plan.


If you look at the predicate information under the plan you’ll notice there aren’t any access predicates, only filter predicates. That means all of the data will be return from the scan of the LINEORDER table and the where clause predicates will be applied as a filter on the returned data.

Now let’s examine the execution plan after the LINEORDER table has been populated into the In-Memory column store.


The inmemory keyword is now present in the predicate information and along with the original where clause predicates, you can see an additional implied predicate, (lo_quantity > 30 OR lo_quantity < 10), which was automatically generated by the optimizer and was pushed down as part of the scan of the LINEORDER table in the In-Memory column store.

Let’s also checking the session statistics. Remember the LINEORDER table has 23996604 rows and is made up of 44 IMCUs.

SELECT name,
value
FROM v$statname sn,
v$mystat ms
WHERE ms.value != 0
AND sn.statistic# = ms.statistic#
AND ( sn.name LIKE 'IM %' )
ORDER BY name;


As you can see, all 44 IMCUs or 23 million rows were scanned but only 9,602,103 rows were actually returned from the In-Memory column store.

By examining the predicate information under the plan, you will be able to tell which where clause predicates and implied predicates have been pushed down.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.