Smart Scans On Compressed Indexes

(Doc ID 1561260.1)

Last updated on JULY 01, 2013

Applies to:

Oracle Exadata Storage Server Software - Version 11.2.3.2.1 and later
Information in this document applies to any platform.

Goal

Seeing cases where Smart Scan seems to not work with compressed indexes. Is this a known limitation?

Here is our example that shows the problem:


"select count(*) from mtm_result".  Here is the plan  
-----------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |       |   133K(100)|          |
|   1 |  SORT AGGREGATE       |                   |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| MTM_RESULT_KFIND1 |   250M|   133K  (1)| 00:26:44 |
-----------------------------------------------------------------------------------

 The index is compressed.

Elapsed time: 33.717

CREATE INDEX "BOOKRUNNER"."MTM_RESULT_KFIND1" ON "BOOKRUNNER"."MTM_RESULT" ("DEAL_INFO_SK", "MTM_FREQ_CD", "CURRENCY_UOM_SK", TRUNC("PRICE_OBSERVED_DTTM"), TRUNC("POSITION_OBSERVED_DTTM"))
 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 3 ...

We see

I/O Cell Offload Eligible Bytes: 0

 

If we uncompress the index, we see

Elapsed time: 12.327

I/O Cell Offload Eligible Bytes: 1006662451

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms