Home » SQL & PL/SQL » SQL & PL/SQL » Does Oracle determine the index to use in a query based on whether update statistics has been run?
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 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Does Oracle determine the index to use in a query based on whether update statistics has been ru [message #224434 is a reply to message #224352] Wed, 14 March 2007 05:11 Go to previous messageGo to next message
renjunatarajan
Messages: 5
Registered: January 2007
Junior Member
If I am using the merge statement to insert rows, as in:

merge into my_new_table
using (select xx......) on () when matched then insert else update;

it has to query the table to determine the join condition, right?

So here, I have to hint the indexes explicitly for Oracle to use the index for inserts. Is that correct?

Thanks,
Re: Does Oracle determine the index to use in a query based on whether update statistics has been ru [message #224482 is a reply to message #224434] Wed, 14 March 2007 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No. The CBO is smart enough to notice if there is an index it can use, without you having to tell it.
Previous Topic: difference between oracle 8i and 9i
Next Topic: Invalid Identifier - Oracle Error
Goto Forum:
  


Current Time: Fri Dec 09 17:20:27 CST 2016

Total time taken to generate the page: 0.32874 seconds