| Author |
How do I rename the storage area of a nested table?
|
|
|
| I have a nested table that I need to rename. How do I do it?
Here is the abbreviated DDL:
create table t_some_table (
...
msg_info messageInfo)
nested table msg_info store as msg_info_pairs;
I need to rename msg_info_pairs to something else.
| |
| DA Morgan 2005-07-28, 3:23 am |
| pat wrote:
> I have a nested table that I need to rename. How do I do it?
>
> Here is the abbreviated DDL:
> create table t_some_table (
> ...
> msg_info messageInfo)
> nested table msg_info store as msg_info_pairs;
>
> I need to rename msg_info_pairs to something else.
Why?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
|
| I am dealing with huge amounts of data. We roll the tables every
twelve hours. We drop the oldest table instead of trying to delete it.
| |
| DA Morgan 2005-07-28, 8:23 pm |
| pat wrote:
> I am dealing with huge amounts of data. We roll the tables every
> twelve hours. We drop the oldest table instead of trying to delete it.
Again ... why? What you are suggesting is a design nightmare and highly
inefficient. What exactly is the problem with TRUNCATE TABLE? And why
would anyone use a nested table for anything? Take a day or two and read
Tom Kyte's books and opinions on the subject of object tables.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| Maxim Demenko 2005-07-28, 8:23 pm |
| pat schrieb:
> I have a nested table that I need to rename. How do I do it?
>
> Here is the abbreviated DDL:
> create table t_some_table (
> ...
> msg_info messageInfo)
> nested table msg_info store as msg_info_pairs;
>
> I need to rename msg_info_pairs to something else.
>
You can do it as with regular table
ALTER TABLE MSG_INFO_PAIRS RENAME TO SOMETHING_ELSE;
But, as Daniel wrote, there may be little, if any, reason to do it...
Best regards
Maxim
| |
|
| Thanks for mentioning Tom Kyte. I searched and found a lot of good
information. For this database, we are one of the special cases he
mentions in one of his articles. We are processing hundreds of GB of
data per day. It is all in the form of messages that may have several
hundred name/value pairs per message. We need to correlate on data
that is stored in the parent table. There are no external
relationships and the data has no other uses. It is all enterpise
logging work. We are 24X7 and have processing requirements on the data
that preclude us from truncating the table until it is seven days old.
We rename tables to avoid the overhead of copying the data from one
table to the next. We keep many tables to reduce the dataset size for
the different processing requirements.
We are maintaining schemas with both nested/non-nested tables during
development. We have experienced over twice the insertion throughput
with nested tables as opposed to non-nested. We eliminate iterating
over the child elements with nested table inserts. Selects have been
slightly slower, but that can be addressed with adding indexes per
Tom's advise.
We haven't made a final decision, but when we do it will be based on
real tests that weigh all of the options.
| |
| DA Morgan 2005-07-28, 8:23 pm |
| pat wrote:
> Thanks for mentioning Tom Kyte. I searched and found a lot of good
> information. For this database, we are one of the special cases he
> mentions in one of his articles. We are processing hundreds of GB of
> data per day. It is all in the form of messages that may have several
> hundred name/value pairs per message. We need to correlate on data
> that is stored in the parent table. There are no external
> relationships and the data has no other uses. It is all enterpise
> logging work. We are 24X7 and have processing requirements on the data
> that preclude us from truncating the table until it is seven days old.
> We rename tables to avoid the overhead of copying the data from one
> table to the next. We keep many tables to reduce the dataset size for
> the different processing requirements.
>
> We are maintaining schemas with both nested/non-nested tables during
> development. We have experienced over twice the insertion throughput
> with nested tables as opposed to non-nested. We eliminate iterating
> over the child elements with nested table inserts. Selects have been
> slightly slower, but that can be addressed with adding indexes per
> Tom's advise.
>
> We haven't made a final decision, but when we do it will be based on
> real tests that weigh all of the options.
Then another suggestion ... partitioning: Drop partition. If you have
that much data then partitioning is certainly appropriate.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
|
|
|
|