From: | <dbennett(at)jade(dot)bensoft(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | New SQL Datatype RECURRINGCHAR |
Date: | 2001-06-29 22:05:35 |
Message-ID: | Pine.LNX.4.10.10106291705140.6296-100000@jade.bensoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Pilosov | 2001-06-29 22:46:46 | Re: functions returning sets |
Previous Message | Matthew | 2001-06-29 22:00:59 | Help with SI buffer overflow error |