Re: Slow UNIONs (ORA 8.0.1.)

Pavel Vetesnik wrote:

> Greetings,
>
> I have a VIEW made as UNION of serveral tables.
> Those are tables of books, magazines, CD-ROMs and on-line resources of our
> library. Each record has numeric ID which represents a topic of the document
> (ie. architecture=1, transportation=10 etc.). They have also a name of the
> document and some other data. The tables are indexed by those topic IDs and
> names.
>
> For example:
> CREATE TABLE cdr_hlavni
> (id NUMBER(10), CONSTRAINT cdr_hlavni_id PRIMARY KEY
> (id),
> signatura CHARACTER VARYING(30),
> cdr_name CHARACTER VARYING(100), --<< "name if the
> document"
> vydavatel CHARACTER VARYING(100),
> issn CHARACTER VARYING(100),
> keywords CHARACTER VARYING(100),
> url CHARACTER VARYING(500),
> psh1 NUMBER(10)) --<< This is the "topic ID"
>
> CREATE INDEX cdr_hlavni_psh1 ON cdr_hlavni(psh1);
> CREATE INDEX cdr_hlavni_cdrname ON cdr_hlavni(cdr_name);
>
> All tables were merged into one UNION VIEW (usion UNION ALL clause). The
> name of the view is VW_STK.
>
> And the problem:
> If I try to SELECT the VW_STK for some topic IDs, I get answers within
> milliseconds. But if I try to order the resultset by the names, the query
> time is about 10 seconds then!
> For example:
> SELECT * FROM vw_stk WHERE psh1=1; -- 0.09 seconds
> SELECT * FROM vw_stk WHERE psh1=1 ORDER BY cdr_name; -- 9.42 seconds
>
> My question is why is this? The tables are indexed, so it should not slow it
> down. If I query the only table, then the answer is fast. But querying an
> union-view makes it so slow.
>
> I know that my explanation was not so clear and I also simplified the
> examples. But if anybody could help me in fine-tuning the unions, I will
> appreciate it.
>
> TIA,
> Pavel

Why is it that so many people believe building an index means it will be used? Nothing I've ever seen in books, magazines, web sites, etc. supports that assumption.

Lets start with some basics. What is ORA 8.0.1? Are you referring to a version of Oracle about a decade old or is this a mistype of 8i and you meant 8.1.#?

Which optimizer mode are you using RBO or CBO?

If CBO have you collected statistics on your tables and indexes?

Have you run an explain plan on any of these statements or are you just guessing?

Sorry to be a bit harsh here but while it is true that ORDER BY is expensive it is not "that" expensive. I'd suggest you return to basics as my guess is your indexes are being ignored.

-- 
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)