OraFAQ Forum: SQL & PL/SQL » check all the distinct value match

Home » SQL & PL/SQL » SQL & PL/SQL » check all the distinct value match (oracle 10g windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

check all the distinct value match [message #687728]

Wed, 17 May 2023 11:22

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

hello Guru,

I have a scenario where i have call type incoming and outgoing against call_number

How can i check if the call_number has done both incoming and Outgoing, i have to filter out any phone number who has not done both the incoming and outgoing

below are the details

create table call_details  (
call_type varchar(10),
call_number varchar(12),
call_duration int
);

insert into call_details values ('OUT','181868',13),
insert into call_details values('OUT','2159010',8),
insert into call_details values ('OUT','2159010',178),
insert into call_details values ('SMS','4153810',1),
insert into call_details values ('OUT','2159010',152),
insert into call_details values ('OUT','9140152',18),
insert into call_details values ('SMS','4162672',1),
insert into call_details values ('SMS','9168204',1),
insert into call_details values ('OUT','9168204',576),
insert into call_details values ('INC','2159010',5),
insert into call_details values ('INC','2159010',4),
insert into call_details values ('SMS','2159010',1),
insert into call_details values('SMS','4535614',1),
insert into call_details values ('OUT','181868',20),
insert into call_details values ('INC','181868',54),
insert into call_details values('INC','218748',20),
insert into call_details values('INC','2159010',9),
 insert into call_details values ('INC','197432',66),
insert into call_details values('SMS','2159010',1),
insert into call_details values('SMS','4535614',1)

so from the above example call_number 218748 has only inc so can be ignored and call_number 9140152 has done outgoing only so can be ignored.

while call_number 181868 has both inc and out so it is valid record

Report message to a moderator

Re: check all the distinct value match [message #687729 is a reply to message #687728]

Wed, 17 May 2023 11:48

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

I have tried below one and seems to be working fine and looing better way to acheive it

SELECT call_number
   FROM call_details
   WHERE call_type in('INC','OUT')--<> 'SMS'
   GROUP BY call_number
   HAVING COUNT(DISTINCT call_type) =2

Report message to a moderator

Re: check all the distinct value match [message #687730 is a reply to message #687729]

Wed, 17 May 2023 11:54

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't see a better query.

Report message to a moderator

Re: check all the distinct value match [message #687731 is a reply to message #687730]

Wed, 17 May 2023 12:02

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Ok thanks, It meanes it is the best way to check all the possible value for a given product

Report message to a moderator

Re: check all the distinct value match [message #687732 is a reply to message #687731]

Wed, 17 May 2023 12:24

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Er... what product? I see only phone calls!

Report message to a moderator

Re: check all the distinct value match [message #687734 is a reply to message #687732]

Wed, 17 May 2023 15:34

Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA

Senior Member

I am guessing that this is just a small sample of data and that speed of the query on a larger set of data might be important. In that case, I think the below query might be faster.

SELECT call_number
FROM   call_details
WHERE  call_type = 'INC'
INTERSECT
SELECT call_number
FROM   call_details
WHERE  call_type = 'OUT';

I have provided some comparisons below. I have provided the following indexes for the optimizer to choose from and you will see that each query chooses a different compound index.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX num_idx ON call_details (call_number)
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX typ_idx ON call_details (call_type)
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX num_typ_idx ON call_details (call_number, call_type)
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX typ_num_idx ON call_details (call_type, call_number)
  2  /

Index created.

-- compare execution times (note the elapsed times after each query):

SCOTT@orcl_12.1.0.2.0> SELECT call_number
  2  FROM   call_details
  3  WHERE  call_type in ('INC', 'OUT')
  4  GROUP  BY call_number
  5  HAVING COUNT (DISTINCT call_type) = 2
  6  /

CALL_NUMBER
------------
2159010
181868

2 rows selected.

Elapsed: 00:00:00.02

SCOTT@orcl_12.1.0.2.0> SELECT call_number
  2  FROM   call_details
  3  WHERE  call_type = 'INC'
  4  INTERSECT
  5  SELECT call_number
  6  FROM   call_details
  7  WHERE  call_type = 'OUT'
  8  /

CALL_NUMBER
------------
181868
2159010

2 rows selected.

Elapsed: 00:00:00.01

I have also provided execution plans. Notice that the second query uses access through one index, instead of filters.

SCOTT@orcl_12.1.0.2.0> EXPLAIN PLAN FOR
  2  SELECT call_number
  3  FROM   call_details
  4  WHERE  call_type in ('INC', 'OUT')
  5  GROUP  BY call_number
  6  HAVING COUNT (DISTINCT call_type) = 2
  7  /

Explained.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1503979016

---------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    13 |   195 |     1   (0)| 00:00:01 |
|*  1 |  FILTER                 |             |       |       |            |          |
|   2 |   HASH GROUP BY         |             |    13 |   195 |     1   (0)| 00:00:01 |
|   3 |    VIEW                 | VM_NWVW_1   |    13 |   195 |     1   (0)| 00:00:01 |
|   4 |     SORT GROUP BY NOSORT|             |    13 |   195 |     1   (0)| 00:00:01 |
|*  5 |      INDEX FULL SCAN    | NUM_TYP_IDX |    13 |   195 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT("$vm_col_1")=2)
   5 - filter("CALL_TYPE"='INC' OR "CALL_TYPE"='OUT')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SCOTT@orcl_12.1.0.2.0> EXPLAIN PLAN FOR
  2  SELECT call_number
  3  FROM   call_details
  4  WHERE  call_type = 'INC'
  5  INTERSECT
  6  SELECT call_number
  7  FROM   call_details
  8  WHERE  call_type = 'OUT'
  9  /

Explained.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3664666359

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     6 |   195 |     4  (50)| 00:00:01 |
|   1 |  INTERSECTION       |             |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|             |     6 |    90 |     2  (50)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN | TYP_NUM_IDX |     6 |    90 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|             |     7 |   105 |     2  (50)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN | TYP_NUM_IDX |     7 |   105 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CALL_TYPE"='INC')
   5 - access("CALL_TYPE"='OUT')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

Report message to a moderator

Re: check all the distinct value match [message #687735 is a reply to message #687734]

Wed, 17 May 2023 16:15

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Thanks Barbara Boehmer,

You have explained in so much great details and easy to understand

Report message to a moderator

Re: check all the distinct value match [message #687738 is a reply to message #687735]

Thu, 18 May 2023 01:04

John Watson
Messages: 8870
Registered: January 2010
Location: Global Village

Senior Member

Two more solutions:

select distinct(call_number) from call_details where call_type='OUT' and call_number in (select call_number from call_details where call_type='INC');

select distinct(call_number) from call_details a where call_type='OUT' and exists (select 1 from call_details b where a.call_number=b.call_number and b.call_type='INC');

Again, you would need to investigate the efficiency of the exec plans, as BB did.

Report message to a moderator

Re: check all the distinct value match [message #687740 is a reply to message #687738]

Thu, 18 May 2023 07:41

Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA

Senior Member

Match recognize solution assuming there are no call types between INC and OUT if we order by call type like it is in the sample: INC, OUT, SMS. In addition, I assume call_number is not null:

select  *
  from  call_details
  match_recognize(
                  partition by call_number
                  order by call_type
                  pattern(i o)
                  define i as call_type = 'INC',
                         o as call_type = 'OUT'
                 )
/

CALL_NUMBER
------------
181868
2159010


Execution Plan
----------------------------------------------------------
Plan hash value: 667559662

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |             |    20 |   160 |     1   (0)| 00:00:01 |
|   1 |  VIEW                                                  |             |    20 |   160 |     1   (0)| 00:00:01 |
|   2 |   MATCH RECOGNIZE BUFFER DETERMINISTIC FINITE AUTOMATON|             |    20 |   300 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                                     | NUM_TYP_IDX |    20 |   300 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        646  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

And if there can be call types between INC and OUT:

select  *
  from  call_details
  match_recognize(
                  partition by call_number
                  order by call_type
                  pattern(i e* o)
                  define i as call_type = 'INC',
                         e as call_type not in ('INC','OUT')
                         o as call_type = 'OUT'
                 )
/

CALL_NUMBER
------------
181868
2159010


Execution Plan
----------------------------------------------------------
Plan hash value: 1883887088

---------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    20 |   160 |     1   (0)| 00:00:01 |
|   1 |  VIEW                   |             |    20 |   160 |     1   (0)| 00:00:01 |
|   2 |   MATCH RECOGNIZE BUFFER|             |    20 |   300 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN      | NUM_TYP_IDX |    20 |   300 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        711  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

SY.

[Updated on: Thu, 18 May 2023 07:43]

Report message to a moderator

Re: check all the distinct value match [message #687741 is a reply to message #687740]

Thu, 18 May 2023 08:06

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Interesting unfortunately:

Quote:

(oracle 10g windows)

[Updated on: Thu, 18 May 2023 12:01]

Report message to a moderator

Previous Topic:	Wrap Triggers
Next Topic:	What is the difference between MINUS and LEFT outer join

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 24 03:24:13 CDT 2023