Slow UNIONs (ORA 8.0.1.)

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 Received on Fri Jan 09 2004 - 08:57:49 CST