| index behaviour [message #564545] |
Sat, 25 August 2012 06:53  |
 |
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
hi,
my coleague asked me lately important question: "why does this index is bigger than whole table?".
unique index was set only on one column with stored data values.
table weight was about 100GB and index only on one column (with data) was over 100GB. so it seemed be very strange for me that index only for one column could be bigger than whole (with many columns) table.
after rebuilding this index, its weight dropped to 30GB.
i wouldn't be surprised if table was treated with many delete commands and insert commands - in that cases index could consist many empty blocks and could be bigger than table. but this table was only treated with update on this specified column.
so i did example.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as teta_admin
SQL>
SQL> DROP TABLE test1;
Table dropped
SQL> CREATE TABLE test1 (col1 NUMBER, col2 DATE);
Table created
SQL> alter table TEST1
2 add constraint pk1 primary key (COL1);
Table altered
SQL> -- Create/Recreate indexes
SQL> create unique index index1 on TEST1 (col2);
Index created
SQL> BEGIN
2 for i in 1..500000 LOOP
3 BEGIN
4 INSERT INTO test1 (col1, col2)
5 SELECT i, SYSDATE + i FROM dual;
6 END;
7 END LOOP ;
8 END;
9 /
PL/SQL procedure successfully completed
SQL> COMMIT;
Commit complete
SQL> COLUMN segment_name format a20;
SQL> SELECT segment_name, segment_type, bytes FROM dba_segments dsa
2 WHERE dsa.segment_name IN ('TEST1', 'PK1', 'INDEX1');
SEGMENT_NAME SEGMENT_TYPE BYTES
-------------------- ------------------ ----------
TEST1 TABLE 10485760
INDEX1 INDEX 10485760
PK1 INDEX 8388608
SQL> BEGIN
2 for i in 1..500000 LOOP
3 UPDATE test1 SET col2 = SYSDATE - i WHERE col1 = i;
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
SQL> COMMIT;
Commit complete
SQL> SELECT segment_name, segment_type, bytes FROM dba_segments dsa
2 WHERE dsa.segment_name IN ('TEST1', 'PK1', 'INDEX1');
SEGMENT_NAME SEGMENT_TYPE BYTES
-------------------- ------------------ ----------
TEST1 TABLE 10485760
INDEX1 INDEX 29360128
PK1 INDEX 8388608
SQL> ALTER INDEX INDEX1 REBUILD;
Index altered
SQL> SELECT segment_name, segment_type, bytes FROM dba_segments dsa
2 WHERE dsa.segment_name IN ('TEST1', 'PK1', 'INDEX1');
SEGMENT_NAME SEGMENT_TYPE BYTES
-------------------- ------------------ ----------
TEST1 TABLE 10485760
INDEX1 INDEX 11534336
PK1 INDEX 8388608
SQL>
... please focus on INDEX1 (index) and TEST1 (table) weight in this example.
first question:
why this index raised 3 times much over whole table weight? is some explanation for that?
and second question:
why after rebuilding, the weight dropped down, but not as much as i suspected. we should know that TABLE1 is consisted of two columns and each of them absorbs some place, so in my understanding after rebuilding index should be a little smaller than whole table.
why this index (after its rebuilding) is still little bigger than whole table?
[Updated on: Sat, 25 August 2012 06:56] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: index behaviour [message #564557 is a reply to message #564552] |
Sat, 25 August 2012 13:48  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:but what is after i commit my data?
index blocks where was old values will be empty or half empty or 1/3 empty (generally partly empty) and new values (which does not fit for old blocks) is stored in new index blocks - yes?
Yes.
Quote:i don't completely understand why index works this way that there are empty spaces in index blocks after modification. maybe someone will be kind explain it for me.
Oracle MUST keep the old values in the unique index. Why? Just because if someone else wants to insert the same value and you have not committed then this other session must be blocked until you end your transaction and release the lock on the old value. But how to know this old value if it was deleted or overwritten? No way; so the old value must kept. At the end of the transaction, if you commit, the values/entries were flagged as deleted by your previous transaction so nothing to do and the space will be reuse then next time but for the moment you have twice the space used.
Quote:why this index is still the same weight as table. table consist of two columns so stores much more of data than index? maybe this is because of internal strucutre of index?
Simple (bad) luck, your other column has the size of the overhead for the index. Use another column as VARCHAR2(100) or add 10 columns and fill it/them and you will see the index has not the same size that the table.
Regards
Michel
[Updated on: Sat, 25 August 2012 14:04] Report message to a moderator |
|
|
|
| Re: index behaviour [message #564578 is a reply to message #564557] |
Sun, 26 August 2012 02:42 |
|
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
thanks MIchel again for responding...
Quote:
Oracle MUST keep the old values in the unique index. Why? Just because if someone else wants to insert the same value and you have not committed then this other session must be blocked until you end your transaction and release the lock on the old value. But how to know this old value if it was deleted or overwritten? No way; so the old value must kept. At the end of the transaction, if you commit, the values/entries were flagged as deleted by your previous transaction so nothing to do and the space will be reuse then next time but for the moment you have twice the space used.
i thoutght a while about this problem yesterday and i found that architecture of B-tree index works this way and it is almost impossible to do this other way.
if we do inserts one by one on column with data oracle fills also index blocks. when index block is full (of course it depends from PCT free) it splits.
data in blocks in index needs to be ordered descendly or ascendly - its only way to store data in index. and this is key of this behavour.
if i put completely new values into column, oracle need to organize index somehow. but this new values differ completetly from my previous values so they doesn't fit into previous blocks. oracle needs to create new blocks instead (with new min and max valuse for each block). old table blocks are just updated, but for index the most blocks are new. its very hard to reorganize whole index online after each DML, so old values in index blocks are just deleted and we obtain free place in this index - but is is also waste of space.
as i said oracle after each DML could reorganize whole index to reasure that there is no waste of space in it but it could be very performance difficult, because it needed to rebuild whole index to create completely new index block with theirs new min and max values. so this is reason why oracle creates empty spaces in index blocks and blocks needs to be rebuild from time to time.
[Updated on: Sun, 26 August 2012 02:59] Report message to a moderator |
|
|
|
| Re: index behaviour [message #564608 is a reply to message #564578] |
Sun, 26 August 2012 09:30 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>so this is reason why oracle creates empty spaces in index blocks and blocks needs to be rebuild from time to time.
for some loose definition of "needs".
Oracle will continue to operate without any error when no manual index rebuild is ever done.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
