Asterisk In Select Query

Posted on 2006-05-18
Medium Priority
Last Modified: 2008-03-17
I have a query that does something like the following:

SELECT d.*, a.* FROM TABLE1 d, TABLE2 a WHERE ......

Is there a degradation in performance or are the indexes ignored by using the d.* and a.* instead of the actual column names like d.COLUMN1, d.COLUMN2, a.COLUMN1, a.COLUMN2?  Thanks.

BTW, I am using SQL Server 2000, 2005 and Oracle 9 and 10.
Question by:pcarrollnf
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16708735
> Is there a degradation in performance or are the indexes ignored by
> using the d.* and a.* instead of the actual column names

there wont be any degradation in performanxce
LVL 43

Assisted Solution

TimCottee earned 100 total points
ID: 16708744
Hi pcarrollnf,

The indexes are not ignored, however there is a slight degradation in performance because the server needs to enumerate the fields. When you specify fields, there is nothing that needs to be done, however if you use * then the server must first get the full field list then prepare the actual query. This does ever so slightly (and possibly not even measurably) degrade your performance. However once the field list is obtained then the normal execution plan will be generated and used.

Tim Cottee
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 100 total points
ID: 16708745
it is preferrable to avoid the *, because
* you gain a minimum avoiding sql to resolve the * into the actual column list
* you should only return those columns (and rows) that you really need

indexes will not be ignored due to this, but you have indexes that contain all the fields that are in the list, the table page does not need to be looked up.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question