User Defined Type performance issue

Hello:

I've found a significant INSERT performance degradation using Oracles user-defined types in a table, and I was wondering if anyone has run into it, and if anyone has any workarounds other than NOT using the UDTs.

I've created a simple UDT of FooUDT, which only has 3 numeric columns.  Then I created a table with 2 columns, an ID column and FooUDT, then I created a baseline table with 4 columns, the ID column, and the same 3 numeric columns making up FooUDT.

I then tested doing bulk insert into these tables using PL/SQL, utilizing the FORALL clause, inserting from a 50 element array in a loop. After inserting 100,000 records in each table, it is clear that the use of UDT for the smae amount of data slowed INSERT performance by 20%. When I added an additional UDT column to the test table and 3 more columns to the baseline table to match, the INSERT performance now degraded by 40%.

So it looks like the use of UDT can be quite expensive. Other than removing of UDT, is there some optimization I can use, such as referring to the UDT's column directly, that may reduce the cost of the UDT?

I am doing all my testing on Oracle 8.17. If there are advantages moving to Oracle 9 for this testing, please advise as well.

Thanks in advance

dreadlord76 Received on Tue Mar 19 2002 - 12:57:06 CST