Re: Schema Design Question

kumar_kmk_at_hotmail.com (Kumar) wrote in message news:<bce41b15.0112271408.1e7d80f2_at_posting.google.com>...
> Hi Raghu
> Your solution works fine for the problem I posted, but I think I need
> to think ahead what could be comming as data which makes this solution
> a little problamatic... ( Sorry I did not think of it before)
>
> 1. Some inputs may have 30 fields and some have as little as 5 fields
> so if we assign 30 varchar columns I think it will be a waste of space
> for 25 fields and when i think data running into 1000's of records
> this might be unacceptable.Is there any wayway oracle optimizes
> without wasting space.
>
> 2. As long as i stick to varchars it should be ok though some fields
> may be of size 1 ( varchar(1) ) and some varchar(1000) here there is a
> 999 penalty.
>
> 3. What if one of the fields is a clob, or I have to store a image.
> This I may overcome by having a clob table and image table and store
> appropriate references. I think the first 2 I need a little thinking.
>
> Hey I understand yours is a free advice, but then what do you think
> about these concerns.
>
> Any thoughts.

hi kumar

for the first situation
the solution i can think is to create three tables table1 with 10 columns table2 with 15 another table3 with 30 columns(becoz you said that u will receive max 30 columns) if the data received is<= 10 then you insert into the table1, >10 and <=15 table2, >15 table3. this way you can optimize the space usage.

secondly in Oracle 8i you create a user defined data type with 20 columns in it and add a column to the table1 with data type as user defined data type, but insert and select operations with user defined data type are complicated i only have an idea but cannot explain more than this.

thirdly with the help of partitions, even this is for idea only i do not have any explanations.
finally probably with 9i you may have better solutions i have no idea about it.

for second problem, the basic property of varchar2 is that it will allocate space according to the size of the data i.e if the data inserted is of size 1 then it will occupy sapce of size 1 only,even though the field size is varchar(1000).if it is of size 1000 bytes then it will occupy 1000 bytes.

as of now i can think of only these solutions. by the way are you working india or abroad. no problem keep posting this will increase my knowledge.

Raghu
Certified DBA Received on Fri Dec 28 2001 - 09:30:43 CET