If I delete most of the rows in the table, or if I truncate it, does it go for hard parsing again, even if it was soft parsing before? I am confused because the statistics will change but the query doesn't change. Besides, does it go for hard parsing if I truncate the table and populate it again with the same data? (The query is the same in all cases)
Truncates will cause extra hard parses but deletes will not.
A truncate command not only removes all rows, it virtually destroys and recreates the table. Truncate may invalidate dependent indexes, create new segments, and change the DBA_OBJECTS.DATA_OBJECT_ID. Any statements that references a table that is truncated will be immediately invalidated.
Deleting is potentially not very dangerous, and does not directly invalidate statements that reference the table. Over time, if there are enough deletes, the automatic optimizer statistics job will detect that significant changes have occurred, and will re-gather statistics on the table and invalidate related queries. But even that invalidation may not happen instantly, as statistics gathering does not always invalidate all dependent queries.
To know for sure, we can measure the number of hard parses. The below test case demonstrates that truncates generate hard parses and deletes do not. (But hard parsing can occur for some unexplained reasons, such as a query being removed from the shared pool. So don't expect the numbers to be exact.)
First, create a table to hold rows that will be deleted, and create a function to get the number of hard parses in the current session.
create table test1(a number);
insert into test1 select level from dual connect by level <= 100000;
commit;
create or replace function get_hard_parse_count return number authid current_user is
v_value number;
begin
execute immediate
q'[
select value
from v$mystat
join v$statname
on v$mystat.statistic# = v$statname.statistic#
where name = 'parse count (hard)'
]'
into v_value;
return v_value;
end;
/
The below code deletes all the rows and counts the rows, repeated 100 times. But the number of hard parses barely increases.
--Count before: 751
select get_hard_parse_count from dual;
declare
v_count number;
begin
for i in 1 .. 100 loop
execute immediate 'delete from test1';
rollback;
execute immediate 'select count(*) from test1';
end loop;
end;
/
--Count after: 759
select get_hard_parse_count from dual;
The below code truncates instead of deletes, and the number of hard parses increases by a huge amount:
--Count before: 760
select get_hard_parse_count from dual;
declare
v_count number;
begin
for i in 1 .. 100 loop
execute immediate 'truncate table test1';
rollback;
execute immediate 'select count(*) from test1';
end loop;
end;
/
--Count after: 1,162
select get_hard_parse_count from dual;