Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Multiply indexes for one query.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Larret Miyuki

New User


Joined: 07 Apr 2010
Posts: 7
Location: Poland

PostPosted: Thu Jul 01, 2010 1:44 pm    Post subject: Multiply indexes for one query.
Reply with quote

Does DB2 for z/OS use multiply indexes for query, or only one that is probably most efficient. If DB2 uses only one index, is there a way to use multiply?
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 01, 2010 4:21 pm    Post subject:
Reply with quote

Hi Larret, Welcome to IBMMAINFRAMES.

Answer is "it depends" on the optimizer and the available statistics.

In General,
Multiple Column Indexes are efficient for equality. And multiple Indexes are more efficient on comparisons.

Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jul 01, 2010 4:36 pm    Post subject:
Reply with quote

The answer to this single line triple-question can fill a redbook.
the answers are : sometimes sometimes sometimes.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 01, 2010 5:48 pm    Post subject:
Reply with quote

The main purposes of indexes are:

    To improve performance. Access to data is often faster with an index than without.
    To ensure that a row is unique. For example, a unique index on the employee table ensures that no two employees have the same employee number.
    To cluster the data.
    To determine which partition the data goes into.
    To provide index-only access to data.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 01, 2010 5:57 pm    Post subject:
Reply with quote

I'll start with,probably, what you want to listen - in a way, adding indexes to a table slows down insert and updates to that table, why? Because, each of these changes have to be applied not only to the actual data but the index pages also. And it will surely (should be acceptable, right?) eats up more disk space. If your database is small and your tables have few indexes on them, then adding a new index, which might not be required at all though, will probably not be noticed on modern fast machines. If your data is huge, very huge, your table is heavily indexed or you are runing on old machnines (actuators are not that good) then sometimes you have to make intelligent choices about which indexes are the most important to the performance of your program. The indexes for seldom-used queries sometimes have to be removed for the sake of more important indexes and the overall performance of updates to the table.

Because action queries on a table are affected by the number of indexes that table has, a table that is updated constantly by multiple users may not be able to have as many indexes as one that stays very static - so yes, as others have siad - yep, it depends.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us