| Does Oracle determine the index to use in a query based on whether update statistics has been run? [message #224346] |
Wed, 14 March 2007 00:56  |
renjunatarajan
Messages: 5
Registered: January 2007
|
Junior Member |
|
|
Hi,
Does oracle determine which index to use for a query based on whether statistics are run for the table?
So if you have a new table with indexes defined, and you plan to migrate data(lots of it in one shot) into the table(with a lots of inserts and updates on the new table), will Oracle use the indexes on the new table? Do you have to hint the indexes for it to decide on the index? (Here, data is continuously changing for the new table )
Normally, how would the gather_table_stats procedure be run in a live system? Is it an automatic job?
Thanks,
rn
|
|
|
|
| Re: Does Oracle determine the index to use in a query based on whether update statistics has been ru [message #224352 is a reply to message #224346] |
Wed, 14 March 2007 01:19  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Statistics gathering is automated by default for 10g. For prior versions you have handle it yourself.
During a load, inserts tend not to query the table, so statistics are unimportant. Updates and Deletes will generally use the same execution plan for each execution until statistics are re-gathered.
So if the table starts out empty, Oracle may choose a Full Scan for updates and deletes, which would be sub-optimal if you insert a lot of new rows during the job. You might need a hint to force index usage in this case.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
