Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
ruodeer

New User

Joined: 06 Jul 2007
Posts: 59
Location: home

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

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!

dick scherrer

Site Director

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

Posted: Fri May 16, 2008 11:49 am    Post subject:

Hello,

 Quote: which will result in a order:

Both will return rows in the specified order.

I also do not understand why this "( C1 ASC, C2 DESC,C3 ASC) " was posted.
mytags

New User

Joined: 28 Apr 2008
Posts: 63
Location: US

 Posted: Fri May 16, 2008 12:06 pm    Post subject: 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.
dick scherrer

Site Director

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

Posted: Fri May 16, 2008 12:29 pm    Post subject:

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.
ruodeer

New User

Joined: 06 Jul 2007
Posts: 59
Location: home

Posted: Fri May 16, 2008 12:43 pm    Post subject:

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!
dick scherrer

Site Director

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

Posted: Fri May 16, 2008 12:46 pm    Post subject:

Hello,

 Quote: which of the following will result in a sort?
Both of them - neither can be met by the index directly.
ruodeer

New User

Joined: 06 Jul 2007
Posts: 59
Location: home

Posted: Fri May 16, 2008 1:24 pm    Post subject:

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?
enrico-sorichetti

Global Moderator

Joined: 14 Mar 2007
Posts: 10439
Location: italy

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

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
dick scherrer

Site Director

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

Posted: Fri May 16, 2008 7:15 pm    Post subject:

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.
ruodeer

New User

Joined: 06 Jul 2007
Posts: 59
Location: home

 Posted: Mon May 19, 2008 8:39 am    Post subject: Ok, thank you Enrico and Dick ,now I get the general idea :-)
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics KSDS and Index file in cobol deepak14450 COBOL Programming 9 Fri Dec 22, 2017 9:36 am Preserve order in Input File pshongal DFSORT/ICETOOL 13 Thu Dec 21, 2017 4:20 pm Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us