Re: Slow UNIONs (ORA 8.0.1.)

my responses are inline...

"Pavel Vetesnik" <Pavel.Vet_at_volny.cz> wrote in message news:btmf8j$2nik$1_at_ns.felk.cvut.cz...
> 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.

This means there is alot more data.... this 'merge' is not indexed. So indexes here would not be useful.

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

order by is always more costly. Are you writing to your temp tablespace in order to do the order by? If you have a large result set and a small PGA then your order revolves writes to the database. This can really slow things down.

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

index has nothing to do with order by.
>
> 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.

I can't tell you how to fix this since you don't know enough to even diagnose the problem. You need to do the following.

1. ORACLE_HOME/rdbms/admin/utlxplan.sql

Read that file and install it.

2. Go to otn.oracle.com and go to the documentation. do a search for 'explain plan' and read the documentation.

Learn how to understand queries. You have alot to learn. Noone here can help you any further until you educate yourself. All we can do is point you to the documentation.

>
> TIA,
> Pavel
>
>
Received on Fri Jan 09 2004 - 11:16:37 CST