Hi,
Consider the following query
select *
from t1
where c1 = ?
and c2 = ?
table t1 has a high number of rows where c1 is null, but rows where c1
is not null, the filter factor is relatively low. The table has
distributed statistics collected on table and key columns.
Now, if I have an index i1 on t1(c1), access path produced by db2exfmt
shows the correct filter factor for c1 = ? predicate, presumably
because optimizer knows the fact that predicate c1 = ? cannot be
satisfied by rows which have null values for c1.
However, if I have an index i2 on t1(c1,c2), the explain shows a very
high filter factor for the same predicate. I can tell that it was
derived from fullkeycard of index i2, which includes the null values.
Optimizer completely ignores the fact that predicate c1 = ? cannot
select null values.
The actual query is a little bit complex, involving join of table t1
with 5 other tables, but the incorrect filter factor calculations
causes optimizer to select wrong table as the outer table in case
index i2 exists.
Has any one encountered similar situation before? Any suggestions?
The DB2 manual says runstats command doesn't support collecting
distributed statistics on a column group, but DB2 catalog has
syscat.colgroup % tables, so I am confused, is it the runstats command
limitation or the optimizer limitation? If it is just runstats
limitation, any external ways to populate distributed column group
statistics?
TIA
P. Adhia 0 1661 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Felix |
last post by:
Dear Sql Server experts:
First off, I am no sql server expert :)
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I'd gleaned from
reading newsgroups. Nothing worked....
|
by: Jerry Brenner |
last post by:
Our users have potentially dirty legacy data that they need to get
into our application. We provide a set of staging tables, which map
to our source tables, that the users do their ETL into. Every row in
the source tables has a generated integer id. Every row in both the
source and staging tables has a unique publicid (varchar(22)). All
foreign key references in the staging tables are through publicids.
(The foreign key reference could...
|
by: Jesper Jensen |
last post by:
Hello group.
I have an issue, which has bothered me for a while now:
I'm wondering why the column statistics, which SQL Server wants me to
create, if I turn off auto-created statistics, are so important to the
optimizer?
Example: from Northwind (with auto create stats off), I do the following:
|
by: Sky Fly |
last post by:
Hello all,
I've written a stored procedure which runs OK for the
first few thousand records it processes, then around
about the 10,000th record it suffers a sudden and dramatic
drop in performance (from about 40 records per second to
about 1 per second).
I've found that when this happens, if I run an UPDATE STATISTICS
query on the affected tables, performance picks up again,
|
by: tkpmep |
last post by:
I use Python to generate a huge amount of data in a .csv file which I
then process using Excel. In particular, I use Excel's solver to solve
a number of non-linear equation, and then regress the results of
hundreds of calls to Solver against a set of known values, enabling me
to calibrate my model. This is a pain: i'd much rather perform all the
computations in Python and improve on Excels' regression as well.
Questions:
1. Is there a...
| |
by: Peter Arrenbrecht |
last post by:
Hi all
We ran into a very annoying optimizer problem recently. We had added a
new key and self-join relation to a large production table. That key
will be filled very rarely and having just being added was practically
never filled in when the first user tried to delete a row from the table.
Now, the optimizer tried to enforce RI on the new relation. But instead
of using the index generated by the relation, it used a table scan!
|
by: serge |
last post by:
I am running a query in SQL 2000 SP4, Windows 2000 Server
that is not being shared with any other users or any sql
connections users. The db involves a lot of tables,
JOINs, LEFT JOINs, UNIONS etc... Ok it's not a pretty
code and my job is to make it better.
But for now one thing I would like to understand with your
help is why the same SP on the same server and everything
the same without me changing anything at all in terms of
SQL...
|
by: romixnews |
last post by:
Hi,
I'm facing the problem of analyzing a memory allocation dynamic and
object creation dynamics of a very big C++ application with a goal of
optimizing its performance and eventually also identifying memory
leaks. The application in question is the Mozilla Web Browser. I also
have had similar tasks before in the compiler construction area. And
it is easy to come up with many more examples, where such kind of
statistics can be very...
|
by: Otto Carl Marte |
last post by:
>From the IBM db2 docs:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0005308.htm
it says that volatile tables (ALTER TABLE mytable VOLATILE
CARDINALITY) do not use statistics. I would just like to clarify this
statement. Does this mean that no statistics at all are used when
determining the execution plan? or does this mean that statistics
applying to cardinality are not used when...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |