Version :11.1.0.7.0 - 64bit Production
OS :RHEL 5.3
I have a range partitioning on ACCOUNTING_DATE column and have 24 monthly partitions.
To get rid of buffer busy waits on index, i have created global partitioned index using below ddl
DDL :
CREATE INDEX IDX_GL_BATCH_ID ON SL_JOURNAL_ENTRY_LINES(GL_BATCH_ID)
GLOBAL PARTITION BY HASH (GL_BATCH_ID) PARTITIONS 16 TABLESPACE OTC_IDX PARALLEL 8 INITRANS 8 MAXTRANS 8 PCTFREE 0 ONLINE;
After index creation, i realized that only one index hash partition got all rows.
select partition_name,num_rows from dba_ind_partitions where index_name='IDX_GL_BATCH_ID';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
SYS_P77 0
SYS_P79 0
SYS_P80 0
SYS_P81 0
SYS_P83 0
SYS_P84 0
SYS_P85 0
SYS_P87 0
SYS_P88 0
SYS_P89 0
SYS_P91 0
SYS_P92 0
SYS_P78 0
SYS_P82 0
SYS_P86 0
SYS_P90 256905355
As far as i understand, HASH partitioning will distribute evenly. By looking at above distribution, i think, i did not benefit of having multiple insert points using HASH partitioning as well.
Here is index column statistics :
select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM,AVG_COL_LEN from dba_tab_col_statistics where table_name='SL_JOURNAL_ENTRY_LINES' and COLUMN_NAME='GL_BATCH_ID';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANALYZED SAMPLE_SIZE HISTOGRAM AVG_COL_LEN
------------------------------ -------------------- ------------ ---------- -------------------- ----------- --------------- -----------
SL_JOURNAL_ENTRY_LINES GL_BATCH_ID 1 0 2010/12/28 22:00:51 259218636 NONE 4