Deletes are odd.
Consider a table of people, with separate indexes on First_Name and Last_Name.
I issue : DELETE FROM people WHERE Last_Name = 'Holmes';
It will use the index on Last_Name to find the rows to delete. It finds "Mr Holmes" and deletes the row, then deletes the index entry from the Last_Name index. It also has to delete the entry from the First_Name index though, and first it needs to find that entry in the segment. It has to get the First_Name value from the table for that row, then use that to probe the index to find the entry for that value (and ROWID) so it can delete it.
Rather than using the index to find a row in a table, it actually uses the table to find the row in the index.
Told you they were odd.
Added:
I figured it is worth adding a demonstration:
create table persons
(first_name varchar2(30), last_name varchar2(30));
insert into persons values ('Sherlock','Holmes');
create index pers_ln_ix on persons (last_name);
create or replace function dummy_func (i_val in varchar2)
return varchar2 deterministic is
begin
dbms_output.put_line('exec dummy_func');
return i_val;
end;
/
create index pers_fn_ix on persons (dummy_func(first_name));
delete from persons
where last_name = 'Holmes';
If you run that, you'll see that the delete fires the dummy_func function, as it needs to obtain the return value so it can find the entry in the index.
8 comments:
Ever seen reads go up like crazy when deleting?
Do a mass delete in a Peoplesoft table with 15(!) indexes and watch the fireworks...
The row (their data) being deleted is already in the memory, so searching the index entry (are you sure that by value - not by the ROWID?) don`t uses the "table".
Wit, the table row is already in memory. That can be located by the index on Last_Name (though could also be located via t table scan).
If the Last_Name index has been used then entry in that index is also in memory, so could be deleted without any other access.
The entry in the first_name index isn't in memory (not from this query anyway). And it does need to be located so it can be removed. In theory, it could full scan the first_name index filtering on the ROWID, but that would be slow for large indexes. It is more practical to do an index range/unique scan to find the entry, since all the necessary values are available on the table row.
The strangeness goes further - but you'll only notice it if you delete multiple rows with a single statement.
The difference might be particularly interesting to Noons because of the way it can change the amount of work done - the strategy Oracle uses to update the (b-tree) indexes varies depending on whether you drive your delete through an index or through a tablescan.
See http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/
Regards
Jonathan Lewis
Interesting reading Jonathan (as always). I'm always amazed by the amount of detailed optimization engineering that has gone into Oracle.
i like this
"If you run that, you'll see that the delete fires the dummy_func function, as it needs to obtain the return value so it can find the entry in the index."
Would you please give a clue how can i see that? Trace the session and then look in .trc file or there is
something else more obvious?
THX
Damir, if you have SERVEROUTPUT on, you will see the 'exec dummy line' output.
That is the function firing. The database has the row from the table, so it knows the first_name value. But it has to fire the index function so that it can determine the indexed value. After it has the indexed value, it can find that value in the index and delete the index entry for that record.
Post a Comment