Home » SQL & PL/SQL » SQL & PL/SQL » Selecting the columns (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Selecting the columns [message #655620] Tue, 06 September 2016 07:14 Go to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

I have table which contains more than 200 columns. While selecting the columns from the table whihc one is effecient to use either * or list of required columns . I didn't see any major difference from Execute plan prospective, but there is difference from execution time.

Some times * is working fine and some list of required columns is working fine

Please help me to understnd this
Re: Selecting the columns [message #655621 is a reply to message #655620] Tue, 06 September 2016 07:18 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
You need to apply a general principle:

Always nominate the columns you wish to project; never use SELECT *.

There are any number of reasons for this. To pick out just one that will apply to you, for a wide table it may mean that there is no need for Oracle to navigate all the blocks of a chained row.
Re: Selecting the columns [message #655622 is a reply to message #655621] Tue, 06 September 2016 07:22 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
>I have table which contains more than 200 columns.
I am willing to bet that the table above does NOT contain Third Normal Form data & that some of the column names are actually application data.
Re: Selecting the columns [message #655623 is a reply to message #655622] Tue, 06 September 2016 07:28 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Just picking a table at random from an EBS database, ONT.OE_ORDER_LINES_HISTORY has 375 columns Smile

Re: Selecting the columns [message #655625 is a reply to message #655623] Tue, 06 September 2016 07:38 Go to previous messageGo to next message
Bill B
Messages: 1799
Registered: December 2004
Senior Member
wither you need all the columns or only a few always specify the column names unless you are doing an adhoc query. The reason is because any code that you use the splat wildcard in will fail any time that the table is altered in any way. Also if you have a column with 200 columns (for example) and you only need 3 columns then why access all the columns or why pull the 197 extra over the network.
Re: Selecting the columns [message #655626 is a reply to message #655620] Tue, 06 September 2016 08:09 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
You should always use column names in your select list, and there are many benefits of it, It is a good coding habit and it saves you from unforeseen issues later (literally many issues that you can not think of might come later)

1. If you need few columns , then selecting them by name drastically reduces the Bytes in execution, fewer blocks are read compared to select *
2. Altering the table to add few columns will not impact your PL/SQL codes (specially you should take care of naming columns in INSERTS)
3. Oracle 12c: you will not end up in mess with invisible columns in the table

and so on...

Re: Selecting the columns [message #655629 is a reply to message #655626] Tue, 06 September 2016 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
SELECT * should NEVER be allowed in any Production application.
Re: Selecting the columns [message #657221 is a reply to message #655629] Wed, 02 November 2016 01:26 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks for your information BlackSwan

As you suggested I compared the total number of columns with required columns .
If we are selecting more than 70% of the total number of columns, then I am using * instead of selecting the individual columns.

Otherwise I am just selecting only the required columns only
Re: Selecting the columns [message #657224 is a reply to message #657221] Wed, 02 November 2016 01:50 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
saipradyumn

If we are selecting more than 70% of the total number of columns, then I am using * instead of selecting the individual columns.
Which is, in my opinion, wrong.
Re: Selecting the columns [message #657225 is a reply to message #657224] Wed, 02 November 2016 01:54 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member


70% was wrong?
Do we need to check for the 90 % ?
or
Every time we need select only the required columns instead of * ?

Re: Selecting the columns [message #657226 is a reply to message #657225] Wed, 02 November 2016 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I vote for the last option. Smile

Re: Selecting the columns [message #657227 is a reply to message #657225] Wed, 02 November 2016 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Everyone here tries to tell you what would be the best option. However, you desperately want us to acknowledge your "SELECT *" approach. That, most probably, won't happen.

Therefore, if you want to use "SELECT *", feel free to do that. I don't agree with it, but I have no influence on your job. It's entirely up to you.
Re: Selecting the columns [message #657231 is a reply to message #657227] Wed, 02 November 2016 05:38 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Yes , Littlefoot /Michel

I agree with you. There are so many other factors also while implementing the logic in prod environment.
But in general I just want to know, even though we are selecting all columns in table ,
do we need write all those column names in select list instead of * .

Always selecting the columns is efficient to use ?
Re: Selecting the columns [message #657232 is a reply to message #657231] Wed, 02 November 2016 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Say you have a table with 2 columns, col1 and col2.
Now you have in your program something like (just algorithm not any language code):
select * from mytable into var1, var2;
Now the project leader realizes they need a new column for a new feature, col3, and add it to the table but your code is not modified, so it will fail because you gave 2 variables for 3 columns.
But if you list the columns in the query, your code will still be valid.

Re: Selecting the columns [message #657236 is a reply to message #657232] Wed, 02 November 2016 06:06 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
I understood your concern Michel .
Apart from that is there any performance difference between those two
Re: Selecting the columns [message #657239 is a reply to message #657236] Wed, 02 November 2016 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generally, there are no differences from performances point of view, only when the table structure changes.

Re: Selecting the columns [message #657241 is a reply to message #657239] Wed, 02 November 2016 06:30 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Michel
Re: Selecting the columns [message #657242 is a reply to message #655621] Wed, 02 November 2016 06:38 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Many organizations have coding standards that say you should always nominate the columns you not to project. If you want to be lazy, Uncle Oracle can sometimes help. For example, this bad code,
orclz>
orclz> create view bad as select * from dept;

View created.

orclz>
is re-written to this,
orclz>
orclz> select dbms_metadata.get_ddl('VIEW','BAD') from dual;

DBMS_METADATA.GET_DDL('VIEW','BAD')
----------------------------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "SCOTT"."BAD" ("DEPTNO", "DNAME", "LOC") AS
  select "DEPTNO","DNAME","LOC" from dept


orclz>
Re: Selecting the columns [message #657243 is a reply to message #657232] Wed, 02 November 2016 06:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Wed, 02 November 2016 06:47

But if you list the columns in the query, your code will still be valid.
I already mentioned few times statement NEVER use * is well, "never say never":

Say you have a table with 2 columns, col1 and col2.
Now you have in your program something like (just algorithm not any language code):
insert into myothertable(col1,col2)
select col1 and col2 from mytable;
Now the project leader realizes they need a new column for a new feature, col3, and add it to both tables but your code is not modified, so it succeeds. But at what cost?
Column col3 in myothertable isn't populated and it can go for a while before someone will notice. And then it is not always possible to recover that missing col3 values. Personally, I prefer code to fail rather that work incorrectly. On other hand now we have calculated columns, hidden columns, identity columns, invisible columns where using * simply will not work as expected. Anyway, as usual, "it depends".

SY.
Re: Selecting the columns [message #657247 is a reply to message #657243] Wed, 02 November 2016 07:47 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Now the project leader realizes they need a new column for a new feature, col3, and add it to both tables but your code is not modified, so it succeeds. But at what cost?
Column col3 in myothertable isn't populated and it can go for a while before someone will notice.
Whilst yes, that's possible, it's pretty unlikely unless absolutely ZERO testing is performed on the system, surely.
Re: Selecting the columns [message #657248 is a reply to message #657247] Wed, 02 November 2016 08:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Do you always do COMPLETE regression testing after adding a column to a table? There can be dozens of places newly added column now should be used resulting in hundreds of test cases to be run for complete regression testing that might take weeks to complete. Reality is - there is often no time for full regression testing and only most important test cases are done. Also, column was added for a new feature, right? This means we might overlooked some test cases to cover that new feature or we didn't realize new feature affects some of existing test cases and they need adjustments, etc. Anyway, all I was trying to say is "never say never" - there is no silver bullet and both * and explicit column list have as advantages as disadvantages.

SY.
Re: Selecting the columns [message #657250 is a reply to message #657248] Wed, 02 November 2016 10:48 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Wed, 02 November 2016 13:20
Do you always do COMPLETE regression testing after adding a column to a table?
Not always, no, however, the scenario that you described has very little to do with regression testing.
"Now the project leader realizes they need a new column for a new feature, col3, and add it to both tables but your code is not modified, so it succeeds."
The testing that would discover that data is not added to col3 in table 2 would be part of the standard testing of the new functionality, not regression testing. Regression testing would be ensuring that that added column and the change to the code did not affect the rest of the application in a negative or unexpected way.

Quote:
There can be dozens of places newly added column now should be used resulting in hundreds of test cases to be run for complete regression testing that might take weeks to complete. Reality is - there is often no time for full regression testing and only most important test cases are done. Also, column was added for a new feature, right?
This means we might overlooked some test cases to cover that new feature
OK, so how does 'select *' solve that? Maybe I'm missing your point here? Are you implying that all of these dozens of places must also be using 'select *'

Quote:
or we didn't realize new feature affects some of existing test cases and they need adjustments, etc.
Again, how exactly does 'select *' solve this? It seems to me that so long as even a basic level of testing is carried out in the scenario that you described earlier then the failure of column 3 in table 1 to flow into column 3 in table 2 would be picked up pdq.

Quote:
Anyway, all I was trying to say is "never say never" - there is no silver bullet and both * and explicit column list have as advantages as disadvantages.
And we're back to the much more comfortable norm of me agreeing with you. I agree, never say never (although, that is of course self refuting Smile ), I just don't feel that the example backs up a scenario where 'select * ' would be preferable in production over explicitly listing required columns.

Cheers
Re: Selecting the columns [message #657253 is a reply to message #657250] Wed, 02 November 2016 18:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Again, neither SELECT * nor SELECT column-list solves all cases. Bot have advantages and disadvantages. I prefer select * since it saves me keystrokes and I don't have to scroll up-and-down trying to understand select that has hundreds of columns. But neither way eliminates documenting logic and dependencies to avoid missed code changes.

SY.
Re: Selecting the columns [message #657273 is a reply to message #657253] Thu, 03 November 2016 14:28 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Wed, 02 November 2016 23:49
Again, neither SELECT * nor SELECT column-list solves all cases.
I never stated that it did. I asked how the specific example that you used to support your use of select * would be solved by select * (as you implied that it would)

Quote:
when compared Bot have advantages and disadvantages.
Not disagreeing here either.
Quote:
I prefer select * since it saves me keystrokes and I don't have to scroll up-and-down trying to understand select that has hundreds of columns.
OK that's fair play. So it's a personal preference rather than a technique that can have specific technical advantages over explicitly listing column. So on balance, whilst there doesn't seem to be a scenario where select * has a technical advantage (whereas an explicit list does), or more accurately, we haven't found one so far in this thread, in instances where it doesn't really matter from a technical perspective it can simply come down to a matter of personal preference to save keystrokes (although, in fairness, I have a piece of code saved (and also attached to a keyboard shortcut) that'll give me that list and all I have to do is enter a table name as a parameter) BUT, it does indeed reduce the amount of code that you have to read through on really really big tables.
But neither way eliminates documenting logic and dependencies to avoid missed code changes. Agreed, you could of course say that about almost any coding practice of a similar ilk though.

Cheers
Re: Selecting the columns [message #657274 is a reply to message #657273] Thu, 03 November 2016 15:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
pablolee wrote on Thu, 03 November 2016 15:28
So on balance, whilst there doesn't seem to be a scenario where select * has a technical advantage (whereas an explicit list does)

What technical advantage that would be?

SY.
Re: Selecting the columns [message #657278 is a reply to message #657274] Fri, 04 November 2016 03:00 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Thu, 03 November 2016 20:19
pablolee wrote on Thu, 03 November 2016 15:28
So on balance, whilst there doesn't seem to be a scenario where select * has a technical advantage (whereas an explicit list does)
What technical advantage that would be?

SY.
Add a column to a table, you have a piece of application code that populates a record or another table with select *, you will get a failure unless you either add that column to the target or change the code. Use explicit list and there is no need for that unless it is actually required by the application. Select all forces you to potentially incorporate columns into your solution that you don't actually need.
Re: Selecting the columns [message #657280 is a reply to message #657278] Fri, 04 November 2016 04:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
As I already explained, it is a two-edged sword. In some cases new column doesn't need to be part of SQL - then code with explicit list needs no changes and code with * does. In other cases new column needs to be part of SQL - then code with explicit list needs changes and code with * doesn't.

SY.
Re: Selecting the columns [message #657281 is a reply to message #657280] Fri, 04 November 2016 04:22 Go to previous messageGo to next message
pablolee
Messages: 2871
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Fri, 04 November 2016 09:09
As I already explained, it is a two-edged sword. In some cases new column doesn't need to be part of SQL - then code with explicit list needs no changes and code with * does. In other cases new column needs to be part of SQL - then code with explicit list needs changes and code with * doesn't.

SY.
OK.
Re: Selecting the columns [message #657286 is a reply to message #657278] Fri, 04 November 2016 07:24 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
pablolee wrote on Fri, 04 November 2016 09:00
[/i] Select all forces you to potentially incorporate columns into your solution that you don't actually need.
Depends. I do a lot of "data transfer" Stuff between different applications, and there "select *" and rowtypes that then get inserted on the target side are really great.

I never to change the select parts on the source side, I never have to change the insert parts on the target side, I just have to change the "mappings" the values between the rowtypes in the "Middle". And the data transfer never breaks when new columns are added, I just have to new columns to the mapping if they are requested to be mapped.


Re: Selecting the columns [message #657288 is a reply to message #657286] Fri, 04 November 2016 07:34 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
I'd say your examples are all ones fitting in the category of exceptions which prove the rule. You have outlier conditions/requirements and are of a significantly higher level of understanding than many other people who write code - which in turn means you know when to step outside the "guidelines" as it were. Using explicit names protects people from themselves a heck of a lot more than * does.

To my mind it's a bit like nologging, or hinting. These are not universally bad, but to use them safely and correctly requires more than a "chuck it in an hope it's fine" approach thus my general advice is to play it safe.

[Updated on: Fri, 04 November 2016 07:37]

Report message to a moderator

Re: Selecting the columns [message #657293 is a reply to message #657288] Fri, 04 November 2016 09:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Now we are discussing completely different situation. Asterisk vs column list is discussed when we need ALL columns. It is quite obvious, that one will use explicit list of columns if not all columns from CURRENT set of table columns is needed.

SY.
Re: Selecting the columns [message #657294 is a reply to message #657293] Fri, 04 November 2016 10:28 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Well not really. Todays "all columns" is not necessarily tomorrows. We can never assume that someone won't just wing a giant BLOB at the end of a table.

Code defensively imo Smile
Re: Selecting the columns [message #657299 is a reply to message #657294] Fri, 04 November 2016 11:24 Go to previous message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Same way we can't assume column added tomorrow will be or will be not needed by current query. As I said, both ways have advantages and disadvantages.

SY.
Previous Topic: summation of multiple values for a particular at specific period
Next Topic: query needed without using only where clause
Goto Forum:
  


Current Time: Tue Oct 16 03:34:01 CDT 2018