Re: nested table performance??

steph wrote:
>
> 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

My $0.02 worth...

If you hunt around as sysdba, and take a look at whats going on under the covers, its just a stock standard parent/child table relationship with some hidden columns et al.

So you've ended up with a loss of control (Oracle decides how its going to get to the nested table rather than you) and really a data model you could have just as easily done with two "normal" tables.

I rate nested tables as very cool as a datatype (for use in PL/SQL etc) but not in a physical table.

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------