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(at)candle(dot)pha(dot)pa(dot)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)