Re: ______SQL & Index Question

In article <ZB5w5.1149$l35.16541_at_iad-read.news.verio.net>,   "Emre Akbag" <akbag_at_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_at_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.