| indexs and performance [message #372068] |
Mon, 08 January 2001 17:53  |
dan kim
Messages: 3
Registered: January 2001
|
Junior Member |
|
|
okay, performace tips needed.
from what i understand, an index is created whenever a primary key is issued to a column in a table. now, if that primary key is a sizeable string, say varchar(100), will performance take a hit, as compared to an integer based data type? i say this because wouldn't the index file size get larger with a larger key?
here's what i'm thinking about doing.
m2m table:
id integer references table_blah
url varchar(100) references table_blip
table_blip:
name varchar(100)
url varchar(100) primary key
or would this be faster?
m2m table:
id integer references table_blah
id2 integer references table_blip
table_blip:
id2 integer primary key
name varchar(100)
url varchar(100)
help on this would be appreciated. trying to break into the oracle field is pretty darn hard.
-dan kim
"associate" oracle dba, with no real dba to study under. :)
|
|
|
|
| Re: indexs and performance [message #372089 is a reply to message #372068] |
Tue, 09 January 2001 13:22  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Be sure that this NAME comulm will NEVER change value - else it's not a candidate PK. People's names change, cities names change etc. If you will want to search this table where an exact match of NAME is the selection criteria, then it's fine, if not then consider a "dataless PK" i.e. a sequence number. Also it won't help to have a sequence number as the PK if you then go ahead and inxed on NAME anyway.
The size of the index does increase based on the values you are indexing (thing of the each index entry as being the indexed columns and the rowid of where the row is). Note that if all the info you want to retrieve is already in the index columns then Oracle won't even need to access the table.
If you are thinking of carying the NAME as a FK on child tables and you don't need it at that level, then it un-necessarily increases the sizes of those child tables too.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
