Why SELECT * is not preferred in embedded SQL programs?

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

Why SELECT * is not preferred in embedded SQL programs?

I can think of the following reasons:
  • If the table structure is changed ( a field is added ), the program will have to be modified
  • Program might retrieve the columns which it might not use, leading on I/O over head.
but are these the only reason for this or there can be other?
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by nicc »

Another quick reason is that by coding the full SELECT anyone looking at the program knows which fields it is retrieving and thus, presumably, using.
Regards
Nic
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

Thanks nicc. Though I wonder that these are the only reasons to not use it in the programs..
zprogrammer
Global Moderator
Global Moderator
Posts: 588
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by zprogrammer »

And also when we do an unload using SELECT * and after ALTER new column ADD we might tend to miss out on the LRECL of the file .. Also doing a select * might be fetching a lot of column than what is actually needed too
zprogrammer
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by enrico-sorichetti »

Looks like NOBODY considered the fact that the GENERAL format is something like

Code: Select all

SELECT ... ... ...
       <list of column names>
       INTO <list of host variables>
       ... ... ...
where the relative position in the lists associates the column to the variable
a

Code: Select all

SELECT *
makes pretty difficult to associate the columns to the variables
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
MartinPacker
Registered Member
Posts: 14
Joined: Tue May 27, 2014 8:45 pm
Contact:

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by MartinPacker »

SELECT * when all you wanted was columns in an index prevents index-only access. And I'm sure there are other access path no-nos.

It also occurs to me that if column data is (eg RACF) protected then the request will fail, whether you wanted columns you don't have access to or not.

Of course an unqualified row selection is equally problematic, which is what I mistakenly originally thought the question was about. :-)
Martin Packer
Principal Systems Investigator, IBM
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

MartinPacker wrote:SELECT * when all you wanted was columns in an index prevents index-only access. And I'm sure there are other access path no-nos.

It also occurs to me that if column data is (eg RACF) protected then the request will fail, whether you wanted columns you don't have access to or not.

Of course an unqualified row selection is equally problematic, which is what I mistakenly originally thought the question was about. :-)
This is very late to acknowledge the reply but I was not aware about that we can RACF secure some columns out of many. That's a news. But why would someone create a column and then don't allow the access to it?
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by nicc »

You don't 'not allow access' - you create a column and restrict access to it i.e. if you have a need to know that data and have the access then you can select it. If you don't have the authority then you cannot.
Regards
Nic
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by enrico-sorichetti »

 and anyway... what happened when You tried !
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

nicc wrote:You don't 'not allow access' - you create a column and restrict access to it i.e. if you have a need to know that data and have the access then you can select it. If you don't have the authority then you cannot.
Thanks!
enrico, when I tried it worked for the table I used. I thinking that it can be generalized DB2 CPU saving method. In case we need all the columns from a table for a program, and I code SELECT * instead of all columns, I see no difference in CPU. Apart from that, I'm not able to relate any other benefit, apart from what you have said above, we can get by not using it. I mean it's kind of a choice, rather a tip when we need all the columns. :?
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by nicc »

In production code select by column. Using * is fine for a quick one-off but not for a regular thing.
Regards
Nic
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

okay, thanks for your answer, nicc.
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Anuj Dhawan »

Just my $ .02...

When you execute an SQL which has SELECT, there are two basic steps which happen under the covers (not always, but for the simplicity of the discussion let's talk about these two tasks) -

[ol][li]To select the qualified rows, use an index [/li]
[li]or get the qualified rows from the table for the application/end-user[/li][/ol]
For instance, if the application just needs the columns which are present in the index, database should not even perform the second step. And usually databases don’t do it. They (can) process query just with the information stored in the index - which means database is doing an index-only scan.

OTOH, if with SELECT * query selects a column that’s not in the index, the database can not do an index-only scan. It has to go through a table-scan. And it has to do it for all such columns. And with SELECT * there are good chances of having many such columns being used in your query and the query will be a CPU hogger.


Basically, in the end, it's all about performance. DB2, from a syntax per se, does not stop you from using SELECT * but performance is an another animal!
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Mukesh Mistry
Registered Member
Posts: 22
Joined: Wed Aug 14, 2013 7:56 pm

Re: Why SELECT * is not preferred in embedded SQL programs?

Post by Mukesh Mistry »

Thanks Anuj.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”