Re: Next tables vs large varchar2 to hold info

The overhead in space requirements for
varrays and nested tables is quite large; but if you want to execute queries which limit the returned data only by checking the nested type then a strategy of using some form of delimited string would leave you doing tablescans on every query.

Its the usual trade-off between space and speed - but at least with nested tables you get more options.

Try using

    SHOW ERRORS
after each create -

The first type won't create because you have omitted the 'as object' after the the type name; when you get past this error you will then get an error that the NOT NULL constraint is not supported for types.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Bill Mann wrote in message <3765A265.FD5DE140_at_worldtalk.com>...
>My application leads it's self well to a nexted table, but I'm not sure
>about the performance of such as solution compared to a simple varchar
>containing a semi-colon formated string that my application could parse
>to search for specific object types.
>
>The nested table is good if the number of object is large, but I can
>have a limit of say 32. Each object will be a varchar2(32) holding a
>user name, and a char(2) containing a coded information about this
>user. Each record in my table could have 0..32 of these types, hence my
>conclusion to use a nested table.
>
>Any comments.
>
>Also, what is wrong with the following :
>
>create or replace type dependent_t (
> permission char not null,
> name varchar(2) not null
>);
Received on Tue Jun 15 1999 - 02:59:42 CDT