Re: non-prefixed indexes on composite partitioned tables

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Fri, 28 Jan 2005 01:10:58 -0800 (PST)
Message-ID: <20050128091059.71955.qmail@web52610.mail.yahoo.com>

Hi Bill,

This is because the prerequisite to create the local index is to have the partitioning key columns inside the index.

It does not need to be in the prefix at all.

You will choose where to put these columns when considering how you are going to access data, scalability, speed (if used with bulk operations), I/O capacity sizing, ...

Regards,
Zoran Martic

• Bill Coulam <bill.coulam_at_scgo.com> wrote:

> Amusing background: My current shop has 90
> instances. The previous data
> architect read about partitioning and thought it
> sounded good. A couple
> tests showed better response, so he said to himself,
> "If partitioning is
> good, subpartitioning must be even better!" So every
> table over 50K rows
> here is composite-hash partitioned, pre-created back
> in 2003 out to 2006.
> Some of them have 394 subpartitions. No archival
> strategy. Every row is
> still here. One of the largest, the one in my
> example below has 55 columns,
> and 13 indexes so far, some of them heinous
> composites.
> My favorite: on tables where there wasn't an
> obvious, selective candidate
> for the hash, he picked any ol' column, often
> favoring one that has a couple
> of distinct values.
>
> </END VENT>
>
> Anyway, so I'm developing a quick report to give me
> a bird's eye of the
> mess, er I mean the tables, indexes, partitioning on
> each, etc.
>
> user_part_indexes seems to be reporting that certain
> indexes are
> non-prefixed, even when they use the table's range
> key as their first
> column.
>
> For example, table BOGUS (table and column names
> changed) is range
> partitioned on mydate and hash partitioned on
> mycode. Here is a sampling of
> a few indexes on BOGUS, gleaned from
> user_part_tables and user_part_indexes
>
> BOGUS RANGE(mydate) +
> HASH(mycode)
> BOGUS_PK NORMAL GLOBAL
> NON-PARTITIONED
> columns: c1
> BOGUS_UK RANGE(mydate) +
> HASH(mycode)LOCAL PREFIXED
> columns: mydate c2 c3 c4 mycode c5 c6 c7 c8
> c9 c10 c11 c12 c13 c14
> c15 c16
> BOGUS_COMP2_LIDX RANGE(mydate) +
> HASH(mycode)LOCAL NON_PREFIXED
> columns: c6 mkt_cd mydate mycode c4 c2
> BOGUS_COMP3_LIDX RANGE(mydate) +
> HASH(mycode)LOCAL PREFIXED
> columns: mydate end_dt_gmt c14 c2 c3 c4
> mycode c10 c11 c6 mkt_cd c1
> BOGUS_COMP4_LIDX RANGE(mydate) +
> HASH(mycode)LOCAL NON_PREFIXED
> columns: mydate c16
> BOGUS_COMP6_LIDX RANGE(mydate) +
> HASH(mycode)LOCAL NON_PREFIXED
> columns: mycode c3
> BOGUS_COMP7_LIDX RANGE(mydate) +
> HASH(mycode)LOCAL NON_PREFIXED
> columns: mydate c2 end_dt_gmt c6 mkt_cd
>
>
> I expected that indexes leading with the range-keyed
> date would be
> considered PREFIXED, like the UK, comp3, comp4 and
> comp7. Instead comp4 and
> comp7 are non-prefixed. Why? I can see the
> relationship that you seem to
> need both mydate and mycode to be prefixed, but if
> so, why wouldn't mycode
> be required as the second column, as mydate is
> required as the first in
> order to be prefixed?
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
                

---

Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 28 2005 - 04:13:30 CST