Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index based database?

Re: Index based database?

From: EscVector <Junk_at_webthere.com>
Date: 14 Dec 2006 13:38:25 -0800
Message-ID: <1166132305.614197.74430@80g2000cwy.googlegroups.com>

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_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. 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_at_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". Received on Thu Dec 14 2006 - 15:38:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US