SQL | Query optimized

Question

I have table FLAG_TERMINAL_EVENTS

<html><head>
<meta http-equiv="content-type" content="text/html; charset=Cp1252">
<!-- base href="http://apexdev.us.oracle.com:7778/pls/apx11w/" -->
<style type="text/css">
table {
background-color:#F2F2F5;
border-width:1px 1px 0px 1px;
border-color:#C9CBD3;
border-style:solid;
}
td {
color:#000000;
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
background-color:#EAEFF5;
padding:8px;
background-color:#F2F2F5;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid solid;
border-width:1px 0px 1px 0px;
}
th {
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
padding:8px;
background-color:#CFE0F1;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid none;
border-width:1px 0px 1px 0px;
white-space:nowrap;
}
</style>
<script type="text/javascript">
window.apex_search = {};
apex_search.init = function (){
 this.rows = document.getElementById('data').getElementsByTagName('TR');
 this.rows_length = apex_search.rows.length;
 this.rows_text =  [];
 for (var i=0;i<apex_search.rows_length;i++){
        this.rows_text[i] = (apex_search.rows[i].innerText)?apex_search.rows[i].innerText.toUpperCase():apex_search.rows[i].textContent.toUpperCase();
 }
 this.time = false;
}

apex_search.lsearch = function(){
 this.term = document.getElementById('S').value.toUpperCase();
 for(var i=0,row;row = this.rows[i],row_text = this.rows_text[i];i++){
  row.style.display = ((row_text.indexOf(this.term) != -1) || this.term  === '')?'':'none';
 }
 this.time = false;
}

apex_search.search = function(e){
    var keycode;
    if(window.event){keycode = window.event.keyCode;}
    else if (e){keycode = e.which;}
    else {return false;}
    if(keycode == 13){
  apex_search.lsearch();
 }
    else{return false;}
}</script>
</head><body onload="apex_search.init();">
<table border="0" cellpadding="0" cellspacing="0">
<tbody><tr><td><input type="text" size="30" maxlength="1000" value="" id="S" onkeyup="apex_search.search(event);" /><input type="button" value="Search" onclick="apex_search.lsearch();"/> 
</td></tr>
</tbody></table>
<br>
<table border="0" cellpadding="0" cellspacing="0">
<tr> <th>SCHEDULE_I</th>
 <th>TERMINAL_EVENT_I</th>
 <th>FLAGS_BLOCK_SEQ_I</th>
 <th>IS_FLAGGED_S</th>
 <th>CREATE_USER_I</th>
 <th>CREATE_TS</th>
 <th>LAST_UPDATE_USER_I</th>
 <th>LAST_UPDATE_TS</th>
</tr>
<tbody id="data">

 <tr>
<td align="right">1464</td>
<td align="right">950</td>
<td align="right">20</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>27-MAY-15</td>
 </tr>
 <tr>
<td align="right">1464</td>
<td align="right">950</td>
<td align="right">21</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1464</td>
<td align="right">950</td>
<td align="right">22</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1464</td>
<td align="right">950</td>
<td align="right">23</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">800</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1058</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1062</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1066</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">802</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">5252</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">2000</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1456</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1470</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1461</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">5200</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1952</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1459</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1466</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1950</td>
<td align="right">950</td>
<td align="right">24</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1464</td>
<td align="right">950</td>
<td align="right">25</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">800</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1058</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1062</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1066</td>
<td align="right">950</td>
<td align="right">26</td>
<td>N</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">802</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">5252</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">2000</td>
<td align="right">950</td>
<td align="right">26</td>
<td>N</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1456</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1470</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1461</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">5200</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1952</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1459</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1466</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
 <tr>
<td align="right">1950</td>
<td align="right">950</td>
<td align="right">26</td>
<td>Y</td>
<td>John Smith</td>
<td>21-MAY-15</td>
<td>John Smith</td>
<td>28-MAY-15</td>
 </tr>
</tbody></table><!-- SQL:
SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", SCHEDULE_I SCHEDULE_I, TERMINAL_EVENT_I TERMINAL_EVENT_I, FLAGS_BLOCK_SEQ_I FLAGS_BLOCK_SEQ_I, IS_FLAGGED_S IS_FLAGGED_S, CREATE_USER_I CREATE_USER_I, CREATE_TS CREATE_TS, LAST_UPDATE_USER_I LAST_UPDATE_USER_I, LAST_UPDATE_TS LAST_UPDATE_TS FROM "CSX"."FLAG_TERMINAL_EVENTS"--></body></html>

I need to do this for each unique FLAGS_BLOCK_SEQ_I

delete from FLAG_TERMINAL_EVENTS where ( select count() from FLAG_TERMINAL_EVENTS where FLAGS_BLOCK_SEQ_I = '26' )=(select count() from FLAG_TERMINAL_EVENTS where FLAGS_BLOCK_SEQ_I = '26' and IS_FLAGGED_S = 'Y');

in optimised way

Please edit the HTML above so that people can read it. Thanks. — Bob Jarvis - Слава Україні, May 28 '15 at 12:50
You really appear to only need the SQL (which is hard to spot). Do you just want to [delete from a `SELECT`](http://stackoverflow.com/a/9843719/812837)? Or from a user-provided list? — Clockwork-Muse, May 28 '15 at 13:21

Ponder Stibbons · Answer 1 · 2015-05-28T14:20:05.627

2

It seems that you need this delete statement:

delete from flag_terminal_events f
  where not exists (
    select 1 from flag_terminal_events s 
      where s.flags_block_seq_i = f.flags_block_seq_i 
        and (s.is_flagged_s<>'Y' or s.is_flagged_s is null) )

You can also use this PL/SQL block:

begin 
  for o in (
    select flags_block_seq_i fbs from flag_terminal_events
    minus
    select flags_block_seq_i from flag_terminal_events 
      where is_flagged_s<>'Y' or is_flagged_s is null)
  loop
    delete from flag_terminal_events where flags_block_seq_i = o.fbs;
  end loop;
end;

edited May 28 '15 at 14:20

answered May 28 '15 at 13:45

Ponder Stibbons

14,723
2
21
24

Might like to make the predicate against is_flagged_s resilient to NULL also. – David Aldridge May 28 '15 at 14:11

SQL | Query optimized

1 Answers1