Cache Sequence

[KSK]

Hi All,

Question) I have initialized cache with four indexes(as shown below), based on which key (key1, key2, Key3 ...) data in the caches are sequenced?

Thanks in advance.


Syntax:


jdecmIndexF42UI11[0].nKeyID=1;

jdecmIndexF42UI11[0].nNumSegments = 3;

jdecmIndexF42UI11[0].CacheKey[0].nOffset = offsetof(F42UI11, zdctid);
jdecmIndexF42UI11[0].CacheKey[0].nSize = DIM(dsF42UI11.zdctid);
jdecmIndexF42UI11[0].CacheKey[0].idDataType = EVDT_STRING;

jdecmIndexF42UI11[0].CacheKey[1].nOffset = offsetof(F42UI11, zdjobs);
jdecmIndexF42UI11[0].CacheKey[1].nSize = sizeof(dsF42UI11.zdjobs);
jdecmIndexF42UI11[0].CacheKey[1].idDataType = EVDT_MATH_NUMERIC;

jdecmIndexF42UI11[0].CacheKey[2].nOffset = offsetof(F42UI11, zdlnix);
jdecmIndexF42UI11[0].CacheKey[2].nSize = sizeof(dsF42UI11.zdlnix);
jdecmIndexF42UI11[0].CacheKey[2].idDataType = EVDT_MATH_NUMERIC;


jdecmIndexF42UI11[1].nKeyID=2;

jdecmIndexF42UI11[1].nNumSegments = 3;

jdecmIndexF42UI11[1].CacheKey[0].nOffset = offsetof(F42UI11, zdctid);
jdecmIndexF42UI11[1].CacheKey[0].nSize = DIM(dsF42UI11.zdctid);
jdecmIndexF42UI11[1].CacheKey[0].idDataType = EVDT_STRING;

jdecmIndexF42UI11[1].CacheKey[1].nOffset = offsetof(F42UI11, zdjobs);
jdecmIndexF42UI11[1].CacheKey[1].nSize = sizeof(dsF42UI11.zdjobs);
jdecmIndexF42UI11[1].CacheKey[1].idDataType = EVDT_MATH_NUMERIC;

jdecmIndexF42UI11[1].CacheKey[2].nOffset = offsetof(F42UI11, zditm);
jdecmIndexF42UI11[1].CacheKey[2].nSize = sizeof(dsF42UI11.zditm);
jdecmIndexF42UI11[1].CacheKey[2].idDataType = EVDT_MATH_NUMERIC;


jdecmIndexF42UI11[2].nKeyID=3;

jdecmIndexF42UI11[2].nNumSegments = 3;

jdecmIndexF42UI11[2].CacheKey[0].nOffset = offsetof(F42UI11, zdctid);
jdecmIndexF42UI11[2].CacheKey[0].nSize = DIM(dsF42UI11.zdctid);
jdecmIndexF42UI11[2].CacheKey[0].idDataType = EVDT_STRING;

jdecmIndexF42UI11[2].CacheKey[1].nOffset = offsetof(F42UI11, zdjobs);
jdecmIndexF42UI11[2].CacheKey[1].nSize = sizeof(dsF42UI11.zdjobs);
jdecmIndexF42UI11[2].CacheKey[1].idDataType = EVDT_MATH_NUMERIC;

jdecmIndexF42UI11[2].CacheKey[2].nOffset = offsetof(F42UI11, zdslne);
jdecmIndexF42UI11[2].CacheKey[2].nSize = sizeof(dsF42UI11.zdslne);
jdecmIndexF42UI11[2].CacheKey[2].idDataType = EVDT_MATH_NUMERIC;


jdecmIndexF42UI11[3].nKeyID=4;
jdecmIndexF42UI11[3].nNumSegments = 4;

jdecmIndexF42UI11[3].CacheKey[0].nOffset = offsetof(F42UI11, zdctid);
jdecmIndexF42UI11[3].CacheKey[0].nSize = DIM(dsF42UI11.zdctid);
jdecmIndexF42UI11[3].CacheKey[0].idDataType = EVDT_STRING;

jdecmIndexF42UI11[3].CacheKey[1].nOffset = offsetof(F42UI11, zdjobs);
jdecmIndexF42UI11[3].CacheKey[1].nSize = sizeof(dsF42UI11.zdjobs);
jdecmIndexF42UI11[3].CacheKey[1].idDataType = EVDT_MATH_NUMERIC;

jdecmIndexF42UI11[3].CacheKey[2].nOffset = offsetof(F42UI11, zdmcln);
jdecmIndexF42UI11[3].CacheKey[2].nSize = sizeof(dsF42UI11.zdmcln);
jdecmIndexF42UI11[3].CacheKey[2].idDataType = EVDT_MATH_NUMERIC;

jdecmIndexF42UI11[3].CacheKey[3].nOffset = offsetof(F42UI11, zdlnid);
jdecmIndexF42UI11[3].CacheKey[3].nSize = sizeof(dsF42UI11.zdlnid);
jdecmIndexF42UI11[3].CacheKey[3].idDataType = EVDT_MATH_NUMERIC;

idJDECMReturn = jdeCacheInitMultipleIndex((*lpdsF42UI11DAO)->hUser,&((*lpdsF42UI11DAO)->hCache),
_J("F42UI11"),(JDECMINDEXSTRUCT *)(jdecmIndexF42UI11),4);

 

[Gargamel]

jdeCacheSetIndex(hCache,hCursor,iIndex) will set the ordering key used to access your data in sequential read.

Without this call, you'll use the first defined index.

 

[JMR]

Not sure I understand exactly what you're asking, but if you were to read the contents of the cache without specifying an index, I believe they would be read using the first key (jdecmIndexF42UI11[0].nKeyID=1

You can read the cache in the order of any of the defined keys by first calling the API, jdeCacheSetIndex.

 

[BOster]

Are you asking what sequence or sort order the records will be stored?

Well, I have never found any documentation from Oracle that explicitly states the sort order or sequence of jdeCache. So I would advice you to be cautious if your logic relys on sort order.

Having said that, in my experience jdeCache will sort based on the primary key which is the first key defined. But be careful, because if your primary key contains something like a MATH_NUMERIC or other data structure or data type it may not sort the way you expect. If you want to try and get your cache to be sequenced consitently I would use an int or long as a primary key.

Again there is no stated garuntee from Oracle on the sort order and even if it appears that it is sorting a certain way there is no garuntees that Oracle wont change there internal implementation in the future and it may not sort the same way.

 

[BOster]

[ QUOTE ]

You can read the cache in the order of any of the defined keys by first calling the API, jdeCacheSetIndex.

[/ QUOTE ]

I originally posted that you couldn't iterate the cache records using the sort order of the secondary key. But JMR is correct you can (see attached test code). I am editing this post to reflect what you should see using these scenarios.

Example

int nMyKey; //primary key
JCAHR szName; //secondary key


data

nMyKey szName
------ -----------
1..........brian
2..........brian oster
3..........allison
4..........zach
5..........john
6..........allison
7..........tom


setting cursor to use secondary index and searching for "Allison"
jdeCacheFetchPosition: returns record 3
jdeCacheFetchPosition: returns record 3
jdeCacheFetch: returns record 1 (brian)
jdeCacheFetch: returns record 2 (brian oster)
...

if you open a brand new cursor and set the index to use the secondary index:
jdeCacheFetch: returns record 3 (allison)
jdeCacheFetch: returns record 6 (allison)
jdeCacheFetch: returns record 1 (brian)
jdeCacheFetch: returns record 2 (brian oster)
jdeCacheFetchPostion(search for allison): returns record 3

 

[BOster]

I owe JMR an apology. After emailing back and forth and doing some testing JMR is in fact correct, you can in fact iterate through jdeCache in the sort order of a secondary key using jdeCacheFetch. I will edit my original post in a second to get rid of the mis-information. I will also post my example code in a little bit for those that are interested.

 

[JMR]

Brian,
Seriously, no apology necessary. I took no offense from your earlier post. I'm married...I'm used to being told I'm wrong

 

[KSK]

Hi Boster,

Thanks for the information.

I have a cache with primary Index as CTID(JCHAR) , JOBS (MATH_NUMERIC) , LNIX (MATH_NUMERIC) .

Data in the caches are not sorted as expected consistently ( failed to sort properly once out of 5 times)

As you mentioned , Since I am having MATH_NUMERIC as primary index , that leads to cause this sorting issue ?

 

[BOster]

I am not sure if it is MATH_NUMERIC or what... all I know is that I too have run into trouble when I expected the cache to be sorted a certain way and it was sorted differently and usually it involved a data type that was something other than a simple primitive data type (int, long, string, etc.) like a MATH_NUMERIC.