By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,474 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

Index creation

P: n/a
What is the syntax in UDB db2, to create an index IX in table TBL "in
tablespace TBSP" which is different from the tablespace of TBL ? TIA
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
This is for Version 8.1.4
http://publib.boulder.ibm.com/infoce...n/r0000919.htm

PM

"dbamota" <db***@nashfinch.com> a écrit dans le message de
news:a8**************************@posting.google.c om...
What is the syntax in UDB db2, to create an index IX in table TBL "in
tablespace TBSP" which is different from the tablespace of TBL ? TIA

Nov 12 '05 #2

P: n/a
"dbamota" <db***@nashfinch.com> wrote in message
news:a8**************************@posting.google.c om...
What is the syntax in UDB db2, to create an index IX in table TBL "in
tablespace TBSP" which is different from the tablespace of TBL ? TIA


The tablespace used for an index depends how the table was defined. If the
create table specified a separate tablespace is to be used for indexes on
that table, then the named tablespace will be used for all indexes on the
that table. Otherwise, the indexes will be placed in the same tablespace as
the table. Check the SQL Reference manual.
Nov 12 '05 #3

P: n/a
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.

Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.

(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?
Nov 12 '05 #4

P: n/a
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.

Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.

(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?
Nov 12 '05 #5

P: n/a
You posted to the wrong thread i think...

PM
"Stanley Sinclair" <st*************@bellsouth.net> a écrit dans le message
de news:6f**************************@posting.google.c om...
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.

Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.

(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?

Nov 12 '05 #6

P: n/a
You posted to the wrong thread i think...

PM
"Stanley Sinclair" <st*************@bellsouth.net> a écrit dans le message
de news:6f**************************@posting.google.c om...
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.

Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.

(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?

Nov 12 '05 #7

P: n/a

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.
Indexes based on integers are usually "faster" than indexes based on char()
strings. This is simply because integer keys can be compared more quickly
than character strings.
Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.
VARCHARs will be slighly more efficient than CHARs, since the length of the
key is known ahead of time.
(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?


Potentially. More rows in the table may mean more levels in the btree
structure of the index. Each additional level requires another page to be
read from disk, as well as an extra set of key comparisons.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
Nov 12 '05 #8

P: n/a

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
Raj's question is not answered. He wants to know if, regardless of
what kind of tablespace is used, an index based on a string is as
efficient as an index based on an integer.
Indexes based on integers are usually "faster" than indexes based on char()
strings. This is simply because integer keys can be compared more quickly
than character strings.
Also, if using a string in an index, is a VARCHAR as efficient as a
CHAR.
VARCHARs will be slighly more efficient than CHARs, since the length of the
key is known ahead of time.
(I am adding:) Does the answer depend on how large the index is? Eg,
less than 20,000 rows vs more than 100,000 rows?


Potentially. More rows in the table may mean more levels in the btree
structure of the index. Each additional level requires another page to be
read from disk, as well as an extra set of key comparisons.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.