Re: Poor Performance of Nested Tables

From: Bill Coulam <bcoulam_at_gmail.com>
Date: Thu, 2 Jun 2005 16:30:08 -0500
Message-ID: <f51d33020506021430721ed892@mail.gmail.com>

As Christian said, index nested_table_id.

It's been three years, but as I recall that is the "hidden" foreign key between the parent and child table. Missing indexes on large child table foreign keys is generally a bad problem when deleting from the parent, even when there are no matching rows in the child, you get full scans. Nasty.

Ideally, you'd never model with nested tables as a column type unless you had a really good reason and an understanding of the limitations. But seeing how you're stuck...

index nested_table_id

On 6/2/05, Madhavi Kanugo <MKanugo_at_ibasis.net> wrote:

> Hello All,

>=20
>=20

> We have a table which has 2 Nested Tables and a VarArray as columns besid= es
> other datatypes. This table holds 2 million records. When deleting record= s

> from this table, we noticed that it does a Full Table scan of the Nested
> tables for each and every row we are deleting resulting in very poor
> performance.

>=20
>=20
>=20

> Does anyone have any good suggestions on how we can improve performance f= or

> this? Anyone encountered this situation before?

>=20
>=20
>=20

> Thanks in advance,

>=20

> Madhavi

>=20
>=20
>=20
>=20
>=20
>=20
>=20

> --
> http://www.freelists.org/webpage/oracle-l

>=20

--=20
bill coulam
bcoulam_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 02 2005 - 17:35:09 CDT