RE: Bigger block sizes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 2 Oct 2015 12:51:29 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A7FA3_at_EXMBX01.thus.corp>

It's the type of thing people do "just in case"; the columns won't necessarily ever hold anything like 4000 bytes, but the size makes them open ended. However the index code has to assume the worst case.

Related oddments: https://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ram Raman [veeeraman_at_gmail.com] Sent: 02 October 2015 13:33
To: Mark Brinsmead
Cc: oralrnr_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Bigger block sizes

Mark,

Who or what would use an index on 4000 byte column in index and how? I am thinking it will not be a human input.

On Thu, Oct 1, 2015 at 11:32 PM, Mark Brinsmead <mark.brinsmead_at_gmail.com<mailto:mark.brinsmead_at_gmail.com>> wrote: One (sort of) legitimate use-case I came across was an inherited database where they used tablespaces with 32KB blocks to house indexes that required insanely huge keys.

(Even with 32KB blocks, we were sometimes unable to create the indexes we wanted/needed, which often included multiple VARCHAR(4000) columns.)

The application itself was highly unusual, and the underlying architecture even more so. (And I had nothing whatsoever to do with either of them.)

Anyway, cases where you truly need indexes with really large keys will warrant block sizes greater than 8KB. With 8KB blocks, you are limited to something like 3916 bytes as your largest key. (Yes, I know -- that ought to be enough for most people. Did I mention that I had no input into the architectural decisions?)

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 02 2015 - 14:51:29 CEST