Re: MDC vs. cluster index: which to use?

>
MDC on type_id? So cluster index is not a good idea?

Henry J. wrote:
>>
MDC *guarantees* clustering, whereas a table with a clustering index
will eventually require maintenance (a.k.a. reorg) to maintain the
cluster ratio.
>
That's not to say that a clustering index isn't still valuable
(especially for high cardinality columns that aren't a reasonable
candidate as an MDC dimension).

For my case, the table is purged and re-populated every day. *And the
other column, product_id, has high cardinality.
Perhaps a cluster index makes more sense? *I guess I'd choose type_id
if I have to pick one column for MDC. *Then it won't be as helpful as
a cluster index on (type_id, product_id)?

On Oct 21, 7:18*am, "Henry J." <tank209...@yahoo.comwrote:
>>
MDC still might be faster - probably faster at loading and equivalent
for searches based on type_id. *For searches on type_id and
product_id, it could outperform the cluster index, if you also create
an index on product_id - and the combination (MDC + index on
product_id) will definitely outperform a cluster index on type_id,
product_id for product_id searches.
>
You'll want to verify these claims - but since you rebuild the table
every day, you are in a perfect position to try these various options
out. *Pick a new one each day until you've found the right
combination.
>
-Chris

On Oct 21, 1:24 am, Ian <ianb...@mobileaudio.comwrote:>
For my case, the table is purged and re-populated every day. And the
other column, product_id, has high cardinality.
Perhaps a cluster index makes more sense? I guess I'd choose type_id
if I have to pick one column for MDC. Then it won't be as helpful as
a cluster index on (type_id, product_id)?

Henry J. wrote:>>>>
By purging it every day, you're going to make it nearly impossible
for a clustering index to be useful. *Unless your data comes in
already sorted by type_id,product_id the cluster ratio is probably
going to be very low, regardless of what you set pctfree to.
>
Furthermore, will a clustering index really help with queries that
aggregating on the clustering key? *Maybe, but maybe not -- that
depends on whether the query is doing an index scan / fetch. *If
it's just doing a tablescan, you'll still need to do a sort.- Hide quotedtext -
>
- Show quoted text -

On Oct 21, 11:19*am, ChrisC <cunningham...@gmail.comwrote:
>
>
>
>
>>>>>>
Thanks Chris.
>
The primary key of the table is (type_id, product_id). *Now if we add
a MDC on type_id, will the MDC be actually used if we query like the
following, or the primary key will be used only? *I guess even if only
the primary key is used, the I/O will still be benefited.
>
* * * * * * select client.client_id, sum( prod.quantity * prod.price *
client.ordersize )
* * * * * * *from prod, client
* * * * * * *where prod.type_id = 4 and prod.product_id =
client.product_id
* * * * * * *group by client.client_id
>
Also, would the following query benefit from both the MDC and the
primary key?
>
* * *select *
* * * * * *from prod
* * * * * * *where prod.type_id = 4 and prod.product_id = *101
>
Or, instead of the primary key, just use an index on (product_id)
would cover both of the above queries?
>
I'll certainly test to find out. *But I'd like to gain better
understanding about indexing. *Also, the turnaround of making such
changes are not as fast as I want and I hope to get to the best
solution a little faster. *Thanks.- Hide quoted text -
>
- Show quoted text -

On Oct 21, 7:18*am, "HenryJ." <tank209...@yahoo.comwrote:
>>
MDCstill might be faster - probably faster at loading and equivalent
for searches based on type_id. *For searches on type_id and
product_id, it could outperform the cluster index, if you also create
an index on product_id - and the combination (MDC+ index on
product_id) will definitely outperform a cluster index on type_id,
product_id for product_id searches.
>
You'll want to verify these claims - but since you rebuild the table
every day, you are in a perfect position to try these various options
out. *Pick a new one each day until you've found the right
combination.
>
-Chris

On Oct 21, 7:18*am, "HenryJ." <tank209...@yahoo.comwrote:
>>
MDCstill might be faster - probably faster at loading and equivalent
for searches based on type_id. *For searches on type_id and
product_id, it could outperform the cluster index, if you also create
an index on product_id - and the combination (MDC+ index on
product_id) will definitely outperform a cluster index on type_id,
product_id for product_id searches.
>
You'll want to verify these claims - but since you rebuild the table
every day, you are in a perfect position to try these various options
out. *Pick a new one each day until you've found the right
combination.
>
-Chris

On Oct 21, 11:19*am, ChrisC <cunningham...@gmail.comwrote:
>
>
>
>
>>>>>>
I have the primary key changed to (type_id, product_id), and added a
MDC on type_id. *Now the following query:
>
* * * *select client.client_id, sum( prod.quantity * prod.price *
client.ordersize )
* * * * * * *from prod, client
* * * * * * *where prod.type_id = 4 and prod.product_id =
client.product_id
* * * * * * *group by client.client_id
>
will utilize the primary key. *However, if I use a host variable for
type_id (i.e., type_id = ?), it will only pick up the MDC on type_id,
which I think is not optimal.
>
My questions are:
>
1) should I add an index on (product_id) which is what's really needed
rather than the primary key on (type_id, product_id). *Note that the
cardinality of type_id is pretty small, around 6.
>
2) should I drop the host variable on type_id? *Note that each type_id
has the same number of product_id's. *Perhaps this is not a good case
for host variable. *BTW, this SQL is embedded in a Java (as static SQL
I think?).
>
Thanks!- Hide quoted text -
>
- Show quoted text -

On Oct 29, 12:56*am, "HenryJ." <tank209...@yahoo.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>
Hi,
there is a shell script know extent utilisation.sh which can be
downloaded from IBM website.it will answers all your queries regarding
which will be a better column and how much space will you save and how
will it improve the performance.- Hide quoted text -
>
- Show quoted text -