Recently I came across a query where the cost was comparatively high. I was wondering what could be the issue for a simple query to have such a high cost. This query has been called many times during the execution and this has considerably increased over all execution time of the process. The query was placed inside a procedure which has a collection as input parameter. This collection is used to filter data from a table with 544K rows.
The business logic was such that we can pass only 5 members to the collection at a time. I was surprised to see oracle does a FTS on the table to fetch 5 rows from an indexed column. I thought of digging a little bit deeper and find the root cause.
Here, oracle calculates the size of collection as 8168 but we have only 5 elements in the collection. I thought of adding the cardinality hint and provide the optimizer some more information.
Modified Query with cardinality hint.
We can clearly see that oracle is calculating the collection size properly and able to generate the best plan. we were able to get a great performance difference with this small changes.