Billy Ma
asked on
Oracle SQL Repair Advisor
I am doing the practice of Oracle course.
However, I do not understand why the simple delete statement shown below can cause the system crash?
delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
It only said because the original plan will need to do full table scan....but if you see the execution plan after applying the SQL Patch recommended by the SQL Repair Advisor, the execution plan still have TABLE ACCESS FULL
i cannot see any differences.
I hope someone can explain.
However, I do not understand why the simple delete statement shown below can cause the system crash?
delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
It only said because the original plan will need to do full table scan....but if you see the execution plan after applying the SQL Patch recommended by the SQL Repair Advisor, the execution plan still have TABLE ACCESS FULL
i cannot see any differences.
I hope someone can explain.
grant connect, resource, dba, query rewrite, unlimited tablespace to scott identified by tiger;
alter user scott account unlock;
drop table t;
create table t(a varchar(40), b number, c varchar(240), d varchar(240));
insert into t values('a', 1, 'b', 'c');
insert into t values('a', 1, 'x', 'c');
insert into t values('e', 2, 'f', 'g');
alter session set "_fix_control"="5868490:OFF";
explain plan for delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
select plan_table_output from table(dbms_xplan.display('plan_table',null));
delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
-------------------------------------------------------------
exec dbms_sqldiag.drop_diagnosis_task('sqldiag_bug_5869490');
variable rep_out clob;
declare
t_id varchar2(50);
begin
t_id := dbms_sqldiag.create_diagnosis_task(
sql_text => 'delete from t t1 where t1.a = ''a'' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d)',
task_name => 'sqldiag_bug_5869490',
problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);
dbms_sqltune.set_tuning_task_parameter(
t_id,
'_SQLDIAG_FINDING_MODE',
dbms_sqldiag.SQLDIAG_FINDINGS_FILTER_PLANS);
dbms_output.put_line ('t_id = ' || t_id);
dbms_sqldiag.execute_diagnosis_task (t_id);
dbms_output.put_line ('executed t_id = ' || t_id);
:rep_out := dbms_sqldiag.report_diagnosis_task (t_id, DBMS_SQLDIAG.TYPE_TEXT);
end;
/
print rep_out
execute dbms_sqldiag.accept_sql_patch(task_name => 'sqldiag_bug_5869490', task_owner => 'SCOTT', replace => TRUE);
explain plan for delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
select plan_table_output from table(dbms_xplan.display('plan_table',null,'advanced'));
delete from t t1 where t1.a = 'a' and rowid <> (select max(rowid) from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have got no idea why this statement crash the database, this is the main question.