optimizing the Select Query

Database

optimizing the Select Query

Hi

i would like to re - write the below mentioned query to a single query without any datasets to create.

as it is taking so much of time to execute, any suggestion to adapt the query only ina single select sql statement

SEL a.PGW_IP_ADDRESS, a.PDN_CONNECTION_ID, b.COUNT_DISTINCT_CDR_NR, a.MAX_SEQ_NR

FROM

(

SEL

PGW_IP_ADDRESS,

PDN_CONNECTION_ID,

CDR_SEQUENCE_NR,

MAX(CDR_SEQUENCE_NR) OVER (PARTITION BY PGW_IP_ADDRESS,PDN_CONNECTION_ID) AS MAX_SEQ_NR

FROM dwhd.MEDIATED_4G_DATA

WHERE BLOCK_RATING_GROUP<>0

) a

INNER JOIN

(

SEL PGW_IP_ADDRESS,PDN_CONNECTION_ID,COUNT(DISTINCT CDR_SEQUENCE_NR) AS COUNT_DISTINCT_CDR_NR

FROM dwhd.MEDIATED_4G_DATA

WHERE BLOCK_RATING_GROUP<>0

GROUP BY 1,2

) b

ON a.PGW_IP_ADDRESS = b.PGW_IP_ADDRESS AND a.PDN_CONNECTION_ID=b.PDN_CONNECTION_ID

ORDER BY 1,2 ASC

GROUP BY 1,2,3,4

5 REPLIES
N/A

Re: optimizing the Select Query


I am not sure if I oversimplyfy but isn't this the same?


SEL PGW_IP_ADDRESS,


       PDN_CONNECTION_ID,


       COUNT(DISTINCT CDR_SEQUENCE_NRAS COUNT_DISTINCT_CDR_NR,


        MAX(CDR_SEQUENCE_NR) as MAX_SEQ_NR


FROM dwhd.MEDIATED_4G_DATA


WHERE BLOCK_RATING_GROUP<>0


GROUP BY 1,2


ORDER BY 1,2 ASC

Re: optimizing the Select Query

Need every combination of PGW_IP_ADDRESS,PDN_CONNECTION_ID  columns count and maximum CDR_SEQUENCE_NR .     

N/A

Re: optimizing the Select Query

Did you run the query I suggested?

I think it will do what you want.

Re: optimizing the Select Query

IF I WANT THE SECOND  MAXIMUM CDR_SEQUENCE_NR , THEN HOW TO CONVERT THIS WITHOUT ANY SUB DATASETS.

N/A

Re: optimizing the Select Query

DON'T SHOUT AT PEOPLE! 

And I don't think this will work for the second maximum without derived tables (if it is not simply max -1).

Also this requirement is incomplete as we don't know if the CDR_SEQUENCE_NR can occur multiple times for a GW_IP_ADDRESS, PDN_CONNECTION_ID combination. You would need to give us much more infos about the table content and example data.

But maybe it is worth for you to spend some time on studying the SQL manuals of aggregate and OLAP functions.