Nested tables

[MikeJones]

Hi all,

Im thinking of using nested tables to solve a problem I have. However the nested table I have would need to hold 2 columns, one of which would need to be a CLOB.

The basic structure would be this...

My_table
Col1 - Primary key
Col2 - Some Info
Col3 - Some more Info there would be quite a few of these columns.
Col4 - Nested table containing...
Col1 - Uniqiue key for this Nested tables data
col2 - my CLOB

I have two questions...

1. Is is possible to have a nested table with more than 1 column? The documentation I have always uses a single column in the examples.

2. I would need to read and manipulate the CLOB as quickly as you can a varchar2, I'm using a CLOB as the charecter sting can be upto 32,000 long (PL/SQL max varchar2 size) but I can only store 4000 on the DB. I've ruled out using longs as they are too restrictive in terms of what functions can be used on them (i.e. none)

Oh and I've not used either of these constructs before so please be gentle with the technicalities...

Thanks in advance,

Mike.

 

[karluk]

I don't have any practical experience with nested tables, but it looks as if the answer to your question about multiple columns is yes - nested tables can indeed be defined with more than one column.

 

[LokiDba]

I have no experience of nested tables but the usual practice when you have a large data type (ie Long, CLOB, etc), is to separate it out into a separate table. This table is then related to the parent using foreign keys, etc.

This is to added performance, ie the parent data structure is smaller and less physical storage has to be searched to retrieve the relevant data. Also the table with your large data type is smallish, it only consists only of two column, the primary key and the large data type.

This also aids searching and retrieving the large data structure.

Hope this is of some use


LokiDBA

 

[CRoberts]

I have not used a nested table, but have worked with CLobs and BLobs. I think that manipulating these large objects could be a problem, I ran into problems with multiple Clobs within the same table. The problem is that since you are dealing with a pointer, you can not tell when a Clob is actually NULL. Similarly, nested tables are initially densely packed, but with deletions, these can become sparse. Thus, it involves actually retrieving the Clob to see if it is NULL. Far easily to have a child table with a row for each Clob. Then, if you only have two Clobs, you have two rows. Easy to count and manage.