Re: Display only part of resultset (ORA 8.1.7)

Pavel Vetesnik wrote:

> 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

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

Don't use the view! Use the underlying query instead; it'll give Oracle the opportunity to use a better plan.

Rule # 7: do not use views in joins (modified)

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel