On Wednesday, June 12, 2013 11:29:03 AM UTC-6, Dom wrote:
>
> In both SQL and Oracle, I can do this: "Select *", where * means all the
> fields in all the tables in the FROM clause.
>
> But in SQL, and NOT IN ORACLE, I can also do this: "Select len
> (p.Last_Name), *", where one field is singled out for special attention,
> and given the qualifer "P", and * once again means all fields from all
> tables.
>
> Why does this not work in Oracle? It seems that once a field has been
> qualified, then "*" will not work, and I have to do a.*, b.*, etc for each
> table in the FROM clause.
>
It's how the statement is parsed and executed, really. Once you 'qualify'
a column, by name, table alias or both, Oracle does not presume you want
all columns from all joined tables; let's look at a portion of a tracefile
processed with tkprof to see what Oracle is actually doing:
********************************************************************************
SQL ID: 9nmsrvn7xy0s1 Plan Hash: 3969568374
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4")
FROM
(SELECT /*+ NO_PARALLEL("EMP") FULL("EMP") NO_PARALLEL_INDEX("EMP") */
:"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "EMP"."DEPTNO" AS C3 FROM
"GRIBNAUT"."EMP" "EMP") SAMPLESUB
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 2
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6 0.00 0.00 0 14
0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 227 (recursive depth: 1)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=361
us)
3 3 3 VIEW VW_DAG_0 (cr=7 pr=0 pw=0 time=331
us cost=3 size=3198 card=82)
3 3 3 HASH GROUP BY (cr=7 pr=0 pw=0 time=327
us cost=3 size=1066 card=82)
14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0
time=55 us cost=2 size=1066 card=82)
The above query pre-processes the EMP table; its results will be used later.
********************************************************************************
SQL ID: 1qvrxdwjd5rs8 Plan Hash: 481548034
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4")
FROM
(SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */
:"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "DEPT"."DEPTNO" AS C3 FROM
"GRIBNAUT"."DEPT" "DEPT") SAMPLESUB
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 2
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6 0.00 0.00 0 14
0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 227 (recursive depth: 1)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=307
us)
4 4 4 VIEW VW_DAG_0 (cr=7 pr=0 pw=0 time=287
us cost=3 size=3198 card=82)
4 4 4 HASH GROUP BY (cr=7 pr=0 pw=0 time=283
us cost=3 size=1066 card=82)
4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0
time=41 us cost=2 size=1066 card=82)
This statement does the same thing for DEPT that it did for EMP; again
these results will be used later.
********************************************************************************
SQL ID: asjr87uffn42s Plan Hash: 615168685
select *
from
emp join dept on dept.deptno = emp.deptno
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 2
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 15
0 14
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.00 0.00 0 17
0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 227
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
14 14 14 HASH JOIN (cr=15 pr=0 pw=0 time=230 us
cost=7 size=1638 card=14)
4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0
time=39 us cost=3 size=120 card=4)
14 14 14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0
time=20 us cost=3 size=1218 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 3 0.00
0.00
SQL*Net message from client 3 0.01
0.01
SQL*Net break/reset to client 2 0.00
0.00
Now Oracle uses the two indivitual result sets to produce the final result
using a hash join between them.
Unfortunately (for you, apparently) Oracle can't parse the '*' for all
joined tables when a specific column is named, and it runs into even more
difficulty should a common column be listed without qualification. Since
the statement didn't execute it's not found in the tracefile output. The
missing statement is:
SQL> select length(empno), * from emp join dept on dept.deptno = emp.deptno;
select length(empno), * from emp join dept on dept.deptno = emp.deptno
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
Once a column is specifically listed in a SELECT statement adding the '*'
makes it impossible to parse the '*' without a qualifier since Oracle now
takes the '*' as a column name, not a placeholder for 'all columns from all
joined tables', and it does require one for each table in the join so that
all desired columns are returned. The statement shown below illustrates
this:
********************************************************************************
SQL ID: c7vt48vf8hdfk Plan Hash: 615168685
select length(empno), emp.*, dept.*
from
emp join dept on dept.deptno = emp.deptno
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 2
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 15
0 14
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.00 0.00 0 17
0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 227
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
14 14 14 HASH JOIN (cr=15 pr=0 pw=0 time=198 us
cost=7 size=1638 card=14)
4 4 4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0
time=41 us cost=3 size=120 card=4)
14 14 14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0
time=20 us cost=3 size=1218 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 2 0.00
0.00
SQL*Net message from client 2 0.00
0.00
********************************************************************************
Of course you're not just adding a column, you're adding a column processed
by a single-value function. Even if the column is listed without the
function call Oracle still changes the intent of the '*' from what you
think it means to what Oracle thinks, which is a column name:
SQL> select empno, * from emp join dept on dept.deptno = emp.deptno;
select empno, * from emp join dept on dept.deptno = emp.deptno
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
As I stated before once you make a comma-delimited column list all values
after the comma will be taken as column names thus the ', * from ...'
generates the error you see as there is no column named '*' in the table.
So let's add a column named * to both tables:
SQL> alter table emp add "*" number;
Table altered.
SQL>
SQL> alter table dept add "*" number;
Table altered.
SQL>
Now let's try one of the failing queries again:
SQL> select empno, "*" from emp join dept on dept.deptno = emp.deptno;
select empno, "*" from emp join dept on dept.deptno = emp.deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL>
Now we have columns named * but we still run into an error -- the columns
are 'ambiguously defined' as they occur in both tables. Running the
working, qualified query returns different results:
SQL> select length(empno), emp.*, dept.* from emp join dept on dept.deptno
= emp.deptno;
LENGTH(EMPNO) EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO * DEPTNO DNAME LOC
*
------------- ---------- ---------- --------- ---------- ---------
---------- ---------- ---------- ---------- ---------- -------------- ---
---------- ----------
4 7369 SMITH CLERK 7902 17-DEC-80
800 20 20 RESEARCH DALLAS
4 7499 ALLEN SALESMAN 7698 20-FEB-81
1600 300 30 30 SALES CHICAGO
4 7521 WARD SALESMAN 7698 22-FEB-81
1250 500 30 30 SALES CHICAGO
4 7566 JONES MANAGER 7839 02-APR-81
2975 20 20 RESEARCH DALLAS
4 7654 MARTIN SALESMAN 7698 28-SEP-81
1250 1400 30 30 SALES CHICAGO
4 7698 BLAKE MANAGER 7839 01-MAY-81
2850 30 30 SALES CHICAGO
4 7782 CLARK MANAGER 7839 09-JUN-81
2450 10 10 ACCOUNTING NEW
YORK
4 7788 SCOTT ANALYST 7566 09-DEC-82
3000 20 20 RESEARCH DALLAS
4 7839 KING PRESIDENT 17-NOV-81
5000 10 10 ACCOUNTING NEW
YORK
4 7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30 30 SALES CHICAGO
4 7876 ADAMS CLERK 7788 12-JAN-83
1100 20 20 RESEARCH DALLAS
LENGTH(EMPNO) EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO * DEPTNO DNAME LOC
*
------------- ---------- ---------- --------- ---------- ---------
---------- ---------- ---------- ---------- ---------- -------------- ---
---------- ----------
4 7900 JAMES CLERK 7698 03-DEC-81
950 30 30 SALES CHICAGO
4 7902 FORD ANALYST 7566 03-DEC-81
3000 20 20 RESEARCH DALLAS
4 7934 MILLER CLERK 7782 23-JAN-82
1300 10 10 ACCOUNTING NEW
YORK
14 rows selected.
SQL>
SQL> select length(empno), emp."*", dept."*" from emp join dept on
dept.deptno = emp.deptno;
LENGTH(EMPNO) * *
------------- ---------- ----------
4
4
4
4
4
4
4
4
4
4
4
LENGTH(EMPNO) * *
------------- ---------- ----------
4
4
4
14 rows selected.
SQL>
Yes, the unadorned * returns all columns, this time with the two "*"
columns, one from EMP and one from DEPT. Enclosing the * in "" the meaning
is changed and we return three columns, not the 13 from the previous query.
Each RDBMS is different and parses queries differently. You cannot simply
assume that since one RDBMS behaves in a certain way that they all will.
David Fitzjarrell
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
