Q
Problem solve Get help with specific problems with your technologies, process and projects.

Why "select star" is bad

I want to display all 27 columns in my table. I used SELECT * ... Is this right? Or should I use all column names...

instead? Is there any performance issue, like one way is faster? Is there any disadvantage if I use SELECT * ...?

There are many reasons why "select star" is bad. Foremost is the harm this can do to performance.

Imagine using SELECT * to retrieve rows from a table which includes a TEXT or BLOB column. A TEXT or BLOB column is large, and is used to store data like the contents of a chapter in a book, or an image.

If you do not intend to display the TEXT or BLOB column, for example if you wanted a list of the last 25 entries this week, then it is immediately obvious that SELECT * is bad when you display the query results on the screen or printer. You don't see a list, you see many kilobytes of TEXT or BLOB data. This prompts some people to post questions like "How do I write SELECT * except for column x?" on database forums. Answer: you can't; you have to list the columns you want.

Programming languages which can format query results, however, allow the programmer to execute SELECT * queries, but then decide which columns to display. If the TEXT or BLOB column is not displayed, everything still looks okay. But bandwidth is wasted, several kilobytes per TEXT or BLOB column, times N rows returned, on every execution. In addition to wasted bandwidth, performance also suffers, especially if a sort is required, such as when DISTINCT or ORDER BY is specified.

The sad fact is that this bad situation is not immediately apparent. In a testing environment, the programmer may have, ahem, other priorities. During volume testing, the poor performance is noticed, but now the programmer has to perform emergency debugging on his own code, to rediscover which columns are actually needed by each module. And if your organization does not do volume testing, you may not realize that SELECT * is bad for performance until the program goes live.

That said, if you do actually need all columns, then SELECT * is fine, provided that you never change the table. Or the query.

For instance, if you needed to change the query, say to join to another table to get a count of related rows, you cannot say GROUP BY * so you have to list the individual columns anyway, even if you need all of them.

Skilled developers will always explicitly list the minimum set of columns needed. The SELECT list thus describes the data used by the code module, and so is self-documenting. Bandwidth is minimized and performance is optimized, especially considering the increased possibility that the query might be satisfied by a covering index.

See also Why Select * is bad for a couple of additional reasons from an ASP perspective.

For More Information


This was last published in March 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close