Why indexing nested tables is so good

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Last week i got a call from a customer who was complaining because a procedure exporting a file was taking a very long time. After some investigation it turned out that, for some reason, during a recent application upgrade, an index on a nested table had not been (re)created.

The symptoms were clear: a procedure that normally took seconds, was now taking 20 minutes to execute.
I could quickly find out the real reason for this huge performance problem after looking at the execution plan of an inner query, that is a query executed several thousands times because it's performed inside an outer explicit cursor:


If it is certainly true that in Oracle (as for any other know database) there is no FAST=TRUE parameter, but it must be said that when a procedure is very slow, either the procedure is poorly written or some index is missing or both... ;-)
If you are lucky, then it's enough to create the right index and voilá, it's almost like turning on that magic switch.
Indeed that was my case as it was enough to (re)create the missing index on the nested table to fix the problem, as follows:

CREATE INDEX IDX_TOTES_CNT ON NESTED_CNT(NESTED_TABLE_ID,MAT_ID);

Note: NESTED_TABLE_ID is a pseudo-column provided by Oracle containing the primary key of the nested table.
Soon the execution plan started looking much better:


The success was confirmed by the fact that the export procedure now took a few seconds again, as it was before the upgrade.
But why do i need an index on a nested table in the first place?
Here is a stripped down version of the inner query. Value par_tote is a cursor parameter that is populated by the outer cursor. This value uniquely identifies the record containing the nested table to be processed. Thereafter, as you can see, i need to filter out certain values (in green) that come from nested table cnt:

select
a.mat_id,
...
b.ean
from table(select cnt
            from totes
           where tote = par_tote) a,
    exits b
where a.mat_id != info_const.conMat_ID_NRR
and a.mat_id = b.mat_id (+)
order by a.mat_id;

With an index on the nested table, the optimizer can perform the table unnesting more efficiently, selecting the recordset with a common NESTED_TABLE_ID (having the same cardinality as tote) and filtering the data basing on mat_id, without having to perform a full table scan on each iteration, which explains why the procedure was taking that outrageous amount of time to execute.