Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Sic transit discus mundi -- From the System Administrator's Guide, by Lars Wirzenius


computers / comp.sys.tandem / Re: SQL Cursor skipping records

SubjectAuthor
* SQL Cursor skipping recordsAdam “Adamlivinlife” Kamal
`* Re: SQL Cursor skipping recordsJShepherd
 `* Re: SQL Cursor skipping recordsAdam “Adamlivinlife” Kamal
  +- Re: SQL Cursor skipping recordsJShepherd
  `* Re: SQL Cursor skipping recordsJShepherd
   `* Re: SQL Cursor skipping recordsAdam “Adamlivinlife” Kamal
    `* Re: SQL Cursor skipping recordsJShepherd
     `* Re: SQL Cursor skipping recordsAdam “Adamlivinlife” Kamal
      +- Re: SQL Cursor skipping recordsRandall
      `* Re: SQL Cursor skipping recordsJShepherd
       `- Re: SQL Cursor skipping recordsJShepherd

1
SQL Cursor skipping records

<e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=684&group=comp.sys.tandem#684

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ae9:df43:0:b0:767:420d:cec2 with SMTP id t64-20020ae9df43000000b00767420dcec2mr34862qkf.5.1691781329068;
Fri, 11 Aug 2023 12:15:29 -0700 (PDT)
X-Received: by 2002:a17:902:f68f:b0:1bb:cd10:823f with SMTP id
l15-20020a170902f68f00b001bbcd10823fmr1029519plg.5.1691781328682; Fri, 11 Aug
2023 12:15:28 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Fri, 11 Aug 2023 12:15:28 -0700 (PDT)
Injection-Info: google-groups.googlegroups.com; posting-host=65.125.102.200; posting-account=0NHiHgoAAAC-mY8P0yk1bd3zDKN8t6jB
NNTP-Posting-Host: 65.125.102.200
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
Subject: SQL Cursor skipping records
From: abolk...@gmail.com (Adam “Adamlivinlife” Kamal)
Injection-Date: Fri, 11 Aug 2023 19:15:29 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 2491
 by: Adam “Adamlivinlif - Fri, 11 Aug 2023 19:15 UTC

I have a SQL cursor that is used to retrieve records from parts table. The program is designed to return no more than 5000 records per read, then it closed the cursor.

In case if there are more records than 5000, it saves off record number 5001 and use it to reopen the cursor and get the rest of the records.

The issue appears to be not all records is returned on the second read.

The cursor look like this

EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY
, WAREHOUSE
, REPORT_NUMBER
, SOURCE_OF_SUPPLY
, PART_NUM
, BIN_LOC

FROM =PARTS FOR BROWSE ACCESS
WHERE COMPANY = :SEARCH-COMPANY AND
WAREHOUSE = :SEARCH-WAREHOUSE AND
REPORT_NUMBER = :SEARCH-REPORT-NUMBER AND
SOURCE_OF_SUPPLY >= :SEARCH-SOS AND
PART_NUM >= :SEARCH-PART-NUM AND
BIN_LOC >= :SEARCH-BIN-LOC AND
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM, BIN_LOC, ALT_BIN ASC
END-EXEC.

On the second read/Open cursor, it returns the record which was saved off to use it to reopen the cursor. but then it skips a bunch of records. I was hoping someone would be able to tell me what am i doing wrong in the cursor. Thanks in advance and have a wonderful weekend !

Re: SQL Cursor skipping records

<ub6atu$10fds$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=685&group=comp.sys.tandem#685

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Fri, 11 Aug 2023 21:58:23 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 45
Message-ID: <ub6atu$10fds$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Fri, 11 Aug 2023 21:58:23 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="12e15db8c84e46dbfda4c20ca23d10d5";
logging-data="1064380"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/ddj+ESh/taln+k0KlkpemweBLyFIq1AU="
Cancel-Lock: sha1:uZjBmDUOV5lBRA7RA0PtFkRq1Kc=
 by: JShepherd - Fri, 11 Aug 2023 21:58 UTC

In article <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>,
abolkini@gmail.com says...
>
>I have a SQL cursor that is used to retrieve records from parts table. The =
>program is designed to return no more than 5000 records per read, then it c=
>losed the cursor.=20
>
>In case if there are more records than 5000, it saves off record number 500=
>1 and use it to reopen the cursor and get the rest of the records.
>
>The issue appears to be not all records is returned on the second read.
>
>The cursor look like this
>
>EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> SELECT COMPANY=20
> , WAREHOUSE
> , REPORT_NUMBER
> , SOURCE_OF_SUPPLY
> , PART_NUM
> , BIN_LOC
>
> FROM =3DPARTS FOR BROWSE ACCESS
> WHERE COMPANY =3D :SEARCH-COMPANY AND
> WAREHOUSE =3D :SEARCH-WAREHOUSE AND
> REPORT_NUMBER =3D :SEARCH-REPORT-NUMBER AND
> SOURCE_OF_SUPPLY >=3D :SEARCH-SOS AND
> PART_NUM >=3D :SEARCH-PART-NUM AND
> BIN_LOC >=3D :SEARCH-BIN-LOC AND
> ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM,=
> BIN_LOC, ALT_BIN ASC
> END-EXEC.
>
>On the second read/Open cursor, it returns the record which was saved off =
>to use it to reopen the cursor. but then it skips a bunch of records. I was=
> hoping someone would be able to tell me what am i doing wrong in the curso=
>r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"

What are the keycols for the table ?

What does an explain plan for the statement show ?

Re: SQL Cursor skipping records

<9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=686&group=comp.sys.tandem#686

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a05:620a:4729:b0:768:b0c:e4b7 with SMTP id bs41-20020a05620a472900b007680b0ce4b7mr151353qkb.7.1692057064954;
Mon, 14 Aug 2023 16:51:04 -0700 (PDT)
X-Received: by 2002:a17:90a:f988:b0:263:3b44:43ae with SMTP id
cq8-20020a17090af98800b002633b4443aemr2407544pjb.8.1692057064604; Mon, 14 Aug
2023 16:51:04 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Mon, 14 Aug 2023 16:51:03 -0700 (PDT)
In-Reply-To: <ub6atu$10fds$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=65.125.102.200; posting-account=0NHiHgoAAAC-mY8P0yk1bd3zDKN8t6jB
NNTP-Posting-Host: 65.125.102.200
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com> <ub6atu$10fds$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
Subject: Re: SQL Cursor skipping records
From: abolk...@gmail.com (Adam “Adamlivinlife” Kamal)
Injection-Date: Mon, 14 Aug 2023 23:51:04 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 5716
 by: Adam “Adamlivinlif - Mon, 14 Aug 2023 23:51 UTC

On Friday, August 11, 2023 at 2:59:09 PM UTC-7, JShepherd wrote:
> In article <e10c4470-30c4-437b...@googlegroups.com>,
> abol...@gmail.com says...
> >
> >I have a SQL cursor that is used to retrieve records from parts table. The =
> >program is designed to return no more than 5000 records per read, then it c=
> >losed the cursor.=20
> >
> >In case if there are more records than 5000, it saves off record number 500> >1 and use it to reopen the cursor and get the rest of the records.
> >
> >The issue appears to be not all records is returned on the second read.
> >
> >The cursor look like this
> >
> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> > SELECT COMPANY=20
> > , WAREHOUSE
> > , REPORT_NUMBER
> > , SOURCE_OF_SUPPLY
> > , PART_NUM
> > , BIN_LOC
> >
> > FROM =3DPARTS FOR BROWSE ACCESS
> > WHERE COMPANY =3D :SEARCH-COMPANY AND
> > WAREHOUSE =3D :SEARCH-WAREHOUSE AND
> > REPORT_NUMBER =3D :SEARCH-REPORT-NUMBER AND
> > SOURCE_OF_SUPPLY >=3D :SEARCH-SOS AND
> > PART_NUM >=3D :SEARCH-PART-NUM AND
> > BIN_LOC >=3D :SEARCH-BIN-LOC AND
> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM,=
> > BIN_LOC, ALT_BIN ASC
> > END-EXEC.
> >
> >On the second read/Open cursor, it returns the record which was saved off =
> >to use it to reopen the cursor. but then it skips a bunch of records. I was=
> > hoping someone would be able to tell me what am i doing wrong in the curso> >r. Thanks in advance and have a wonderful weekend !
> The where clause shown seems to end with "AND ORDER BY <col list>"
>
> What are the keycols for the table ?
>
> What does an explain plan for the statement show ?

Happy Monday,

Thanks for taking the time to reply back. The key is the selected columns
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NOT NULL
, WARE CHAR(3) NO DEFAULT NOT NULL
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
, PART_NUM CHAR(30) NO DEFAULT NOT NULL
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL

Since you brought up the ORDER BY and explain plan, I noticed the key of the table is in ascending order and exactly is my ORDER BY columns, so I removed ORDER BY from the cursor but It still skipped records.

Here is EXPLAIN plan:

Operation 1.0 : Scan
Table PARTS
with correlation name PARTS
Access type : No locks, browse access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 19 out of 20 columns
Access path 1 : Primary, sequential cache
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY= :SEARCH-COMPANY , WARE = :SEARCH-WARE ,
REPORT_NUMBER = :SEARCH-REPORT-NUMBER
End key pred. : COMPANY = :SEARCH-COMPANY , WARE :SEARCH-WARE , REPORT_NUMBER = :SEARCH-REPORT-NUMBER
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >= :SEARCH-SOS ) AND ( BIN_LOC >= :SEARCH-BIN-LOC )
AND ( PART_NUM >= :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
Executor pred. : None
Table selectivity : Expect to select 1.2341% of rows from table
Expected row count: 5 rows after the scan
Operation cost : 35
Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 34 Kbytes
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
PARTS.REPORT_NUMBER asc,
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,
Sort cost : 1
Total cost : 36

Re: SQL Cursor skipping records

<ubgc02$2tb3g$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=687&group=comp.sys.tandem#687

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Tue, 15 Aug 2023 17:17:54 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 129
Message-ID: <ubgc02$2tb3g$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com> <ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Tue, 15 Aug 2023 17:17:54 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="39a7410a63f25d709f4b635490f05025";
logging-data="3058800"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19FY3aVJmh+oHndEH+1CRHzQv0PmCkVLYA="
Cancel-Lock: sha1:ipBe3qS3/iF5WN+LKdW3+DufoZ0=
 by: JShepherd - Tue, 15 Aug 2023 17:17 UTC

In article <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>,
abolkini@gmail.com says...
>
>On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:
>> In article <e10c4470-30c4-437b...@googlegroups.com>,=20
>> abol...@gmail.com says...=20
>> >=20
>> >I have a SQL cursor that is used to retrieve records from parts table. T=
>he =3D=20
>> >program is designed to return no more than 5000 records per read, then i=
>t c=3D=20
>> >losed the cursor.=3D20=20
>> >=20
>> >In case if there are more records than 5000, it saves off record number =
>500=3D
>> >1 and use it to reopen the cursor and get the rest of the records.=20
>> >=20
>> >The issue appears to be not all records is returned on the second read.=
>=20
>> >=20
>> >The cursor look like this=20
>> >=20
>> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
>> > SELECT COMPANY=3D20
>> > , WAREHOUSE=20
>> > , REPORT_NUMBER=20
>> > , SOURCE_OF_SUPPLY=20
>> > , PART_NUM=20
>> > , BIN_LOC=20
>> >
>> > FROM =3D3DPARTS FOR BROWSE ACCESS=20
>> > WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
>> > WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
>> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
>> > SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
>> > PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
>> > BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
>> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM=
>,=3D=20
>> > BIN_LOC, ALT_BIN ASC=20
>> > END-EXEC.=20
>> >=20
>> >On the second read/Open cursor, it returns the record which was saved of=
>f =3D=20
>> >to use it to reopen the cursor. but then it skips a bunch of records. I =
>was=3D=20
>> > hoping someone would be able to tell me what am i doing wrong in the cu=
>rso=3D
>> >r. Thanks in advance and have a wonderful weekend !
>> The where clause shown seems to end with "AND ORDER BY <col list>"=20
>>=20
>> What are the keycols for the table ?=20
>>=20
>> What does an explain plan for the statement show ?
>
>Happy Monday,
>
>Thanks for taking the time to reply back. The key is the selected columns=
>=20
> COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=
>T NULL
> , WARE CHAR(3) NO DEFAULT NOT NULL
> , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
> , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
> , PART_NUM CHAR(30) NO DEFAULT NOT NULL
> , BIN_LOC CHAR(9) NO DEFAULT NOT NULL
>
>
>Since you brought up the ORDER BY and explain plan, I noticed the key of th=
>e table is in ascending order and exactly is my ORDER BY columns, so I remo=
>ved ORDER BY from the cursor but It still skipped records.=20
>
>Here is EXPLAIN plan:
>
> Operation 1.0 : Scan
> Table PARTS
> with correlation name PARTS
> Access type : No locks, browse access
> Lock mode : Chosen by the system
> Column processing : Requires retrieval of 19 out of 20 columns
>=20
> Access path 1 : Primary, sequential cache
> SBB for reads : Virtual, double buffer
> Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
>E ,=20
> REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
>ER
> End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
> :SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
>UMBER
> Index selectivity : Expect to examine 100% of rows from table
> Index pred. : None
> Base table pred. : Will be evaluated by the disk process
> ( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_=
>LOC >=3D :SEARCH-BIN-LOC )
> AND ( PART_NUM >=3D :SEARCH-PART-NUM )
> Pred. selectivity : Expect to select 1.2341% of rows from table
>=20
> Executor pred. : None
> Table selectivity : Expect to select 1.2341% of rows from table
> Expected row count: 5 rows after the scan
> Operation cost : 35
>=20
> Operation 1.1 : Sort
> Requested : Explicitly in the query
> Sort rows in the : Result of a Select
> Purpose : To order rows for an Order By
> Sort technique : FASTSORT
> Sort type : Plan to use User Process Sort
> UPS workspace : 34 Kbytes
> Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
> PARTS.REPORT_NUMBER asc,
> PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> PARTS.BIN_LOC asc,=20
> Sort cost : 1
>=20
> Total cost : 36

Was "ORDER BY COMPANY_GROUP" a typo in the original query text ?

What was the deal with the messed up where clause in the original query ?

What is the current query text ?

Re: SQL Cursor skipping records

<ubgdpo$2tng1$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=688&group=comp.sys.tandem#688

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Tue, 15 Aug 2023 17:48:40 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 128
Message-ID: <ubgdpo$2tng1$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com> <ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Tue, 15 Aug 2023 17:48:40 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="39a7410a63f25d709f4b635490f05025";
logging-data="3071489"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/MdO+l+fciug3WTjQVOQC8Xk25VrGU49k="
Cancel-Lock: sha1:vP82fqrftc+SNb/+layC0Crxj6g=
 by: JShepherd - Tue, 15 Aug 2023 17:48 UTC

In article <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>,
abolkini@gmail.com says...
>
>On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:
>> In article <e10c4470-30c4-437b...@googlegroups.com>,=20
>> abol...@gmail.com says...=20
>> >=20
>> >I have a SQL cursor that is used to retrieve records from parts table. T=
>he =3D=20
>> >program is designed to return no more than 5000 records per read, then i=
>t c=3D=20
>> >losed the cursor.=3D20=20
>> >=20
>> >In case if there are more records than 5000, it saves off record number =
>500=3D
>> >1 and use it to reopen the cursor and get the rest of the records.=20
>> >=20
>> >The issue appears to be not all records is returned on the second read.=
>=20
>> >=20
>> >The cursor look like this=20
>> >=20
>> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
>> > SELECT COMPANY=3D20
>> > , WAREHOUSE=20
>> > , REPORT_NUMBER=20
>> > , SOURCE_OF_SUPPLY=20
>> > , PART_NUM=20
>> > , BIN_LOC=20
>> >
>> > FROM =3D3DPARTS FOR BROWSE ACCESS=20
>> > WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
>> > WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
>> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
>> > SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
>> > PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
>> > BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
>> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM=
>,=3D=20
>> > BIN_LOC, ALT_BIN ASC=20
>> > END-EXEC.=20
>> >=20
>> >On the second read/Open cursor, it returns the record which was saved of=
>f =3D=20
>> >to use it to reopen the cursor. but then it skips a bunch of records. I =
>was=3D=20
>> > hoping someone would be able to tell me what am i doing wrong in the cu=
>rso=3D
>> >r. Thanks in advance and have a wonderful weekend !
>> The where clause shown seems to end with "AND ORDER BY <col list>"=20
>>=20
>> What are the keycols for the table ?=20
>>=20
>> What does an explain plan for the statement show ?
>
>Happy Monday,
>
>Thanks for taking the time to reply back. The key is the selected columns=
>=20
> COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=
>T NULL
> , WARE CHAR(3) NO DEFAULT NOT NULL
> , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
> , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
> , PART_NUM CHAR(30) NO DEFAULT NOT NULL
> , BIN_LOC CHAR(9) NO DEFAULT NOT NULL
>
>
>Since you brought up the ORDER BY and explain plan, I noticed the key of th=
>e table is in ascending order and exactly is my ORDER BY columns, so I remo=
>ved ORDER BY from the cursor but It still skipped records.=20
>
>Here is EXPLAIN plan:
>
> Operation 1.0 : Scan
> Table PARTS
> with correlation name PARTS
> Access type : No locks, browse access
> Lock mode : Chosen by the system
> Column processing : Requires retrieval of 19 out of 20 columns
>=20
> Access path 1 : Primary, sequential cache
> SBB for reads : Virtual, double buffer
> Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
>E ,=20
> REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
>ER
> End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
> :SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
>UMBER
> Index selectivity : Expect to examine 100% of rows from table
> Index pred. : None
> Base table pred. : Will be evaluated by the disk process
> ( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_=
>LOC >=3D :SEARCH-BIN-LOC )
> AND ( PART_NUM >=3D :SEARCH-PART-NUM )
> Pred. selectivity : Expect to select 1.2341% of rows from table
>=20
> Executor pred. : None
> Table selectivity : Expect to select 1.2341% of rows from table
> Expected row count: 5 rows after the scan
> Operation cost : 35
>=20
> Operation 1.1 : Sort
> Requested : Explicitly in the query
> Sort rows in the : Result of a Select
> Purpose : To order rows for an Order By
> Sort technique : FASTSORT
> Sort type : Plan to use User Process Sort
> UPS workspace : 34 Kbytes
> Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
> PARTS.REPORT_NUMBER asc,
> PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> PARTS.BIN_LOC asc,=20
> Sort cost : 1
>=20
> Total cost : 36

--------------------------------------------------
>>In case if there are more records than 5000, it saves off record number
>>and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part
of the where clause on reopening the cursor?

Re: SQL Cursor skipping records

<3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=689&group=comp.sys.tandem#689

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ae9:dec5:0:b0:76d:228d:891a with SMTP id s188-20020ae9dec5000000b0076d228d891amr141591qkf.1.1692125990186;
Tue, 15 Aug 2023 11:59:50 -0700 (PDT)
X-Received: by 2002:a63:b547:0:b0:563:dddb:2016 with SMTP id
u7-20020a63b547000000b00563dddb2016mr2453612pgo.5.1692125989786; Tue, 15 Aug
2023 11:59:49 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Tue, 15 Aug 2023 11:59:49 -0700 (PDT)
In-Reply-To: <ubgdpo$2tng1$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=50.204.224.200; posting-account=0NHiHgoAAAC-mY8P0yk1bd3zDKN8t6jB
NNTP-Posting-Host: 50.204.224.200
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
Subject: Re: SQL Cursor skipping records
From: abolk...@gmail.com (Adam “Adamlivinlife” Kamal)
Injection-Date: Tue, 15 Aug 2023 18:59:50 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 7095
 by: Adam “Adamlivinlif - Tue, 15 Aug 2023 18:59 UTC

On Tuesday, August 15, 2023 at 10:48:43 AM UTC-7, JShepherd wrote:
> In article <9c035799-1510-429d...@googlegroups.com>,
> abol...@gmail.com says...
> >
> >On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:
> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=20
> >> abol...@gmail.com says...=20
> >> >=20
> >> >I have a SQL cursor that is used to retrieve records from parts table.. T=
> >he =3D=20
> >> >program is designed to return no more than 5000 records per read, then i=
> >t c=3D=20
> >> >losed the cursor.=3D20=20
> >> >=20
> >> >In case if there are more records than 5000, it saves off record number =
> >500=3D
> >> >1 and use it to reopen the cursor and get the rest of the records.=20
> >> >=20
> >> >The issue appears to be not all records is returned on the second read.=
> >=20
> >> >=20
> >> >The cursor look like this=20
> >> >=20
> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> >> > SELECT COMPANY=3D20
> >> > , WAREHOUSE=20
> >> > , REPORT_NUMBER=20
> >> > , SOURCE_OF_SUPPLY=20
> >> > , PART_NUM=20
> >> > , BIN_LOC=20
> >> >
> >> > FROM =3D3DPARTS FOR BROWSE ACCESS=20
> >> > WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
> >> > WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
> >> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
> >> > SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
> >> > PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
> >> > BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM=
> >,=3D=20
> >> > BIN_LOC, ALT_BIN ASC=20
> >> > END-EXEC.=20
> >> >=20
> >> >On the second read/Open cursor, it returns the record which was saved of=
> >f =3D=20
> >> >to use it to reopen the cursor. but then it skips a bunch of records. I =
> >was=3D=20
> >> > hoping someone would be able to tell me what am i doing wrong in the cu=
> >rso=3D
> >> >r. Thanks in advance and have a wonderful weekend !
> >> The where clause shown seems to end with "AND ORDER BY <col list>"=20
> >>=20
> >> What are the keycols for the table ?=20
> >>=20
> >> What does an explain plan for the statement show ?
> >
> >Happy Monday,
> >
> >Thanks for taking the time to reply back. The key is the selected columns=
> >=20
> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO> >T NULL
> > , WARE CHAR(3) NO DEFAULT NOT NULL
> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL
> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL
> >
> >
> >Since you brought up the ORDER BY and explain plan, I noticed the key of th=
> >e table is in ascending order and exactly is my ORDER BY columns, so I remo=
> >ved ORDER BY from the cursor but It still skipped records.=20
> >
> >Here is EXPLAIN plan:
> >
> > Operation 1.0 : Scan
> > Table PARTS
> > with correlation name PARTS
> > Access type : No locks, browse access
> > Lock mode : Chosen by the system
> > Column processing : Requires retrieval of 19 out of 20 columns
> >=20
> > Access path 1 : Primary, sequential cache
> > SBB for reads : Virtual, double buffer
> > Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
> >E ,=20
> > REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
> >ER
> > End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
> > :SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
> >UMBER
> > Index selectivity : Expect to examine 100% of rows from table
> > Index pred. : None
> > Base table pred. : Will be evaluated by the disk process
> > ( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_> >LOC >=3D :SEARCH-BIN-LOC )
> > AND ( PART_NUM >=3D :SEARCH-PART-NUM )
> > Pred. selectivity : Expect to select 1.2341% of rows from table
> >=20
> > Executor pred. : None
> > Table selectivity : Expect to select 1.2341% of rows from table
> > Expected row count: 5 rows after the scan
> > Operation cost : 35
> >=20
> > Operation 1.1 : Sort
> > Requested : Explicitly in the query
> > Sort rows in the : Result of a Select
> > Purpose : To order rows for an Order By
> > Sort technique : FASTSORT
> > Sort type : Plan to use User Process Sort
> > UPS workspace : 34 Kbytes
> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
> > PARTS.REPORT_NUMBER asc,
> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> > PARTS.BIN_LOC asc,=20
> > Sort cost : 1
> >=20
> > Total cost : 36
> --------------------------------------------------
> >>In case if there are more records than 5000, it saves off record number
> >>and use it to reopen the cursor and get the rest of the records.
> Where does record number come from and how does it become part
> of the where clause on reopening the cursor?

When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5000 record limit will cause the program to save off the data for record 5001 and close the cursor. The second read will use the saved off the data as a starting point to read the next 5000 records. So record number is not a part of the table and it's not used in the WHERE clause. It's an internal program counter to know when to stop reading as the page limit is set to 5000 records. Thanks again for your help!

Re: SQL Cursor skipping records

<ubmeb2$3v5si$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=696&group=comp.sys.tandem#696

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!news.chmurka.net!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Fri, 18 Aug 2023 00:34:42 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 227
Message-ID: <ubmeb2$3v5si$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me> <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Fri, 18 Aug 2023 00:34:42 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="8196bd4d29da50ab4dd177bebf2812f8";
logging-data="4167570"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18kwFm7CpgCamD41f3GOgtxk0ukxFoqbOo="
Cancel-Lock: sha1:TNB8qZ6nteLeWOd/xqLeVY9NsFU=
 by: JShepherd - Fri, 18 Aug 2023 00:34 UTC

In article <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>,
abolkini@gmail.com says...
>
>On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:
>> In article <9c035799-1510-429d...@googlegroups.com>,=20
>> abol...@gmail.com says...=20
>> >=20
>> >On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =
>wrote:=20
>> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
>> >> abol...@gmail.com says...=3D20=20
>> >> >=3D20=20
>> >> >I have a SQL cursor that is used to retrieve records from parts table=
>. T=3D=20
>> >he =3D3D=3D20=20
>> >> >program is designed to return no more than 5000 records per read, the=
>n i=3D=20
>> >t c=3D3D=3D20=20
>> >> >losed the cursor.=3D3D20=3D20=20
>> >> >=3D20=20
>> >> >In case if there are more records than 5000, it saves off record numb=
>er =3D=20
>> >500=3D3D=20
>> >> >1 and use it to reopen the cursor and get the rest of the records.=3D=
>20=20
>> >> >=3D20=20
>> >> >The issue appears to be not all records is returned on the second rea=
>d.=3D=20
>> >=3D20=20
>> >> >=3D20=20
>> >> >The cursor look like this=3D20=20
>> >> >=3D20
>> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
>> >> > SELECT COMPANY=3D3D20=20
>> >> > , WAREHOUSE=3D20=20
>> >> > , REPORT_NUMBER=3D20=20
>> >> > , SOURCE_OF_SUPPLY=3D20=20
>> >> > , PART_NUM=3D20=20
>> >> > , BIN_LOC=3D20=20
>> >> >=20
>> >> > FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
>> >> > WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
>> >> > WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
>> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
>> >> > SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
>> >> > PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
>> >> > BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
>> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=
>NUM=3D=20
>> >,=3D3D=3D20=20
>> >> > BIN_LOC, ALT_BIN ASC=3D20=20
>> >> > END-EXEC.=3D20=20
>> >> >=3D20
>> >> >On the second read/Open cursor, it returns the record which was saved=
> of=3D=20
>> >f =3D3D=3D20=20
>> >> >to use it to reopen the cursor. but then it skips a bunch of records.=
> I =3D=20
>> >was=3D3D=3D20=20
>> >> > hoping someone would be able to tell me what am i doing wrong in the=
> cu=3D=20
>> >rso=3D3D
>> >> >r. Thanks in advance and have a wonderful weekend !
>> >> The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=
>0=20
>> >>=3D20=20
>> >> What are the keycols for the table ?=3D20=20
>> >>=3D20
>> >> What does an explain plan for the statement show ?=20
>> >=20
>> >Happy Monday,=20
>> >
>> >Thanks for taking the time to reply back. The key is the selected column=
>s=3D=20
>> >=3D20=20
>> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
>> >T NULL=20
>> > , WARE CHAR(3) NO DEFAULT NOT NULL=20
>> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
>> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
>> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
>> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
>> >=20
>> >
>> >Since you brought up the ORDER BY and explain plan, I noticed the key of=
> th=3D=20
>> >e table is in ascending order and exactly is my ORDER BY columns, so I r=
>emo=3D=20
>> >ved ORDER BY from the cursor but It still skipped records.=3D20
>> >=20
>> >Here is EXPLAIN plan:=20
>> >=20
>> > Operation 1.0 : Scan=20
>> > Table PARTS=20
>> > with correlation name PARTS=20
>> > Access type : No locks, browse access=20
>> > Lock mode : Chosen by the system=20
>> > Column processing : Requires retrieval of 19 out of 20 columns
>> >=3D20
>> > Access path 1 : Primary, sequential cache=20
>> > SBB for reads : Virtual, double buffer
>> > Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=
>=3D=20
>> >E ,=3D20=20
>> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
>> >ER=20
>> > End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
>> > :SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
>> >UMBER
>> > Index selectivity : Expect to examine 100% of rows from table=20
>> > Index pred. : None=20
>> > Base table pred. : Will be evaluated by the disk process
>> > ( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
>> >LOC >=3D3D :SEARCH-BIN-LOC )=20
>> > AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
>> > Pred. selectivity : Expect to select 1.2341% of rows from table
>> >=3D20
>> > Executor pred. : None=20
>> > Table selectivity : Expect to select 1.2341% of rows from table=20
>> > Expected row count: 5 rows after the scan=20
>> > Operation cost : 35
>> >=3D20
>> > Operation 1.1 : Sort=20
>> > Requested : Explicitly in the query=20
>> > Sort rows in the : Result of a Select=20
>> > Purpose : To order rows for an Order By=20
>> > Sort technique : FASTSORT=20
>> > Sort type : Plan to use User Process Sort=20
>> > UPS workspace : 34 Kbytes=20
>> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
>> > PARTS.REPORT_NUMBER asc,=20
>> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
>> > PARTS.BIN_LOC asc,=3D20=20
>> > Sort cost : 1=20
>> >=3D20=20
>> > Total cost : 36
>> --------------------------------------------------
>> >>In case if there are more records than 5000, it saves off record number
>> >>and use it to reopen the cursor and get the rest of the records.
>> Where does record number come from and how does it become part=20
>> of the where clause on reopening the cursor?
>
>
>When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5=
>000 record limit will cause the program to save off the data for record 50=
>01 and close the cursor. The second read will use the saved off the data as=
> a starting point to read the next 5000 records. So record number is not a =
>part of the table and it's not used in the WHERE clause. It's an internal p=
>rogram counter to know when to stop reading as the page limit is set to 500=
>0 records. Thanks again for your help!

In the absence of any sample data,
I loaded a few rows with the variable part of the key as

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
thru
SS P000000099 BIN000099

----------------------------
-- start the query

set param ?company 12;
set param ?warehouse "W12";
set param ?report_number 1234;

set param ?source_of_supply " ";
set param ?part_num " ";
set param ?bin_loc " ";

prepare s1 from
+> SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
+> FROM =parts FOR BROWSE ACCESS
+> WHERE COMPANY = ?company and
+> WAREHOUSE = ?warehouse and
+> REPORT_NUMBER = ?report_number and
+> SOURCE_OF_SUPPLY >= ?source_of_supply and
+> PART_NUM >= ?part_num and
+> BIN_LOC >= ?bin_loc
+>;
--- SQL command prepared.
execute s1;
SOURCE_OF_SUPPLY PART_NUM BIN_LOC
---------------- ---------- ---------
SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
SS P000000004 BIN000004
SS P000000005 BIN000005
SS P000000006 BIN000006
SS P000000007 BIN000007
SS P000000008 BIN000008
SS P000000009 BIN000009

Assuming that 10 rows are fetched per open cursor
and the last row fetched was

set param ?source_of_supply "SS";
set param ?part_num "P000000010";
set param ?bin_loc "zzzzzzzzz";

The variable parts of the key is the problem
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc


Click here to read the complete article
Re: SQL Cursor skipping records

<14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=701&group=comp.sys.tandem#701

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ac8:5a84:0:b0:401:e2bb:e429 with SMTP id c4-20020ac85a84000000b00401e2bbe429mr5610qtc.9.1692406570783;
Fri, 18 Aug 2023 17:56:10 -0700 (PDT)
X-Received: by 2002:a17:903:1c4:b0:1bc:7312:78e0 with SMTP id
e4-20020a17090301c400b001bc731278e0mr267624plh.6.1692406570179; Fri, 18 Aug
2023 17:56:10 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.goja.nl.eu.org!2.eu.feeder.erje.net!feeder.erje.net!feeder1.feed.usenet.farm!feed.usenet.farm!peer02.ams4!peer.am4.highwinds-media.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Fri, 18 Aug 2023 17:56:09 -0700 (PDT)
In-Reply-To: <ubmeb2$3v5si$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=50.204.224.200; posting-account=0NHiHgoAAAC-mY8P0yk1bd3zDKN8t6jB
NNTP-Posting-Host: 50.204.224.200
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me> <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
<ubmeb2$3v5si$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>
Subject: Re: SQL Cursor skipping records
From: abolk...@gmail.com (Adam “Adamlivinlife” Kamal)
Injection-Date: Sat, 19 Aug 2023 00:56:10 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 11399
 by: Adam “Adamlivinlif - Sat, 19 Aug 2023 00:56 UTC

On Thursday, August 17, 2023 at 5:34:45 PM UTC-7, JShepherd wrote:
> In article <3a8f4387-ed26-450b...@googlegroups.com>,
> abol...@gmail.com says...
> >
> >On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:
> >> In article <9c035799-1510-429d...@googlegroups.com>,=20
> >> abol...@gmail.com says...=20
> >> >=20
> >> >On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =
> >wrote:=20
> >> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
> >> >> abol...@gmail.com says...=3D20=20
> >> >> >=3D20=20
> >> >> >I have a SQL cursor that is used to retrieve records from parts table=
> >. T=3D=20
> >> >he =3D3D=3D20=20
> >> >> >program is designed to return no more than 5000 records per read, the=
> >n i=3D=20
> >> >t c=3D3D=3D20=20
> >> >> >losed the cursor.=3D3D20=3D20=20
> >> >> >=3D20=20
> >> >> >In case if there are more records than 5000, it saves off record numb=
> >er =3D=20
> >> >500=3D3D=20
> >> >> >1 and use it to reopen the cursor and get the rest of the records.=3D=
> >20=20
> >> >> >=3D20=20
> >> >> >The issue appears to be not all records is returned on the second rea=
> >d.=3D=20
> >> >=3D20=20
> >> >> >=3D20=20
> >> >> >The cursor look like this=3D20=20
> >> >> >=3D20
> >> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> >> >> > SELECT COMPANY=3D3D20=20
> >> >> > , WAREHOUSE=3D20=20
> >> >> > , REPORT_NUMBER=3D20=20
> >> >> > , SOURCE_OF_SUPPLY=3D20=20
> >> >> > , PART_NUM=3D20=20
> >> >> > , BIN_LOC=3D20=20
> >> >> >=20
> >> >> > FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
> >> >> > WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
> >> >> > WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
> >> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
> >> >> > SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
> >> >> > PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
> >> >> > BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
> >> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=
> >NUM=3D=20
> >> >,=3D3D=3D20=20
> >> >> > BIN_LOC, ALT_BIN ASC=3D20=20
> >> >> > END-EXEC.=3D20=20
> >> >> >=3D20
> >> >> >On the second read/Open cursor, it returns the record which was saved=
> > of=3D=20
> >> >f =3D3D=3D20=20
> >> >> >to use it to reopen the cursor. but then it skips a bunch of records.=
> > I =3D=20
> >> >was=3D3D=3D20=20
> >> >> > hoping someone would be able to tell me what am i doing wrong in the=
> > cu=3D=20
> >> >rso=3D3D
> >> >> >r. Thanks in advance and have a wonderful weekend !
> >> >> The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=
> >0=20
> >> >>=3D20=20
> >> >> What are the keycols for the table ?=3D20=20
> >> >>=3D20
> >> >> What does an explain plan for the statement show ?=20
> >> >=20
> >> >Happy Monday,=20
> >> >
> >> >Thanks for taking the time to reply back. The key is the selected column=
> >s=3D=20
> >> >=3D20=20
> >> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
> >> >T NULL=20
> >> > , WARE CHAR(3) NO DEFAULT NOT NULL=20
> >> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
> >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
> >> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
> >> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
> >> >=20
> >> >
> >> >Since you brought up the ORDER BY and explain plan, I noticed the key of=
> > th=3D=20
> >> >e table is in ascending order and exactly is my ORDER BY columns, so I r=
> >emo=3D=20
> >> >ved ORDER BY from the cursor but It still skipped records.=3D20
> >> >=20
> >> >Here is EXPLAIN plan:=20
> >> >=20
> >> > Operation 1.0 : Scan=20
> >> > Table PARTS=20
> >> > with correlation name PARTS=20
> >> > Access type : No locks, browse access=20
> >> > Lock mode : Chosen by the system=20
> >> > Column processing : Requires retrieval of 19 out of 20 columns
> >> >=3D20
> >> > Access path 1 : Primary, sequential cache=20
> >> > SBB for reads : Virtual, double buffer
> >> > Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=
> >=3D=20
> >> >E ,=3D20=20
> >> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
> >> >ER=20
> >> > End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
> >> > :SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
> >> >UMBER
> >> > Index selectivity : Expect to examine 100% of rows from table=20
> >> > Index pred. : None=20
> >> > Base table pred. : Will be evaluated by the disk process
> >> > ( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
> >> >LOC >=3D3D :SEARCH-BIN-LOC )=20
> >> > AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
> >> > Pred. selectivity : Expect to select 1.2341% of rows from table
> >> >=3D20
> >> > Executor pred. : None=20
> >> > Table selectivity : Expect to select 1.2341% of rows from table=20
> >> > Expected row count: 5 rows after the scan=20
> >> > Operation cost : 35
> >> >=3D20
> >> > Operation 1.1 : Sort=20
> >> > Requested : Explicitly in the query=20
> >> > Sort rows in the : Result of a Select=20
> >> > Purpose : To order rows for an Order By=20
> >> > Sort technique : FASTSORT=20
> >> > Sort type : Plan to use User Process Sort=20
> >> > UPS workspace : 34 Kbytes=20
> >> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
> >> > PARTS.REPORT_NUMBER asc,=20
> >> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> >> > PARTS.BIN_LOC asc,=3D20=20
> >> > Sort cost : 1=20
> >> >=3D20=20
> >> > Total cost : 36
> >> --------------------------------------------------
> >> >>In case if there are more records than 5000, it saves off record number
> >> >>and use it to reopen the cursor and get the rest of the records.
> >> Where does record number come from and how does it become part=20
> >> of the where clause on reopening the cursor?
> >
> >
> >When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5=
> >000 record limit will cause the program to save off the data for record 50=
> >01 and close the cursor. The second read will use the saved off the data as=
> > a starting point to read the next 5000 records. So record number is not a =
> >part of the table and it's not used in the WHERE clause. It's an internal p=
> >rogram counter to know when to stop reading as the page limit is set to 500> >0 records. Thanks again for your help!
> In the absence of any sample data,
> I loaded a few rows with the variable part of the key as
>
> SS P000000000 BIN000000
> SS P000000001 BIN000001
> SS P000000002 BIN000002
> SS P000000003 BIN000003
> thru
> SS P000000099 BIN000099
>
> ----------------------------
> -- start the query
>
> set param ?company 12;
> set param ?warehouse "W12";
> set param ?report_number 1234;
>
> set param ?source_of_supply " ";
> set param ?part_num " ";
> set param ?bin_loc " ";
>
> prepare s1 from
> +> SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
> +> FROM =parts FOR BROWSE ACCESS
> +> WHERE COMPANY = ?company and
> +> WAREHOUSE = ?warehouse and
> +> REPORT_NUMBER = ?report_number and
> +> SOURCE_OF_SUPPLY >= ?source_of_supply and
> +> PART_NUM >= ?part_num and
> +> BIN_LOC >= ?bin_loc
> +>;
> --- SQL command prepared.
>
> execute s1;
>
> SOURCE_OF_SUPPLY PART_NUM BIN_LOC
> ---------------- ---------- ---------
>
> SS P000000000 BIN000000
> SS P000000001 BIN000001
> SS P000000002 BIN000002
> SS P000000003 BIN000003
> SS P000000004 BIN000004
> SS P000000005 BIN000005
> SS P000000006 BIN000006
> SS P000000007 BIN000007
> SS P000000008 BIN000008
> SS P000000009 BIN000009
>
> Assuming that 10 rows are fetched per open cursor
> and the last row fetched was
>
> set param ?source_of_supply "SS";
> set param ?part_num "P000000010";
> set param ?bin_loc "zzzzzzzzz";
>
> The variable parts of the key is the problem
> SOURCE_OF_SUPPLY >= ?source_of_supply and
> PART_NUM >= ?part_num and
> BIN_LOC >= ?bin_loc
>
> The high value of the bin_loc restart value prevents any more rows
> from being fetched, at least in this test data,
> because the three cols are AND'd. .
>
> With a more random bin_loc you would likely skip subsequent rows with
> a bin_loc value less than the restart value
>
> execute s1;
>
> --- 0 row(s) selected.


Click here to read the complete article
Re: SQL Cursor skipping records

<f8bef310-ccc6-49f8-ad51-4a5872ed3608n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=703&group=comp.sys.tandem#703

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a05:6214:ab4:b0:649:fc3d:7659 with SMTP id ew20-20020a0562140ab400b00649fc3d7659mr102701qvb.12.1692753035234;
Tue, 22 Aug 2023 18:10:35 -0700 (PDT)
X-Received: by 2002:a63:d315:0:b0:565:ed29:8244 with SMTP id
b21-20020a63d315000000b00565ed298244mr2195711pgg.3.1692753034726; Tue, 22 Aug
2023 18:10:34 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Tue, 22 Aug 2023 18:10:34 -0700 (PDT)
In-Reply-To: <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2607:fea8:3fa9:4200:a83f:b7df:e2d:7763;
posting-account=6VebZwoAAAAgrpUtsowyjrKRLNlqxnXo
NNTP-Posting-Host: 2607:fea8:3fa9:4200:a83f:b7df:e2d:7763
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me> <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
<ubmeb2$3v5si$1@dont-email.me> <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <f8bef310-ccc6-49f8-ad51-4a5872ed3608n@googlegroups.com>
Subject: Re: SQL Cursor skipping records
From: rsbec...@nexbridge.com (Randall)
Injection-Date: Wed, 23 Aug 2023 01:10:35 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 12457
 by: Randall - Wed, 23 Aug 2023 01:10 UTC

On Friday, August 18, 2023 at 8:56:11 p.m. UTC-4, Adam “Adamlivinlife” Kamal wrote:
> On Thursday, August 17, 2023 at 5:34:45 PM UTC-7, JShepherd wrote:
> > In article <3a8f4387-ed26-450b...@googlegroups.com>,
> > abol...@gmail.com says...
> > >
> > >On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:
> > >> In article <9c035799-1510-429d...@googlegroups.com>,=20
> > >> abol...@gmail.com says...=20
> > >> >=20
> > >> >On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =
> > >wrote:=20
> > >> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
> > >> >> abol...@gmail.com says...=3D20=20
> > >> >> >=3D20=20
> > >> >> >I have a SQL cursor that is used to retrieve records from parts table=
> > >. T=3D=20
> > >> >he =3D3D=3D20=20
> > >> >> >program is designed to return no more than 5000 records per read, the=
> > >n i=3D=20
> > >> >t c=3D3D=3D20=20
> > >> >> >losed the cursor.=3D3D20=3D20=20
> > >> >> >=3D20=20
> > >> >> >In case if there are more records than 5000, it saves off record numb=
> > >er =3D=20
> > >> >500=3D3D=20
> > >> >> >1 and use it to reopen the cursor and get the rest of the records.=3D=
> > >20=20
> > >> >> >=3D20=20
> > >> >> >The issue appears to be not all records is returned on the second rea=
> > >d.=3D=20
> > >> >=3D20=20
> > >> >> >=3D20=20
> > >> >> >The cursor look like this=3D20=20
> > >> >> >=3D20
> > >> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
> > >> >> > SELECT COMPANY=3D3D20=20
> > >> >> > , WAREHOUSE=3D20=20
> > >> >> > , REPORT_NUMBER=3D20=20
> > >> >> > , SOURCE_OF_SUPPLY=3D20=20
> > >> >> > , PART_NUM=3D20=20
> > >> >> > , BIN_LOC=3D20=20
> > >> >> >=20
> > >> >> > FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
> > >> >> > WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
> > >> >> > WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
> > >> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
> > >> >> > SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
> > >> >> > PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
> > >> >> > BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
> > >> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=
> > >NUM=3D=20
> > >> >,=3D3D=3D20=20
> > >> >> > BIN_LOC, ALT_BIN ASC=3D20=20
> > >> >> > END-EXEC.=3D20=20
> > >> >> >=3D20
> > >> >> >On the second read/Open cursor, it returns the record which was saved=
> > > of=3D=20
> > >> >f =3D3D=3D20=20
> > >> >> >to use it to reopen the cursor. but then it skips a bunch of records.=
> > > I =3D=20
> > >> >was=3D3D=3D20=20
> > >> >> > hoping someone would be able to tell me what am i doing wrong in the=
> > > cu=3D=20
> > >> >rso=3D3D
> > >> >> >r. Thanks in advance and have a wonderful weekend !
> > >> >> The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=
> > >0=20
> > >> >>=3D20=20
> > >> >> What are the keycols for the table ?=3D20=20
> > >> >>=3D20
> > >> >> What does an explain plan for the statement show ?=20
> > >> >=20
> > >> >Happy Monday,=20
> > >> >
> > >> >Thanks for taking the time to reply back. The key is the selected column=
> > >s=3D=20
> > >> >=3D20=20
> > >> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
> > >> >T NULL=20
> > >> > , WARE CHAR(3) NO DEFAULT NOT NULL=20
> > >> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
> > >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
> > >> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
> > >> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
> > >> >=20
> > >> >
> > >> >Since you brought up the ORDER BY and explain plan, I noticed the key of=
> > > th=3D=20
> > >> >e table is in ascending order and exactly is my ORDER BY columns, so I r=
> > >emo=3D=20
> > >> >ved ORDER BY from the cursor but It still skipped records.=3D20
> > >> >=20
> > >> >Here is EXPLAIN plan:=20
> > >> >=20
> > >> > Operation 1.0 : Scan=20
> > >> > Table PARTS=20
> > >> > with correlation name PARTS=20
> > >> > Access type : No locks, browse access=20
> > >> > Lock mode : Chosen by the system=20
> > >> > Column processing : Requires retrieval of 19 out of 20 columns
> > >> >=3D20
> > >> > Access path 1 : Primary, sequential cache=20
> > >> > SBB for reads : Virtual, double buffer
> > >> > Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=
> > >=3D=20
> > >> >E ,=3D20=20
> > >> > REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
> > >> >ER=20
> > >> > End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
> > >> > :SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
> > >> >UMBER
> > >> > Index selectivity : Expect to examine 100% of rows from table=20
> > >> > Index pred. : None=20
> > >> > Base table pred. : Will be evaluated by the disk process
> > >> > ( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
> > >> >LOC >=3D3D :SEARCH-BIN-LOC )=20
> > >> > AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
> > >> > Pred. selectivity : Expect to select 1.2341% of rows from table
> > >> >=3D20
> > >> > Executor pred. : None=20
> > >> > Table selectivity : Expect to select 1.2341% of rows from table=20
> > >> > Expected row count: 5 rows after the scan=20
> > >> > Operation cost : 35
> > >> >=3D20
> > >> > Operation 1.1 : Sort=20
> > >> > Requested : Explicitly in the query=20
> > >> > Sort rows in the : Result of a Select=20
> > >> > Purpose : To order rows for an Order By=20
> > >> > Sort technique : FASTSORT=20
> > >> > Sort type : Plan to use User Process Sort=20
> > >> > UPS workspace : 34 Kbytes=20
> > >> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
> > >> > PARTS.REPORT_NUMBER asc,=20
> > >> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
> > >> > PARTS.BIN_LOC asc,=3D20=20
> > >> > Sort cost : 1=20
> > >> >=3D20=20
> > >> > Total cost : 36
> > >> --------------------------------------------------
> > >> >>In case if there are more records than 5000, it saves off record number
> > >> >>and use it to reopen the cursor and get the rest of the records.
> > >> Where does record number come from and how does it become part=20
> > >> of the where clause on reopening the cursor?
> > >
> > >
> > >When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5=
> > >000 record limit will cause the program to save off the data for record 50=
> > >01 and close the cursor. The second read will use the saved off the data as=
> > > a starting point to read the next 5000 records. So record number is not a =
> > >part of the table and it's not used in the WHERE clause. It's an internal p=
> > >rogram counter to know when to stop reading as the page limit is set to 500=
> > >0 records. Thanks again for your help!
> > In the absence of any sample data,
> > I loaded a few rows with the variable part of the key as
> >
> > SS P000000000 BIN000000
> > SS P000000001 BIN000001
> > SS P000000002 BIN000002
> > SS P000000003 BIN000003
> > thru
> > SS P000000099 BIN000099
> >
> > ----------------------------
> > -- start the query
> >
> > set param ?company 12;
> > set param ?warehouse "W12";
> > set param ?report_number 1234;
> >
> > set param ?source_of_supply " ";
> > set param ?part_num " ";
> > set param ?bin_loc " ";
> >
> > prepare s1 from
> > +> SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
> > +> FROM =parts FOR BROWSE ACCESS
> > +> WHERE COMPANY = ?company and
> > +> WAREHOUSE = ?warehouse and
> > +> REPORT_NUMBER = ?report_number and
> > +> SOURCE_OF_SUPPLY >= ?source_of_supply and
> > +> PART_NUM >= ?part_num and
> > +> BIN_LOC >= ?bin_loc
> > +>;
> > --- SQL command prepared.
> >
> > execute s1;
> >
> > SOURCE_OF_SUPPLY PART_NUM BIN_LOC
> > ---------------- ---------- ---------
> >
> > SS P000000000 BIN000000
> > SS P000000001 BIN000001
> > SS P000000002 BIN000002
> > SS P000000003 BIN000003
> > SS P000000004 BIN000004
> > SS P000000005 BIN000005
> > SS P000000006 BIN000006
> > SS P000000007 BIN000007
> > SS P000000008 BIN000008
> > SS P000000009 BIN000009
> >
> > Assuming that 10 rows are fetched per open cursor
> > and the last row fetched was
> >
> > set param ?source_of_supply "SS";
> > set param ?part_num "P000000010";
> > set param ?bin_loc "zzzzzzzzz";
> >
> > The variable parts of the key is the problem
> > SOURCE_OF_SUPPLY >= ?source_of_supply and
> > PART_NUM >= ?part_num and
> > BIN_LOC >= ?bin_loc
> >
> > The high value of the bin_loc restart value prevents any more rows
> > from being fetched, at least in this test data,
> > because the three cols are AND'd. .
> >
> > With a more random bin_loc you would likely skip subsequent rows with
> > a bin_loc value less than the restart value
> >
> > execute s1;
> >
> > --- 0 row(s) selected.
> Thank you again for your help. I noticed if I remove the bin-_loc I get more records back.
> I am not sure what is the solution of this paging issue. In my mind I am giving the cursor a starting point on the second select. If both have the same order by to retrieve data, why the second select is not working. I still can not get it and that's preventing me from solving the issue. Would you please shed any light on how to fix this. Thanks in advance and have a wonderful weekend!


Click here to read the complete article
Re: SQL Cursor skipping records

<uc59gn$2vq23$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=706&group=comp.sys.tandem#706

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Wed, 23 Aug 2023 15:44:23 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 281
Message-ID: <uc59gn$2vq23$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me> <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
<ubmeb2$3v5si$1@dont-email.me> <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Wed, 23 Aug 2023 15:44:23 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="07d28896b1a59a721a16cb38794bfc22";
logging-data="3139651"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19Z172aN9VN9S5xfpM+Dj+kU1ncfXpU3EY="
Cancel-Lock: sha1:dO6O6OsgJwPbXouBMPI6Wge46d8=
 by: JShepherd - Wed, 23 Aug 2023 15:44 UTC

In article <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>,
abolkini@gmail.com says...
>
>On Thursday, August 17, 2023 at 5:34:45=E2=80=AFPM UTC-7, JShepherd wrote:
>> In article <3a8f4387-ed26-450b...@googlegroups.com>,=20
>> abol...@gmail.com says...=20
>> >=20
>> >On Tuesday, August 15, 2023 at 10:48:43=3DE2=3D80=3DAFAM UTC-7, JShepher=
>d wrote:=20
>> >> In article <9c035799-1510-429d...@googlegroups.com>,=3D20=20
>> >> abol...@gmail.com says...=3D20=20
>> >> >=3D20=20
>> >> >On Friday, August 11, 2023 at 2:59:09=3D3DE2=3D3D80=3D3DAFPM UTC-7, J=
>Shepherd =3D=20
>> >wrote:=3D20=20
>> >> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D3D20=3D20=20
>> >> >> abol...@gmail.com says...=3D3D20=3D20=20
>> >> >> >=3D3D20=3D20=20
>> >> >> >I have a SQL cursor that is used to retrieve records from parts ta=
>ble=3D=20
>> >. T=3D3D=3D20=20
>> >> >he =3D3D3D=3D3D20=3D20=20
>> >> >> >program is designed to return no more than 5000 records per read, =
>the=3D=20
>> >n i=3D3D=3D20=20
>> >> >t c=3D3D3D=3D3D20=3D20=20
>> >> >> >losed the cursor.=3D3D3D20=3D3D20=3D20=20
>> >> >> >=3D3D20=3D20=20
>> >> >> >In case if there are more records than 5000, it saves off record n=
>umb=3D=20
>> >er =3D3D=3D20=20
>> >> >500=3D3D3D=3D20=20
>> >> >> >1 and use it to reopen the cursor and get the rest of the records.=
>=3D3D=3D=20
>> >20=3D20=20
>> >> >> >=3D3D20=3D20=20
>> >> >> >The issue appears to be not all records is returned on the second =
>rea=3D=20
>> >d.=3D3D=3D20=20
>> >> >=3D3D20=3D20=20
>> >> >> >=3D3D20=3D20=20
>> >> >> >The cursor look like this=3D3D20=3D20=20
>> >> >> >=3D3D20
>> >> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
>> >> >> > SELECT COMPANY=3D3D3D20=3D20=20
>> >> >> > , WAREHOUSE=3D3D20=3D20=20
>> >> >> > , REPORT_NUMBER=3D3D20=3D20=20
>> >> >> > , SOURCE_OF_SUPPLY=3D3D20=3D20=20
>> >> >> > , PART_NUM=3D3D20=3D20=20
>> >> >> > , BIN_LOC=3D3D20=3D20=20
>> >> >> >=3D20=20
>> >> >> > FROM =3D3D3D3DPARTS FOR BROWSE ACCESS=3D3D20=3D20=20
>> >> >> > WHERE COMPANY =3D3D3D3D :SEARCH-COMPANY AND=3D3D20=3D20=20
>> >> >> > WAREHOUSE =3D3D3D3D :SEARCH-WAREHOUSE AND=3D3D20=3D20=20
>> >> >> > REPORT_NUMBER =3D3D3D3D :SEARCH-REPORT-NUMBER AND=3D3D20=3D20=20
>> >> >> > SOURCE_OF_SUPPLY >=3D3D3D3D :SEARCH-SOS AND=3D3D20=3D20=20
>> >> >> > PART_NUM >=3D3D3D3D :SEARCH-PART-NUM AND=3D3D20=3D20=20
>> >> >> > BIN_LOC >=3D3D3D3D :SEARCH-BIN-LOC AND=3D3D20=3D20=20
>> >> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PA=
>RT_=3D=20
>> >NUM=3D3D=3D20=20
>> >> >,=3D3D3D=3D3D20=3D20=20
>> >> >> > BIN_LOC, ALT_BIN ASC=3D3D20=3D20=20
>> >> >> > END-EXEC.=3D3D20=3D20=20
>> >> >> >=3D3D20=20
>> >> >> >On the second read/Open cursor, it returns the record which was sa=
>ved=3D=20
>> > of=3D3D=3D20=20
>> >> >f =3D3D3D=3D3D20=3D20=20
>> >> >> >to use it to reopen the cursor. but then it skips a bunch of recor=
>ds.=3D=20
>> > I =3D3D=3D20=20
>> >> >was=3D3D3D=3D3D20=3D20=20
>> >> >> > hoping someone would be able to tell me what am i doing wrong in =
>the=3D=20
>> > cu=3D3D=3D20=20
>> >> >rso=3D3D3D
>> >> >> >r. Thanks in advance and have a wonderful weekend !
>> >> >> The where clause shown seems to end with "AND ORDER BY <col list>"=
>=3D3D2=3D=20
>> >0=3D20=20
>> >> >>=3D3D20=3D20=20
>> >> >> What are the keycols for the table ?=3D3D20=3D20=20
>> >> >>=3D3D20=20
>> >> >> What does an explain plan for the statement show ?=3D20=20
>> >> >=3D20=20
>> >> >Happy Monday,=3D20=20
>> >> >=20
>> >> >Thanks for taking the time to reply back. The key is the selected col=
>umn=3D=20
>> >s=3D3D=3D20=20
>> >> >=3D3D20=3D20=20
>> >> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D3D=20
>> >> >T NULL=3D20=20
>> >> > , WARE CHAR(3) NO DEFAULT NOT NULL=3D20=20
>> >> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=3D20=20
>> >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=3D20=20
>> >> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=3D20=20
>> >> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=3D20=20
>> >> >=3D20=20
>> >> >=20
>> >> >Since you brought up the ORDER BY and explain plan, I noticed the key=
> of=3D=20
>> > th=3D3D=3D20=20
>> >> >e table is in ascending order and exactly is my ORDER BY columns, so =
>I r=3D=20
>> >emo=3D3D=3D20=20
>> >> >ved ORDER BY from the cursor but It still skipped records.=3D3D20=20
>> >> >=3D20=20
>> >> >Here is EXPLAIN plan:=3D20=20
>> >> >=3D20=20
>> >> > Operation 1.0 : Scan=3D20=20
>> >> > Table PARTS=3D20=20
>> >> > with correlation name PARTS=3D20=20
>> >> > Access type : No locks, browse access=3D20=20
>> >> > Lock mode : Chosen by the system=3D20
>> >> > Column processing : Requires retrieval of 19 out of 20 columns
>> >> >=3D3D20=20
>> >> > Access path 1 : Primary, sequential cache=3D20
>> >> > SBB for reads : Virtual, double buffer
>> >> > Begin key pred. : COMPANY=3D3D3D :SEARCH-COMPANY , WARE =3D3D3D :SEA=
>RCH-WAR=3D=20
>> >=3D3D=3D20=20
>> >> >E ,=3D3D20=3D20=20
>> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMB=3D3D=3D20=20
>> >> >ER=3D20=20
>> >> > End key pred. : COMPANY =3D3D3D :SEARCH-COMPANY , WARE =3D3D3D=3D20=
>=20
>> >> > :SEARCH-WARE , REPORT_NUMBER =3D3D3D :SEARCH-REPORT-N=3D3D=3D20=20
>> >> >UMBER=20
>> >> > Index selectivity : Expect to examine 100% of rows from table=3D20=
>=20
>> >> > Index pred. : None=3D20
>> >> > Base table pred. : Will be evaluated by the disk process
>> >> > ( SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS ) AND ( BIN_=3D3D=20
>> >> >LOC >=3D3D3D :SEARCH-BIN-LOC )=3D20=20
>> >> > AND ( PART_NUM >=3D3D3D :SEARCH-PART-NUM )
>> >> > Pred. selectivity : Expect to select 1.2341% of rows from table
>> >> >=3D3D20=20
>> >> > Executor pred. : None=3D20=20
>> >> > Table selectivity : Expect to select 1.2341% of rows from table=3D20=
>=20
>> >> > Expected row count: 5 rows after the scan=3D20=20
>> >> > Operation cost : 35=20
>> >> >=3D3D20=20
>> >> > Operation 1.1 : Sort=3D20=20
>> >> > Requested : Explicitly in the query=3D20=20
>> >> > Sort rows in the : Result of a Select=3D20=20
>> >> > Purpose : To order rows for an Order By=3D20=20
>> >> > Sort technique : FASTSORT=3D20=20
>> >> > Sort type : Plan to use User Process Sort=3D20=20
>> >> > UPS workspace : 34 Kbytes=3D20=20
>> >> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=3D20=20
>> >> > PARTS.REPORT_NUMBER asc,=3D20=20
>> >> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,=20
>> >> > PARTS.BIN_LOC asc,=3D3D20=3D20=20
>> >> > Sort cost : 1=3D20=20
>> >> >=3D3D20=3D20
>> >> > Total cost : 36=20
>> >> --------------------------------------------------=20
>> >> >>In case if there are more records than 5000, it saves off record num=
>ber=20
>> >> >>and use it to reopen the cursor and get the rest of the records.
>> >> Where does record number come from and how does it become part=3D20
>> >> of the where clause on reopening the cursor?=20
>> >=20
>> >
>> >When the cursor is opened, it will fetch up to 5000 record, on 5001, the=
> 5=3D=20
>> >000 record limit will cause the program to save off the data for record =
>50=3D=20
>> >01 and close the cursor. The second read will use the saved off the data=
> as=3D=20
>> > a starting point to read the next 5000 records. So record number is not=
> a =3D=20
>> >part of the table and it's not used in the WHERE clause. It's an interna=
>l p=3D=20
>> >rogram counter to know when to stop reading as the page limit is set to =
>500=3D
>> >0 records. Thanks again for your help!
>> In the absence of any sample data,=20
>> I loaded a few rows with the variable part of the key as=20
>>=20
>> SS P000000000 BIN000000=20
>> SS P000000001 BIN000001=20
>> SS P000000002 BIN000002=20
>> SS P000000003 BIN000003=20
>> thru=20
>> SS P000000099 BIN000099=20
>>=20
>> ----------------------------=20
>> -- start the query=20
>>=20
>> set param ?company 12;=20
>> set param ?warehouse "W12";=20
>> set param ?report_number 1234;=20
>>=20
>> set param ?source_of_supply " ";=20
>> set param ?part_num " ";=20
>> set param ?bin_loc " ";=20
>>=20
>> prepare s1 from=20
>> +> SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC=20
>> +> FROM =3Dparts FOR BROWSE ACCESS=20
>> +> WHERE COMPANY =3D ?company and=20
>> +> WAREHOUSE =3D ?warehouse and=20
>> +> REPORT_NUMBER =3D ?report_number and=20
>> +> SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
>> +> PART_NUM >=3D ?part_num and=20
>> +> BIN_LOC >=3D ?bin_loc=20
>> +>;=20
>> --- SQL command prepared.=20
>>=20
>> execute s1;=20
>>=20
>> SOURCE_OF_SUPPLY PART_NUM BIN_LOC=20
>> ---------------- ---------- ---------=20
>>=20
>> SS P000000000 BIN000000=20
>> SS P000000001 BIN000001=20
>> SS P000000002 BIN000002=20
>> SS P000000003 BIN000003=20
>> SS P000000004 BIN000004=20
>> SS P000000005 BIN000005=20
>> SS P000000006 BIN000006=20
>> SS P000000007 BIN000007=20
>> SS P000000008 BIN000008=20
>> SS P000000009 BIN000009=20
>>=20
>> Assuming that 10 rows are fetched per open cursor=20
>> and the last row fetched was=20
>>=20
>> set param ?source_of_supply "SS";=20
>> set param ?part_num "P000000010";=20
>> set param ?bin_loc "zzzzzzzzz";=20
>>=20
>> The variable parts of the key is the problem=20
>> SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
>> PART_NUM >=3D ?part_num and=20
>> BIN_LOC >=3D ?bin_loc=20
>>=20
>> The high value of the bin_loc restart value prevents any more rows=20
>> from being fetched, at least in this test data,=20
>> because the three cols are AND'd. .=20
>>=20
>> With a more random bin_loc you would likely skip subsequent rows with=20
>> a bin_loc value less than the restart value=20
>>=20
>> execute s1;=20
>>=20
>> --- 0 row(s) selected.
>
>
>Thank you again for your help. I noticed if I remove the bin-_loc I get mor=
>e records back.
>I am not sure what is the solution of this paging issue. In my mind I am g=
>iving the cursor a starting point on the second select. If both have the sa=
>me order by to retrieve data, why the second select is not working. I still=
> can not get it and that's preventing me from solving the issue. Would you =
>please shed any light on how to fix this. Thanks in advance and have a won=
>derful weekend!=20
>


Click here to read the complete article
Re: SQL Cursor skipping records

<uc5cbh$30b0o$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=707&group=comp.sys.tandem#707

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Cursor skipping records
Date: Wed, 23 Aug 2023 16:32:49 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 332
Message-ID: <uc5cbh$30b0o$1@dont-email.me>
References: <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>
<ub6atu$10fds$1@dont-email.me> <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>
<ubgdpo$2tng1$1@dont-email.me> <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>
<ubmeb2$3v5si$1@dont-email.me> <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com> <uc59gn$2vq23$1@dont-email.me>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Wed, 23 Aug 2023 16:32:49 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="07d28896b1a59a721a16cb38794bfc22";
logging-data="3157016"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19do49WvCVHtax2wR+C+rbdYr8sq+XgkIs="
Cancel-Lock: sha1:qxKS+yWDgOhOEExImbHx7ZFMcBo=
 by: JShepherd - Wed, 23 Aug 2023 16:32 UTC

In article <uc59gn$2vq23$1@dont-email.me>, invalid@nowhere.com says...
>
>In article <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>,
>abolkini@gmail.com says...
>>
>>On Thursday, August 17, 2023 at 5:34:45=E2=80=AFPM UTC-7, JShepherd wrote:
>>> In article <3a8f4387-ed26-450b...@googlegroups.com>,=20
>>> abol...@gmail.com says...=20
>>> >=20
>>> >On Tuesday, August 15, 2023 at 10:48:43=3DE2=3D80=3DAFAM UTC-7,
JShepher=
>>d wrote:=20
>>> >> In article <9c035799-1510-429d...@googlegroups.com>,=3D20=20
>>> >> abol...@gmail.com says...=3D20=20
>>> >> >=3D20=20
>>> >> >On Friday, August 11, 2023 at 2:59:09=3D3DE2=3D3D80=3D3DAFPM UTC-7,
J=
>>Shepherd =3D=20
>>> >wrote:=3D20=20
>>> >> >> In article <e10c4470-30c4-437b...@googlegroups.com>,=3D3D20=3D20=20
>>> >> >> abol...@gmail.com says...=3D3D20=3D20=20
>>> >> >> >=3D3D20=3D20=20
>>> >> >> >I have a SQL cursor that is used to retrieve records from parts
ta=
>>ble=3D=20
>>> >. T=3D3D=3D20=20
>>> >> >he =3D3D3D=3D3D20=3D20=20
>>> >> >> >program is designed to return no more than 5000 records per read,
= >>the=3D=20
>>> >n i=3D3D=3D20=20
>>> >> >t c=3D3D3D=3D3D20=3D20=20
>>> >> >> >losed the cursor.=3D3D3D20=3D3D20=3D20=20
>>> >> >> >=3D3D20=3D20=20
>>> >> >> >In case if there are more records than 5000, it saves off record
n=
>>umb=3D=20
>>> >er =3D3D=3D20=20
>>> >> >500=3D3D3D=3D20=20
>>> >> >> >1 and use it to reopen the cursor and get the rest of the
records.=
>>=3D3D=3D=20
>>> >20=3D20=20
>>> >> >> >=3D3D20=3D20=20
>>> >> >> >The issue appears to be not all records is returned on the second
= >>rea=3D=20
>>> >d.=3D3D=3D20=20
>>> >> >=3D3D20=3D20=20
>>> >> >> >=3D3D20=3D20=20
>>> >> >> >The cursor look like this=3D3D20=3D20=20
>>> >> >> >=3D3D20
>>> >> >> >EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
>>> >> >> > SELECT COMPANY=3D3D3D20=3D20=20
>>> >> >> > , WAREHOUSE=3D3D20=3D20=20
>>> >> >> > , REPORT_NUMBER=3D3D20=3D20=20
>>> >> >> > , SOURCE_OF_SUPPLY=3D3D20=3D20=20
>>> >> >> > , PART_NUM=3D3D20=3D20=20
>>> >> >> > , BIN_LOC=3D3D20=3D20=20
>>> >> >> >=3D20=20
>>> >> >> > FROM =3D3D3D3DPARTS FOR BROWSE ACCESS=3D3D20=3D20=20
>>> >> >> > WHERE COMPANY =3D3D3D3D :SEARCH-COMPANY AND=3D3D20=3D20=20
>>> >> >> > WAREHOUSE =3D3D3D3D :SEARCH-WAREHOUSE AND=3D3D20=3D20=20
>>> >> >> > REPORT_NUMBER =3D3D3D3D :SEARCH-REPORT-NUMBER AND=3D3D20=3D20=20
>>> >> >> > SOURCE_OF_SUPPLY >=3D3D3D3D :SEARCH-SOS AND=3D3D20=3D20=20
>>> >> >> > PART_NUM >=3D3D3D3D :SEARCH-PART-NUM AND=3D3D20=3D20=20
>>> >> >> > BIN_LOC >=3D3D3D3D :SEARCH-BIN-LOC AND=3D3D20=3D20=20
>>> >> >> > ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY,
PA=
>>RT_=3D=20
>>> >NUM=3D3D=3D20=20
>>> >> >,=3D3D3D=3D3D20=3D20=20
>>> >> >> > BIN_LOC, ALT_BIN ASC=3D3D20=3D20=20
>>> >> >> > END-EXEC.=3D3D20=3D20=20
>>> >> >> >=3D3D20=20
>>> >> >> >On the second read/Open cursor, it returns the record which was
sa=
>>ved=3D=20
>>> > of=3D3D=3D20=20
>>> >> >f =3D3D3D=3D3D20=3D20=20
>>> >> >> >to use it to reopen the cursor. but then it skips a bunch of
recor=
>>ds.=3D=20
>>> > I =3D3D=3D20=20
>>> >> >was=3D3D3D=3D3D20=3D20=20
>>> >> >> > hoping someone would be able to tell me what am i doing wrong in
= >>the=3D=20
>>> > cu=3D3D=3D20=20
>>> >> >rso=3D3D3D
>>> >> >> >r. Thanks in advance and have a wonderful weekend !
>>> >> >> The where clause shown seems to end with "AND ORDER BY <col list>"=
>>=3D3D2=3D=20
>>> >0=3D20=20
>>> >> >>=3D3D20=3D20=20
>>> >> >> What are the keycols for the table ?=3D3D20=3D20=20
>>> >> >>=3D3D20=20
>>> >> >> What does an explain plan for the statement show ?=3D20=20
>>> >> >=3D20=20
>>> >> >Happy Monday,=3D20=20
>>> >> >=20
>>> >> >Thanks for taking the time to reply back. The key is the selected
col=
>>umn=3D=20
>>> >s=3D3D=3D20=20
>>> >> >=3D3D20=3D20=20
>>> >> > COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D3D=20
>>> >> >T NULL=3D20=20
>>> >> > , WARE CHAR(3) NO DEFAULT NOT NULL=3D20=20
>>> >> > , REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=3D20=20
>>> >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=3D20=20
>>> >> > , PART_NUM CHAR(30) NO DEFAULT NOT NULL=3D20=20
>>> >> > , BIN_LOC CHAR(9) NO DEFAULT NOT NULL=3D20=20
>>> >> >=3D20=20
>>> >> >=20
>>> >> >Since you brought up the ORDER BY and explain plan, I noticed the
key=
>> of=3D=20
>>> > th=3D3D=3D20=20
>>> >> >e table is in ascending order and exactly is my ORDER BY columns, so
= >>I r=3D=20
>>> >emo=3D3D=3D20=20
>>> >> >ved ORDER BY from the cursor but It still skipped records.=3D3D20=20
>>> >> >=3D20=20
>>> >> >Here is EXPLAIN plan:=3D20=20
>>> >> >=3D20=20
>>> >> > Operation 1.0 : Scan=3D20=20
>>> >> > Table PARTS=3D20=20
>>> >> > with correlation name PARTS=3D20=20
>>> >> > Access type : No locks, browse access=3D20=20
>>> >> > Lock mode : Chosen by the system=3D20
>>> >> > Column processing : Requires retrieval of 19 out of 20 columns
>>> >> >=3D3D20=20
>>> >> > Access path 1 : Primary, sequential cache=3D20
>>> >> > SBB for reads : Virtual, double buffer
>>> >> > Begin key pred. : COMPANY=3D3D3D :SEARCH-COMPANY , WARE =3D3D3D
:SEA=
>>RCH-WAR=3D=20
>>> >=3D3D=3D20=20
>>> >> >E ,=3D3D20=3D20=20
>>> >> > REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMB=3D3D=3D20=20
>>> >> >ER=3D20=20
>>> >> > End key pred. : COMPANY =3D3D3D :SEARCH-COMPANY , WARE =3D3D3D=3D20=
>>=20
>>> >> > :SEARCH-WARE , REPORT_NUMBER =3D3D3D :SEARCH-REPORT-N=3D3D=3D20=20
>>> >> >UMBER=20
>>> >> > Index selectivity : Expect to examine 100% of rows from table=3D20=
>>=20
>>> >> > Index pred. : None=3D20
>>> >> > Base table pred. : Will be evaluated by the disk process
>>> >> > ( SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS ) AND ( BIN_=3D3D=20
>>> >> >LOC >=3D3D3D :SEARCH-BIN-LOC )=3D20=20
>>> >> > AND ( PART_NUM >=3D3D3D :SEARCH-PART-NUM )
>>> >> > Pred. selectivity : Expect to select 1.2341% of rows from table
>>> >> >=3D3D20=20
>>> >> > Executor pred. : None=3D20=20
>>> >> > Table selectivity : Expect to select 1.2341% of rows from
table=3D20=
>>=20
>>> >> > Expected row count: 5 rows after the scan=3D20=20
>>> >> > Operation cost : 35=20
>>> >> >=3D3D20=20
>>> >> > Operation 1.1 : Sort=3D20=20
>>> >> > Requested : Explicitly in the query=3D20=20
>>> >> > Sort rows in the : Result of a Select=3D20=20
>>> >> > Purpose : To order rows for an Order By=3D20=20
>>> >> > Sort technique : FASTSORT=3D20=20
>>> >> > Sort type : Plan to use User Process Sort=3D20=20
>>> >> > UPS workspace : 34 Kbytes=3D20=20
>>> >> > Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=3D20=20
>>> >> > PARTS.REPORT_NUMBER asc,=3D20=20
>>> >> > PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,=20
>>> >> > PARTS.BIN_LOC asc,=3D3D20=3D20=20
>>> >> > Sort cost : 1=3D20=20
>>> >> >=3D3D20=3D20
>>> >> > Total cost : 36=20
>>> >> --------------------------------------------------=20
>>> >> >>In case if there are more records than 5000, it saves off record
num=
>>ber=20
>>> >> >>and use it to reopen the cursor and get the rest of the records.
>>> >> Where does record number come from and how does it become part=3D20
>>> >> of the where clause on reopening the cursor?=20
>>> >=20
>>> >
>>> >When the cursor is opened, it will fetch up to 5000 record, on 5001,
the=
>> 5=3D=20
>>> >000 record limit will cause the program to save off the data for record
=
Click here to read the complete article

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor