Re: New SQL Datatype RECURRINGCHAR
От | Rod Taylor |
---|---|
Тема | Re: New SQL Datatype RECURRINGCHAR |
Дата | |
Msg-id | 002d01c103fb$ba311ce0$2205010a@jester обсуждение исходный текст |
Ответ на | New SQL Datatype RECURRINGCHAR (<dbennett@jade.bensoft.com>) |
Ответы |
Re: New SQL Datatype RECURRINGCHAR
|
Список | pgsql-hackers |
This is rather like MySQL's enum. I still opt for the join, and if you like make a view for those who don't want to know the data structure. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: <dbennett@jade.bensoft.com> To: <pgsql-hackers@postgresql.org> Sent: Friday, June 29, 2001 6:05 PM Subject: [HACKERS] New SQL Datatype RECURRINGCHAR > Idea for a new SQL Data Type: > > RECURRINGCHAR > > The idea with RECURRINGCHAR is treated exactly like a VARCHAR in it's > usage. However, it's designed for table columns that store a small set of > repeated values (<=256 values). This allows for a great deal of savings in > the storage of the data. > > Example: > > Query: > select count(*) from order > Returns: > 100,000 > > Query: > select distinct status from order > Returns: > OPEN > REWORK > PLANNED > RELEASED > FINISHED > SHIPPED > > It's apparent that there is a lot of duplicate space used in the storage > of this information. The idea is if order.status was stored as a > RECURRINGCHAR > then the only data stored for the row would be a reference to the value of > the column. The actual values would be stored in a separate lookup table. > > Advantages: > > - Storage space is optimized. > > - a query like: > > select distinct {RECURRINGCHAR} from {table} > > can be radically optimized > > - Eliminates use of joins and extended knowledge of data relationships > for adhoc users. > > This datatype could be extended to allow for larger sets of repeated > values: > > RECURRINGCHAR1 (8-bit) up to 256 unique column values > RECURRINGCHAR2 (16-bit) up to 65536 unique column values > > Reasoning behind using 'long reference values': > > It is often an advantage to actually store an entire word representing a > business meaning as the value of a column (as opposed to a reference > number or mnemonic abbreviation ). This helps to make the system > 'self documenting' and adds value to users who are performing adhoc > queries on the database. > > ---- > David Bennett > President - Bensoft > 912 Baltimore, Suite 200 > Kansas City, MO 64105 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
В списке pgsql-hackers по дате отправления: