about optimize low cardinality data query

[janet]

HI,

We had some data which had low cardinality (86) , the count for that
table is 9 million rows.

How to optimize query which involve joining this table on that column?

I'm not sure if dynamic bitmap Index Anding would help ....but how to
let DB2 use it?

From my explain, it seems scan the whole table, and did not see the
IXAND in the explain table...

any suggestion is very welcomed...

Thanks very much.

[Mark A]

"janet" <es**********@yahoo.com> wrote in message
news:db**************************@posting.google.c om...

HI,

We had some data which had low cardinality (86) , the count for that
table is 9 million rows.

How to optimize query which involve joining this table on that column?

I'm not sure if dynamic bitmap Index Anding would help ....but how to
let DB2 use it?

From my explain, it seems scan the whole table, and did not see the
IXAND in the explain table...

any suggestion is very welcomed...

Thanks very much.

DB2 will only use an index if it results in accesses few pages (combination
of index pages and data pages). Sometimes using an index is simply not
faster.

However if the 86 different values are skewed (some of values have far fewer
than the presumed average of 100,000 rows for each unique occurrence of the
predicate), then you should gather more detailed statistics to let DB2 know
that. You can use the following runstat options:

DETAILED INDEXES ALL WITH DISTRIBUTION
ON KEY COLUMNS

Check the Command Reference for details.

If the IXAND index is defined as the clustering index (and a reorg was
performed, then runstats), then that might also help.

[Paul Vernon]

"janet" <es**********@yahoo.com> wrote in message
news:db**************************@posting.google.c om...

HI,

We had some data which had low cardinality (86) , the count for that
table is 9 million rows.

How to optimize query which involve joining this table on that column?

Sounds like a job for MDC.
Add ORGANIZE BY ( low_card_column ) to you table defn.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[janet]

"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<c9**********@gazette.almaden.ibm.com>...

"janet" <es**********@yahoo.com> wrote in message
news:db**************************@posting.google.c om...

HI,

We had some data which had low cardinality (86) , the count for that
table is 9 million rows.

How to optimize query which involve joining this table on that column?

Sounds like a job for MDC.
Add ORGANIZE BY ( low_card_column ) to you table defn.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

HI, Thanks for your suggestions... I think I would try cluster index
(MDC) to see if it helps...

and I would let you know...

[Mark A]

> > > We had some data which had low cardinality (86) , the count for that

table is 9 million rows.

How to optimize query which involve joining this table on that column?

Sounds like a job for MDC.
Add ORGANIZE BY ( low_card_column ) to you table defn.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

HI, Thanks for your suggestions... I think I would try cluster index
(MDC) to see if it helps...

and I would let you know...

I would make sure you understand this feature before you try it. May not be
the best solution in this application.

[PM \(pm3iinc-nospam\) CGO]

summary tables could also help depending on the case.

PM

[janet]

"PM \(pm3iinc-nospam\) CGO" <PM (pm3iinc-nospam)@cgocable.ca> wrote in message news:<YG*****************@charlie.risq.qc.ca>...

summary tables could also help depending on the case.

PM

hi

I tried created cluster index based on the predicate which had highest
cardinality. but did not help...

[Ian]

janet wrote:

"PM \(pm3iinc-nospam\) CGO" <PM (pm3iinc-nospam)@cgocable.ca> wrote in message news:<YG*****************@charlie.risq.qc.ca>...

summary tables could also help depending on the case.

PM

hi

I tried created cluster index based on the predicate which had highest
cardinality. but did not help...

You do realize that when you create a clustering index, you have to reorg
the table afterwards, right?

Also, choosing the column with the highest cardinality for a clustering
index is probably not be the best choice. You want the clustering index
to be on a column that has many rows you need to access in a query. For
example, in a sales_order table, you would want to cluster on
customer_number, not invoice_number, so that all invoices for a
particular customer are on the same physical page (or close by).


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----