Oracle - Computing lookup values for search screen

We currently have an Oracle table named tAttributes defined as:

create table tAttributes  
{  
    attribute_id integer not null,  
    document_id integer not null,  
    attribute_val varchar(4000 byte) not null  
}

Attribute_id is 开发者_开发问答the unique identifier for the attribute's category, attribute_val is the physical value of the attribute and document_id is a unique identifier for a document. For different documentid's the tAttributes table can contain the same value of attribute_id and vice versa. The size of our tAttributes table is quite large having about 60 million rows.

Given a set of document_id's I need to write the distinct attribute_val's per attribute_id to a lookup table, tAttributesLookup, defined as:

create table tAttributesLookup  
{  
    attribute_id integer not null,  
    attribute_val varchar(4000 byte) not null  
}  

I first attempted to populate tAttributesLookup by performing a "select distinct(attribute_val) etc..." against tAttributes for distinct attribute_id but the performance was on the order of hours.

I now think that tAttributesLookup should be populated dynamically - when a row is inserted into tAttributes I would first check tAttributesLookup if the attribute_id, attribute_val pair exists and if not insert them. Does this make sense? We have multiple apps inserting rows to tAttributes so how do I make sure that apps will not deadlock if they attempted to write the same attributes value to tAttributesLookup?

Thanks in advance.

UPDATE 1

I think this is an important point: ideally tAttributesLookup should only contain attributes for documents that have a certain state. To achieve this I was hoping to populate tAttributesLookup with the full universe of distinct attribute values and then use the Oracle partition function to partition the tAttributesLookup according to whether the attribute value belongs to a document(s) of a specific state.

UPATE 2

insert into  
  tAttributesLookup (attribute_id, attribute_val)  
select  
  a.attribute_id, distinct(a.attribute_val)  
from   
  tAttributes a,   
  tDocuments d   
where  
  a.document_id = d.document_id  
  and  
  a.attribute_id = X;  

---

From what it looks like you are trying to implement a search functionality for tAttributes. You don't need a separate table for what basically should be just an index on tAttributes(attribute_val). If you need to also filter for document state, add it to tAttributes and partition by it or include it in the same index as attribute_val.