Re: Index based database?

Jim Smith wrote:
> In message <1166110172.104499.291490_at_80g2000cwy.googlegroups.com>,
> EscVector <Junk_at_webthere.com> writes
> >The tables are not narrow.
> >Example 27 col table has 26 single column indexes.
> >
> >Still normal?
> >
> >
> >Jim Smith wrote:
> >> In message <1166109023.164657.84250_at_l12g2000cwl.googlegroups.com>,
> >> EscVector <Junk_at_webthere.com> writes
> >> >I've seen this a few times since getting into Warehousing, but I need a
> >> >sanity check.
> >> >
> >> >How often has anyone seen the total INDEX bytes exceed DATA bytes,
> >> >meaning I have more data stored in standard indexes, not index
> >> >organized tables, than regular heap tables.
> >> >
> >> It is not unusual.
> >>
> >> Given the rowid overhead in an index, if the table is quite narrow a
> >> single index could easily be larger than the table. If you have multiple
> >> indexes the total index space could be several times the size of the
> >> table.
> >> --
> >> Jim Smith
> >> Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
> >> RSS <http://oracleandting.blogspot.com/atom.xml>
> >
>
> Please don't top post.
>
> You are querying the number of indexes rather than the total size, which
> was how I read it in the first place.
>
> 26 indexes seems over the top, but if it is a data warehouse fact table
> with foreign keys to dimension tables perhaps they are justified.
>
> The only real test is - does it perform well?
> --
> Jim Smith
> Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
> RSS <http://oracleandting.blogspot.com/atom.xml>

Sorry for the top post...

And thanks for the response.

I'm looking at the system overall, and found one table containing almost as many indexes to columns. They are unique, not fk indexes.

The functional design uses schema as functional use container, as in STAGE, LOAD, etc.

The 26 index table is in a Translation schema.

Total data bytes for this one schema = 6291456000 Total index bytes for this one schema = 15728640000

data is 40% of index or another way, index space required is 2.5 time larger than data.

Think simple, not a big warehouse.

Looking for first or seconds thoughts as you see it. Questions to ask Data Modeler. Received on Thu Dec 14 2006 - 15:13:10 CST