We help IT Professionals succeed at work.

Oracle SQL Repair Advisor

Billy Ma
Billy Ma asked
on
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.
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);

Open in new window

Comment
Watch Question

Database Administrator
Commented:
I would not expect that "delete..." statement (or any other SQL statement) to cause an Oracle system to crash.  What exactly crashed?  Or, exactly what Oracle error message(s) did you receive?

I do see two problems with your "delete..." statement.

First the "<>" operator.  Try to avoid these if possible, since Oracle cannot use an index to help with these.  They tend to cause full table scans (TABLE ACCESS FULL).  Since you also have "where t1.a = 'a'", that could help avoid a full table scan, but only if this column is indexed *AND* if the number of records with a value of 'a' is a small number (like 10%) compared to the total number of records in the table.

Second, the "where" clauses in the sub-query are written backwards.  Queries should have the "known" values to the right of the "=" sign.  So, that sub-query should be written like this:

(select max(rowid) from t t2 where t2.a= t1.a and t2.b = t1.b and t2.d=t1.d)
Billy MaVice President

Author

Commented:
sorry, I cannot give u Grade A as this SQL Statement does cause the Oracle database crash, that's why they have implemented a fix for this.
I have got no idea why this statement crash the database, this is the main question.