|
|
| Author |
Message |
ruodeer
Active User
Joined: 06 Jul 2007
Posts: 51
Location: USA
|
|
|
|
Hello everyone,
her is the Index defination:
| Code: |
| ( C1 ASC, C2 DESC,C3 ASC) |
I am confused about the following two predicate
which will result in a order:
| Code: |
where c1='A' order by C2 ASC,C3 DESC
where c1='A' order by C1 ASC,C2 ASC |
thanks! |
|
| Back to top |
|
 |
References
|
 Posted: Fri May 16, 2008 8:55 am Post subject: Re: which predicate results in a order on INDEX. |
|
|
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7997
Location: 221 B Baker St
|
|
|
|
Hello,
| Quote: |
| which will result in a order: |
Please clarify your question.
Both will return rows in the specified order.
I also do not understand why this "( C1 ASC, C2 DESC,C3 ASC) " was posted. |
|
| Back to top |
|
|
mytags
Active User
Joined: 28 Apr 2008
Posts: 64
Location: US
|
|
|
|
hi,
It will get displayed by ascending order of c2 and descending order of C3 i think your confusion is got over now.
Thanks
Hari.  |
|
| Back to top |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7997
Location: 221 B Baker St
|
|
|
|
Hello,
| Quote: |
| It will get displayed by ascending order of c2 and descending order of C3 |
Not necessarily - that depends on which code is used - 2 separate situations are posted. . .
| Quote: |
| i think your confusion is got over now. |
I believe you've only added confusion. |
|
| Back to top |
|
|
ruodeer
Active User
Joined: 06 Jul 2007
Posts: 51
Location: USA
|
|
|
|
Dick & mytags,thanks for your kindly reply.
sorry for confusion.
let me try to clarify:
saying I want to query three columns on a table , to avoid sorts, the DBA has defined an index by specifying
| Code: |
| ( C1 ASC, C2 DESC,C3 ASC) |
.
which of the following will result in a sort?
| Code: |
where c1='A' order by C2 ASC,C3 DESC
where c1='A' order by C1 ASC,C2 ASC |
hopefully I am clear ,thanks! |
|
| Back to top |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7997
Location: 221 B Baker St
|
|
|
|
Hello,
| Quote: |
| which of the following will result in a sort? |
Both of them - neither can be met by the index directly. |
|
| Back to top |
|
|
ruodeer
Active User
Joined: 06 Jul 2007
Posts: 51
Location: USA
|
|
|
|
thanks Dick,actually I got this question on a DB2 certification test sample , and the answer is
| Code: |
| where c1='A' order by C2 ASC,C3 DESC |
may not result a sort, and I donot understand the answer so I put the question here, thank you anyway.
So as per my unserstanding only the
| Code: |
| ORDER BY C1 ASC, C2 DESC,C3 ASC |
and
| Code: |
| ORDER BY C1 DESC, C2 ASC,C3 DESC |
can avoid a sort right? |
|
| Back to top |
|
|
enrico-sorichetti
Global Moderator
Joined: 14 Mar 2007
Posts: 2656
Location: italy
|
|
|
|
let' s get rid of the confusion,
whether it is an interview question or a real scenario is irrelevant
but just take a decision Yourself
just use a bit of logic
if the table has been defined with an index in descending order
| Quote: |
| ( C1 ASC, C2 DESC,C3 ASC) |
and later on You run a query asking for the same column in ascending order
,
then...
a sort will/might be performed but as usual it depends...
on other untold constraints |
|
| Back to top |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7997
Location: 221 B Baker St
|
|
|
|
Hello,
| Quote: |
| may not result a sort |
I suppose it is possible that the optimizer, in some rare situation, could decide that a sort was not needed, but in general, a sort will be needed to satisfy the SELECT.
As Enrico mentioned, "other untold constraints" can have an impact. |
|
| Back to top |
|
|
ruodeer
Active User
Joined: 06 Jul 2007
Posts: 51
Location: USA
|
|
|
|
| Ok, thank you Enrico and Dick ,now I get the general idea :-) |
|
| Back to top |
|
|
|
|
