"Pavel Vetesnik" <Pavel.Vet_at_volny.cz> wrote in message news:<bs9gp9$6vd$1_at_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
> >
> >
> >
> >
> >
your views as currently composed are forcing full table scans. see my other post.
Received on Fri Dec 26 2003 - 11:13:40 CST
