No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48052
Total active users1733
Act. users last 24h10
Act. users last hour1
Registered user hits last week395
Registered user hits last month1204
Go up

subquery using * instead of specific columns
Next thread: Migrating Raw devices to ASMLIB
Prev thread: GI Ihstallation for standalone database

Message Score Author Date
We have a view that a developer created with 9 su...... Tim Boles Aug 04, 2017, 20:07
An explain plan displayed using select * from ...... Michel Cadot Aug 04, 2017, 21:42

Follow up by mail Click here


Subject: subquery using * instead of specific columns
Author: Tim Boles, United States
Date: Aug 04, 2017, 20:07, 73 days ago
Os info: ANY
Oracle info: 12.1
Message: We have a view that a developer created with 9 subqueries that use * instead of specific columns that they need in the view. They said something about normal joins were not returning the same data. I was just wondering if they were using more temp space for sorting by utilizing the * instead of specific rows for each of the tables.

So I am curious about the utilization of subquery that uses something like

select spec.field_spec_name, trans.trans_name
from
(select * from spec_hdr
where
DELETE_MARKED=0) spec,
trans
where spec.id=trans.spec_id;


INSTEAD OF

select spec.field_spec_name, trans.trans_name
from
(select field_spec_name from spec_hdr
where
DELETE_MARKED=0) spec,
trans
where spec.id=trans.spec_id;

I am wondering if there is any different in the amount of data that Oracle has to process.



Tim
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: subquery using * instead of specific columns
Author: Michel Cadot, France
Date: Aug 04, 2017, 21:42, 73 days ago
Message:
An explain plan displayed using
select * from table(dbms_xplan.display(format=>'all'));
will tell you if Oracle will project the subquery on all or only the used columns.
Example:
SQL> explain plan for

2 select ename, dname
3 from (select * from emp where mgr is null) emp,
4 dept
5 where emp.deptno = dept.deptno
6 /

Explained.

SQL> select * from table(dbms_xplan.display(format=>'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2694310824

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 13 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / EMP@SEL$2
4 - SEL$F5BB74E1 / DEPT@SEL$1
5 - SEL$F5BB74E1 / DEPT@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("MGR" IS NULL)
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) "EMP"."ENAME"[VARCHAR2,10], "DNAME"[VARCHAR2,14]
2 - (#keys=0) "EMP"."ENAME"[VARCHAR2,10], "DEPT".ROWID[ROWID,10]
3 - "EMP"."ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
4 - "DEPT".ROWID[ROWID,10]
5 - "DNAME"[VARCHAR2,14]


You can see that when it accesses the inline view at step 3, Oracle projects only the 2 used columns of this view: ename and deptno (it was in 11.2.0.4 but I think 12.1 does this too).

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here