So now we can do some arithmetic.
I assume this is the global index, which means its rowid will be stored
with 10 bytes +1 for length since it's non-unique.
This gives an index entry size of 7 + 11 + 2 (row overhead) + 2 (row
directory pointer) = 22
So allowing 192 bytes for block and transaction overhead that's 8000/22 =
363 index entries per leaf block if created at pctfree = 0.
You previous reported leaf_blocks = 11371694, table_rows = 2643162455
(assume index entries = table rows, and indexes are accurate).
At a possible maximum of 363 rows per leaf block compared to 2643162455
/ 11371694 = 232 index entries per leaf block, that's an average of 63%
utilisation -- which isn't particularly bad.
We also had the figure for distinct keys telling us that you have about
3378432 distinct values so 2643162455 / 3378432= 782 rows per key on
average, which means at your current average rows per block each key
spreads over 4 leaf blocks - although it's possible that your index will
have a pattern (for each key) of 2 blocks at 50% utilisation and one block
approaching full and just about to split.
Bottom line - assuming that this "average" pattern is correct and you don't
actually get (say) 90% of your data in 10% of your key values then
1) your index is quite a lot bigger than it could be
2) you could reclaim about 25% of the current space used by the index by
rebuilding at pctfree = 10 (default)
3) you probably won't reduce I/O (or improve performance) by rebuilding the
index
4) if you rebuild the index then you will probably have a period where a
large number of index leaf block split take place as batches of new data
arrive.
Regards
Jonathan Lewis
On Fri, Apr 10, 2020 at 3:39 AM Bhavani Dhulipalla <bhavanidba6@xxxxxxxxx>
wrote:
Hi Jonathan
Thank you for your help - The Average column length for this column is 7
bytes and there are no null values in this column
bdhulipa@ebisprd2>@IND UM_DWH_NXT.MEMBER_BANK_BAL
Display indexes where table or index name matches
%UM_DWH_NXT.MEMBER_BANK_BAL%...
TABLE_OWNER TABLE_NAME
INDEX_NAME POS# COLUMN_NAME
-------------------- ------------------------------
------------------------------ ---- --------------------------
UM_DWH_NXT MEMBER_BANK_BAL
XIF3MEMBER_BANK_BALANCE 1 INTERNAL_ACCT_ID
bdhulipa@ebisprd2>SELECT AVG_COL_LEN,NUM_NULLS FROM
DBA_TAB_COL_STATISTICS WHERE OWNER='UM_DWH_NXT' AND
TABLE_NAME='MEMBER_BANK_BAL' AND COLUMN_NAME='INTERNAL_ACCT_ID';
AVG_COL_LEN NUM_NULLS
----------- ----------
7 0
Also it seems like one block is there in the cache
bdhulipa@ebisprd2>SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE
OWNER='UM_DWH_NXT' AND OBJECT_nAME='XIF3MEMBER_BANK_BALANCE';
DATA_OBJECT_ID
--------------
2181616
bdhulipa@ebisprd1>select count(*) from (select /*+ PARALLEL(8) */
distinct file#, block# from gv$bh where objd=2181616);
COUNT(*)
----------
1
