Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
which predicate results in a order on INDEX.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Fri May 16, 2008 8:55 am    Post subject: which predicate results in a order on INDEX.
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 11:49 am    Post subject:
Reply with quote

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
View user's profile Send private message
mytags

New User


Joined: 28 Apr 2008
Posts: 63
Location: US

PostPosted: Fri May 16, 2008 12:06 pm    Post subject:
Reply with quote

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. icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 12:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Fri May 16, 2008 12:43 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 12:46 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Fri May 16, 2008 1:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10374
Location: italy

PostPosted: Fri May 16, 2008 3:03 pm    Post subject: Reply to: which predicate results in a order on INDEX.
Reply with quote

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
Quote:
(.... C2 ASC... )
,
then...
a sort will/might be performed but as usual it depends...
on other untold constraints
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 7:15 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Mon May 19, 2008 8:39 am    Post subject:
Reply with quote

Ok, thank you Enrico and Dick ,now I get the general idea :-)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us