File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes creating a temporary index on a table through code Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "creating a temporary index on a table through code" Watch "creating a temporary index on a table through code" New topic
Author

creating a temporary index on a table through code

tushar bhasme
Ranch Hand

Joined: Feb 11, 2008
Posts: 50
Is it feasible to add an index to a table through java code temporarily, and then remove it? We want to add the index temporarily only as the table already has a lot many indices, and we dont want any more permanent index. After the results are fetched, we want to delete the index.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

Moving to JDBC.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Generally, index creation/deletion is a DDL command and it should be possible to run such command from JDBC, yes.

However, this really is not a good idea. Although the details may vary depending on the database, building an index is rather expensive operation. Certainly the whole of the table will have to be read and the index, which is a complicated structure in itself, will have to be created and written to the disk. If you then want to use the index to answer just one query and then drop it again, in most databases this will be much, much more work than running the query without the index, as full scan is the worst access path that can get used.

Worse still, on some databases the newly created index might not get used. Eg, on Oracle, with cost-based optimization, you'd need to collect statistics on the new index and then the optimizer might choose not to use it anyway. If your query accesses significant option of the table, the index will be ignored too. Also, depending on a database, creating or dropping an index might require some locks on the table and perhaps cause some database structures (eg. query plans) to get invalidated, complicating the picture further.

All in all, you should either create the index permanently or not at all. Weight in the maintenance overhead of one additional index against the performance improvement of your query and decide on it.
tushar bhasme
Ranch Hand

Joined: Feb 11, 2008
Posts: 50
thanks martin... that was helpful...
antty ge
Greenhorn

Joined: Dec 24, 2010
Posts: 7
 
Consider Paul's rocket mass heater.
 
subject: creating a temporary index on a table through code
 
Similar Threads
arrayList.ensureCapacity()
What does it mean when a conditional has only the keyword "true" for a condition?
SQL query problem: how tp select all duplication in the table?
selectBooleanCheckbox inside dataTableEx
modifying table