The obvious difference with fetching more columns is you'll consume more memory.
How much more? Well, unsurprisingly, it depends...
If the columns you exclude are (mostly) null then there will be minimal difference:
create table t as
select level id,
lpad('x', 20, 'x') c1,
sysdate c2,
sysdate c3,
level c4,
level c5,
level c6,
cast(null as varchar2(500)) c7,
cast(null as varchar2(500)) c8,
cast(null as varchar2(500)) c9
from dual
connect by level <= 10000;
exec dbms_session.FREE_UNUSED_USER_MEMORY ;
declare
type r_tab is table of t%rowtype index by binary_integer;
all_cols r_tab;
begin
select *
bulk collect into all_cols
from t;
show_mem('All cols, nulls');
end;
/
All cols, nulls session pga memory: 11.511
exec dbms_session.FREE_UNUSED_USER_MEMORY ;
declare
type r is record (
id t.id%type, c1 t.c1%type, c2 t.c2%type,
c3 t.c3%type, c4 t.c4%type, c5 t.c5%type,
c6 t.c6%type
);
type r_t is table of r index by binary_integer;
sub_cols r_t;
begin
select id, c1, c2, c3, c4, c5, c6
bulk collect into sub_cols
from t;
show_mem('Col subset, nulls');
end;
/
Col subset, nulls session pga memory: 10.761So selecting all the columns uses 11.5mb, vs 10.7mb when you exclude those that are null. Not much over 10,000 rows.
On the other hand, if you're excluding columns containing long strings the savings could be significant:
drop table t purge;
create table t as
select level id,
lpad('x', 20, 'x') c1,
sysdate c2,
sysdate c3,
level c4,
level c5,
level c6,
lpad('x', 500, 'x') c7,
lpad('y', 500, 'y') c8,
lpad('z', 500, 'z') c9
from dual
connect by level <= 10000;
exec dbms_session.FREE_UNUSED_USER_MEMORY ;
declare
type r_tab is table of t%rowtype index by binary_integer;
all_cols r_tab;
begin
select *
bulk collect into all_cols
from t;
show_mem('All cols, long strings');
end;
/
All cols, long strings session pga memory: 27.948
exec dbms_session.FREE_UNUSED_USER_MEMORY ;
declare
type r is record (
id t.id%type, c1 t.c1%type, c2 t.c2%type,
c3 t.c3%type, c4 t.c4%type, c5 t.c5%type,
c6 t.c6%type
);
type r_t is table of r index by binary_integer;
sub_cols r_t;
begin
select id, c1, c2, c3, c4, c5, c6
bulk collect into sub_cols
from t;
show_mem('Col subset, long strings');
end;
/
Col subset, long strings session pga memory: 10.761Selecting the subset uses the same amount of memory (10.7mb). But getting all the columns is now up to nearly 28mb, quite an increase. This could also have an impact on the performance of the SQL itself, particularly if it's "complex" and needs to sort data or write it to temp.
For example, in the SQL below I'm using the materialize hint to force a write to temp. Notice that the SQL getting all the columns uses far more reads, writes and buffers:
set serveroutput off
alter session set statistics_level = all;
with rws as (
select /*+ materialize */* from t
)
select count(*) from rws;
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID cy1qpkps337c0, child number 1
-------------------------------------
with rws as ( select /*+ materialize */* from t ) select count(*)
from rws
Plan hash value: 1481629063
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.17 | 7560 | 5000 | 2500 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 1 |00:00:00.17 | 7560 | 5000 | 2500 |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.11 | 5047 | 2500 | 2500 |
| 3 | TABLE ACCESS FULL | T | 1 | 8412 | 10000 |00:00:00.03 | 2506 | 2500 | 0 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 2507 | 2500 | 0 |
| 5 | VIEW | | 1 | 8412 | 10000 |00:00:00.04 | 2507 | 2500 | 0 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D708F_3B7555B | 1 | 8412 | 10000 |00:00:00.03 | 2507 | 2500 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
with rws as (
select /*+ materialize */id, c1, c2, c3, c4, c5, c6 from t
)
select count(*) from rws;
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID bzhjgyagqfq5a, child number 0
-------------------------------------
with rws as ( select /*+ materialize */id, c1, c2, c3, c4, c5, c6
from t ) select count(*) from rws
Plan hash value: 1481629063
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 2682 | 2581 | 81 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 1 |00:00:00.09 | 2682 | 2581 | 81 |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.06 | 2590 | 2500 | 81 |
| 3 | TABLE ACCESS FULL | T | 1 | 8412 | 10000 |00:00:00.03 | 2506 | 2500 | 0 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 86 | 81 | 0 |
| 5 | VIEW | | 1 | 8412 | 10000 |00:00:00.03 | 86 | 81 | 0 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7090_3B7555B | 1 | 8412 | 10000 |00:00:00.01 | 86 | 81 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2) So personally I'd only select exactly what you need. But in many cases this is the last mile of tuning. So unless you're really tight on memory or have complicated SQL to get the data, I wouldn't worry about it too much. Usually there's much bigger gains to be made elsewhere in your application.
Code for the show_mem procedure:
create or replace procedure show_mem (
details varchar2
) as
begin
for mem in (
select vstt.name,
max( vsst.value ) value
from v$sesstat vsst,
v$statname vstt,
v$session vses
where vstt.statistic# = vsst.statistic#
and vsst.sid = vses.sid
and vstt.name in (
'session pga memory'
)
and vses.username is not null
and vsst.sid = sys_context('USERENV', 'SID')
group by vstt.name
order by vstt.name
) loop
dbms_output.put_line(
details || ' ' || mem.name || ': ' ||
round((mem.value/1024/1024), 3)
);
end loop;
end show_mem;
/