Re: When did inline views arrive?
Date: 1996/06/12
Message-ID: <4pmkom$3oe_at_raffles.technet.sg>#1/1
From: tkyte_at_us.oracle.com (Thomas J. Kyte)
Reply-To: tkyte_at_us.oracle.com
Newsgroups: comp.databases.oracle
Subject: Re: When did inline views arrive?
Date: Wed, 12 Jun 1996 03:05:51 GMT
Organization: Oracle Corporation
Message-ID: <31be322d.3492852_at_dcsun4>
References: <4pa5sp$j9l_at_vip.cybercity.dk> <31BDEA54.1AED_at_thomtech.com>
It was introduced unofficially with 7.1 and became a support feature with 7.2. PL/SQL doesn't like inline views until 2.3 (v7.3 of the database) however. You need to use dbms_sql in 7.2 and before to use inline views in stored procedures. 7.1 doesn't document it cause it was at that point an undocumented/unsupported feature.
In 7.2 it is documented however.
I agree it is *very* useful. Especially in a multiple many to many relationship. For example to list an department, a count of emps in that dept and a count of addresses (or some other relationship) you had to run a series of queries.. Now you can:
select a.*, b.cnt, c.cnt
from dept a,
( select deptno, count(*) from emp group by deptno ) b
( select deptno, count(*) from address group by deptno ) c
where a.deptno = b.deptno
and a.deptno = c.deptno
/
My favorite use of this is the following sql*plus script:
column dummy noprint
column pct_used format 999.9 heading "%|Used" column name format a16 heading "Tablespace Name"column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name ) b
where a.tablespace_name (+) = b.tablespace_name /
Which shows the free space by tablespace. It used to take a join of views prior to the inline view capability....
On Tue, 11 Jun 1996 14:51:16 -0700, ashok kapur <akapur_at_thomtech.com> wrote:
>Torben Krogh Jeppesen wrote:
>>
>> Hey, out there.
>>
>> Does anybody know when the "inline view" came into being on Oracle
>> databases?
>>
>> An inline view comes in handy when you want to count the number of
>> distinct values in some column:
>>
>> SELECT COUNT(*)
>> FROM
>> ( SELECT DISTINCT SOME_FIELD
>> FROM SOME_TABLE)
>> ;
>>
>> I know there are other ways to do this, but in my opinion this is the
>> most logical way to express it.
>>
>> I have just recently discovered the feature, and I have never seen any
>> documentation from Oracle. The syntax diagram in Server SQL Language
>> Reference does not describe the inline view as a possibiliy and Forms
>> 3.0 and PRO*C certainly don't like it, yet SQL*PLUS has no problem
>> whatsoever.
>>
>> Torben Jeppesen
>>
>> Computer consultant, PMP Software, Denmark
>> Email: tkjeppesen_at_vip.cybercity.dkI believe that they were introduced in version 7.1.6
>--
>Ashok Kapur (akapur_at_thomtech.com)
>Thomson Technology Consulting Group
>1375 Piccard Drive, Suite 250
>Rockville, MD 20850
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Jun 12 1996 - 00:00:00 CEST