Re: Datacompression, Data Storage..

Arun wrote:
>
> Hi Everybody,
>
> I'm looking for information on Data Storage/ Datacompression techniques in
> an Oracle Database. My interest is in finding how the information is
> stored and if any SQL or PLSQL procedures that control the storage and
> make the storage and retrieval more effective.
>
> I'd really appreciate if you could direct me to resources having this
> information. I'm collecting information as a part of my course study.
>
> Thanks in advance,
> Arun.
>
> P.S. If you could also reply to my e-mail it would be of help.

Retrieval...http://orahost.info.isbiel.ch:81/orabooks/7.2.3/odoc/product/server/72/svtuning/toc.html

            http://orahost.info.isbiel.ch:81/oracle/gen_idx.html <-anything with the word "tuning"
Storage...old question, same reply.....(from Fri, 07 Jun 1996 10:28:22 posting)

Read the Oracle7 Server SQL Reference on functions DUMP and VSIZE.     

http://orahost.info.isbiel.ch:81/orabooks/7.2.3/odoc/product/server/72/svsql/ch4.html

Here is an example (enlarge your mail window to 132 columns for beter reading):

SQL> desc bart

 Name                            Null?    Type

 ------------------------------- -------- ----

 FIELD1                          NOT NULL VARCHAR2(30)

 FIELD2                                   NUMBER

 

SQL> l

  1 select field1, vsize(field1), dump(field1) aa,

  2 field2, vsize(field2), dump(field2) bb

  3 from bart

  4* order by field2

SQL> /  

FIELD1     VSIZE(FIELD1) DUMP(FIELD1)                       FIELD2

• ------------- ------------------------------ ---------- ------------- --------------------

ONE                    3 Typ=1 Len=3: 79,78,69                  
1             2 Typ=2 Len=2: 193,2

TWO                    3 Typ=1 Len=3: 84,87,79                  
2             2 Typ=2 Len=2: 193,3

THREE                  5 Typ=1 Len=5: 84,72,82,69,69            
3             2 Typ=2 Len=2: 193,4

FOUR                   4 Typ=1 Len=4: 70,79,85,82               
4             2 Typ=2 Len=2: 193,5

NULL                   4 Typ=1 Len=4:
78,85,76,76                                NULL

Also the Oracle7 Server Concepts has the following under Tables->How data is stored
(there are exceptions to what I have here for clusters and such):

Each row has a three byte row header.

Each column less than 250 bytes require 1 byte extra to store the length.

Each column greater than 250 bytes require 3 bytes extra to store the length.

brian.maclean_at_teldta.com Received on Thu May 22 1997 - 00:00:00 CDT