Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Question Unanswered: How does oracle handle high NUM_DISTINCT/NUM_ROWS ratios?

    Hello,
    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 14:11. Reason: mistake in headlline

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    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)!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •