Column Filtering with Smart Scan

Now, I’ll do the same query while selecting just one column, the OBJECT_NAME column:

In the examples above, I’m querying MYOBJ, which looks like this:

As you can see, BIGCOL is a column of data type VARCHAR2(4000).  The tests above returned 18,513 rows, so if every row had BIGCOL fully-populated, we should be doing about (18513 * 4000) bytes less of IO.  But since I know that I’m only populating 255 bytes of data…

… our IO savings should be about (18,513 * 255) =~ 4.5Mb.

In our second query, we benefited from the use of a storage index, which helped the run-time, but shouldn’t impact the number of bytes returned over the InfiniBand interconnect.  If you look at the numbers from the queries above, we see 7,730,380 bytes returned from the first query and 1,230,720 returned from the second query – a savings of about 6.2Mb.  Which is a greater savings than what we actually expected by NOT selecting the larger column, an indication that column filtering is playing a pretty big role.

But let’s look at some different tests against these tables and see the storage index impact in both:

Here, the difference in bytes over the InfiniBand interconnect between the first query and second is 1,176,032 – 651,744 = 524,288 bytes.  Each query returned 2048 rows.  The row length of our BIGCOL column is 255 bytes for all 2,048 rows:

Further, 524,288/2,048  = 256 bytes per row saved when NOT selecting the BIGCOL column.  So this pretty much shows the impact of column filtering with Smart Scan.