ORACLE - What does STORAGE in an explain plan signify or mean?

Question

I was reviewing the explain plan for a slow query and I noticed that in one database it has TABLE ACCESS FULL but in another database the exact same query came back with TABLE ACCESS STORAGE FULL. The ORACLE docs / website and my web searches have not been useful in understanding what the phrase STORAGE means.

Does anyone know what the STORAGE means in this context?

I have already reworked the query so it can utilize an index, 40 secs down to under 1, so that is not the issue, just curious what the STORAGE is telling me? Maybe something related to ASM?

Answer 1

It refers to Exadata's Smart Scan and cell offload capability - that part of the plan is being passed down to the storage tier which executes that part of the query.

Answer 2

In Exadata box the Oracle Server is divided into two parts, (1) Database Servers and (2) Storage Servers connected by 40 Gb/sec Infini Band fabric.

With Exadata, Full Table Scans with filters gets processed differently. Assume that the below SQL is going for Full Table Scan.

SELECT <columns> FROM <Table> WHERE <Filter on Col1>;

The database server offloads processing to storage server. Assuming this is the first time the table is accessed, the storage server creates Region indexes on the column Col1 of the table.

Region Index: This region index considers data stored on an Exadata disk/cell in 1 MB chunks. For each 1 MB chunk, for the required column, the min and max values are obtained and stored. A collection of such Region indexes, called storage index gives the list of min and max values of our filter column. Using such an index subsequent queries using selective filters skip huge regions of unrelated data while scanning the Exadata cells. Up to 8 or 9 columns are allowed for storage indexes. Storage indexes make Full Table Scans on repeated queries with filters on same subset of columns much faster.

This is a brief story behind the key word STORAGE in the execution plan. For detailed explanation please check the below link.

http://kerryosborne.oracle-guy.com/2010/08/oracle-exadata-storage-indexes/