Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us