| View previous topic :: View next topic |
| Author |
Message |
pzmohanty
Beginner
Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
|
 Posted: Thu Aug 19, 2004 12:10 am Post subject: does querying on primary key fetch records sorted on key |
 |
|
hi all,
i have following 2 queries :
1) Are primary key fields are INDEXED by default or not ?
2) if i am Querying records from a table , then can i expect the records to be sorted on primary kay by default , without even myself giving ORDER BY clause in Query.
Thanx in advance.
pzmohanty |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Thu Aug 19, 2004 6:12 am Post subject: |
|
|
Answers are:
1) In DB2 you don't have anything like a default index. If no appropriate index for a primary key definition is defined, the table is unusable.
2) There is no default sorting of a query result. It depends on the access the DB2 optimizer chooses before accessing the data. If an index access is chosen, you have a sorted result list (by accident). If a tablespace scan access is chosen and your data is freshly loaded from a sorted input dataset, again you have a sort order by accident.
If you need ordering, issue an ORDER BY clause. Whether this results in an extra sort step or not is chosen by the optimizer.
regards
Christian |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 19, 2004 2:12 pm Post subject: |
|
|
pzmohanty,
| Quote: |
Are primary key fields are INDEXED by default or not ?
|
Answer : When creating a table, when you define a primary key, you need to create the INDEX. The table is marked as unavailable until its primary index is explicitly created unless the CREATE TABLE statement is processed by the schema processor. In that case, DB2 implicitly creates an INDEX.
When the PRIMARY KEY or UNIQUE clause is used in the CREATE TABLE statement and the CREATE TABLE statement is processed by the schema processor, DB2 implicitly creates the unique indexes used to enforce the uniqueness of the primary or unique keys. Each index is
created as if the following CREATE INDEX statement were issued:
| Code: |
CREATE UNIQUE INDEX xxx ON table-name (column1,...)
|
| Quote: |
2) if i am Querying records from a table , then can i expect the records to be sorted on primary kay by default , without even myself giving ORDER BY clause in Query.
|
Answer: NO.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
