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!

Performance issue will insert table.

suman amaraApr 7 2015 — edited Apr 10 2015

Hi Team ,

I am getting performance issue as below insert stmt.

INSERT INTO SA_REPORT_DATA
(REPORT_ID, CUTOFF_DATE, COL_1, COL_2, COL_3)
(
SELECT 'ISRP-734', to_date('&DateTo', 'YYYY-MM-DD'),
       SNE.ID AS HLR
,      SNR.FROM_NUMBER||' - '||SNR.TO_NUMBER AS NUMBER_RANGE
,      COUNT(M.MSISDN) AS AVAILABLE_MSISDNS
FROM
       SA_NUMBER_RANGES SNR
,      SA_SERVICE_SYSTEMS SSS
,      SA_NETWORK_ELEMENTS SNE
,      SA_MSISDNS M
WHERE
       SSS.SEQ = SNR.SRVSYS_SEQ
AND    SSS.SYSTYP_ID = 'OMC HLR'
AND    SNE.SEQ = SSS.NE_SEQ
AND    SNR.ID_TYPE = 'M'
AND    M.MSISDN  >= SNR.FROM_NUMBER
AND    M.MSISDN  <= SNR.TO_NUMBER
AND    M.STATE  = 'AVL'
GROUP BY
       SNE.ID,SNR.FROM_NUMBER||' - '||SNR.TO_NUMBER
)

currently taking around 7hours to complete. and Total number of 37650 Records are there if we query the Select stmt.

Please let me know any further information about table. Look like if we you Hints is it you full...?

Locked Post

New comments cannot be posted to this locked post.

Locked on May 8 2015

Added on Apr 7 2015

#plsql

17 comments

6,200 views