Can "hash partitioned" indexes cause performance degradation ?
Hi
I’ve decided to use hash partitioned indexes in my database.
Table has 150 mln rows.
I’ve changed 5 indexes – details below.
Insert to this table cause more physical reads (sequential) than before changing indexes even if number of rows is less. I think that these physical reads are because of index built process while rows are inserted to the table – not because of using these indexes. This was the case before changing indexes structure but after recreating indexes this is worse (I did it to make situation better but situation is worse – great ).
INSERT INTO roz$int_okp_transakcje
(ID, paczka_id, ……
SELECT rit.ID, :b3, …..
FROM roz$tmp_int_okp_transakcje rit
WHERE rit.plik_id = :b2
AND rit.rodzaj_rekordu = :b1
AND rit.status IN ('W', 'N', 'B')
Table roz$int_okp_transakcje definition (id is primary key):
(there are 13 partitions in each case)
CREATE TABLE ROZ.ROZ$INT_OKP_TRANSAKCJE
(
ID NUMBER(20) NOT NULL,
….
…
)
……
PARTITION BY RANGE (ID)
(
PARTITION RTRA200712 VALUES LESS THAN (200801010000000000)
TABLESPACE INT200712
PARTITION RTRA200801 VALUES LESS THAN (200802010000000000)
TABLESPACE INT200801
……
……
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
1- Which specific performance issue did you expect to solve by using hash partitioned indexes?
2- Have you compared explain plan and trace taken before and after your change?
Hi PAVB,
Insert statement cause performance issues on indexes segments.
This is not caused by using index for searching but by reading the index blocks while index is build because of new rows inserts to the table which has indexes created on it (i think that this is true). These indexes weren't partitioned so after reading some articles about partitioning i recreated indexes using hash partitions.
Now after that there are more reads on partitions which contains those indexes than before when they weren't partitioned.
Help me, please in understanding such behaviour.
I won't like partitions until i know how they can behave in real word.
Best Regards Arek Masny
P.S. I like you PAVB because You like the same movie which i like
(Star Wars) :-)