Home » SQL & PL/SQL » SQL & PL/SQL » Inline View (PL/SQL, Oracle10g)
Inline View [message #345744] Thu, 04 September 2008 12:42 Go to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
Please help me better understand inline views. Here's a simple example:

SELECT COUNT(*) FROM (SELECT DISTINCT(TABLE_NAME)
FROM USER_TAB_COLUMNS);

My expectation is such that the resulting table name from the query with the (), completes the "FROM" statement. Thus, i expected it to perform a query against the resulting table name and return a count of however many number of records are found.

To be a little more detailed, say for instance that the resulting table name is "TEST". I am then thinking that the above query will appear in the background as:

SELECT COUNT(*) FROM (TEST);

I should then expect that that it will return the number of records found in table TEST.

However, i keep getting a returned value of '1', when in fact, there are more than 1 record in the table if I query the table directly.

Do I have a wrong understanding of Inline views? Am I doing something wrong?

Your quick feedback will be appreciated.

Thanks.

Greg
Re: Inline View [message #345746 is a reply to message #345744] Thu, 04 September 2008 12:54 Go to previous messageGo to next message
andydba
Messages: 34
Registered: September 2008
Member

Hi,

This would work like this:

SELECT COUNT(*) FROM (SELECT DISTINCT(TABLE_NAME)
FROM USER_TAB_COLUMNS);

The inline view
(SELECT DISTINCT(TABLE_NAME) FROM USER_TAB_COLUMNS)
would return all distinct table names from USER_TAB_COLUMNS, then oralce internally assigns a name to this result set and the outer query would be executed against that result set.

when you say "select * from table_name" you are actually referring to a dataset which is known as table_name. An oracle table is nothing but a data set. Inline view is a feature that let's you build a dataset on the fly using other data sets.

-- Andy
Re: Inline View [message #345748 is a reply to message #345744] Thu, 04 September 2008 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
there are more than 1 record in the table if I query the table directly.

Which one?
Copy and paste your SQL*Plus session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Inline View [message #345761 is a reply to message #345746] Thu, 04 September 2008 13:45 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
andydba wrote on Thu, 04 September 2008 13:54
Hi,

This would work like this:

SELECT COUNT(*) FROM (SELECT DISTINCT(TABLE_NAME)
FROM USER_TAB_COLUMNS);

The inline view
(SELECT DISTINCT(TABLE_NAME) FROM USER_TAB_COLUMNS)
would return all distinct table names from USER_TAB_COLUMNS, then oralce internally assigns a name to this result set and the outer query would be executed against that result set.

when you say "select * from table_name" you are actually referring to a dataset which is known as table_name. An oracle table is nothing but a data set. Inline view is a feature that let's you build a dataset on the fly using other data sets.

-- Andy


Re: Inline View [message #345764 is a reply to message #345761] Thu, 04 September 2008 14:03 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
Hi Andy,

Thanks for the quick reply.

I read your email reply but I'm not sure i understood it completely. My problem is how i should write the SQL so that i'm able to use the inline view to query against a particular table.

Let me give you a copy of the actual script that i'm working on to give you an idea of what i'm trying to accomplish. You can run the script and you'll see what i mean.

After looking at the attached script, i hope you can give me some answeres to these questions: 1) why is it not performing the query against table TEST when there is only one unique table name returned, and 2) how should i write the SQL so that it performs the query against table TEST, using the inline view?

Thanks.

Greg

Re: Inline View [message #345768 is a reply to message #345764] Thu, 04 September 2008 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You really does not understand what is an inline view.
An inline view is like a table.
When you "select from table" you don't select inside the row that are in the table.
If you want, "select count(*) from (select col from tab)" is like "create table x as select col from tab)" followed by "select ... from x", you don't "select from values in col".

Regards
Michel
Re: Inline View [message #345772 is a reply to message #345768] Thu, 04 September 2008 14:37 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
Michel Cadot wrote on Thu, 04 September 2008 15:32
You really does not understand what is an inline view.
An inline view is like a table.
When you "select from table" you don't select inside the row that are in the table.
If you want, "select count(*) from (select col from tab)" is like "create table x as select col from tab)" followed by "select ... from x", you don't "select from values in col".

Regards
Michel


Re: Inline View [message #345774 is a reply to message #345772] Thu, 04 September 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your purpose repeating others post?

Regards
Michel

[Updated on: Thu, 04 September 2008 14:41]

Report message to a moderator

Re: Inline View [message #345775 is a reply to message #345772] Thu, 04 September 2008 14:41 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
Hi Michel,

I think i was clear in my first email that i need to understand inline view. It appears from your email reply that i shouldn't be using an inline view for what i'm trying to do. But i'm still at a loss as to what to do or how to write the script so i can get the correct output.

Do you have any recommendation?

Thanks.

Greg
Re: Inline View [message #345776 is a reply to message #345775] Thu, 04 September 2008 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem you're trying to achieve has already been asked here.
I think you can find the answer.

Regards
Michel
Re: Inline View [message #345778 is a reply to message #345774] Thu, 04 September 2008 14:45 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
My apologies Michel. I was not aware i'm repeating any posts.

Greg
Re: Inline View [message #345780 is a reply to message #345778] Thu, 04 September 2008 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use dynamic SQL ("execute immediate") to achieve what you want.

Regards
Michel
Re: Inline View [message #345794 is a reply to message #345780] Thu, 04 September 2008 17:10 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Greg -
As Michel states, you definitely need to use EXECUTE IMMEDIATE
to determine this dynamically (without writing a select count
query for every table in your schema).

When my company got bought out and our systems integrated,
the new regime wanted to know what fields were not used
on a backend DB created by a 3rd party vendor for a system
used to enter claim information, that they were junking.

I was out that day and came in to find my development group frantically querying counts against individual data fields - a lot of our tables had more than a hundred fields - to see which ones were null or blank, which numerics always had zero, etc.

I simply wrote a proc to take in the table and field names (and datatypes) from the all_tab_cols table, and then based on
the field-type, executed the appropriate query.

I'm never at a terminal when I am on this group so I dont have the proc handy but would be glad to assist along the way after you look into EXECUTE IMMEDIATE.

(The real pain in the neck was dynamically searching for blanks when a varchar2(N) can have from 1 to N blanks!)
Regards,
Harry
Re: Inline View [message #345795 is a reply to message #345744] Thu, 04 September 2008 17:17 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I just wanted to add, Andy, that I very much liked your
explanation of inline views:

Quote:


when you say "select * from table_name" you are actually referring to a dataset which is known as table_name. An oracle table is nothing but a data set. Inline view is a feature that let's you build a dataset on the fly using other data sets.




When mentoring junior programmers at work, I try to overcome their fear of seeing the "Select from Hell" that goes on for pages by telling them there is still only a 'SELECT', a 'FROM' and a 'WHERE' - find them first at the outer level -
think of the nested select as just a field and the FROM (SELECT...) as just a table.

Your wording communicates very effectively.

Thanx
Harry
Re: Inline View [message #345805 is a reply to message #345764] Thu, 04 September 2008 21:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If I understand the problem correctly, you are trying to identify unpopulated columns. You are looking for columns that do not have any non-null values in any row. A simple way to do this is to analyze your schema using dbms_stats, then select those tables and columns where the number of rows (user_tables.num_rows) is the same as the number of null rows for a given column (user_tab_columns.num_nulls). Alternatively, you could use some dynamic sql as previously suggested. I have demonstrated both methods below. The first method is likely to be much faster and analyzing your tables and gathering statistics is something that should be done routinely anyhow.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (USER)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT t.table_name, c.column_name
  2  FROM   user_tables t, user_tab_columns c
  3  WHERE  t.table_name = c.table_name
  4  AND    t.num_rows = c.num_nulls
  5  ORDER  BY t.table_name, c.column_name
  6  /

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          ENAME
BONUS                          JOB
BONUS                          SAL
DIET                           BIOTIN
DIET                           CHROMIUM
DIET                           IODINE
DIET                           MOLYBDENUM
DUPLICATES                     ID1
DUPLICATES                     ID2
DUPLICATES                     NAME_AND_AD1
DUPLICATES                     NAME_AND_AD2
EDP_CURRICIULUM                CTX_RESERVED
ORDERS                         ADDRESS
ORDERS                         AMOUNT
ORDERS                         CITY
ORDERS                         COMPANY
ORDERS                         ITEMS
ORDERS                         ORDERED
ORDERS                         PAYMENT
ORDERS                         PHONE
ORDERS                         RECEIVED
ORDERS                         SHIPPING
ORDERS                         STATE
ORDERS                         WEB
ORDERS                         ZIP

26 rows selected.

SCOTT@orcl_11g>


SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION is_empty
  2    (p_table_name  IN user_tab_columns.table_name%TYPE,
  3  	p_column_name IN user_tab_columns.column_name%TYPE)
  4    RETURN		 VARCHAR2
  5  AS
  6    v_empty		 VARCHAR2 (3);
  7  BEGIN
  8    EXECUTE IMMEDIATE
  9  	 'SELECT ''NO''
 10  	  FROM	 DUAL
 11  	  WHERE  EXISTS
 12  		 (SELECT *
 13  		  FROM "' || p_table_name || '"
 14  		  WHERE "' || p_column_name || '" IS NOT NULL)
 15  	  UNION ALL
 16  	  SELECT ''YES''
 17  	  FROM	 DUAL
 18  	  WHERE  NOT EXISTS
 19  		 (SELECT *
 20  		  FROM "' || p_table_name || '"
 21  		  WHERE "' || p_column_name || '" IS NOT NULL)'
 22  	INTO v_empty;
 23    RETURN v_empty;
 24    EXCEPTION
 25  	 WHEN OTHERS THEN RETURN 'UNK';
 26  END is_empty;
 27  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT t.table_name, c.column_name
  2  FROM   user_tables t, user_tab_columns c
  3  WHERE  t.table_name = c.table_name
  4  AND    is_empty (t.table_name, c.column_name) = 'YES'
  5  ORDER  BY t.table_name, c.column_name
  6  /

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          ENAME
BONUS                          JOB
BONUS                          SAL
DIET                           BIOTIN
DIET                           CHROMIUM
DIET                           IODINE
DIET                           MOLYBDENUM
DUPLICATES                     ID1
DUPLICATES                     ID2
DUPLICATES                     NAME_AND_AD1
DUPLICATES                     NAME_AND_AD2
EDP_CURRICIULUM                CTX_RESERVED
ORDERS                         ADDRESS
ORDERS                         AMOUNT
ORDERS                         CITY
ORDERS                         COMPANY
ORDERS                         ITEMS
ORDERS                         ORDERED
ORDERS                         PAYMENT
ORDERS                         PHONE
ORDERS                         RECEIVED
ORDERS                         SHIPPING
ORDERS                         STATE
ORDERS                         WEB
ORDERS                         ZIP

26 rows selected.

SCOTT@orcl_11g>


Re: Inline View [message #346471 is a reply to message #345805] Mon, 08 September 2008 12:22 Go to previous messageGo to next message
gcarroyo
Messages: 7
Registered: July 2007
Junior Member
Thanks a lot Barbara and to all others for spending your time and effort to help me out.

Greg
Re: Inline View [message #354704 is a reply to message #346471] Mon, 20 October 2008 22:40 Go to previous messageGo to next message
wmensah
Messages: 4
Registered: October 2008
Junior Member
select count(distinct(table_name)) from user_tab_columns;

COUNT(DISTINCT(TABLE_NAME))
---------------------------
14
Re: Inline View [message #354777 is a reply to message #354704] Tue, 21 October 2008 03:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
wmensah wrote on Mon, 20 October 2008 20:40
select count(distinct(table_name)) from user_tab_columns;

COUNT(DISTINCT(TABLE_NAME))
---------------------------
14



All that does is provide the number of tables in the schema and is not even the most efficient method of doing that. It has nothing to do with what the original poster wanted, which is a list of unpopulated columns. So, you have provided a useless response to a thread that was already answered. In the future, please read the entire thread before responding, rather than just taking the first post in the thread out of context.

Re: Inline View [message #354873 is a reply to message #354777] Tue, 21 October 2008 13:55 Go to previous messageGo to next message
wmensah
Messages: 4
Registered: October 2008
Junior Member
my bad......u don't have to snap!
Re: Inline View [message #354875 is a reply to message #354873] Tue, 21 October 2008 14:38 Go to previous messageGo to next message
wmensah
Messages: 4
Registered: October 2008
Junior Member
ok, let's see....does that mean the inline view does not return a table? Greg got it but i don't.

cs440> begin
2 for cur in (select table_name from user_tab_columns) loop
3 select * from cur.table_name;
4 end loop;
5 end;
6 /
select * from cur.table_name;
*
ERROR at line 3:
ORA-06550: line 3, column 19:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


is that saying cur.table_name is not a table?

ps: i've read the above posts and still don't get it (i know datasets was mentioned also) so if anyone would be kind enough to explain what's going on. Thanks.
Re: Inline View [message #354877 is a reply to message #354875] Tue, 21 October 2008 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a variable name as object name in static SQL. For Oracle "cur.table_name" is table named "table_name" in schema "cur".

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Inline View [message #354879 is a reply to message #345744] Tue, 21 October 2008 15:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is my simple understanding of inline views:

An inline view is a construct in Oracle SQL where you use a QUERY instead of a table name, usually in the from clause. Consider the following SQL:

select * from dual;
select * from (select * from dual);
select * from (select * from (select * from dual));

Quote:
Q: What are these three selects?
A: They are exactly the same select.

Indeed, inline views brings up the excellent topic of Oracle Query Transformation. Oracle almost never executes the SQL you give it. Instead it translates and reduces and simplifies the SQL you give into something else and then executes that. You can be assured that whatever Oracle executes, it is SEMANTICALLY EQUIVELANT to what you gave it (Oracle is still asking the same question you asked, just with "more optimized" SQL). To understand what I mean, consider this:

select * from dual                                  is executed as  select * from dual;
select * from (select * from dual)                  is executed as  select * from dual;
select * from (select * from (select * from dual))  is executed as  select * from dual;

You gave Oracle three different SQL statements, but it executed the same thing internally because it could figure out to reduce/simplify your bloated versions to something easier. Each has the same execution plan.

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Now consider this

with
     little_dual as (select * from dual)
select *
from little_dual
/

What does this execute as? You better be thinking select * from dual;

I look upon the WITH CLAUSE as another form of inline view.

So to recap, my basic description of inline view is: within the scope of an Oracle query, an inline view is another query that takes the place of a table. Notice that inline views are relevant only inside a larger SQL statement which is why your plsql did not parse.

Hope this helps. Kevin
Re: Inline View [message #355053 is a reply to message #345744] Wed, 22 October 2008 07:08 Go to previous messageGo to next message
wmensah
Messages: 4
Registered: October 2008
Junior Member
oh i see. That makes a lot of sense. Thanks.
Re: Inline View [message #355624 is a reply to message #345744] Sun, 26 October 2008 11:09 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I found this at the following link. It gives another piece of the puzzle about inline views. In particular I think this insert statement just below is most useful to an understanding, especially when mated with the author's comment:

Quote:
This has the same effect as if you used a view.


Oracle SQL tricks


Derived tables (from-clause subqueries, inline views):

  select * from (select * from emp);
  insert into (select deptno from dept)
    select trunc(a) from (select sum(empno)/5000 a from emp);

This has the same effect as if you used a view. It is useful for queries that have a repeated complex expression; for example

  select x from (select avg(sin(empno)) x from emp group by deptno)
    where x > 0;

It's also useful when you want to use one of the values found by a subquery, for example when deleting all outdated entries from a log:

  delete from log where rowid in
    (select rowid from log
     minus
     select a.rowid
       from log a, (select id, max(time) mtime from log group by id) b
       where a.id = b.id and a.time = b.mtime);

As of 9i, inline views can also be expressed with a "WITH clause", for example

  with sub1 as (select deptno, avg(sin(empno)) x from emp group by deptno)
  select a.deptno, a.x, b.x from sub1 a, sub1 b where a.deptno > b.deptno;


Kevin
Previous Topic: Receiving parameters to Sql script through concurrent program
Next Topic: want to put range on output
Goto Forum:
  


Current Time: Sun Dec 11 04:04:28 CST 2016

Total time taken to generate the page: 0.07547 seconds