473,574 Members | 2,272 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Index not used only on a particular environment

Hello,
I have a table which has around 3 billion records on an env and a
simple query against it goes for a tablescan eventhough an index has
been defined and this happens only on an env.

Details are as below. Please advice
Query Considered :
select A.OPTN_CD from VIN_VEH_OPTNS as A WHERE A.DWH_UPD_TIMST M <
(SELECT MAX(DWH_ETL_ST_ TIMSTM) FROM DWH_CONTROL where DWH_SEQ_NM
='SSeq' and DWH_ETL_STATUS= 'S')

Indexes: (which is same on both env)
No Index on DWH_CONTROL table
Index on VIN_VEH_OPTNS
DWH_UPD_TIMSTM ASC

Env1 : where Index is used
Logically partitioned db having 4 logical partitions
VIN_VEH_OPTNS table has around 100 million records (this is a
partitioned table spread across 3 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 4th partition)
Access Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/--------/ \-------\
GRPBY FETCH
( 4) (----)
| / \
BTQ RIDSCN Table:
( 5) ( 9) CARD
| | VIN_VEH_OPTNS
GRPBY SORT
( 6) ( 10)
| |
TBSCAN IXSCAN
( 7) ( 11)
| / \
Table: Index: Table:
CARD CARD CARD
DWH_CONTROL XIF4VIN_VEHOPTN S VIN_VEH_OPTNS

Env2: where Table scan is performed
Logically parititioned db having 8 logical parittions
VIN_VEH_OPTNS table has around 3 billion records(this is a partitioned
table spread across 7 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 8th partition)

Access Plan:
RETURN
( 1)
|
BTQ
( 2)
|
FETCH
(----)
/ \
RIDSCN Table:
( 4) CARD
| VIN_VEH_OPTNS
SORT
( 5)
|
IXSCAN
( 6)
/ \
Index: Table:
CARD CARD
XIF4VIN_VEHOPTN S VIN_VEH_OPTNS
Any suggestion is greatly appreciated.

Thanks, Victor

Sep 20 '06 #1
5 1709
I've posted a wrong access plan for the Env2(where tablescan is
happening). Here is the correct one

Optimizer Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/ \
BTQ TBSCAN
( 4) ( 3)
| |
GRPBY Table:
( 5) CARD
| VIN_VEH_OPTNS
TBSCAN
( 6)
|
Table:
CARD
DWH_CONTROL
Victor wrote:
Hello,
I have a table which has around 3 billion records on an env and a
simple query against it goes for a tablescan eventhough an index has
been defined and this happens only on an env.

Details are as below. Please advice
Query Considered :
select A.OPTN_CD from VIN_VEH_OPTNS as A WHERE A.DWH_UPD_TIMST M <
(SELECT MAX(DWH_ETL_ST_ TIMSTM) FROM DWH_CONTROL where DWH_SEQ_NM
='SSeq' and DWH_ETL_STATUS= 'S')

Indexes: (which is same on both env)
No Index on DWH_CONTROL table
Index on VIN_VEH_OPTNS
DWH_UPD_TIMSTM ASC

Env1 : where Index is used
Logically partitioned db having 4 logical partitions
VIN_VEH_OPTNS table has around 100 million records (this is a
partitioned table spread across 3 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 4th partition)
Access Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/--------/ \-------\
GRPBY FETCH
( 4) (----)
| / \
BTQ RIDSCN Table:
( 5) ( 9) CARD
| | VIN_VEH_OPTNS
GRPBY SORT
( 6) ( 10)
| |
TBSCAN IXSCAN
( 7) ( 11)
| / \
Table: Index: Table:
CARD CARD CARD
DWH_CONTROL XIF4VIN_VEHOPTN S VIN_VEH_OPTNS

Env2: where Table scan is performed
Logically parititioned db having 8 logical parittions
VIN_VEH_OPTNS table has around 3 billion records(this is a partitioned
table spread across 7 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 8th partition)

Access Plan:
RETURN
( 1)
|
BTQ
( 2)
|
FETCH
(----)
/ \
RIDSCN Table:
( 4) CARD
| VIN_VEH_OPTNS
SORT
( 5)
|
IXSCAN
( 6)
/ \
Index: Table:
CARD CARD
XIF4VIN_VEHOPTN S VIN_VEH_OPTNS
Any suggestion is greatly appreciated.

Thanks, Victor
Sep 20 '06 #2
Why isn't the small table replicated?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #3
Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.


Serge Rielau wrote:
Why isn't the small table replicated?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #4
Victor wrote:
Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.
The benefit of a replicated table is that it can be indexed on each node.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #5
ok, Thanks. I would implement a replicated MQT and recheck.

Serge Rielau wrote:
Victor wrote:
Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.
The benefit of a replicated table is that it can be indexed on each node.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 21 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
5395
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why...
8
5223
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is developing a web-based application, one part of which involves allowing the user the ability to page through transaction "history" information. The...
9
3166
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...
29
5430
by: shmartonak | last post by:
For maximum portability what should the type of an array index be? Can any integer type be used safely? Or should I only use an unsigned type? Or what? If I'm using pointers to access array elements as *(mptr+k) where I've declared MYTYPE *mptr; what should be the type of 'k'? Should it be ptrdiff_t?
6
1566
by: Ian Ribas | last post by:
Hello, This is probably a common problem, but I couldn't really find a direct answer in the archives (or maybe just couldn't find one that satisfied me ;-). I created an index specifically to help a query and the optimizer does not use it. It prefers an older index that has one less column, but that yields much poorer performance. The...
2
1875
by: Martin v. Löwis | last post by:
I've been working on PEP 353 for some time now. Please comment, in particular if you are using 64-bit systems. Regards, Martin PEP: 353 Title: Using ssize_t as the index type Version: $Revision: 42333 $
4
2819
by: Massimo | last post by:
I have a very very strange situation with a particular application and sql server 2005 enterprise. This application combines numerical data from multiple tables. User can make query over this kind of tables, can build queries with "group by" "order by" and "join", "sum, count(*) ecc. on many many columns. I cannot know the query that...
122
5453
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...
0
1652
by: muzu1232004 | last post by:
If I have a table t1 with a column a as follows A 1 1 1 1 1 1 1
0
7803
marktang
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...
0
7720
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...
0
8230
jinu1996
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...
1
7810
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...
0
6451
agi2029
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...
0
5299
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...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2240
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
0
1056
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.