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

From: G M <zlmei_at_hotmail.com>
Date: 18 Dec 2001 11:47:08 -0800
Message-ID: <50a5e6b6.0112181147.4ac015f0@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 Tue Dec 18 2001 - 13:47:08 CST