Re: data (and index) size difference using number(6) or number?

i think no difference since both cases have the same storage spaces. The only difference is when you query the dba_tab_columns, you will see something like this:

TABLE_NAME COLUMN_NAME DATA_LENGTH DATA_PRECISION DATA_SCALE -------------- ------------ ----------- -------------- ----------

TEST_BLASTGAPS LEN                   22
TEST_BLASTGAPS LEN6                  22              6          0

David

zlmei_at_hotmail.com (G M) wrote in message news:<50a5e6b6.0112181147.4ac015f0_at_posting.google.com>...
> Hi,
>
> Oracle 805 on Sun box.
>
> We have a big table (86 million rows) which has a column defined:
>
> LEN NUMBER
>
> I can modify it to NUMBER(6). Would I gain any performance by doing
> this? I have a index created on that column. I did some test:
>
> ---------
> SQL> create table test_blastgaps (len number, len6 number(6));
>
> Table created.
>
> SQL> insert into test_blastgaps (len,len6) select len, len from
> blastgaps where rownum<100001;
>
> 100000 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> create INDEX MT.test_blastgaps_len_index on mt.test_blastgaps
> (len )
> 2 TABLESPACE INDEXES nologging;
>
> Index created.
>
> SQL> create INDEX MT.test_blastgaps_len6_index on mt.test_blastgaps
> (len6 )
> 2 TABLESPACE INDEXES nologging;
>
> Index created.
>
> SQL> analyze table test_blastgaps compute statistics;
>
> Table analyzed.
>
> SQL> select bytes,EXTENTS
> 2 from user_segments
> 3 where segment_name like 'TEST_BLASTGAPS_LEN%';
>
> BYTES EXTENTS
> ---------- ----------
> 2088960 4
> 2088960 4
>
> SQL> select LEAF_BLOCKS from dba_indexes where INDEX_NAME like
> 'TEST_BLASTGAPS_LEN%';
>
> LEAF_BLOCKS
> -----------
> 402
> 402
>
> I do not see any index size-reduction (therefore expect no performance
> gain). So my question is: Would I gain anything by changing column
> from "NUMBER" to "NUMBER(6)" in this case?
>
> Any comments?
>
> TIA.
Received on Wed Dec 19 2001 - 02:13:54 CST