Re: mystic query (???)

vuht2000_at_yahoo.com wrote:
> 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

Cost Based Optimizer (CBO) requires two things:

1. Read the documentation
2. Statistics generated using DBMS_STATS.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)