| Selecting the columns [message #655620] |
Tue, 06 September 2016 07:14  |
 |
saipradyumn
Messages: 419
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  |
John Watson
Messages: 8929
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 #655625 is a reply to message #655623] |
Tue, 06 September 2016 07:38 |
Bill B
Messages: 1971
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 #657242 is a reply to message #655621] |
Wed, 02 November 2016 06:38 |
John Watson
Messages: 8929
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> 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 |
Solomon Yakobson
Messages: 3273
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 |
pablolee
Messages: 2882
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 |
Solomon Yakobson
Messages: 3273
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 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Solomon Yakobson wrote on Wed, 02 November 2016 13:20Do 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  ), 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 |
Solomon Yakobson
Messages: 3273
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 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Solomon Yakobson wrote on Wed, 02 November 2016 23:49Again, 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 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
pablolee wrote on Thu, 03 November 2016 15:28So 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 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Solomon Yakobson wrote on Thu, 03 November 2016 20:19pablolee wrote on Thu, 03 November 2016 15:28So 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 |
Solomon Yakobson
Messages: 3273
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 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Solomon Yakobson wrote on Fri, 04 November 2016 09:09As 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 |
ThomasG
Messages: 3211
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 |
Roachcoach
Messages: 1576
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 |
Solomon Yakobson
Messages: 3273
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
