Hi All,
Database version 12.1 SE
Im trying to make the access of my table as efficient as possible.
I have a table (its actually a materialised view but nevertheless).. It s a summary data MV. Its got about 200k rows currently. it has 6 columns.
Code, (1734 unique values)
Budget_id (265 unique values)
fin_month (12 unique values)
fin_year (3 unique values)
budget_value
Actual_value
I have a composite index (code, budget_id, fin_month, fin_year) on the MV.
My where clause on this MV is almost always based on the four criteria (code, budget_id, fin_month, fin_year) to get my value
but because the most unique column 'code' is still less than 1% unique, the index never gets used, and it carries out a full table scan.
I have found if I create a key column as a concatenation of the four where columns, it's create a key almost 100% unique. Putting an index on the key column, then querying based on i.e. where key = code||budget_id||fin_month||fin_year uses the index, and is a lot quicker. (explain cost = 4 vs 690)
So my question is: Is this a sensible approach to this type of table/MV, is there anything I've missed that would do the same thing without me doing it manually? Shouldn't creating a composite index do the same thing?
Thanks
Richard