🚀 go-pugleaf

In article <ZB5w5.1149$l35.16541@iad-read.news.verio.net>,
  "Emre Akbag" <akbag@scr.siemens.com> wrote:
> Hi,
>
> say I have a table with many columns and 100.000 rows.
> Table is also partitioned in 10 pieces according to the values in
Column C:
>
>     Column B has 70.000 different values
>     Column C has 10 different values.
>
> I'll query the table always giving two arguments for B and C.
>
> How should I index this table:
>
> 1) local index on (B,C)
> 2) local index on (C,B) (What's the difference between 1 and 2 ?)
> 3) global index on B
> 4) local index on C
> 5) none of them, but...
>
> I'd appreciate any ideas.
>
> --
> Thanks in advance
>
> Emre AKBAG
>
> akbag@scr.siemens.com
> Siemens Corporate Research
>
>

My thoughts would lean toward the following scenario:

Given that the table is partitioned on Column C I would create a local,
prefixed, equipartitioned index.  This will let Oracle manage both the
table and the index (even when table partitions change), partition the
index in the same fashion as the table and possibly provide faster
access via an indexed query.  Since you will always be passing values
for Column B and Column C in your query only one,  Column C,  need be
indexed to reap the benefits of faster query times.  There are cases
where a local, prefixed index is not desirable but in your case it
certainly is, in my opinion, the best solution.

Global indexes must be maintained by the DBA without assistance from
the instance so they can be much more labor-intensive than local
indexes, especially when table partitions change or are dropped.  With
a local index only the affected partition or partitions need be rebuilt
if a table partition changes or is dropped; the remaining partitions
are unaffected.

To provide a response to your other question:

> 1) local index on (B,C)
> 2) local index on (C,B) (What's the difference between 1 and 2 ?)

The difference between 1 and 2 is that the leading column in index 1 is
B, indicating that B must be used in all queries against the table to
utilize the index.  Index 2 has Column C as the leading column,
indicating that Column C must always be used to utilize the index.
Again, in your case where values for both columns are always used
either index would be suitable.  As long as the leading column in an
index is used in the where clause the index will be accessed (unless,
of course, the CBO [Cost-Based Optimizer] indicates otherwise).
--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.