How to partition an Oracle table with 65 million rows?

Question

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.

Answer 1

Partitioning is a licensable option for the Enterprise Edition. This makes it expensive. Unless you already have purchased it this is not the way to go. And even it you have purchased it, it might not be the right approach.

Why not start with more straightforward tuning? Obviously you have neglected to provide even cursory information regard data distribution (how many distinct values of FIELD_ID?) or database version, let alone something as useful as an explain plan, so the following are guesses.

"Show me the components where field id 5 = '03530'"

That sort of query would most likely benefit from a compound index on:

tblValues(field_id, comp_id)

dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Is the sort of thing which is bound to suck. You show those sorts of queries are common. In which case you need to use Oracle Text. That is designed for indexing and searching large free text fields. Find out more.

In your case I think you need a CTXCAT index; in Oracle 11g we can build Composite Domain Indexes which mix CTXCAT indexes with regular columns. You might get some good results from creating a CTXCAT index on value ad creating sub-indexes on (field_id) and perhaps (field_id, comp_id). Find out more.