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.

Anyone?

In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
EscVector <Junk@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>

The tables are not narrow.
Example 27 col table has 26 single column indexes.

Still normal?


Jim Smith wrote:
> In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
> EscVector <Junk@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>

In message <1166110172.104499.291490@80g2000cwy.googlegroups. com>,
EscVector <Junk@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@l12g2000cwl.googlegroups. com>,
>> EscVector <Junk@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>

EscVector wrote:
> The tables are not narrow.
> Example 27 col table has 26 single column indexes.
>
> Still normal?
>
>
> Jim Smith wrote:
>> In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
>> EscVector <Junk@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. Scroll to the bottom to reply.

Given that this is a warehouse it may be fine and it may be excessive.

Is is a "real" warehouse designed as one or just a copy of an OLTP schema?
Are they being used?
What index type?
What version of Oracle?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> EscVector wrote:
> > The tables are not narrow.
> > Example 27 col table has 26 single column indexes.
> >
> > Still normal?
> >
> >
> > Jim Smith wrote:
> >> In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
> >> EscVector <Junk@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. Scroll to the bottom to reply.
>
> Given that this is a warehouse it may be fine and it may be excessive.
>
> Is is a "real" warehouse designed as one or just a copy of an OLTP schema?
> Are they being used?
> What index type?
> What version of Oracle?
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Sorry about the top post.... Was in beta and screwed up..

Answer:
1. Kimbal Based Warehouse no legacy OLTP and no ODS, designed for
warehouse/reporting

2. Indexes being used: not always - but that's another issue.

3. Index type = B*Tree, all B*Tree

4. Database Versions (see below)
COMP_ID COMP_NAME VERSION STATU
CATALOG Oracle Database Catalog Views 10.2.0.2.0 VALID
CATPROC Oracle Database Packages and Types 10.2.0.2.0 VALID
OWM Oracle Workspace Manager 10.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 10.2.0.2.0 VALID
XML Oracle XDK
10.2.0.2.0 VALID
CATJAVA Oracle Database Java Packages 10.2.0.2.0 VALID
EXF Oracle Expression Filter
10.2.0.2.0 VALID
XDB Oracle XML Database
10.2.0.2.0 VALID
RUL Oracle Rules Manager
10.2.0.2.0 VALID

Jim Smith wrote:
> In message <1166110172.104499.291490@80g2000cwy.googlegroups. com>,
> EscVector <Junk@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@l12g2000cwl.googlegroups. com>,
> >> EscVector <Junk@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.

EscVector wrote:
> DA Morgan wrote:
> > EscVector wrote:
> > > The tables are not narrow.
> > > Example 27 col table has 26 single column indexes.
> > >
> > > Still normal?
> > >
> > >
> > > Jim Smith wrote:
> > >> In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
> > >> EscVector <Junk@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. Scroll to the bottom to reply.
> >
> > Given that this is a warehouse it may be fine and it may be excessive.
> >
> > Is is a "real" warehouse designed as one or just a copy of an OLTP schema?
> > Are they being used?
> > What index type?
> > What version of Oracle?
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan@x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
> Sorry about the top post.... Was in beta and screwed up..
>
> Answer:
> 1. Kimbal Based Warehouse no legacy OLTP and no ODS, designed for
> warehouse/reporting
>
> 2. Indexes being used: not always - but that's another issue.
>
> 3. Index type = B*Tree, all B*Tree
>
> 4. Database Versions (see below)
> COMP_ID COMP_NAME VERSION STATU
> CATALOG Oracle Database Catalog Views 10.2.0.2.0 VALID
> CATPROC Oracle Database Packages and Types 10.2.0.2.0 VALID
> OWM Oracle Workspace Manager 10.2.0.1.0 VALID
> JAVAVM JServer JAVA Virtual Machine 10.2.0.2.0 VALID
> XML Oracle XDK
> 10.2.0.2.0 VALID
> CATJAVA Oracle Database Java Packages 10.2.0.2.0 VALID
> EXF Oracle Expression Filter
> 10.2.0.2.0 VALID
> XDB Oracle XML Database
> 10.2.0.2.0 VALID
> RUL Oracle Rules Manager
> 10.2.0.2.0 VALID

I guess I should have said that I'm looking for first thoughts and
questions to ask the Data Modeler.

My initial thoughts are that some if not many of these indexes could be
combined, but there are app considerations, so may not be cost
effective. During runs we have 90gb undo, so that is also a big
downside to having so many indexes. 90gb for 6gb of real data. Undo
gets backed up by RMAN unless resized prior to backup. 99.9333333% of
backup would be UNDO. Total data in db is around 70gb.

I'm interested in the design as it pertains to eliminating waste as in
"the fast way to speed something up is to not do it".

Junk@webthere.com says...
> DA Morgan wrote:
> >
> > Is is a "real" warehouse designed as one or just a copy of an OLTP schema?
> > Are they being used?
> > What index type?
>
> 1. Kimbal Based Warehouse no legacy OLTP and no ODS, designed for
> warehouse/reporting
>
> 2. Indexes being used: not always - but that's another issue.
>
> 3. Index type = B*Tree, all B*Tree

It is often common for indexes in a DW to be bitmap rather than b-tree.

Good book by Bert Scalzo specifically on Oracle/DW/star schemas is worth a
read.

GM

EscVector wrote:
> DA Morgan wrote:
> > EscVector wrote:
> > > The tables are not narrow.
> > > Example 27 col table has 26 single column indexes.
> > >
> > > Still normal?
> > >
> > >
> > > Jim Smith wrote:
> > >> In message <1166109023.164657.84250@l12g2000cwl.googlegroups. com>,
> > >> EscVector <Junk@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. Scroll to the bottom to reply.
> >
> > Given that this is a warehouse it may be fine and it may be excessive.
> >
> > Is is a "real" warehouse designed as one or just a copy of an OLTP schema?
> > Are they being used?
> > What index type?
> > What version of Oracle?
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan@x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
> Sorry about the top post.... Was in beta and screwed up..
>
> Answer:
> 1. Kimbal Based Warehouse no legacy OLTP and no ODS, designed for
> warehouse/reporting
>
> 2. Indexes being used: not always - but that's another issue.
>
> 3. Index type = B*Tree, all B*Tree
>
> 4. Database Versions (see below)
> COMP_ID COMP_NAME VERSION STATU
> CATALOG Oracle Database Catalog Views 10.2.0.2.0 VALID
> CATPROC Oracle Database Packages and Types 10.2.0.2.0 VALID
> OWM Oracle Workspace Manager 10.2.0.1.0 VALID
> JAVAVM JServer JAVA Virtual Machine 10.2.0.2.0 VALID
> XML Oracle XDK
> 10.2.0.2.0 VALID
> CATJAVA Oracle Database Java Packages 10.2.0.2.0 VALID
> EXF Oracle Expression Filter
> 10.2.0.2.0 VALID
> XDB Oracle XML Database
> 10.2.0.2.0 VALID
> RUL Oracle Rules Manager
> 10.2.0.2.0 VALID

I guess I should have said that I'm looking for first thoughts and
questions to ask the Data Modeler.

My initial thoughts are that some if not many of these indexes could be
combined, but there are app considerations, so may not be cost
effective. During runs we have 90gb undo, so that is also a big
downside to having so many indexes. 90gb for 6gb of real data. Undo
gets backed up by RMAN unless resized prior to backup. 99.9333333% of
backup would be UNDO. Total data in db is around 70gb.

I'm interested in the design as it pertains to eliminating waste as in
"the fast way to speed something up is to not do it".