CODEWITHSUNDEEP
oracle-databasequery-optimizationaggregate-functions
cbmeeks
cbmeeks

Reputation: 11420

Is there a more efficient way to count the number of aggregate records in Oracle SQL?

I have more experience with MySQL and MSSQL but I don't consider myself a SQL expert.

I have a requirement for some SQL work running on an Oracle database. Not even sure the version yet but it should be somewhat recent (10, 11??).

Anyway, I have to count the number of distinct records that spans two tables. For sake of argument, let's call them master and detail.

The following SQL gives me the number I want against the data. However, this SQL will eventually be put in a UDF (or Oracle equivalent). But my question is, is there a better way? Either using some advanced Oracle optimization or even just a better SQL query.

Thanks

select count(*) from
(
  select 
    mas.barcode
  , det.barcode_val

  from mas 
    inner join det on (det.trans_id = mas.trans_id and mas.trans_sub_id = det.trans_sub_id)
  where 
    mas.trans_id = 12345
    and det.code_type = 'COMMODORE'
  group by
    mas.barcode
  , det.barcode_val
);

Data:

MAS

trans_id     trans_sub_id     barcode
-------------------------------------
12345                   1      COM_A
12345                   2      COM_A
12345                   3      COM_B


DET

trans_id     trans_sub_id     code_type     barcode_val
-------------------------------------------------------
12345                   1     COMMODORE     C64
12345                   1     COMMODORE     C64
12345                   1     TANDY         TRASH80
12345                   2     COMMODORE     C128
12345                   2     ATARI         800XL
12345                   2     COMMODORE     AMIGA500
12345                   3     COMMODORE     C64


Results before count
--------------------
COM_A     C64
COM_A     C128
COM_A     AMIGA500
COM_B     C64


Results after count
-------------------
4

Upvotes: 0

Views: 290

Answers (2)

Felipe Moreno
Felipe Moreno

Reputation: 498

If you use the 

    COUNT(DISTINCT mas.barcode || det.barcode_val)

make sure to put a delimiter between the pipeline:

    COUNT(DISTINCT mas.barcode || '-' || det.barcode_val)

For example imagine the following scenario:

    Column1    Column2   Column1 || Column2   Column1 || '-' || Column2
    A          B         AB                   A-B
    AB        <null>     AB                   AB-
    1          201       1201                 1-201
    <null>     1201      1201                 -1201

This table has 4 rows with 4 different values. But if you try a 

    COUNT(DISTINCT COLUMN1 || COLUMN2)

you would get just 2 "distinct" groups. Just a tip to try to avoid those corner cases.

Upvotes: 0

Emyl
Emyl

Reputation: 10536

SELECT 
COUNT(DISTINCT mas.barcode || det.barcode_val)
FROM mas 
INNER JOIN det 
ON (det.trans_id = mas.trans_id and mas.trans_sub_id = det.trans_sub_id)
WHERE 
mas.trans_id = 12345
AND det.code_type = 'COMMODORE'

or

SELECT COUNT(*) FROM ( 
  SELECT DISTINCT mas.barcode, det.barcode_val
  FROM mas 
  INNER JOIN det 
  ON (det.trans_id = mas.trans_id and mas.trans_sub_id = det.trans_sub_id)
  WHERE 
  mas.trans_id = 12345
  AND det.code_type = 'COMMODORE'
)

Upvotes: 1

Related Questions