non-prefixed indexes on composite partitioned tables
- From: "Bill Coulam" <bill.coulam@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Jan 2005 18:26:28 -0600
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
