Re: A thought on Index Organized Tables

On Wed, Feb 24, 2010 at 8:04 PM, Gokulakannan Somasundaram
<gokul007@gmail.com> wrote:
> OK I think, i can think of a solution to achieve fast full index scan like
> oracle.
> a) Issue ids to every block that gets created inside the index. we are
> already doing that
> b) Now before the fast full index scan starts, note down the max id that got
> issued.
> c) Now do a scan similar to Full Table Scan till that max id. Now, while we
> are scanning the blocks, note down the right siblings in a list, if the
> right sibling block id is greater than the max id that got issued. These are
> the ones, which have got split after the scan started
> d) Now after we reach that max block, try to range scan on missing links
> till we hit the end / get a right sibling less than the max block id noted.
>
> Once we do this for all the missing links, we have scanned through the
> entire chain. So this will definitely be faster than range scan.
>
> Thanks to you, i have thought about an important issue and also a solution
> for it.

I haven't thought about whether this is sufficient but if it is then
an initial useful thing to add would be to use it for queries where we
have a qual that can be checked using the index key even though we
can't do a range scan. For example if you have a btree index on
<a,b,c> and you have a WHERE clause like "WHERE c=0"

That would be a much smaller change than IOT but it would still be a
pretty big project. Usually the hardest part is actually putting the
logic in the planner to determine whether it's advantageous. I would
suggest waiting until after 9.0 is out the door to make sure you have
the attention of Heikki or Tom or someone else who can spend the time
to check that it will actually work before putting lots of work coding
it.

-- 
greg