[Gusdev-gusdev] performance issues in querying dots.similarity/dots.similarityspan tables

[Sucheta T. <su...@vb...>]

Folks,

I don't know if any of you encountered this problem, but for us, it has 
been a major bottleneck to query the similarity and similarityspan tables 
to get the desirable output.

For example if I say a query:

$sql="select  /*+CHOOSE*/   distinct ss.query_start, 
ss.query_end,ss.is_reversed
,ss.reading_frame,s.subject_id, to_char(s.pvalue_mant,'FM9990.099') || 'e' 
|| TO
_CHAR(s.pvalue_exp,'FMS999'), eaa.name, s.similarity_id  from dots.similarity s
, dots.similarityspan ss, dots.transcript t, dots.externalaasequence eaa  where
t.na_feature_id=464205 and t.gene=to_char(s.query_id) and 
s.similarity_id=ss.sim
ilarity_id and s.subject_id=eaa.source_id and rownum < 10 order by 
s.similarity_
id";

It takes atleast 5 minutes. One thing to note here is here for the 
transcript corresponding to the na_feature_id is blasted against only one 
database, so the query is quite straight forward. But there are instances 
where the scaffolds are blasted aganst 23 different organisms. In that 
case, in order to obtain the value against one particular database, I use 
much nested query going and getting the value from externaldatabase and 
externaldatabaserelease and then using it in similarity table.

I was wondering if anyone has any efficient way of querying these tables.

Thanks

Sucheta