Oracle Cardinality hint

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.

Original Query




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.


One thought on “Oracle Cardinality hint

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s