Tempindx and index page size

Billybob73 — Thu, 07 Nov 2019 08:11:26 GMT

Hi,

I 'm creating a new table in a proc sql using a table that has considerable lengths.

NAME	TYPE	LENGTH
Variable1	2	5000
Variable2	2	255
Variable3	2	15000
Variable4	2	5000
Variable5	2	5000
Variable6	2	15000
Variable7	2	5000
Variable8	2	25
Variable9	1	8
Variable10	2	255
Variable11	1	8
Variable12	2	5000
Variable13	2	5000
Variable14	2	15000
Variable15	1	8

This proc sql gives the following output :

ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000000278'n because the length of the index value (15026 bytes) is too

large. The index page size (currently 32767) must be large enough to store three index values plus a small overhead

structure. Reduce the length of the index value or use the IBUFSIZE= option to increase the index page size (up to a maximum

of 32,767 bytes).

I alread found out that reducing these lengths solves the problem, however I'm stuck with a couple of questions as I don't understand exactly why the ERROR is happening :

1. What does 'the length of the index value' mean in the error message ?

2. The maximum IBUFSIZE option 32,767 did not work for me. Still I got the error message.... Is there another solution apart from reducing the lengths ?

3. The input data set for the proc sql is not big at all. Only 108 records. It seems that the length of the fields influences a memory buffer. Is that right ? How to prevent this by default ?

4. What is this tmpindex and how does this relate tot the index page size ? I'm confused by the word index because I normally consider this a method to make queries faster on fields that are indexed

Thanks for clarifying !

Regards

Re: Tempindx and index page size

Patrick — Thu, 07 Nov 2019 08:57:03 GMT

Have you already seen the SAS Note here?

The error message says:

"...must be large enough to store three index values plus a small overhead structure". Below code works if you set ibufsize to the maximum and the indexed variable isn't much longer than 10000 characters.

options ibufsize=32767;
proc options option=ibufsize;
run;

data test;
  length var $10000;
  do var='a','b','c';
    output;
  end;
  stop;
run;

proc sql;
  create index var on test(var);
quit;

topic Tempindx and index page size in New SAS User

Tempindx and index page size

Re: Tempindx and index page size