|
View previous topic :: View next topic
|
| Author |
Message |
Poha Eater
New User
Joined: 31 Aug 2016
Posts: 74
Location: India
|
|
|
|
Hi Everyone,
I have confusion regarding the Index processing. After reading lot of material online i am still having the below queries.
1. Is it possible in case of Unique Index that data which is being inserted in the table is not being updated in Ascending or descending sequence ? if Yes, then how can we REORG the data in case of Unique Index because i have read that we can REORG the data only in case of Clustered Index.
2. In case of Clustered Index, What will happen if we dont provide the Index column name in REORG utility ? Will it consider any default index ?
3. Is it possible that DB2 will use the Index in a query when the Index column is not given in WHERE clause of the query. In other way, can DB2 Index will automatically pick up the Index in every sql query written in COBOL-DB2 program ?
4. When Unique Index can provide us the exact record then why do we need Non-Unique Index ? How Non-Unique Index is better than Unique Index ? If possible please provide an example.
I am a beginner in DB2 and used DB2 in my previous projects where i was working as a Mainframe Developer. So never used REORG utility that is why i am having these doubts.
Please let me know if anyone can help me to understand these concepts. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
1. NO and it depends how ASC DESC used while creating the INDEX. If the CLUSTER option is not specified then it treats that particular INDEX as implicit Cluster INDEX. More of a such questions should be asked to DBAs since that's why they are for.
2. If you are REORGanizing on TABLE SPACE name , it should not matter.
3.No. if none of the INDEX columns is used then it does Table spaces scans, if you are using few columns from the Index then it will use the INDEX but matching columns are less. So it is necessary to maintain DB2 Column cardinality.
4.We need Non-Unique Index when situations comes like say, you want to create a INDEX on FIRST and LAST NAME then it cannot be UNIQUE. Where you read that Non- Unique Index is better over Unique? |
|
| Back to top |
|
|
Poha Eater
New User
Joined: 31 Aug 2016
Posts: 74
Location: India
|
|
|
|
Hi Rohit,
Thanks for your reply. Your reply answered all my queries. Now just want to confirm regarding Point No. 3.
Suppose i have the below queries for EMPLOYEE table where Index is EmpId:
Case 1 : SELECT EmpId, EmpName, Empdept FROM Employee WHERE EmpId = '123ABC';
Case 2 : SELECT EmpId, EmpName, Empdept FROM Employee;
So my understanding from your answer is that Index will be considered in Case 1 because in Case 1 we are using Index (EmpId) in WHERE clause whereas in Case 2 the Index will not be used.
Please confirm.
And i dont get what do you meant by this
| Quote: |
| if you are using few columns from the Index then it will use the INDEX but matching columns are less. So it is necessary to maintain DB2 Column cardinality. |
Here when you say 'if you are using few columns' then do you mean in Select or in WHERE clause. Please explain with example if possible.
Really appreciate your patience for taking up my queries. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
In your case2 there are no predicates in where so no question about indexing.
Moreover, like I said if you have created a index on first and last name but in your where you only used first name then the index is used but the matching columns will be 1 instead of 2 ( which is not good either ) in your explain. |
|
| Back to top |
|
|
Poha Eater
New User
Joined: 31 Aug 2016
Posts: 74
Location: India
|
|
|
|
Got it now. Thanks again Rohit for all the explanation  |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| You’re Welcome!! |
|
| Back to top |
|
|
|
|
