473,378 Members | 1,680 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

about optimize low cardinality data query

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
7 5931
"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
"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
"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
> > > 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
summary tables could also help depending on the case.

PM
Nov 12 '05 #6
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: 'bonehead | last post by:
Greetings, I'd like to figure out some syntax for retrieving the data from a table when I don't know all the of field names. What I do know are, the name of the table, the names of the primary...
5
by: giraph | last post by:
Okay, well what my query looks like is: SELECT id, title FROM myTable LIMIT 0, 500 This query is taking a long time and changes in time each time i run it, from 0-10 seconds. And I don't...
2
by: kshirsagar007 | last post by:
friends, I would like to optimize the following query....as its taking 2 minutes to get the records. select a.CODE "Code", ud.Name "Name", a.SchemeId "SchemeID" from (MASTER sh,...
1
by: kmugunda | last post by:
Hi below is a query where certain set of data is fetched from a table based on certain condition, but the execution takes lot of time , is there a way we can optimize the code below SELECT...
11
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from...
5
by: pedalpete | last post by:
I'm fairly new to sql, and am having a problem with a query taking forever to run. it's 8 seconds to run each query. I think I have my indexes correct, but I can't seem to find the output which...
2
by: uwcssa | last post by:
is there a way to see the actual (not just estimated) cost and cardinality at each plan operator? SQL Server and Oracle has such feature but I failed to find a way in DB2 yet (up to V9.5)
0
by: cem babaeren | last post by:
i have two problems: 1) when i execute this query in sql 2005; i get more than 10.000 results. However i get only 1087 results in vba excel. why would it be so? 2) i have three collections...
14
by: Hembd | last post by:
Hello, I'm new to creating databases and coding in general. Through research I have created a DB that works. However, I have filled it with test data and find that a few queries which are used to...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.