For versions 9 and older, it was well documented how character and other "long" data are stored in an index based on the declared size of the data (full vs. compressed vs. partial) - here's a almost palaeolithic description:)

For version 10 and above, I have not found an according statement in the current documentation.

Does this somehow depend on the values of the INLINE/PREFIX clauses (introduced with v10) of the according columns that specify how much portion of "longer" character or blob data will be stored within a data page vs. an extension page?

In particular: Under which circumstances will the database engine use compressed or hashed values of the according data in contrast to full values?

(Aside: This question has come up in context with that other one.)

asked 22 May '14, 04:06

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%

edited 22 May '14, 04:08

1

Any hint would be appreciated...:)

(30 May '14, 10:25) Volker Barth

Hello Volker,

I believe you will find the basics of this are covered in the '10.0.0 New features' article under the "Main features" article as

which clearly identifies that we now use a newer compressed B-Tree exclusively and entire key values are present in compressed form. Along with other benefits, this new implmentation eliminated the limitations and side-effects from the earlier hash-based [correction: implementation]; making the need for Full-Compares for key matching lookups moot.

Later on in 11.0 there were additional indexing features that benefitted from this 10.0.x starting point; Index-only retrieval and even more compression. And again in 12.0 a further performance refinements around clustering of sequential values . . .

All of which should be mostly transparent to the schema's design, unlike the issues with the older indexing technologies which could require schema modification (index design) to address implementation specific aspects; and thus the need for articles like the one that Breck Carter penned.

HTH

Nick

You will note the my links are to the 12.0.1 DocCommentXchange doc set. The version 16.0 one no longer has the V10.0.0/.1 "What's new in ..." articles and have replace those with a link to 'older version'.

permanent link

answered 06 Jun '14, 13:46

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 09 Jun '14, 10:22

The new indexes store a compressed form of the index key value in the index entry, separate and distinct from the value in the row.

Thanks for the clarification, Nick. I have read the above quote from the cited v10 "What's New" topic but wasn't sure that "index key value in the index entry" refers to the whole indexed data. As you state, that's the exact meaning.

And a special thanks for the pointer to the further enhancements in v11/v12!

(06 Jun '14, 18:04) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×81
×26

question asked: 22 May '14, 04:06

question was seen: 4,318 times

last updated: 09 Jun '14, 10:22