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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] Date: 2003/07/16 Wed AM 07:54:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: inline views
In one case, the statement's execution plan used a nested loop, where it read the local table then probed the index on the remote table. This caused several million trips across the network to retrieve less than 10,000 records (IIRC). select local.cola, local.colb, remote.colc, remote.cold from local, remote where local.cola = remote.cola; We rewrote the query to retrieve all of the data from the remote table then the nested loops was much more efficient. select local.cola, local.colb, remote_i.colc, remote_i.cold from local (select remote.cola, remote.colc, remote.cold from remote) remote_i where local.cola = remote_i.cola; VIVEK_SHARMA wrote: 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: inline views
What I remember from an old post regarding this issue, You don't have to create another object, view, to workaround your requirement. Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- VIVEK_SHARMA Sent: Friday, June 06, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Where are they advantageous to use where not ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ramon E. Estevez INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: inline views
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: Daniel W. Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: inline views
Title: RE: inline views it is also useful when you DBA won't allow you to create a new view ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Daniel W. Fink [mailto:[EMAIL PROTECTED]] Sent: Friday, June 06, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Subject: Re: inline views 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: Daniel W. Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: inline views
Here is one case where an inline view is adventageous. PL/SQL may work better, but the requirement is a single SQL statement. Display a single employee from each department. The employee displayed should be a random selection and not just the first one retrieved from the table. (extra columns added for explanatory purposes) SQL select e1.deptno, 2e1.empno, 3e1.dept_rank, 4e2.max_emps, 5s1.rand_num, 6mod(s1.rand_num, e2.max_emps)+1 filter_condition 7 from (select deptno, 8 empno, 9 rank() over (partition by deptno order by empno) as dept_rank 10 from emp) e1, 11 (select deptno, 12 count(deptno) max_emps 13 from emp 14 group by deptno) e2, 15 (select to_char(sysdate, 'ss') rand_num 16 from dual) s1 17 where e1.deptno is not null 18 and e1.deptno = e2.deptno 19 and e1.dept_rank = mod(s1.rand_num, e2.max_emps)+1; DEPTNO EMPNO DEPT_RANK MAX_EMPS RA FILTER_CONDITION -- -- -- -- -- 10 7934 3 3 263 20 7566 2 5 262 30 7654 3 6 263 -- 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: Daniel W. Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
