Re: Index based database?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 17 Dec 2006 14:18:13 -0800
Message-ID: <1166393890.116577@bubbleator.drizzle.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
A true warehouse and B*Tree indexes appears in conflict. I would expect at least some bitmap or bitmap domain indexes.

I would treat the indexes with suspicion.

You might want to start with the following:

ALTER INDEX <index_name> MONITORING USAGE;

followed in a few hours/days with:

SELECT * FROM v$object_usage;

But if these indexes were created by the data modeler without any actual usage data ... the data modeler likely needs retraining.

-- 
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

Received on Sun Dec 17 2006 - 16:18:13 CST