SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

my query is too slow

Jimmie_MMay 28 2009 — edited May 29 2009

DDL-table name(200,000 data) sheet2; Inv_num number(10), Price varchar2(20)

Inv_num	price
9606	X
9613	X
9620	X
9644	X
9668	$34.56 
6401	X
6401	$ 52.32
4720	X
4720	$ 539.03
4737	$ 924.05
4737	X
4782	X
4782	$ 89.40

I want to have the following result(not duplicated record + remove one of duplicated record with price 'x');

Inv_num	price
9606	X
9613	X
9620	X
9644	X
9668	$34.56 
6401	$ 52.32
4720	$ 539.03
4737	$ 924.05
4782	$ 89.40

I did it but it is too slow, would you revise my query thanks

SELECT  Inv_num,price

FROM Sheet2
where Sheet2.Inv_num  in(
SELECT Sheet2.Inv_num  FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_numFROM Sheet2
GROUP BY Sheet2.Inv_num HAVING (((Count(Sheet2.Inv_num))>1)))) and Sheet2.price <>'x'
union
SELECT Inv_num,price

FROM Sheet2
where Sheet2.Inv_num not  in(
SELECT Sheet2.Inv_num  FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_num FROM Sheet2
GROUP BY Sheet2.Inv_num
HAVING (((Count(Sheet2.Inv_num))>1))))

thanks in advance

This post has been answered by Boneist on May 28 2009

Jump to Answer

BluShadow

You can't know about a row being removed unless you have a record of that row somewhere, either as a copy of your old data (see example below) or you've recorded the information using delete triggers etc.

SQL> ed
Wrote file afiedt.buf

  1  with old_data as (select 1 as id, 'A' as dta from dual union all
  2                     select 2, 'B' from dual union all
  3                     select 3, 'C' from dual)
  4      ,new_data as (select 1 as id, 'A' as dta from dual union all
  5                    select 3, 'X' from dual union all
  6                    select 4, 'Y' from dual)
  7  --
  8      ,ins_upd as (select * from new_data minus select * from old_data)
  9      ,del_upd as (select * from old_data minus select * from new_data)
 10      ,upd as (select id from ins_upd intersect select id from del_upd)
 11      ,ins as (select id from ins_upd minus select id from upd)
 12      ,del as (select id from del_upd minus select id from upd)
 13  --
 14  select 'Inserted' as action, null as old_id, null as old_dta, new_data.id as new_id, new_data.dta as new_dta
 15  from new_data join ins on (ins.id = new_data.id)
 16  union all
 17  select 'Updated', old_data.id, old_data.dta, new_data.id, new_data.dta
 18  from old_data join new_data on (old_data.id = new_data.id)
 19                join upd on (upd.id = new_data.id)
 20  union all
 21  select 'Deleted', old_data.id as old_id, old_data.dta as old_dta, null as new_id, null as new_dta
 22  from old_data join del on (del.id = old_data.id)
 23  union all
 24  select 'No Change' as action, new_data.id as old_id, new_data.dta as old_dta, new_data.id as new_id, new_data.dta as new_dta
 25  from new_data where id not in (select id from ins_upd union all
 26*                                select id from del_upd)
SQL> /

ACTION        OLD_ID O     NEW_ID N
--------- ---------- - ---------- -
Inserted                        4 Y
Updated            3 C          3 X
Deleted            2 B
No Change          1 A          1 A

SQL>

1 person found this helpful

Aketi Jyuuzou

Answer

I used OLAP B-)

My OLAP articles (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html
http://codezine.jp/article/corner/71

create table w_acct1 as
select 'A1' acct, 0 vers from dual union all
select 'A2' acct, 0 vers from dual union all
select 'A3' acct, 0 vers from dual union all
select 'A1' acct, 1 vers from dual union all
select 'A2' acct, 1 vers from dual union all
select 'A1' acct, 2 vers from dual union all
select 'A4' acct, 2 vers from dual;

select acct,vers,
case when appearCnt=1 and preCnt = 0 then 'null'
     when appearCnt=preCnt then 'null'
     else acct end as LD,
case when appearCnt=1 and preCnt = 0 then 'NEW'
     when appearCnt=preCnt then 'REMOVED'
     else 'NO_CHANGE' end as ADD_REMOVE
from (select a.acct,b.vers,
      count(b.acct)
      over(partition by a.acct order by b.vers) as appearCnt,
      count(b.acct)
      over(partition by a.acct
           order by b.vers rows between unbounded preceding
                                    and 1 preceding) as preCnt,
      Lag(b.acct) over(partition by a.acct order by b.vers) as LagVal
        from (select distinct acct from w_acct1) a
        Left Join w_acct1 b
      partition by (b.vers)
          on a.acct = b.acct)
 where appearCnt > preCnt
    or LagVal is not null
order by vers,acct;

AC  VERS  LD    ADD_REMOV
--  ----  ----  ---------
A1     0  null  NEW      
A2     0  null  NEW      
A3     0  null  NEW      
A1     1  A1    NO_CHANGE
A2     1  A2    NO_CHANGE
A3     1  null  REMOVED  
A1     2  A1    NO_CHANGE
A2     2  null  REMOVED  
A4     2  null  NEW

Marked as Answer by RichardSquires · Sep 27 2020

fac586

or some other cool Oracle feature I'm missing?

Workspace Manager?

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 26 2009

Added on May 28 2009

10 comments

2,792 views

SQL & PL/SQL

my query is too slow

Comments

Post Details