By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,610 Members | 2,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,610 IT Pros & Developers. It's quick & easy.

about optimize low cardinality data query

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/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.
Nov 12 '05 #2

P: n/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?


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

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #3

P: n/a
"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...
Nov 12 '05 #4

P: n/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.
Nov 12 '05 #5

P: n/a
summary tables could also help depending on the case.

PM
Nov 12 '05 #6

P: n/a
"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...
Nov 12 '05 #7

P: n/a
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! =-----
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.