Re: Let's talk up 6.3

>
> I have made no indices yet.  And these are the only two tables
> in the database (beside the system ones).

No indexes.  No wonder it takes so long.  Put an index on
mdc1_runs.runnum and mdc1_simu.version, and see how fast it is.  Did
Oracle manage to do this quickly without the indexes?

Having it crash is certainly not an acceptable outcome, but I am sure
indexes will fix the problem.

Now, the fact that it runs quickly as separate queries, even without the
indexes, but takes a long time with the indexes, I think is
understandable.  Think of a join of two tables.  You can do through each
quickly, but if you join two non-indexed fields, it will take quite some
time.  I think our subselect code is doing just that.  We designed it
that way to give good performance for the majority of subselects,
including correlated ones.


>
> bbrmdc=> explain verbose select distinct runtype from mdc1_runs where runnum in
> bbrmdc-> (select runnum from mdc1_simu where version = '4.3.7g');
> NOTICE:  QUERY PLAN:
>
>
> Unique  (cost=686.02 size=0 width=0)
>   ->  Sort  (cost=686.02 size=0 width=0)
>         ->  Seq Scan on mdc1_runs  (cost=686.02 size=1455 width=12)
>               SubPlan
>                 ->  Seq Scan on mdc1_simu  (cost=733.02 size=1 width=12)
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)