Re: Index based database?

On 14 Dec 2006 12:48:13 -0800, "EscVector" <Junk_at_webthere.com> 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
>

A warehouse database Kimball-style has roughly 2 parts:

1. The part that is there to generate part 2
2. The part that is queried for reporting

In part 1 typically complete tables are processed, leaving little use for indexes.

In part 2 usually there are B-tree indexes on the datawarehouse key and the natural key of the dimensions, but those are typically small tables. On the fact tables there are preferably bitmap indexes on each column that refers to the datawarehouse key of the corresponding dimension, so no B-tree indexes there.

So in my opinion it is very odd to have a volume of index data that is of the same order of magnitude as the volume of table data.

Jaap. Received on Tue Dec 19 2006 - 15:31:52 CST