The table that needs partitioning contains a list of Components and their Values.
So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the Components/Fields, so it has a Component foreign key as well as a Field foreign key. Right now this tblValues table contains 68,000,000 values. Components table contains ~80,000 rows. Each component has around 500-800 fields of inventory.
I was thinking about creating a partition by hash on the field id. As the user will query, they will pick something like.. ."Show me the components where field id 5 = '03530'" Something along those lines.
Sample queries are below that are common:
select * from tblComponents comp inner join tblValues v on comp.comp_id = v.comp_id where v.field_id = 55 and dbms_lob.substr(v.value, 2000, 1) like '%brick%'
Also, the values are currently stored as CLOB's and can have numeric values or string values.
Any tips regarding how you would partition or index this would be great, especially for optimizing querying.