Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, rakesh.

Asked: November 07, 2017 - 6:30 pm UTC

Last updated: November 09, 2017 - 1:43 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

There is a table say t_tab with columns a,b and c. Data in the table is huge(more than million). You run the following three statements:
1. select * from t_tab
2. select a,b,c from t_tab
3. select b,c,a from t_tab
Will there be a difference in performance? If yes, which will be faster and why?

THanks

and Connor said...

The first query will need to resolve what "*" is, but that will take a nanosecond.

Other than that, they'll all be the same.

SQL> create table t as
  2  select d.owner, d.object_name, d.object_type from dba_objects d, ( select 1 from dual connect by level <= 200 ) ;

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
  15753000

1 row selected.

SQL>
SQL> set timing on
SQL> begin
  2    for i in ( select owner, object_name, object_type from t )
  3    loop
  4       null;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.71
SQL>
SQL> begin
  2    for i in ( select object_type, object_name, owner from t )
  3    loop
  4       null;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.70
SQL>
SQL> begin
  2    for i in ( select * from t )
  3    loop
  4       null;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.73


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

To Rakesh

J. Laurindo Chiappa, November 08, 2017 - 12:32 pm UTC

Another possible source of performance diff could be an Index in this columns A,B and C : if all the columns cited in the SELECT are present in a given index, the RDBMS can bring back the data only reading the index, while if you use an * in the SELECT, the RDBMS will NEED to access the table itself...
But if no index with the desired columns exists, no practical diff (performance-wise) in using * ou indicating the columns....

Best regards,

Chiappa

Connor McDonald
November 09, 2017 - 1:43 am UTC

This question says the table only contains the columns queried (a,b,c) but definitely a valid point.

As a general rule, I prefer anyone coding to use

select [cols]

in preference to

select *

unless they are selecting into a %rowtype variable.


Table/join elimination

lh, November 09, 2017 - 8:53 am UTC

Hi

If there exists more columns in table/view than actually are needed in select's column list, then
using * could disable possible table/join elimination.


lh
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.