nested table performance??

Hi,

Oracle 10GR1 on Linux:
I've got a table containing about 160000 rows to which I've added a nested table column. Each nested table contains several hundred entries. I have created the nested table with no particular measures regarding indexes - as far as i've understood the documentation Oracle creates the necessary index on the underlying storage table anyway. Probably I've understood wrong, as performance is rather bad when i touch the nested table.

SELECT pk_id FROM mytable WHERE pk_ID=1301;

where mytable is the table containing the nested table column and pk_id is the primary key column. This takes about 0.03 seconds.

SELECT * FROM mytable WHERE pk_ID=1301;

This takes 50 seconds.

What can I do to increase performance of this?

br,
Stephan Received on Wed Jan 18 2006 - 03:03:55 CST