Oracle Sql - Script suns extremely slow when include a subquery in a IN statement

Question

Table: Number

Table: Data

B     C     D
----- ----- --
34    A     B
34    C     D
35    E     F
35    G     H
36    I     J
36    K     L

I want to return value in B when A is a duplicate mapping to B. In the example, 34 is selected(in practice, it will return over 100+ duplicate values).

After getting 34, I want to return all rows in 'Data' table where B=34

B     C     D
----- ----- -----
34    A     B
34    C     D

My Try:

With Number as (
    select  B,count(B) as count
    from  Number
    where 1=1
    group by B
)
, Number2 As (
    select B
    from Number 
    where count>1
, Data As (
    select * from Data where B in (select * from Number2)
)

But when I run the script, it runs extremely slow. I am wondering if there is any better way to make it faster

Littlefoot · Accepted Answer · 2020-06-18 05:16:56Z

How about JOIN? Lines #1 - 15 represent sample data (you don't type that); query you do need begins at line #16.

SQL> with
  2  tnumber (a, b) as
  3    (select 12, 34 from dual union all
  4     select 22, 34 from dual union all
  5     select 11, 35 from dual union all
  6     select 13, 36 from dual
  7    ),
  8  tdata (b, c, d) as
  9    (select 34, 'a', 'b' from dual union all
 10     select 34, 'c', 'd' from dual union all
 11     select 35, 'e', 'f' from dual union all
 12     select 35, 'g', 'h' from dual union all
 13     select 36, 'i', 'j' from dual union all
 14     select 36, 'k', 'l' from dual
 15    )
 16  select d.*
 17  from tdata d join (select n.b
 18                     from tnumber n
 19                     group by n.b
 20                     having count(*) > 1
 21                    ) x
 22                 on d.b = x.b;

         B C D
---------- - -
        34 a b
        34 c d

SQL>

Popeye · Accepted Answer · 2020-06-18 04:23:12Z

0

You can use GROUP BY and HAVING clause as follows:

SELECT *
  FROM DATA_TABLE D
 WHERE D.B IN (
    SELECT B
      FROM NUMBER_TABLE
     GROUP BY B
    HAVING COUNT(1) > 1)

Index on column B in both the table will help you reducing the query cost.

answered Jun 18, 2020 at 4:23

Popeye

36k4 gold badges12 silver badges31 bronze badges

Comments

D.J. · Accepted Answer · 2020-06-18 12:36:02Z

0

Give it a try:

SELECT D.* FROM(SELECT B FROM Number GROUP BY B HAVING COUNT(B) > 1)TEMP
JOIN DATA D ON D.B = TEMP.B

answered Jun 18, 2020 at 12:36

D.J.

3864 silver badges17 bronze badges

Collectives™ on Stack Overflow

Oracle Sql - Script suns extremely slow when include a subquery in a IN statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related