My DB is at remote machine. i am invoking Sqlplus through AR Server machine and it is of same version.
I am also using Toad version 9 from my desktop to connect DB server.
you could have your DBA add the index if he is worried about the optimizer. The optimizer can be configured to use this by your DBA.
By presenting this SQL Statement to your DBA, he should be the one suggesting how to speed the query up and what indexes to add.
Thanks, one thing more as this is the inbuilt query use by ARSystem application, can we make changes to this?
indexes we can add from developer studio but other than that.
I will suggest not to add index directly into database. Work with your dba to find out which field requires index and add index using devstduio.
I have found, especially with Oracle DBA's, that they would prefer to add the indexes themselves as it is their job to "tune" the DB for best performance. This varies from customer to customer dependant on policies ets.
You should not need to change the query to speed this up, just add the relevant indexes for use within the query.
Misi has provided a good starting point.
Add the index from Dev Studio, then check how long it took to execute.
It is the DBA's job to gain maximum performance with the queries, thus why he/she should be making the suggestions to what indexes to add and where to add them.
Adding an index like this will have very little inpact on commit time.
You might need your DBA to rebuild the index, or whatever it is called, in order to get the correct statistics built for the index that includes all your current records.
Best Regards - Misi, RRR AB, http://rrr.se
Well just a little side story if I may
Be sure to document and save somewhere the information if your dba creates the index directly in database.
I found myself with a customer who migrated and "forgot" that the DBA did some tune work.
They had some custom workflow that took like 8 hours to complete before. On their new brand rig it was more than 24 hours
After two days work and 10 indexes, it went to 4. And after I gave them the documentation, project manager told me "oh, this index rings a bell, wait a sec", and he finally found an email the DBA sent him two years ago with some of the indexes I found... Reason it got lost? Indexes were done in database rather than in developer studio ^_^
Oh and btw, the custom workflow was a lot on commit (basically they re-created the CMDB every night). So a lot of select and insert / update.
Thanks for the replies.
One thing more i want to know, if i put index on a field through developer studio, how much time it will take to come into affect or is it instantaneous process?
The index is created right after you create it with developer studio.
I guess you need to reindex for the index after it's created.
But what is reindex of Index? how to do it? Why we need to do it?
Well, once you create an index, you need to update / create the index from the data the index is created on.
The way to do that depends on the database. For example with MS SQL server it's as easy as a right click on the index in SQL Server Management studio and "rebuild".
For SQL Server (online only works with developer or enterprise edition):
ALTER INDEX [index_name] ON table_name REBUILD WITH (ONLINE = ON)
For Oracle something like (or you use Oracle developer studio or something):
alter index index_name rebuild online;
I guess after that it's a DBA work