You can find the actual execution plan, the actual row source cardinalities and exactly where the time was spent.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
user11689469 wrote:why should index be used when single digit row count all fit in single block?
I agree that my problem may be that Oracle does not seem to use the Primary index on tables which consists in the 3 columns of the Join.
However, it does not seem to use it for SELECT as well and SELECT is very fast....
user11689469 wrote:we only know what you post here.
My table structure is much more complex than this.
My data is separated into different tables to fullfill Encryption / Data separation among entities.
So, each table contains a set of Encrypted data, each table having it's own Encryption key.
Then, the View provided decrypted data... (I know this seems meaningless but this is what I was asked for...) and the decryption is only allowed for special accounts / applications...
However, if the index is not used, I'm afraid that Oracle tries to decrypt all the records that he get access too even if doesn't need to decrypt them.
Then, if Oracle would use the index, it would only access / decrypt 6 records.
If Oracle does Full Scan, it will decrypt all the records -> which will be time consuming.
However it does for the UPDATE but not for the SELECT which is identical....