• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

creating a temporary index on a table through code

 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 22449
121
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving to JDBC.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks martin... that was helpful...
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic