aspose file tools*
The moose likes JDBC and the fly likes Creating an index for a table. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Creating an index for a table." Watch "Creating an index for a table." New topic
Author

Creating an index for a table.

Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
I'm trying to create an index for a table. I'm trying to figure out the best way to do this. I came out with two options. Are they basically the same? Or is there something that I don't notice.

1.)


2.)


The other option that I'm thinking to do is to create primary key but it doesn't seem necessary since primary key is only necessary when I need to build referential integrity, right?

Thanks in advance for the help.
Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
And the SQL commands that I mention in my first post above is very very slow with huge tables. Is there anyway to improve this?

Thank you.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18564
    
    8

I don't know which of those two statements is the one you want. But yes, it does take a long time to build an index over a huge table. There's no getting around that. The good thing is, you're only going to build the index once. If you were going to do it on a daily basis, that might be a problem.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

The difference is what the database does in the background. Both enforce a uniqueness constraint, but the "INDEX" guarantees a database-backed index is available. Often "UNIQUE KEY" does this too, so its not a huge difference. For indexes, you shoul[d specify whether you want a hash or tree depending on what you need. You can have indexes seperate from primary keys. Primary Keys (and other keys like foreign/unique) are more about referential integrity whereas Indexes are about performance.


My Blog: Down Home Country Coding with Scott Selikoff
Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
Thanks Paul + Scott,

Scott,
Often "UNIQUE KEY" does this too, so its not a huge difference

You mentioned 'often'. Is there any case "UNIQUE KEY" won't do this?

Thanks again.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

You need to read your database documentation. Its hard to say what its doing behind the scenes. I would think in order to best enforce a uniqueness constraint you would want a hash index, but its really up the DBMS.
Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
I see. Thanks Scott.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Creating an index for a table.