Certainly no one can take it against you for experimenting in the
interest of research. Bright ideas are born this way. However, we also
certainly hope that this research of yours isn't in the final phases
of production rollout already but rather just in the proof-of-concept
stage before you go and build your whole application on it.
Just my take on this:
- As you stated, your main purpose in using this approach is to save
space. Do some testing on representative data and find out if you
actually are saving space. Looking at what you have right now, I
suspect not. Nested tables use internal columns in its implementation
particularly NESTED_TABLE_ID (some 16 bytes of RAW I think). Most of
the time, that's a lot overhead per row in your nested table. You
could cut that overhead dramatically if your nested table is IOT --
which it isn't yet.
- Storage is cheap. Trying to tune the performance of a flawed design
is many times more expensive in terms of time, money, and fingernails
for those who tend to chew them in frustration. So you will also need
to see how this approach scales in all its intended uses. Heavy
queries, all possible DMLs and not just inserts.
- Regarding the actual insert you're trying to do. An INSERT-SELECT
will do that better than PLSQL. Plus an /*+ append */ hint too.
- Also, note that NOLOGGING (which you turned on to attempt to speed
up your current INSERT) only works for bulk insert operations (/*+
append */). Right now, it's not doing anything for your INSERT
VALUES().
- About your regular commits. As Tom Kyte puts it, "Only commit when
you really have to."
Good luck on your research! This endeavour will yield more benefits if
you read the available literature on Oracle. The official manuals, Tom
Kyte's books, and even these forums. Well, I guess we're both on the
right track participating in this and all. =)
Cheers,
Romeo
Received on Thu May 27 2004 - 12:02:18 CDT