Can "hash partitioned" indexes cause performance degradation ?

[amasny]

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 ).

----------------------------------------------------------------------

This is problematic insert statement excerpt :

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
……
……

----------------------------------------------------------------------

Below are indexes original definitions :

1.
CREATE INDEX ROZ.ROKTR_AKC_DATA_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(AKCEPTANT_ID, DATA_ROZLICZENIA)
TABLESPACE ROZLICZENIA_IND;
2.
CREATE INDEX ROZ.ROKTR_MERCH_DATA_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(MERCHANT_ID, DATA_ROZLICZENIA)
TABLESPACE ROZLICZENIA_IND;
3.
CREATE INDEX ROZ.ROKTR_AKC_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(KOD_AKCEPTANTA)
LOCAL (
PARTITION RTRA200712
TABLESPACE INT200712
PARTITION RTRA200801
TABLESPACE INT200801
……….
4.
CREATE INDEX ROZ.FK_ROKTR_DKTR2_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(MERCHANT_ID)
LOCAL (
PARTITION RTRA200712
TABLESPACE INT200712
PARTITION RTRA200801
TABLESPACE INT200801
……
5.
CREATE INDEX ROZ.FK_ROKTR_DKTR_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(AKCEPTANT_ID)
LOCAL (
PARTITION RTRA200712
TABLESPACE INT200712
PARTITION RTRA200801
TABLESPACE INT200801

----------------------------------------------------------------------

Below are definition of indexes changed by me :
(10 partitions)

CREATE INDEX ROZ.ROKTR_AKC_DATA_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(AKCEPTANT_ID, DATA_ROZLICZENIA)
global partition by hash (akceptant_id)
(
partition ROKTR_AKC_DATA_PART01 tablespace forhashpart01,
partition ROKTR_AKC_DATA_PART02 tablespace forhashpart02,
……

CREATE INDEX ROZ.ROKTR_MERCH_DATA_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(MERCHANT_ID, DATA_ROZLICZENIA)
global partition by hash (merchant_id)
(
partition ROKTR_MERCH_DATA_PART_PART01 tablespace forhashpart01,
partition ROKTR_MERCH_DATA_PART_PART02 tablespace forhashpart02,
…..


CREATE INDEX ROZ.ROKTR_AKC_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(KOD_AKCEPTANTA)
global partition by hash (KOD_AKCEPTANTA)
(
partition ROKTR_AKCEPT_PART01 tablespace forhashpart01,
partition ROKTR_AKCEPT_PART02 tablespace forhashpart02,
…….


CREATE INDEX ROZ.FK_ROKTR_DKTR_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(AKCEPTANT_ID)
global partition by hash (AKCEPTANT_ID)
(
partition ROKTR_DKTR_PART01 tablespace forhashpart01,
partition ROKTR_DKTR_PART02 tablespace forhashpart02,
…….

CREATE INDEX ROZ.FK_ROKTR_DKTR2_IND ON ROZ.ROZ$INT_OKP_TRANSAKCJE
(MERCHANT_ID)
global partition by hash (MERCHANT_ID)
(
partition ROKTR_DKTR2_PART01 tablespace forhashpart01,
partition ROKTR_DKTR2_PART02 tablespace forhashpart02,
…………………

----------------------------------------------------------------------

IN ATTACHED DOC YOU HAVE AWR STATISTICS AND OTHER INFORMATIONS

-----------------------------------------------------------------------

WHAT IS WRONG ??????????

Best Regards Arek Masny

P.S. “Stupid guy have tools – it doesn’t matter, he can’t use it”

[PAVB]

Two questions.

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?

[amasny]

Two questions.

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) :-)