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
 

 

B Tree Structure for Composite Index

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

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sat Dec 05, 2009 1:42 pm    Post subject: B Tree Structure for Composite Index
Reply with quote

Hi All,

Recently I was reading a article on B Tree structure for a index. The article was dealing with a case where index is defined on one column. I want to know what will the B Tree structure look like, if we have say index on 4 cloumn and in which case the DB2 will have matching index scan or non matching.

Any pointers to article on composite index?
Back to top
View user's profile Send private message

mtaylor

Active User


Joined: 20 Feb 2009
Posts: 108
Location: Kansas City

PostPosted: Sat Dec 05, 2009 7:20 pm    Post subject:
Reply with quote

It's not hard to imagine that in each non leaf node, where there was a single value indicating the max value in the associated leaf/node, there are four values for each of the four columns and a comparison function that compares each of the four values on some lexographical ordering based on ascending/desending.

But google doesn't readily return any useful links and I've not taken a database implementation course so that's just speculation.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


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

PostPosted: Sat Dec 05, 2009 7:37 pm    Post subject:
Reply with quote

Google on "composite index" and btree will give some information
to think about.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Dec 05, 2009 10:56 pm    Post subject:
Reply with quote

Hello,

Possibly an oversimplification, but you might consider a composite index as an index_value rather than multiple columns values.

We (people rather than the machines) are aided by seeing the "pieces". The system doesn't need them really.

The values used in the btree are the index_value. I believe if you look at the article again substituting the index_value for the "one column" you will have what you are looking for.

And i may have misunderstood the question. . .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Dec 07, 2009 2:08 pm    Post subject:
Reply with quote

Saurabh,

You might want to take a look at this .


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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 09, 2009 9:32 pm    Post subject:
Reply with quote

As dick said : it's just a concatenation of 4 columns. an index on a char(16) will look exactly the same as one on 4 char(4) columns.

In DB2 LUW there is a concept of INCLUDE columns on unique indexes.
Suppose you have a unique index on 3 columns, but like to add a fourth to make your accesspath index-only.
On z/os you can not use that index without sacrificing your unique constraint on the first 3 columns. On LUW you can.

For these kind of indexes it is true that only the first 3 columns are on the non-leaf pages of the index, and on the leaf-pages all 4 cols are present. thus enforcing uniqueness and keeping smaller B-tree.
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Copybook structure in Load module Vai123 COBOL Programming 7 Fri Sep 16, 2016 8:29 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts VSAM Alternate Index in CICS jacobdng CICS 2 Fri May 06, 2016 1:41 pm


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