Re: Display only part of resultset (ORA 8.1.7)

From: Pavel Vetesnik <Pavel.Vet_at_volny.cz>
Date: Tue, 23 Dec 2003 14:44:57 +0100
Message-ID: <bs9gp9$6vd$1@ns.felk.cvut.cz>

OK,

thanks all of you (Ryan, Frank, Rainer & Noel).

My code looks like this:

---

SELECT * FROM
    (SELECT nazev, psh1, rownum rnum FROM

        (SELECT nazev, psh1
            FROM (SELECT nazev, psh1 FROM vw_stk WHERE id_psh1=10 ORDER BY
nazev) a
            WHERE ROWNUM<=120))
        WHERE rnum>=100;
----------------------------------------------------------------------------

The only problem is the access time. It takes about 3 seconds (+-60 thousands records view). But it seems the best time achieved and it is not problem of the code above, but because of design.

The vw_stk is view containing UNION of several tables. When I tried the code above to a table, then access time was just great! It seems that Oracle has some problems with UNIONs (or I don't know how to use them correctly).

I created a little test and run the code above on table with 60.000 records. Query time was 0.02 seconds. Great!
When I run it on the view which was an union of the "60.000 table" and simillar "3.000 table", the query time was 2.55 seconds. Why it is so different? I even made the view as "UNION ALL" (because the view made as UNION only lasted 73.5 seconds!).

So please can you help me how to correctly merge several tables to one view to achieve good query speed?

Thank you,
Pavel

"Noel" <tbal_at_go2.pl> píše v diskusním příspěvku news:bs67jt$9oh$1_at_inews.gazeta.pl...
>
> > ------------------------------------------
> > SELECT nazev, psh1
> > FROM (SELECT nazev, psh1 FROM vw_stk WHERE id_psh1=10 ORDER BY
nazev)
> > WHERE ROWNUM<=20;
> > ------------------------------------------
> > The problem is, that I can't take other records (only first ones) using
> this
> > technique (condition WHERE ROWNUM>20 AND ROWNUM<=40 can't work, because
> > ROWNUM is just pseudocolumn created dynamically).
>
> You can use rownum:
>
> SELECT nazev, psh1
> FROM (
> SELECT rownum rn, nazev, psh1
> FROM vw_stk
> WHERE id_psh1=10
> ORDER BY nazev) a
> WHERE A.RN <= 20;
>
> --
> Noel
>
>
>
>
>
Received on Tue Dec 23 2003 - 07:44:57 CST