a waste
hmmm -- lets see:
select * from t where pk = :x;
on a
a) non indexed, 900meg table versus
b) an indexed, 900meg table
which might burn up the disks -- verus getting the answer in a nanosecond??
you see, you have to define "waste". indexes are there to generally "make things go faster", not to be "smaller than the table"
The index has the rowid in it (the table, it does not). rowids are sizeable ...
the table -- don't know how you loaded it or anything, but perhaps it was loaded "packed" (pctfree=0) maybe even compressed.
But the index was created "using the defaults" and has 10% free.
but actually, i don't know whats up because I created a 50,000,000 row table:
ops$tkyte@ORA9IR2> declare
2 l_stmt long := 'create table t ( pk char(12), d1 date default sysdate, d2 date default sysdate, d3 date default sysdate, n1 number, n2 number, n3 number';
3 begin
4 for i in 1 .. 100
5 loop
6 l_stmt := l_stmt || ', c' || i || ' char(1) default ''x'' not null ';
7 end loop;
8 l_stmt := l_stmt || ' )';
9 execute immediate l_stmt;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert /*+ APPEND */ into t (pk,n1,n2,n3)
2 select rownum, rownum,rownum,rownum from big_table.big_table where rownum <= 50000000;
50000000 rows created.
ops$tkyte@ORA9IR2> select segment_name, segment_type, bytes/1024/1024 meg, bytes/1024/1024/1024 gig
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE MEG GIG
------------------------------ ------------------ ---------- ----------
T TABLE 14491.0156 14.1513824
ops$tkyte@ORA9IR2> create index t_idx on t(pk,d1,d2,d3,n1,n2,n3);
Index created.
ops$tkyte@ORA9IR2> select segment_name, segment_type, bytes/1024/1024 meg, bytes/1024/1024/1024 gig
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE MEG GIG
------------------------------ ------------------ ---------- ----------
T TABLE 14491.0156 14.1513824
T_IDX INDEX 3621 3.53613281
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks............................. 0
Total Blocks............................ 1,854,848
Total Bytes............................. 15,194,914,816
Total MBytes............................ 14,491
Unused Blocks........................... 4,317
Unused Bytes............................ 35,364,864
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 1,847,177
Last Used Block......................... 3,875
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec show_space( 'T_IDX', p_type => 'INDEX' );
Free Blocks............................. 0
Total Blocks............................ 463,488
Total Bytes............................. 3,796,893,696
Total MBytes............................ 3,621
Unused Blocks........................... 122
Unused Bytes............................ 999,424
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 2,310,153
Last Used Block......................... 8,070
PL/SQL procedure successfully completed.
<b>so, i guess we are at the point where "things just don't add up". I would expect 50 million rows to take more than 900 MB, especially given your description since:
ops$tkyte@ORA9IR2> select 900*1024*1024/50000000 from dual;
900*1024*1024/50000000
----------------------
18.874368
900 meg -- mulitplied out to bytes and divided by 50 M, works out to about 19 bytes max per row in a perfect perfect world....
And if you got 6 columns into that index as described, in 1.1 gig -- well -- what can I say besides "well done"
So -- can you re-check your numbers?</b>