02-17-12, 13:08 #1Registered User
- Join Date
- Feb 2012
Unanswered: How does oracle handle high NUM_DISTINCT/NUM_ROWS ratios?
I've got a noobish question.
I've got a column with 6 possible varchar(255) values and large number of entries.
Does Oracle optimize in any way the disk usage for such data?
Following common sense it would save considerable amount of space if the varchars were replaced with numbers which would then be mapped back to their original values. Does Oracle do such thing automatically?
If not one could be inclined to replace all such texts with numbers and save a lot of disk space. However taking this to the extreme, one could easily end up with a numbers-only database.
It could be just the lack of sleep, but right now this idea is confusing me considerably while I'm trying to redesign my old DB layout.
Varchars or numbers mapped to varchars....
UPDATE: Mistake in headline: I meant low ratios of course
Last edited by ioudas; 02-17-12 at 13:11. Reason: mistake in headlline
02-17-12, 13:19 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
Oracle stores the data as you specify the datatypes.
Stop confusing yourself & making the database more complicated than it needs to be.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
02-17-12, 13:58 #3Registered User
- Join Date
- Mar 2007
Answers to your questions in the first half of your post are no.
Oracle may compress data under some circumstances. However it does not concern one column, it affects the whole table/index/partition.
Its exact functionality depends on Oracle version (it was enhanced in 11g) and DML statements run on that object (INSERT/UPDATE/DELETE). As you did not post it, maybe you should use your favourite web search engine and enter "Oracle compress". Alternatively you may search for "compress" in the description of CREATE TABLE statement for your Oracle version. It is present in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Good luck in investigating/researching this feature abilities (unless you fall to sleep)!