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
 

 

Effective use of DB2 Query.

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

New User


Joined: 25 Mar 2010
Posts: 1
Location: Mumbai

PostPosted: Thu Mar 25, 2010 2:59 pm    Post subject: Effective use of DB2 Query.
Reply with quote

Hi,

In One of my DB2 table has two index,

Let's suppose,
1). System-id (Only two different values 'T' and 'C' for this column)
2). Cycle-no (25 Different values available for this column which are '01' ,'02'......,'25')

Note: Guess we have equal probability of getting System-id 'T' or 'C', same as equal probability for getting '01' or '02' or .... '25'
Means, If table is having 10 Lacs records then System-id = 'T' records will be 5 lacs(10 Lacs/2 different values) and System-id = 'C' records will be 5 lacs .
Same as for each cycle-no, the number of records will be around 40,000 (10 Lacs / 25 different values).

Let's suppose, we want to extract rows for which System-id = 'C' and Cycle-no = '10'

For above conditions, which where clause will be Time effective and cost effective.
1). Select * from table v1 where System-id = 'C' and Cycle-no = '10'
2). Select * from table v1 where Cycle-no = '10' and System-id = 'C' (Means sequence changed from 1st)


Also,

In above query ,will it use both the indexes or will it use the first index specified in where clause.

Kindly advice on this for the optimization of query.

Please let me know in case of more information.

Thanks,
Parth Patel
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 25, 2010 3:14 pm    Post subject:
Reply with quote

Parth,

I think for first query it will use first index and for second one it will choose second one.

Why dont you just run the query using Explain & check the result.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Mar 25, 2010 4:08 pm    Post subject:
Reply with quote

my guesses :
If he only uses one index : it will be the one with the highest filtering factor, ie. Cycle-no
It also depends on the Clustering factor of each index.
likely DB2 will do a multi-ix AND
but even a Tablescan is a possibility.

An index with two different values (fullkeycard) for 1.000.000 rows is just stupid.

why not expand the indexes with the other column?
one index on (cycle-no, system-id) and one on (system-id,cycleno)
I guess you need both indexes for R.I. or other queries.
Back to top
View user's profile Send private message
Gopal Tripathi

New User


Joined: 16 Sep 2009
Posts: 15
Location: Hyd,Ind

PostPosted: Thu Mar 25, 2010 5:39 pm    Post subject:
Reply with quote

Did you consider any one of your indexes to be clustered, i think it will be preferred over if db2 does a multi-ix scan.

Guy C could u please elaborate a bit-

"An index with two different values (fullkeycard) for 1,000,000 rows is just stupid."
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Fri Mar 26, 2010 1:56 pm    Post subject:
Reply with quote

"An index with two different values (fullkeycard) for 1,000,000 rows is just stupid."

The index would contain 2 values, each followed by a huge RID-list.

with 2 distinct values you have to read 50% of rows. Unless it is highly clustered, you will need to read 90% of the pages.
If you would do it via an index you would need to read 50% of the index leaf-pages + 90% of the data pages.

you could only use that index for three purposes :
- R.I.
- clustering
- select count(*) from tab where code = ?
select code,count(*) from tab group by code

With some investigation, you could expand that index with one or more other data columns so that some of your queries are index-only, or more matching, and still keep the functionality of R.I ,clustering,select count(*) .
It would only increase the size of the index a bit.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2432
Location: Netherlands, Amstelveen

PostPosted: Fri Mar 26, 2010 2:02 pm    Post subject:
Reply with quote

Redbook : DB2 9 for z/OS Performance Topics has a lot of info about this.
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