Re: Index based database?

From: EscVector <Junk_at_webthere.com>
Date: 14 Dec 2006 12:48:13 -0800
Message-ID: <1166129293.189536.228670@80g2000cwy.googlegroups.com>

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 Received on Thu Dec 14 2006 - 14:48:13 CST