mystic query (???)

friends,
I have this query:
INSERT INTO tag_class (tp_id, tag_17, tag_20, chr_id, strand, position, class, gene, model, exon_id)
SELECT a.tp_id, a.tag_17, a.tag_20, a.chr_id, a.strand, a.position, 3 as class, b.gene, b.model, c.exon_id
FROM tag_position a, generic_gene_master b, gene_position c WHERE a.chr_id=b.chr_id

   and a.strand <> b.strand
   and (a.position between b.class_start and b.class_end)    and b.gene=c.gene and b.model=c.model    and (a.position between c.coord_start and c.coord_end);

indexes are set. I ran it in 2 machines, 1 is PC/Linux with Oracle 9.2.0.1, the other is Dell PowerEdge 2650, dual CPU, 3.2GHz, with Oracle 9.2.0.5. The 2 Oracle sites are newly setup and everything is left default (except such things as avoiding SYSTEM tablespace...). On the PC the plan shows that indexes are used and the query runs in 2 mins 30 sec. While on the server, the plan doesn't show any index, only fts. When I added /*+rule*/, yes, it shows indexes. This is my first question: why doesn't it use indexes without hint (all tables are analyzed). But even after I added the hint, the query still runs forever (I break it after 1 hours). So what are the other things that hurt my query?
looking forwards to your help,
Thanks,
Tam Received on Thu May 06 2004 - 13:31:10 CDT