On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?
Please advise. Thanks.
nat
2  1393 
natG wrote:
On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?
Yes you have. reverse scans means the index can be scanned in reverse
ORDER of the index entries, not in reverse order of the index column.
You can search for a name in a phone book by starting at the end. But
that phone book doesn't provide you a way to look up by firstname first :-)
If you only want to afford one index you can consider placing the middle
column first in the index. That way the index can at least be used
partially for the second query type.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference http://www.ibm.com/software/data/ond...ness/conf2006/
On Tue, 22 Aug 2006 21:56:39 -0400, Serge Rielau wrote:
natG wrote:
>On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?
Yes you have. reverse scans means the index can be scanned in reverse
ORDER of the index entries, not in reverse order of the index column.
You can search for a name in a phone book by starting at the end. But
that phone book doesn't provide you a way to look up by firstname first :-)
If you only want to afford one index you can consider placing the middle
column first in the index. That way the index can at least be used
partially for the second query type.
Thanks for the correction (and the explain).
nat This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Tim |
last post by:
Hi all,
Here is a brief description of a problem I encountered, and how I
found a work around after 3 long days.
I have a VB6 app that uses ADO and ODBC to get communicate with SQL
server 2000 (sp3, running in win2003).
Everything was running great for a few weeks, but one day an update
statement that used to work just stopped working. It was a simple
update of 1 field in a table (about 30 columns, about 20k records).
|
by: lawrence |
last post by:
Under the domain publicpen.com I've several dozen sites in
subdiretories, such as www.publicpen.com/honenbeger. I've no trouble
with any of these sites. But under one, which I put in yesterday,
www.publicpen.com/staengl, when I point my browser there, the browser,
instead of showing me the index.htm pages like it should, it instead
tries to download it as an object. Why?
Below are the contents of index.htm. Does anyone see anythging...
|
by: WalterR |
last post by:
This is my first time here, so there may be earlier relevant threads of
which I am unaware. Though my experience with DB2 is not extensive,
such as it is was under OS/390 or equ. My main experience is IMS DB,
which leads to my question.
In IMS, there is an HDAM access method which can find a record without
using an index as such. At initial database load, it first formats the
entire space allocation into blocks of the given size. ...
|
by: Simon Harvey |
last post by:
Hi there everyone,
I have a fairly open ended but simple question that I could really use some
assistance with. When I first bought visual studio (under the student
license) i thought it was great. Especially the design tool for ASP.net. It
was like making websites as easily as VB or C# - Drag and drop - a total
life saver.
But, now that I want to make some more complicated pages I'm finding it
really hard to work in VS. The other...
|
by: Zygo Blaxell |
last post by:
I have a table with a few million rows of temperature data keyed
by timestamp. I want to group these rows by timestamp intervals
(e.g. every 32 seconds), compute aggregate functions on the columns,
and ultimately feed the result into a graph-drawing web thingy.
I'm trying a few different ways to get what seems to be the same data,
and seeing some odd behavior from the query planner.
The table looks like this:
| | |
by: serge |
last post by:
I am reading "SQL Server Query Performance Tuning Distilled",
on page 104 it talks about one of the index design recommendations
which is to choose the column that has very high selectivity of values
instead of a column that has very few selectivity of values.
My question is if I have currently indexes on my tables that have
1, 2, 3, 4, ... values only on thousands of rows, are these nonclustered
indexes pretty much useless indexes that...
|
by: sam_cit |
last post by:
Hi Everyone,
I have the following structure in my program
struct sample
{
char *string;
int string_len;
};
|
by: C.L. |
last post by:
I was looking for a function or method that would return the index to the first
matching element in a list. Coming from a C++ STL background, I thought it might
be called "find". My first stop was the Sequence Types page of the Library
Reference (http://docs.python.org/lib/typesseq.html); it wasn't there. A search
of the Library Reference's index seemed to confirm that the function did not
exist. A little later I realized it might be called...
|
by: Will Honea |
last post by:
I've hit an interesting trap trying to migrate data off an OS/2 server
running version 7.2 (fp14) over to 8.2 on Linux. Seems that one table has
a column defined in the DDL as "BIGINT NOT NULL GENERATED ALWAYS AS
IDENTITY (START WITH +1, INCREMENT BY +2, NO CACHE)". Any rows in that
table will kill db2move. EXPORT to IXF succeeds, but attempting to
load/import the file refuses to load any rows. The really scary part is
that this table...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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
| |
