SELECT *

Gopalakrishna (CTS) Umapathy

SELECT *
Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every run,
Will not the order in which it is retrieved , be the same. (Considering the
table does not grow in size, indexes remaining the same for every run of the
SELECT * )


Cheers,
Gopal.

David Hayes

Re: SELECT *
(in response to Gopalakrishna (CTS) Umapathy)
Gopal
There is a chance that the rows will be returned in the same order. However, if
you want to guarantee this, you must use an order by.
Regards
Dave Hayes

"Umapathy, Gopalakrishna (Cognizant)" wrote:

> Hi,
>
> One basic question on SELECT * ........
> SELECT * would select all rows in the table.
> What I understand is that the order in which it is retrieved, is not
> guaranteed for each run.
>
> But consider a scenario, where the SELECT * is coded in a program(I mean
> static SQL).
> And once bound, the access path is going to be the same , and for every run,
> Will not the order in which it is retrieved , be the same. (Considering the
> table does not grow in size, indexes remaining the same for every run of the
> SELECT * )
>
> Cheers,
> Gopal.
>
> ------------------------------------------------------------------------
> Name: InterScan_Disclaimer.txt
> InterScan_Disclaimer.txt Type: Plain Text (text/plain)
> Encoding: 7bit



Sanjeev (CTS) S

Re: SELECT *
(in response to David Hayes)
Gopal,

Access path doesn't store the order in which data is going to be retrieved.
It stores how the data is going to be accessed i.e if index is going to be
used, which index or not and lot many other informations about how it is
going to be accessed. It never says the order. The order depends on how the
data is stored in the table. You mentioned "table/index doesn't grow in
size". Even then i think no one can guarantee that DB2 will return data in
the same order always.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: Umapathy, Gopalakrishna (Cognizant)
> [SMTP:[login to unmask email]
> Sent: Thursday, January 10, 2002 2:19 PM
> To: [login to unmask email]
> Subject: SELECT *
>
> Hi,
>
> One basic question on SELECT * ........
> SELECT * would select all rows in the table.
> What I understand is that the order in which it is retrieved, is not
> guaranteed for each run.
>
> But consider a scenario, where the SELECT * is coded in a program(I mean
> static SQL).
> And once bound, the access path is going to be the same , and for every
> run,
> Will not the order in which it is retrieved , be the same. (Considering
> the
> table does not grow in size, indexes remaining the same for every run of
> the
> SELECT * )
>
>
> Cheers,
> Gopal.
> << File: InterScan_Disclaimer.txt >>



Ben Relle

Re: SELECT *
(in response to Sanjeev (CTS) S)
As others have already pointed out, the order isn't guaranteed unless you add
'ORDER BY' to the sql, but on a different note, Select * isn't the best idea
either. The programmer should be explicit about which columns he needs - he'll
only have to not use various audit columns etc. that the table may contain
within his process, and this is inefficient use of the RDMS.

Ben

"Umapathy, Gopalakrishna (Cognizant)" wrote:

> Hi,
>
> One basic question on SELECT * ........
> SELECT * would select all rows in the table.
> What I understand is that the order in which it is retrieved, is not
> guaranteed for each run.
>
> But consider a scenario, where the SELECT * is coded in a program(I mean
> static SQL).
> And once bound, the access path is going to be the same , and for every run,
> Will not the order in which it is retrieved , be the same. (Considering the
> table does not grow in size, indexes remaining the same for every run of the
> SELECT * )
>
> Cheers,
> Gopal.
>
> ------------------------------------------------------------------------
> Name: InterScan_Disclaimer.txt
> InterScan_Disclaimer.txt Type: Plain Text (text/plain)
> Encoding: 7bit



Eric Pearson

Re: SELECT *
(in response to Ben Relle)
If you do not have ORDER BY, there is no guarantee on the order.
This is regardless of your index structure, regardless whether you
have run additional runstats, regardless whether you did BIND or
REBIND. No guarantee. Period.

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: Umapathy, Gopalakrishna (Cognizant)
[mailto:[login to unmask email]
Sent: Thursday, January 10, 2002 3:49 AM
To: [login to unmask email]
Subject: SELECT *


Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every run,
Will not the order in which it is retrieved , be the same. (Considering the
table does not grow in size, indexes remaining the same for every run of the
SELECT * )


Cheers,
Gopal.



Chuck Gross

Re: SELECT *
(in response to Eric Pearson)
As many have already pointed out, it is generally not a good idea to use
SELECT * as all columns in the table are going to be returned whether or
not the program really needs them. This has negative performance
implications. There is also a danger that future maintenance to the
table that adds a column may cause the program that has this SELECT *
embedded within it may have some unfortunate problems. This is because
the new column will be returned to the program but the program is not
expecting it. Storage corruption may be one result of this situation.

Regarding the order by issue, as many have pointed out, it is always
best practice to specify the order in which rows are to be returned if
the program is dependent on this ordering. Just because the SQL returns
the rows in a particular order now does not mean that it will always
return the data in that order. For instance perhaps the index selected
for use at bind time is subsequently dropped or marked invalid. The SQL
will be automatically rebound and the access strategy may change. Just
because you specify order by does not mean that a sort will be incurred.
If the access strategy selected at bind time is based on an index that
returns the rows in the order as specified by the order by clause, no
sort will be incurred.

To put it succinctly, an SQL statement should specify, as completely as
possible, the actual data requirements needed to meet the objective of
the program. No more, no less.


Warm Regards,
Chuck Gross
Director of Database Services
MillenniuM Information Systems
Phone: 703-526-4888
Cell: 614-323-4279
Email: [login to unmask email]

MillenniuM Information Systems
Proprietary and Confidential
Not For Release Without Approval


-----Original Message-----
From: Umapathy, Gopalakrishna (Cognizant)
[mailto:[login to unmask email]
Sent: Thursday, January 10, 2002 3:49 AM
To: [login to unmask email]
Subject: SELECT *

Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every
run,
Will not the order in which it is retrieved , be the same. (Considering
the
table does not grow in size, indexes remaining the same for every run of
the
SELECT * )


Cheers,
Gopal.



Todd Burch

Re: SELECT *
(in response to Chuck Gross)
All the other posts were correct, and probably answered the question
that was implied, but I wanted to clarify one thing for our original
poster.

From a terminology standpoint, "SELECT *" has nothing to do with the
qualification of rows or ordering of rows. The "*" has only
to do with the "select list".

So, "SELECT *" would select all columns in the table, and the order
of the columns would always be the same, assuming the definition of the
table stayed the same.

Todd. (picky, pick, picky)


-----Original Message-----
From: [login to unmask email]
Sent: Thursday, January 10, 2002 3:26 AM
To: [login to unmask email]
Subject: SELECT *




-------------------------------------------------------------------------- --
Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every
run,
Will not the order in which it is retrieved , be the same. (Considering
the
table does not grow in size, indexes remaining the same for every run of
the
SELECT * )


Cheers,
Gopal.


Todd Burch
CDB Software, Inc.
Tel: 800-627-6561 or 281-920-3305
Internet Address: [login to unmask email]
Home Page: http://www.cdbsoftware.com



Larry Hubbard

Re: SELECT *
(in response to Todd Burch )
I also agree with what has been said except for the sentence that says:

"So, "SELECT *" would select all columns in the table, and the order
of the columns would always be the same, assuming the definition of the
table stayed the same."

The "would always be the same" is the part we had proven not to be exactly
true. It was UDB V6 on AIX with very high volume of transaction traffic. The
table was a 3 row table that was used to populate a drop down menu for user
selection purposes. NO ORDER BY was placed on the select and at especially
high volume times when the data never left the bufferpool, the rows would
randomly come to the program in different order. Adding the ORDER BY
eliminated the problem.

For James Bond it was "Never say Never" but for DB2 it appears to be "Never
say Always".


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Todd Burch *****************************************************
Sent: Thursday, January 10, 2002 10:35 AM
To: [login to unmask email]
Subject: Re: SELECT *


All the other posts were correct, and probably answered the question
that was implied, but I wanted to clarify one thing for our original
poster.

>From a terminology standpoint, "SELECT *" has nothing to do with the
qualification of rows or ordering of rows. The "*" has only
to do with the "select list".

So, "SELECT *" would select all columns in the table, and the order
of the columns would always be the same, assuming the definition of the
table stayed the same.

Todd. (picky, pick, picky)


-----Original Message-----
From: [login to unmask email]
Sent: Thursday, January 10, 2002 3:26 AM
To: [login to unmask email]
Subject: SELECT *




--------------------------------------------------------------------------
--
Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every
run,
Will not the order in which it is retrieved , be the same. (Considering
the
table does not grow in size, indexes remaining the same for every run of
the
SELECT * )


Cheers,
Gopal.


Todd Burch
CDB Software, Inc.
Tel: 800-627-6561 or 281-920-3305
Internet Address: [login to unmask email]
Home Page: http://www.cdbsoftware.com

================






Larry Hubbard

Re: SELECT *
(in response to Larry Hubbard)
As pointed out to me by a couple of members, my response applied to row
order when Todd's response was for column order. My apologies to Todd and
for any confusion my response might have caused. Got to go check my
insurance now and see if trifocals are part of the coverage.

-----Original Message-----
From: Larry Hubbard [mailto:[login to unmask email]
Sent: Thursday, January 10, 2002 10:56 AM
To: DB2 Data Base Discussion List
Subject: RE: SELECT *


I also agree with what has been said except for the sentence that says:

"So, "SELECT *" would select all columns in the table, and the order
of the columns would always be the same, assuming the definition of the
table stayed the same."

The "would always be the same" is the part we had proven not to be exactly
true. It was UDB V6 on AIX with very high volume of transaction traffic. The
table was a 3 row table that was used to populate a drop down menu for user
selection purposes. NO ORDER BY was placed on the select and at especially
high volume times when the data never left the bufferpool, the rows would
randomly come to the program in different order. Adding the ORDER BY
eliminated the problem.

For James Bond it was "Never say Never" but for DB2 it appears to be "Never
say Always".


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Todd Burch *****************************************************
Sent: Thursday, January 10, 2002 10:35 AM
To: [login to unmask email]
Subject: Re: SELECT *


All the other posts were correct, and probably answered the question
that was implied, but I wanted to clarify one thing for our original
poster.

>From a terminology standpoint, "SELECT *" has nothing to do with the
qualification of rows or ordering of rows. The "*" has only
to do with the "select list".

So, "SELECT *" would select all columns in the table, and the order
of the columns would always be the same, assuming the definition of the
table stayed the same.

Todd. (picky, pick, picky)


-----Original Message-----
From: [login to unmask email]
Sent: Thursday, January 10, 2002 3:26 AM
To: [login to unmask email]
Subject: SELECT *




--------------------------------------------------------------------------
--
Hi,

One basic question on SELECT * ........
SELECT * would select all rows in the table.
What I understand is that the order in which it is retrieved, is not
guaranteed for each run.

But consider a scenario, where the SELECT * is coded in a program(I mean
static SQL).
And once bound, the access path is going to be the same , and for every
run,
Will not the order in which it is retrieved , be the same. (Considering
the
table does not grow in size, indexes remaining the same for every run of
the
SELECT * )


Cheers,
Gopal.


Todd Burch
CDB Software, Inc.
Tel: 800-627-6561 or 281-920-3305
Internet Address: [login to unmask email]
Home Page: http://www.cdbsoftware.com

================