Re: Insert running long

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_at_gmail.com> 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_at_ebisprd2>_at_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_at_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_at_ebisprd2>SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE
> OWNER='UM_DWH_NXT' AND OBJECT_nAME='XIF3MEMBER_BANK_BALANCE';
>
>
>
> DATA_OBJECT_ID
>
> --------------
>
> 2181616
>
>
>
> bdhulipa_at_ebisprd1>select count(*) from (select /*+ PARALLEL(8) */
> distinct file#, block# from gv$bh where objd=2181616);
>
>
> COUNT(*)
>
> ----------
>
> 1
>
>
>

--
http://www.freelists.org/webpage/oracle-l