| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Design Pattern Question (help!)
How viable this is depends on the volumes of data you are dealing with.
The problem is that TABLE_DETAIL will become very big very quickly indeed. You'll also find that the PK index will be enormous. Performance will start out ok until the extra 'columns' start being stored in different disk blocks. Then you have a situation where to retrieve one 'record' involves hitting different parts of the disk drive, which harms performance. Even if you have lots of memory you'll still have the overhead of the initial retrieval and multiple blocks being used to store what is actually one record.
Things you can do:
Make sure the table has a high value for PCTFREE and keep adding columns. You can't have written that much code yet as otherwise you wouldn't be considering normalising it. The repeated requests to add columns may be part of your de-facto development methodology and will sooner or later slow down or stop.
Create TABLE and TABLE_DETAIL but watch out for the size of the related indices and how much sort space is required to re-create them. Also watch out for performance. You might want to *test* clustering the table - this would at least 'encourage' all the data to be in the same set of blocks. Also try writing reports. See just how many joins you need to get data out of such a data structure! There's a good reason why this isn't done that often....
If you're feeling brave and ambitious create one table with about 100 columns of different data types (textcol_01, numcol_01, datecol_02 etc) and then define a view which names the columns your application uses. You could even create a small meta data table that explains which view column equates to which table column. Adding NULL columns to the end of a table in Oracle does not take up space provided all the columns in front of it are used. This is overkill if you only have one table.
Rich
www.orindasoft.com - makers of OrindaBuild which generates Java JDBC access code for calling PL/SQL Procedures. Received on Tue Oct 07 2003 - 04:11:15 CDT
 |
 |