We are facing some performance issues with nested table queries, A table has 5 nested table/varray columns. We selected the table using TABLE() operator. But still the performance is vey slow. Can any one help us in this regard.

We've indexed all the nested tables.Also indexed the parent table.

The query is something like this,

Select id,a.*,b.* from parent_table p,
where p.timestamp=sysdate.

timestamp column is also indexed.

Query takes 5 to 6 minutes. The number of records is 1 lakh.

If we remove the nested/varray columns from query, query takes just few milli seconds only.

Please help us in this regard.