Hi Tom.

I have a table with following definition:

SQL> desc OAUTH2_REFRESH_TOKEN
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(16)
TOKEN_ID VARCHAR2(255 CHAR)
TOKEN BLOB
AUTHENTICATION BLOB

ID is a primary key.

Looks like there are many deletes on this table using ID. I've noticed that they are are doing a lot of consistent gets.

DELETE
FROM OAUTH2_REFRESH_TOKEN
WHERE "ID"= '8A81B295444C2881014452803709648E'SQL> 2 3
4
SQL> /

1 row deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 2420547146

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |

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

| 0 | DELETE STATEMENT | | 1 | 50 | 10377 (1)| 0
0:02:05 |

| 1 | DELETE | OAUTH2_REFRESH_TOKEN | | | |
|

|* 2 | INDEX FULL SCAN| SYS_C00203982 | 1 | 50 | 10377 (1)| 0
0:02:05 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(RAWTOHEX("ID")='8A81B295444C2881014452803709648E')


Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
10592 consistent gets
0 physical reads
3548 redo size
833 bytes sent via SQL*Net to client
846 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed


I rebuilt PK index and the size of it went from 88MB to 10MB. Number of GETS also decreased.
Before:
SQL> select bytes/1024/1024 from dba_segments where segment_name = 'SYS_C00203982';

BYTES/1024/1024
---------------
88


After:
SQL> select bytes/1024/1024 from dba_segments where segment_name = 'SYS_C00203982';

BYTES/1024/1024
---------------
10


DELETE
FROM dice."OAUTH2_REFRESH_TOKEN"
WHERE "ID"= '8A81B295444C2881014452803709648E'SQL> 2 3
4 /

1 row deleted.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 2420547146

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 50 | 1242 (2)| 00:00:15 |
| 1 | DELETE | OAUTH2_REFRESH_TOKEN | | | | |
|* 2 | INDEX FULL SCAN| SYS_C00203982 | 1 | 50 | 1242 (2)| 00:00:15 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(RAWTOHEX("ID")='8A81B295444C2881014452803709648E')


Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
1228 consistent gets
0 physical reads
3460 redo size
845 bytes sent via SQL*Net to client
846 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

I don't want to be rebulding this index all the time and was wondring if there is a better way to set it up.