Re: RE: inline views

you can get the same performance improvements by passing a REF Cursor out to the client also.

basically he is saying that if you are over a network particularly in a web application where you cant always control the speed of the internet access that the client is using and you do:

select column
from table
where column = 1;

Oracle will do a series of 'fetches' across SQLNET. Which it will get some records, then go back and request some more and so on... this means you have alot of network trips. Since your not getting everything at once. So you increase network traffic with the additional 'requests' and you increase wait time just going back and forth.

Its like a busdriver competing against a sports car driver to see who can get the most people to an end location. Bus is slow but can carry alot of people per trip and the sports car is faster but can only carry a few people per trip.

So the inline view does more work up front on the database side:

select col
from (select col from table where x = 1)

so you are 'shrinking' the size of the table you are selecting from.

Now the way I have done it is to pass a REF Cursor, which I think(though I have not tested it) gives even better performance. Why?

Well you are saying on the database side:

Get everything from this query and put it in the REF Cursor, then in one pass send the whole REF Cursor to the client.

Also you need inline views to solve some questions. Such as how to get records M through N from a resultset.
>
> From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infosys.com>
> Date: 2003/07/16 Wed AM 07:54:25 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: inline views
>
> Daniel, List
>
> Can you give more detail with an EXAMPLE please ?
>
> Thanks
>
>
> -----Original Message-----
> Sent: Friday, June 06, 2003 7:05 PM
> To: Multiple recipients of list ORACLE-L
>
> I have used an inline view to reduce network traffic when retrieiving
> data from a remote db. Instead of using a nested loop and making
> multiple trips, it made 1 trip and brought over all of the data. The
> query time was reduced from 30 minutes to 5 minutes.
>
> --
> Daniel W. Fink
> http://www.optimaldba.com
>
>
> VIVEK_SHARMA wrote:
>
> >Where are they advantageous to use & where not ?
> >
> >Thanks
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infosys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Jul 16 2003 - 06:28:42 CDT