Re: Ignore Duplicate Rows ?

Michael Hannan

Re: Ignore Duplicate Rows ?
(in response to Richard A Yevich)
This just clarifies my views re Cursor verses Singleton Select.

My benchmark tests showed that Cursor with single fetch and OPTIMIZE FOR 1
ROW used only a little extra CPU in batch over Singleton Select (the
difference is more significant if SQL repeated in a loop due to lookaside
reducing Getpage component), but also used less CPU than Singleton Select in
some cases where Singleton Select had a bad access path or had to scan a
long way looking for a second row (even when there wasn't one).

Note the fixed overhead for Open/Close/Fetch Cursor is higher than for
SELECT (in Akira's presentations).

However in CICS environment, there is a different story.
Each and every SQL call has an additional 0.2 millisecs (3090 180J machine)
overhead which shows up in the difference between Class 1 and Class 2 CPU
times. For CICS, Class 1 does not include the application processing in general.

This makes Singleton SELECT often able to beat Cursor with Fetch quite
significantly in well behaved cases.

As always, it depends! Unfortuneately the issues are too complex for the
average programmer to select the best solution most of the time. Experienced
DB2 programmers may be different, but we do not even get any consensus on
this issue.

I sometimes recommend Singleton Select (when access path O.K.) before a
backup Cursor for online CICS situations where most of the time the Select
will find < 2 rows. For batch I wouldn't bother unless a very critical SQL
and < 2 rows 90% of the time.

I wish Singleton SELECT had options:
1. Don't check for 2nd row.
2. OPTIMIZE FOR 1 ROW.
to make it perfect for existance checks, (or any row I suppose).

From: Michael Hannan

>From: Richard A Yevich <[login to unmask email]>
>Subject: Re: Ignore Duplicate Rows ?
>To: [login to unmask email]
>
>Terry/Kishore,
>
>Terry great response but let's go one further. A single SQL SELECT
>statement IS A CURSOR with the two fetches possible. It is just that the
>OPEN CURSOR, FETCH, CLOSE CURSOR is handled logically inside of DB2 instead
>of the programmer issuing the CALL.
>
>As Michael Hannon, and others, have proven through benchmarks over and over,
>an OPEN CURSOR with OPTIMIZE FOR 1 ROW, FETCH, CLOSE CURSOR, is difficult to
>beat. The details of this have been published on DB2-L at least 6 times over
>the last few years. They are in the archives if anyone is truly interested.
>
>Richard Yevich
>
>> -----Original Message-----
>> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
>> Purcell, Terry
>> Sent: Tuesday, December 28, 1999 8:47 PM
>> To: [login to unmask email]
>> Subject: Re: Ignore Duplicate Rows ?
>>
>>
>> Kishore,
>>
>> We have concensus that you should not rely on the results after
>> -811 (as per
>> IBM manual). Before answering your question, I am intrigued to
>> know why you
>> believe open-fetch-close processing is not good?
>>
>> Assuming materialisation is not required (if it was then singleton select
>> and cursor would give similar performance), then if a singleton select was
>> used, you would need to cater for the use of a cursor for those -811
>> situations, or use column functions (MIN, MAX etc.). Use of the column
>> function can range from OK (if index to support, and small number of rows
>> qualify) to disasterous (if it introduces materialisation). If
>> you want the
>> entire row, then you need to concatenate the entire row, then perform the
>> column function (very ugly).
>>
>> Remember however, a singleton select does not simply return when it
>> qualifies one row, it must always perform a "look" for a second qualifying
>> row to evaluate the -811 condition.
>>
>> My recommendation would be to code a cursor and perform 2 fetches (the
>> second fetch to determine the duplicate); and code OPTIMIZE FOR 2
>> ROWS. The
>> access path can avoid sequential or list prefetch, multi-index processing,
>> or the overhead of invoking parallel processing or a merge-scan or hybird
>> join with the use of the optimize clause (especially OPTIMIZE FOR 1 ROW).
>> Any of these alone can cause the cursor to significantly outperform the
>> singleton select (singleton select cannot easily disable many of these).
>>
>> Obviously the above mentioned detail has been compiled without specific
>> knowledge of your SQL requirements. If you do not believe this applies to
>> your SQL, then you may wish to provide more detail to the
>> listserver so that
>> we can make a more informed judgement.
>>
>> Regards
>> Terry Purcell
>> CPT Consulting
>>
>> > -----Original Message-----
>> > From: kishore erukulapati [SMTP:[login to unmask email]
>> > Sent: Tuesday, 28 December 1999 4:58 am
>> > To: [login to unmask email]
>> > Subject: Ignore Duplicate Rows ?
>> >
>> > Hi ,
>> > Rows from a table have to be selected based on a specific criteria .
>> > If
>> > Duplicates are found we need to select any row randomly . How can we do
>> > this
>> > most efficiently .
>> > We thought of few alternatives :
>> > 1.
>> >
>> > Select from a table based on specific criteria
>> > If -811
>> > Open the cursor
>> > Fetch only once
>> > Close the cursor
>> > End-if
>> > For getting one row , We need to do open-fetch-close processing
>> > which is not good
>> >
>> > 2. Use max(col-1) , max(col-2) .. , max(col-n)
>> > from table where .....
>> > This will not give a single row But gives the maximum values from
>> > all rows which satisfy the criteria . But we need column values from a
>> > single row .
>> >
>> > Any ideas
>> >
>> > Cheers,
>> > Kishore
>> >
>> > ______________________________________________________
>> > Get Your Private, Free Email at http://www.hotmail.com
>> >
>> >
>> >
>> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
>> the list can
>> >
>>
>>
>> To change your subscription options or to cancel your
>> subscription visit the DB2-L webpage at
>http://www.ryci.com/db2-l. The owners of the list can be reached at
>[login to unmask email]
>
>
>


>



kishore erukulapati

Ignore Duplicate Rows ?
Hi ,
Rows from a table have to be selected based on a specific criteria . If
Duplicates are found we need to select any row randomly . How can we do this
most efficiently .
We thought of few alternatives :
1.

Select from a table based on specific criteria
If -811
Open the cursor
Fetch only once
Close the cursor
End-if
For getting one row , We need to do open-fetch-close processing
which is not good

2. Use max(col-1) , max(col-2) .. , max(col-n)
from table where .....
This will not give a single row But gives the maximum values from
all rows which satisfy the criteria . But we need column values from a
single row .

Any ideas

Cheers,
Kishore

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Steve Mazer

Re: Ignore Duplicate Rows ?
(in response to kishore erukulapati)
If you are using DB2 UDB for Unix/NT/OS2, then you can try using the 'fetch
first 1rows only' clause on the the select statement. If not, then you may
consider using option 1, since option 2 is certainly not random.

Regards,
Steve
At 09:58 AM 12/27/99 -0800, you wrote:
>Hi ,
> Rows from a table have to be selected based on a specific criteria . If
>Duplicates are found we need to select any row randomly . How can we do this
>most efficiently .
>We thought of few alternatives :
>1.
>
>Select from a table based on specific criteria
>If -811
> Open the cursor
> Fetch only once
> Close the cursor
>End-if
> For getting one row , We need to do open-fetch-close processing
>which is not good
>
>2. Use max(col-1) , max(col-2) .. , max(col-n)
> from table where .....
> This will not give a single row But gives the maximum values from
>all rows which satisfy the criteria . But we need column values from a
>single row .
>
>Any ideas
>
>Cheers,
>Kishore
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>

___________________________
Steve Mazer
Senior Database Consultant
Fourth Millennium Technologies
IBM Gold Consultant



[login to unmask email]

Re: Ignore Duplicate Rows ?
(in response to Steve Mazer)
Kishore,

I seem to remember that when a -811 is received there are values returned
for a given row to the application and that it is unpredictable as to which
row will be returned at any given execution of the SQL. If this is the
case then this row could be used as your random row with no need to open a
cursor.

Someone please correct me if I'm mistaken.

Cheers,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 878-3525, Tie Line 8-427-3525
-----------------------------------------------------
Happiness is not around the corner.
Happiness is the corner.
- BMW
-----------------------------------------------------


kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99 12:58:13
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Ignore Duplicate Rows ?



Hi ,
Rows from a table have to be selected based on a specific criteria . If
Duplicates are found we need to select any row randomly . How can we do
this
most efficiently .
We thought of few alternatives :
1.

Select from a table based on specific criteria
If -811
Open the cursor
Fetch only once
Close the cursor
End-if
For getting one row , We need to do open-fetch-close processing
which is not good

2. Use max(col-1) , max(col-2) .. , max(col-n)
from table where .....
This will not give a single row But gives the maximum values from
all rows which satisfy the criteria . But we need column values from a
single row .

Any ideas

Cheers,
Kishore

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Richard A Yevich

Re: Ignore Duplicate Rows ?
(in response to damcon2@US.IBM.COM)
Jay,

The contents of the host-variables are unpredictable when an -811 occurs.
They may or may not have valid data. This is documented in the IBM Manual
now.

Richard Yevich

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> [login to unmask email]
> Sent: Monday, December 27, 1999 2:46 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
>
> Kishore,
>
> I seem to remember that when a -811 is received there are values returned
> for a given row to the application and that it is unpredictable
> as to which
> row will be returned at any given execution of the SQL. If this is the
> case then this row could be used as your random row with no need to open a
> cursor.
>
> Someone please correct me if I'm mistaken.
>
> Cheers,
> Jay
>
>
> Jay Reavill
> [login to unmask email]
> IBM Global Services
> Tampa, Florida
> Tel: (813) 878-3525, Tie Line 8-427-3525
> -----------------------------------------------------
> Happiness is not around the corner.
> Happiness is the corner.
> - BMW
> -----------------------------------------------------
>
>
> kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99 12:58:13
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Subject: Ignore Duplicate Rows ?
>
>
>
> Hi ,
> Rows from a table have to be selected based on a specific
> criteria . If
> Duplicates are found we need to select any row randomly . How can we do
> this
> most efficiently .
> We thought of few alternatives :
> 1.
>
> Select from a table based on specific criteria
> If -811
> Open the cursor
> Fetch only once
> Close the cursor
> End-if
> For getting one row , We need to do open-fetch-close processing
> which is not good
>
> 2. Use max(col-1) , max(col-2) .. , max(col-n)
> from table where .....
> This will not give a single row But gives the maximum values from
> all rows which satisfy the criteria . But we need column values from a
> single row .
>
> Any ideas
>
> Cheers,
> Kishore
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



[login to unmask email]

Re: Ignore Duplicate Rows ?
(in response to Richard A Yevich)
I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
will be efficient if u want row to be returned randomly.

Thanks

Sanjeev





kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99 05:58:13
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Ignore Duplicate Rows ?


Hi ,
Rows from a table have to be selected based on a specific criteria . If
Duplicates are found we need to select any row randomly . How can we do
this
most efficiently .
We thought of few alternatives :
1.

Select from a table based on specific criteria
If -811
Open the cursor
Fetch only once
Close the cursor
End-if
For getting one row , We need to do open-fetch-close processing
which is not good

2. Use max(col-1) , max(col-2) .. , max(col-n)
from table where .....
This will not give a single row But gives the maximum values from
all rows which satisfy the criteria . But we need column values from a
single row .

Any ideas

Cheers,
Kishore

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Venkat (PCA) Pillay

Re: Ignore Duplicate Rows ?
(in response to ssethi@LOT.TATASTEEL.COM)
Sanjeev

Singleton select does not optimize for 1 row. One has to use cursor
to use OPTIMIZE FOR 1 ROW.

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Monday, December 27, 1999 10:27 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
> I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
> will be efficient if u want row to be returned randomly.
>
> Thanks
>
> Sanjeev
>
>
>
>
>
> kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99 05:58:13
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Subject: Ignore Duplicate Rows ?
>
>
> Hi ,
> Rows from a table have to be selected based on a specific criteria .
> If
> Duplicates are found we need to select any row randomly . How can we do
> this
> most efficiently .
> We thought of few alternatives :
> 1.
>
> Select from a table based on specific criteria
> If -811
> Open the cursor
> Fetch only once
> Close the cursor
> End-if
> For getting one row , We need to do open-fetch-close processing
> which is not good
>
> 2. Use max(col-1) , max(col-2) .. , max(col-n)
> from table where .....
> This will not give a single row But gives the maximum values from
> all rows which satisfy the criteria . But we need column values from a
> single row .
>
> Any ideas
>
> Cheers,
> Kishore
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>
>
>
>
>
>



Sibimon Philip

Re: Ignore Duplicate Rows ?
(in response to Venkat (PCA) Pillay)
In our shop we take value in the host variable for processing if -811 is
returned. We do not open, fetch and close in order to get a row if there is
a -811. So far no problem even though manual says the result is
unpredictable.

I know that there was a discussion regarding -811 some time back and I
expressed that -811 should be considered as warning not as an error. This
will make the programming a lot easier in the situation like this. Since I
was a programmer before becoming a DBA, I believe that DBMS should make any
thing possible to make programming faster without compromising the integrity
of data and performance.

Standard disclaimer. All opinions are my own.

Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]



-----Original Message-----
From: Pillay, Venkat (PCA) [mailto:[login to unmask email]
Sent: Tuesday, December 28, 1999 8:23 AM
To: [login to unmask email]
Subject: Re: Ignore Duplicate Rows ?


Sanjeev

Singleton select does not optimize for 1 row. One has to use cursor
to use OPTIMIZE FOR 1 ROW.

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Monday, December 27, 1999 10:27 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
> I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
> will be efficient if u want row to be returned randomly.
>
> Thanks
>
> Sanjeev
>
>
>
>
>
> kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99 05:58:13
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Subject: Ignore Duplicate Rows ?
>
>
> Hi ,
> Rows from a table have to be selected based on a specific criteria .
> If
> Duplicates are found we need to select any row randomly . How can we do
> this
> most efficiently .
> We thought of few alternatives :
> 1.
>
> Select from a table based on specific criteria
> If -811
> Open the cursor
> Fetch only once
> Close the cursor
> End-if
> For getting one row , We need to do open-fetch-close processing
> which is not good
>
> 2. Use max(col-1) , max(col-2) .. , max(col-n)
> from table where .....
> This will not give a single row But gives the maximum values from
> all rows which satisfy the criteria . But we need column values from a
> single row .
>
> Any ideas
>
> Cheers,
> Kishore
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>
>
>
>
>
>








Gary V Scarcella

Re: Ignore Duplicate Rows ?
(in response to Sibimon Philip)
I think you should believe the IBM manual and not rely on unpredictable
results that just happen to be OK so far.
Remember that even if results seem to be OK now that the unpredictable
result could start happening after any DB2 upgrade/PTF and then your "easy
programming" technique will turn into a nightmare.

Gary Scarcella
Internet address: [login to unmask email]

> ----------
> From: Philip, Sibimon[SMTP:[login to unmask email]
> Reply To: DB2 Data Base Discussion List
> Sent: Tuesday, December 28, 1999 10:36 AM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
> In our shop we take value in the host variable for processing if -811 is
> returned. We do not open, fetch and close in order to get a row if there
> is
> a -811. So far no problem even though manual says the result is
> unpredictable.
>
> I know that there was a discussion regarding -811 some time back and I
> expressed that -811 should be considered as warning not as an error. This
> will make the programming a lot easier in the situation like this. Since I
> was a programmer before becoming a DBA, I believe that DBMS should make
> any
> thing possible to make programming faster without compromising the
> integrity
> of data and performance.
>
> Standard disclaimer. All opinions are my own.
>
> Thanks
> Sibimon Philip
> 972-702-2515 - Office
> 972-417-3597 - Residence
> E-mail - [login to unmask email]
>
>
>
> -----Original Message-----
> From: Pillay, Venkat (PCA) [mailto:[login to unmask email]
> Sent: Tuesday, December 28, 1999 8:23 AM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
>
> Sanjeev
>
> Singleton select does not optimize for 1 row. One has to use
> cursor
> to use OPTIMIZE FOR 1 ROW.
>
> > -----Original Message-----
> > From: [login to unmask email] [SMTP:[login to unmask email]
> > Sent: Monday, December 27, 1999 10:27 PM
> > To: [login to unmask email]
> > Subject: Re: Ignore Duplicate Rows ?
> >
> > I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
> > will be efficient if u want row to be returned randomly.
> >
> > Thanks
> >
> > Sanjeev
> >
> >
> >
> >
> >
> > kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99
> 05:58:13
> > PM
> >
> > Please respond to DB2 Data Base Discussion List <[login to unmask email]>
> >
> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> >
> >
> > To: [login to unmask email]
> > cc:
> > Subject: Ignore Duplicate Rows ?
> >
> >
> > Hi ,
> > Rows from a table have to be selected based on a specific criteria .
> > If
> > Duplicates are found we need to select any row randomly . How can we do
> > this
> > most efficiently .
> > We thought of few alternatives :
> > 1.
> >
> > Select from a table based on specific criteria
> > If -811
> > Open the cursor
> > Fetch only once
> > Close the cursor
> > End-if
> > For getting one row , We need to do open-fetch-close processing
> > which is not good
> >
> > 2. Use max(col-1) , max(col-2) .. , max(col-n)
> > from table where .....
> > This will not give a single row But gives the maximum values from
> > all rows which satisfy the criteria . But we need column values from a
> > single row .
> >
> > Any ideas
> >
> > Cheers,
> > Kishore
> >
> > ______________________________________________________
> > Get Your Private, Free Email at http://www.hotmail.com
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
>
>
>
> the
>
>
>
>
>
>
>
>



kishore erukulapati

Re: Ignore Duplicate Rows ?
(in response to Gary V Scarcella)
But Optimize for 1 Row can be used only if the no. of rows returned
is only one row . In this case there may be many duplicate rows.
Optimize for 1 row degrades the performance .

Kishore


>From: "Pillay, Venkat (PCA)" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Ignore Duplicate Rows ?
>Date: Tue, 28 Dec 1999 09:23:21 -0500
>
>Sanjeev
>
> Singleton select does not optimize for 1 row. One has to use
>cursor
>to use OPTIMIZE FOR 1 ROW.
>
> > -----Original Message-----
> > From: [login to unmask email] [SMTP:[login to unmask email]
> > Sent: Monday, December 27, 1999 10:27 PM
> > To: [login to unmask email]
> > Subject: Re: Ignore Duplicate Rows ?
> >
> > I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
> > will be efficient if u want row to be returned randomly.
> >
> > Thanks
> >
> > Sanjeev
> >
> >
> >
> >
> >
> > kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99
>05:58:13
> > PM
> >
> > Please respond to DB2 Data Base Discussion List <[login to unmask email]>
> >
> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> >
> >
> > To: [login to unmask email]
> > cc:
> > Subject: Ignore Duplicate Rows ?
> >
> >
> > Hi ,
> > Rows from a table have to be selected based on a specific criteria .
> > If
> > Duplicates are found we need to select any row randomly . How can we do
> > this
> > most efficiently .
> > We thought of few alternatives :
> > 1.
> >
> > Select from a table based on specific criteria
> > If -811
> > Open the cursor
> > Fetch only once
> > Close the cursor
> > End-if
> > For getting one row , We need to do open-fetch-close processing
> > which is not good
> >
> > 2. Use max(col-1) , max(col-2) .. , max(col-n)
> > from table where .....
> > This will not give a single row But gives the maximum values from
> > all rows which satisfy the criteria . But we need column values from a
> > single row .
> >
> > Any ideas
> >
> > Cheers,
> > Kishore
> >
> > ______________________________________________________
> > Get Your Private, Free Email at http://www.hotmail.com
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
>can
> >
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
>can
> >
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Ken Koleto

Re: Ignore Duplicate Rows ?
(in response to Sibimon Philip)
I agree with you, Gary. There's another 'easy technique' that's making most of us work this weekend!

Ken

usual disclaimer...



Sibimon Philip

Re: Ignore Duplicate Rows ?
(in response to kishore erukulapati)
Kishore,

Optimize for 1 Row tells DB2 to optimize the cursor's access path to get
only one row. There can be multiple rows satisfied in the cursor. But if you
fetch all the rows, the access path selected may not be good and it may be
costly.

But in some case I have seen in three large table join, DB2 was using merge
scan and hybrid join which was taking lot of time and when we put optimize
clause it used nested loop join and the total time DB2 took was 1/4th. You
can test it and see which one is faster.

Thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]

-----Original Message-----
From: kishore erukulapati [mailto:[login to unmask email]
Sent: Tuesday, December 28, 1999 12:15 PM
To: [login to unmask email]
Subject: Re: Ignore Duplicate Rows ?


But Optimize for 1 Row can be used only if the no. of rows returned
is only one row . In this case there may be many duplicate rows.
Optimize for 1 row degrades the performance .

Kishore


>From: "Pillay, Venkat (PCA)" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Ignore Duplicate Rows ?
>Date: Tue, 28 Dec 1999 09:23:21 -0500
>
>Sanjeev
>
> Singleton select does not optimize for 1 row. One has to use
>cursor
>to use OPTIMIZE FOR 1 ROW.
>
> > -----Original Message-----
> > From: [login to unmask email] [SMTP:[login to unmask email]
> > Sent: Monday, December 27, 1999 10:27 PM
> > To: [login to unmask email]
> > Subject: Re: Ignore Duplicate Rows ?
> >
> > I think simple SELECT with OPTIMIZE FOR 1 ROW and handling -811 sqlcode
> > will be efficient if u want row to be returned randomly.
> >
> > Thanks
> >
> > Sanjeev
> >
> >
> >
> >
> >
> > kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99
>05:58:13
> > PM
> >
> > Please respond to DB2 Data Base Discussion List <[login to unmask email]>
> >
> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> >
> >
> > To: [login to unmask email]
> > cc:
> > Subject: Ignore Duplicate Rows ?
> >
> >
> > Hi ,
> > Rows from a table have to be selected based on a specific criteria .
> > If
> > Duplicates are found we need to select any row randomly . How can we do
> > this
> > most efficiently .
> > We thought of few alternatives :
> > 1.
> >
> > Select from a table based on specific criteria
> > If -811
> > Open the cursor
> > Fetch only once
> > Close the cursor
> > End-if
> > For getting one row , We need to do open-fetch-close processing
> > which is not good
> >
> > 2. Use max(col-1) , max(col-2) .. , max(col-n)
> > from table where .....
> > This will not give a single row But gives the maximum values from
> > all rows which satisfy the criteria . But we need column values from a
> > single row .
> >
> > Any ideas
> >
> > Cheers,
> > Kishore
> >
> > ______________________________________________________
> > Get Your Private, Free Email at http://www.hotmail.com
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
>can
> >
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
>can
> >
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Terry Purcell

Re: Ignore Duplicate Rows ?
(in response to Ken Koleto)
Kishore,

We have concensus that you should not rely on the results after -811 (as per
IBM manual). Before answering your question, I am intrigued to know why you
believe open-fetch-close processing is not good?

Assuming materialisation is not required (if it was then singleton select
and cursor would give similar performance), then if a singleton select was
used, you would need to cater for the use of a cursor for those -811
situations, or use column functions (MIN, MAX etc.). Use of the column
function can range from OK (if index to support, and small number of rows
qualify) to disasterous (if it introduces materialisation). If you want the
entire row, then you need to concatenate the entire row, then perform the
column function (very ugly).

Remember however, a singleton select does not simply return when it
qualifies one row, it must always perform a "look" for a second qualifying
row to evaluate the -811 condition.

My recommendation would be to code a cursor and perform 2 fetches (the
second fetch to determine the duplicate); and code OPTIMIZE FOR 2 ROWS. The
access path can avoid sequential or list prefetch, multi-index processing,
or the overhead of invoking parallel processing or a merge-scan or hybird
join with the use of the optimize clause (especially OPTIMIZE FOR 1 ROW).
Any of these alone can cause the cursor to significantly outperform the
singleton select (singleton select cannot easily disable many of these).

Obviously the above mentioned detail has been compiled without specific
knowledge of your SQL requirements. If you do not believe this applies to
your SQL, then you may wish to provide more detail to the listserver so that
we can make a more informed judgement.

Regards
Terry Purcell
CPT Consulting

> -----Original Message-----
> From: kishore erukulapati [SMTP:[login to unmask email]
> Sent: Tuesday, 28 December 1999 4:58 am
> To: [login to unmask email]
> Subject: Ignore Duplicate Rows ?
>
> Hi ,
> Rows from a table have to be selected based on a specific criteria .
> If
> Duplicates are found we need to select any row randomly . How can we do
> this
> most efficiently .
> We thought of few alternatives :
> 1.
>
> Select from a table based on specific criteria
> If -811
> Open the cursor
> Fetch only once
> Close the cursor
> End-if
> For getting one row , We need to do open-fetch-close processing
> which is not good
>
> 2. Use max(col-1) , max(col-2) .. , max(col-n)
> from table where .....
> This will not give a single row But gives the maximum values from
> all rows which satisfy the criteria . But we need column values from a
> single row .
>
> Any ideas
>
> Cheers,
> Kishore
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>



Richard A Yevich

Re: Ignore Duplicate Rows ?
(in response to Richard A Yevich)
Terry/Kishore,

Terry great response but let's go one further. A single SQL SELECT
statement IS A CURSOR with the two fetches possible. It is just that the
OPEN CURSOR, FETCH, CLOSE CURSOR is handled logically inside of DB2 instead
of the programmer issuing the CALL.

As Michael Hannon, and others, have proven through benchmarks over and over,
an OPEN CURSOR with OPTIMIZE FOR 1 ROW, FETCH, CLOSE CURSOR, is difficult to
beat. The details of this have been published on DB2-L at least 6 times over
the last few years. They are in the archives if anyone is truly interested.

Richard Yevich

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Purcell, Terry
> Sent: Tuesday, December 28, 1999 8:47 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
>
> Kishore,
>
> We have concensus that you should not rely on the results after
> -811 (as per
> IBM manual). Before answering your question, I am intrigued to
> know why you
> believe open-fetch-close processing is not good?
>
> Assuming materialisation is not required (if it was then singleton select
> and cursor would give similar performance), then if a singleton select was
> used, you would need to cater for the use of a cursor for those -811
> situations, or use column functions (MIN, MAX etc.). Use of the column
> function can range from OK (if index to support, and small number of rows
> qualify) to disasterous (if it introduces materialisation). If
> you want the
> entire row, then you need to concatenate the entire row, then perform the
> column function (very ugly).
>
> Remember however, a singleton select does not simply return when it
> qualifies one row, it must always perform a "look" for a second qualifying
> row to evaluate the -811 condition.
>
> My recommendation would be to code a cursor and perform 2 fetches (the
> second fetch to determine the duplicate); and code OPTIMIZE FOR 2
> ROWS. The
> access path can avoid sequential or list prefetch, multi-index processing,
> or the overhead of invoking parallel processing or a merge-scan or hybird
> join with the use of the optimize clause (especially OPTIMIZE FOR 1 ROW).
> Any of these alone can cause the cursor to significantly outperform the
> singleton select (singleton select cannot easily disable many of these).
>
> Obviously the above mentioned detail has been compiled without specific
> knowledge of your SQL requirements. If you do not believe this applies to
> your SQL, then you may wish to provide more detail to the
> listserver so that
> we can make a more informed judgement.
>
> Regards
> Terry Purcell
> CPT Consulting
>
> > -----Original Message-----
> > From: kishore erukulapati [SMTP:[login to unmask email]
> > Sent: Tuesday, 28 December 1999 4:58 am
> > To: [login to unmask email]
> > Subject: Ignore Duplicate Rows ?
> >
> > Hi ,
> > Rows from a table have to be selected based on a specific criteria .
> > If
> > Duplicates are found we need to select any row randomly . How can we do
> > this
> > most efficiently .
> > We thought of few alternatives :
> > 1.
> >
> > Select from a table based on specific criteria
> > If -811
> > Open the cursor
> > Fetch only once
> > Close the cursor
> > End-if
> > For getting one row , We need to do open-fetch-close processing
> > which is not good
> >
> > 2. Use max(col-1) , max(col-2) .. , max(col-n)
> > from table where .....
> > This will not give a single row But gives the maximum values from
> > all rows which satisfy the criteria . But we need column values from a
> > single row .
> >
> > Any ideas
> >
> > Cheers,
> > Kishore
> >
> > ______________________________________________________
> > Get Your Private, Free Email at http://www.hotmail.com
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can
> >
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Richard A Yevich

Re: Ignore Duplicate Rows ?
(in response to Terry Purcell)
Philip,

Almost correct but change a little wording. OPTIMIZE FOR 1 ROW tells DB2 to
get the first row using the "fastest possible access path" for the first and
perhaps only 1 row. This of course, can also be the fastest access path,
for the rest of the rows. It truly depends on the physical design, SQL
complexities, etc.

Richard Yevich

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> Philip, Sibimon
> Sent: Tuesday, December 28, 1999 12:38 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
>
> Kishore,
>
> Optimize for 1 Row tells DB2 to optimize the cursor's access path to get
> only one row. There can be multiple rows satisfied in the cursor.
> But if you
> fetch all the rows, the access path selected may not be good and it may be
> costly.
>
> But in some case I have seen in three large table join, DB2 was
> using merge
> scan and hybrid join which was taking lot of time and when we put optimize
> clause it used nested loop join and the total time DB2 took was 1/4th. You
> can test it and see which one is faster.
>
> Thanks
> Sibimon Philip
> 972-702-2515 - Office
> 972-417-3597 - Residence
> E-mail - [login to unmask email]
>
> -----Original Message-----
> From: kishore erukulapati [mailto:[login to unmask email]
> Sent: Tuesday, December 28, 1999 12:15 PM
> To: [login to unmask email]
> Subject: Re: Ignore Duplicate Rows ?
>
>
> But Optimize for 1 Row can be used only if the no. of rows returned
> is only one row . In this case there may be many duplicate rows.
> Optimize for 1 row degrades the performance .
>
> Kishore
>
>
> >From: "Pillay, Venkat (PCA)" <[login to unmask email]>
> >Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: Re: Ignore Duplicate Rows ?
> >Date: Tue, 28 Dec 1999 09:23:21 -0500
> >
> >Sanjeev
> >
> > Singleton select does not optimize for 1 row. One has to use
> >cursor
> >to use OPTIMIZE FOR 1 ROW.
> >
> > > -----Original Message-----
> > > From: [login to unmask email] [SMTP:[login to unmask email]
> > > Sent: Monday, December 27, 1999 10:27 PM
> > > To: [login to unmask email]
> > > Subject: Re: Ignore Duplicate Rows ?
> > >
> > > I think simple SELECT with OPTIMIZE FOR 1 ROW and handling
> -811 sqlcode
> > > will be efficient if u want row to be returned randomly.
> > >
> > > Thanks
> > >
> > > Sanjeev
> > >
> > >
> > >
> > >
> > >
> > > kishore erukulapati <[login to unmask email]>@RYCI.COM> on 12/27/99
> >05:58:13
> > > PM
> > >
> > > Please respond to DB2 Data Base Discussion List <[login to unmask email]>
> > >
> > > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> > >
> > >
> > > To: [login to unmask email]
> > > cc:
> > > Subject: Ignore Duplicate Rows ?
> > >
> > >
> > > Hi ,
> > > Rows from a table have to be selected based on a specific
> criteria .
> > > If
> > > Duplicates are found we need to select any row randomly . How
> can we do
> > > this
> > > most efficiently .
> > > We thought of few alternatives :
> > > 1.
> > >
> > > Select from a table based on specific criteria
> > > If -811
> > > Open the cursor
> > > Fetch only once
> > > Close the cursor
> > > End-if
> > > For getting one row , We need to do open-fetch-close processing
> > > which is not good
> > >
> > > 2. Use max(col-1) , max(col-2) .. , max(col-n)
> > > from table where .....
> > > This will not give a single row But gives the maximum values from
> > > all rows which satisfy the criteria . But we need column values from a
> > > single row .
> > >
> > > Any ideas
> > >
> > > Cheers,
> > > Kishore
> > >
> > > ______________________________________________________
> > > Get Your Private, Free Email at http://www.hotmail.com
> > >
> > >
> > > To change your subscription options or to cancel your
> subscription visit
> > > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> >can
> > >
> > >
> > >
> > > To change your subscription options or to cancel your
> subscription visit
> > > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> >can
> > >
> >
> >
> >
> >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can
> >
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
> To change your subscription options or to cancel your
> subscription visit the
>
>
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]