How improve the performance of the query
A - Makes no sense. employee_id is unique, the query retrieves at most 1 row based on an equality filter, this should be a dead simple INDEX UNIQUE SCAN.

B - I don't think so. employee_id is unique, the number of distinct values equals to the number of rows. With huge gaps in a unique column, a height balanced histogram can still provide extra information for better estimates for a full/range scan, but this is an index unique scan.

C - Maybe, but very unlikely in a real scenario. The above query should return its result after a few block reads and gets (lets say, &lt;10, index height + 1 table access). Instead, it took 453 disk reads and 797 gets. Maybe the query read the whole table instead of an index unique scan. In that case, decreasing MBRC would increase the cost of a FTS maybe up to the point, where the index scan becomes favored. Still, in a real scenario, with proper statistics, I doubt this would happen.

D - Maybe, but again, unlikely in a real scenario. "Decreasing" the index may reduce the cost of it. The clustering factor and number of leaf blocks do not affect the cost of an index unique scan. This leaves us with the index height, and yes, that may decrease, which means the cost decreases as well, but this difference is so small, I find it difficult to believe the database would choose a FTS with 453 reads over an index unique scan with an index that has a much lower BLEVEL (typically between 0..3, it is rare to have indexes with higher BLEVEL).

E - Makes no sense. Same as A.