1. We were very surprised to observe that the consistent gets for a query (shown below) changed (from 120K to 18K) when the only modification was adding a comment to it. How can this happen?
2. Furthermore, the plans for the two queries were identical. How can consistent gets differ if the plan is the same?
3. Is consistent gets the best metric for comparing the performance of two versions of a query, or is there a better one? (In the past we've tried to use execution timing for comparison, but the timing of a single query can vary greatly between runs.)
select
count(*) from (
SELECT source_id, project_id,
max_score,
fields_matched
FROM (SELECT source_id, project_id, MAX(scoring) as max_score,
apidb.tab_to_string(set(CAST(COLLECT(table_name) AS apidb.varchartab)), ', ') fields_matched,
max(index_name) keep (dense_rank first order by scoring desc, source_id, table_name) as index_name,
max(oracle_rowid) keep (dense_rank first order by scoring desc, source_id, table_name) as oracle_rowid
FROM ( SELECT SCORE(1) * (select nvl(max(weight), 1) from apidb.TableWeight where table_name = 'Blastp')
as scoring,
'apidb.blastp_text_ix' as index_name, b.rowid as oracle_rowid, b.source_id, b.project_id,
external_database_name as table_name
FROM apidb.Blastp b
WHERE CONTAINS(b.description, 'protein', 1) > 0
AND 'Blastp' IN ('Product', 'Notes', 'Comments', 'InterPro', 'EcNumber', 'GoTerms', 'Phenotype', 'Notes', 'and the rest')
AND 'gene' = 'gene'
AND b.pvalue_exp < -30
AND b.query_organism IN ('Plasmodium falciparum', 'Plasmodium vivax', 'Plasmodium yoelii', 'Plasmodium berghei', 'Plasmodium chabaudi', 'Plasmodium knowlesi')
UNION
SELECT
SCORE(1)* nvl(tw.weight, 1)
as scoring,
'apidb.gene_text_ix' as index_name, gt.rowid as oracle_rowid, gt.source_id, gt.project_id, gt.field_name as table_name
FROM apidb.GeneDetail gt, apidb.TableWeight tw, apidb.GeneAttributes ga
WHERE CONTAINS(content, 'protein', 1) > 0
AND gt.field_name IN ('Product', 'Notes', 'Comments', 'InterPro', 'EcNumber', 'GoTerms', 'Phenotype')
AND 'gene' = 'gene'
AND gt.field_name = tw.table_name(+)
AND gt.source_id = ga.source_id
AND ga.species IN ('Plasmodium falciparum', 'Plasmodium vivax', 'Plasmodium yoelii', 'Plasmodium berghei', 'Plasmodium chabaudi', 'Plasmodium knowlesi')
UNION
SELECT SCORE(1) * nvl(tw.weight, 1)
as scoring,
'apidb.isolate_text_ix' as index_name, wit.rowid as oracle_rowid, wit.source_id, wit.project_id, wit.field_name as table_name
FROM apidb.IsolateDetail wit, apidb.TableWeight tw
WHERE CONTAINS(content, 'protein', 1) > 0
AND wit.field_name in ('fred')
AND 'gene' = 'isolate'
AND wit.field_name = tw.table_name(+)
)
GROUP BY source_id, project_id
ORDER BY max_score desc, source_id
)
);