Home » SQL & PL/SQL » SQL & PL/SQL » An index with no referances to actual rows
An index with no referances to actual rows [message #214925] Thu, 18 January 2007 11:12 Go to next message
yair_fi
Messages: 3
Registered: January 2007
Junior Member
Hi all,

I find my self wondering about a useful object - and couldn't
find if it existed.

It's an index on a column (or several columns) that doesn't
hold references to the rows themselves, but rather just a
reference count.

This can make queries such as "select distinct" or
"select count(*) group by" queries faster. I find that these queries are
used quite often (in our case) and we are implementing software
solutions to handle them (on millions of records per table,
with up to tens of different values).

What I'm thinking about is actually a low-level lookup table,
with (what seems to me) low resource demands. It may even be
used to replace the actual values in the column (which may be
long strings) with integers – invisible to the user, and all
queries done on that table.


A bitmap index is currently the fastest way to do this (if I
understand correctly) – but it consumes too mach time and space
to maintain.

Is there such an object?

Sorry about the length entry, and thanks for your help
Yair

[Updated on: Thu, 18 January 2007 11:14]

Report message to a moderator

Re: An index with no referances to actual rows [message #214926 is a reply to message #214925] Thu, 18 January 2007 11:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you consider an IOT?
Re: An index with no referances to actual rows [message #214927 is a reply to message #214925] Thu, 18 January 2007 11:30 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You could look at Index Only Tables, which don't require an actual table. Other than that, if you want aggregated versions of the data, the normal approach would be to use materialized views. Oracle has a query rewrite capability where it can use these in place of aggregated queries against the base table.
Re: An index with no referances to actual rows [message #215317 is a reply to message #214925] Sun, 21 January 2007 03:23 Go to previous messageGo to next message
yair_fi
Messages: 3
Registered: January 2007
Junior Member
Thanks - but it's not quite there yet.

Index Only Tables are not good enough because we may have other indexes that are more valuable for the other queries.

and materialized view sound very heavy, and need updates after the inserts, which will call for a lot of work in comparison to the object i suggested.


Re: An index with no referances to actual rows [message #215323 is a reply to message #215317] Sun, 21 January 2007 05:16 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
FYI it's Index-Organized Table.

I haven't tried this but you should be able to use the ON PREBUILT TABLE clause of CREATE MATERIALIZED VIEW to use an index-organized table.

I don't see how your theoretical aggregate lookup index could use any less resources than this.

yair_fi wrote on Sun, 21 January 2007 03:23
Index Only Tables are not good enough because we may have other indexes that are more valuable for the other queries.

So have other indexes? Or more than one IOT? Or have indexes on the IOT itself? I don't see what you mean here.

yair_fi wrote on Sun, 21 January 2007 03:23
materialized view sound very heavy, and need updates after the inserts, which will call for a lot of work in comparison to the object i suggested.

The object you suggested would need updating to reflect changes to the source data. How could it avoid that? How else could it work?

btw you mentioned bitmap indexes, but I don't see a use for them here.
Re: An index with no referances to actual rows [message #215332 is a reply to message #215323] Sun, 21 January 2007 08:14 Go to previous messageGo to next message
yair_fi
Messages: 3
Registered: January 2007
Junior Member
Thanks. I'm looking into the overhead caused by a Materialized view (calculating fields, sum(*)group by fields), running with fast refresh on commit.

William Robertson wrote on Sun, 21 January 2007

I don't see how your theoretical aggregate lookup index could use any less resources than this.

The object you suggested would need updating to reflect changes to the source data. How could it avoid that? How else could it work?



The main difference is that the refresh of a materialized view still has some potentially heavy overhead: it needs to hold the updates (in the "materialized view log" for the fast refresh) until the commit, It needs to scan them again (which may be a big scan) etc.

The object i suggested works on a per line update, like an index, and does an almost trivial amount of work (very unlike a normal index).

William Robertson wrote on Sun, 21 January 2007

yair_fi wrote on Sun, 21 January 2007 03:23
Index Only Tables are not good enough because we may have other indexes that are more valuable for the other queries.

So have other indexes? Or more than one IOT? Or have indexes on the IOT itself? I don't see what you mean here.


I meant that this table may already have another IOT. And the feature i suggested may be used on the same table for different groups of fields - then we can't implement all of them as a IOT.


I guess my entire feature can be replaced by the materialized view, and to make it equivalent - with a hint that the materialized view needs to refresh on a per row update rather than the commit. for small enough materialized views, this may be optimal.

But then we lose the possibility to use the indexed values as a lookup table, and enjoy a trivial compression in the same time.

well, just thoughts...
Yair
Re: An index with no referances to actual rows [message #215555 is a reply to message #215332] Mon, 22 January 2007 19:19 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I don't believe there's an accurate way to know the records than to count them each time. To keep a running counter (in a table) requires an update for each rown added or deleted in the main table. It needs to be updated when you truncate, or add/swap partitions etc. The MV also has unescapable overhead and it's correct in realtime. DBMS_STATS can give you a quick estimate or an exact count based on how much overhead you're happy with - but isn't really a practical solution.

Take a look at table monitoring for a running approximation too...
Re: An index with no referances to actual rows [message #215954 is a reply to message #214925] Wed, 24 January 2007 07:17 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
It almost sounds like the OP is talking about virtual columns, which Oracle does not have in 10g, but may in 11g.
Re: An index with no referances to actual rows [message #215964 is a reply to message #214925] Wed, 24 January 2007 07:50 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
They may not support aggregated virtual columns. That would make locking of the table very complex - an update would have to lock the entire set of rows affected by the aggregate, as they would all need to be updated.
Previous Topic: SQL queries . find second largest salary of emp.
Next Topic: two users
Goto Forum:
  


Current Time: Sun Dec 11 08:02:37 CST 2016

Total time taken to generate the page: 0.04410 seconds