Home » RDBMS Server » Performance Tuning » index behaviour (11.2.0.3.0)
index behaviour [message #564545] Sat, 25 August 2012 06:53 Go to next message
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 #564549 is a reply to message #564545] Sat, 25 August 2012 07:59 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
this is me once again.
my auto answer (after some while of thinking) is that there is no somethink like updates on index blocks. in case of column modification index blocks are deleted and inserted after HWM.

am i right?
Re: index behaviour [message #564551 is a reply to message #564549] Sat, 25 August 2012 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are right that there is no such thing that update in an index and that an update is a delete follow by an insert.
You are wrong saying they are inserted after the HWM; it depends on your data.
Now see how you first inserted the date and how you updated them.
You first inserted 500000 values from sysdate+1 to sysdate+500000.
Then you updated the values from sysdate-1 to sysdtae-500000, none of these values fits in the same block than the previous values, so Oracle is forced to used new blocks to fill them with the new values.
Also it is a unique index, so Oracle cannot free the "deleted" values because it must still knows them in case of another session wants to insert the same value as the "deleted" ones becausee till you commit they are not really deleted.

Regards
Michel
Re: index behaviour [message #564552 is a reply to message #564551] Sat, 25 August 2012 10:25 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thanks Michel for answer.

Michel Cadot wrote on Sat, 25 August 2012 16:46
You are right that there is no such thing that update in an index and that an update is a delete follow by an insert.
You are wrong saying they are inserted after the HWM; it depends on your data.
Now see how you first inserted the date and how you updated them.
You first inserted 500000 values from sysdate+1 to sysdate+500000.
Then you updated the values from sysdate-1 to sysdtae-500000, none of these values fits in the same block than the previous values, so Oracle is forced to used new blocks to fill them with the new values.


yes.
i did it intentionaly for this exercise to be sure that every row is updated with completely different value.

Michel Cadot wrote on Sat, 25 August 2012 16:46

Also it is a unique index, so Oracle cannot free the "deleted" values because it must still knows them in case of another session wants to insert the same value as the "deleted" ones becausee till you commit they are not really deleted.


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?

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.

and final question. 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?
Re: index behaviour [message #564557 is a reply to message #564552] Sat, 25 August 2012 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: restart database after increase the db_cache_size?
Next Topic: SQL Tuning Assistance Required
Goto Forum:
  


Current Time: Fri Mar 29 08:35:10 CDT 2024