Re: ACCELERATING STORE PROCEDURE

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 21 Feb 2009 07:56:40 -0800 (PST)
Message-ID: <6b44b15d-8220-4a77-aef5-9f2c2a713ae5_at_q25g2000vbn.googlegroups.com>

On Feb 20, 1:24 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Greetings from Mexico friendly, Charles and Mladen, about Juan Pacheco
> Reyes i dont know him, humm.. oh yes my apologies about the capitals
> it was a mistake, i know are not educated, sorry, i pretend make a
> combination of both advices from you, unified the 5 querys into 1 and
> of course release the RDBMS for text processing, so:
>
> a) Unified querys: Not sure what means, "do the same without PL/SQL"
> i mean, can i replicate the exactly algorithm from Charles? of course
> including the real names and rest of the conditions into the sp, or
> not?
>
> b) Each of everyone of your notes are very true Mladen, i can not
> avoid my novice on this right jeje? by the way the "Select Distinct"
> on
>
> CURSOR CL_CDR IS SELECT DISTINCT(CALL_SOURCE_REGID) FROM
> om_db.CDRS WHERE CALL_SOURCE_REGID IS NOT NULL;
>
> It is necessary because, its true there are not duplicate CDR's but
> the CALL_SOURCE_REGID means the phone number of the client, so i have
> to make all the statistics for each client. Now i have a question, the
> original raw data is inserted with bcp utility, the best option to
> release the RDBMS for text processing i think is split
> CALLED_PARTY_ON_DEST adding a separate numeric column and insert the
> following value into it by writing a trigger, but is it possible make
> this considering the bcp utility, in Sybase bcp disables triggers and
> constraints, in Oracle?
>
> Muchas Gracias, Thanks a lot

I spent an hour (or two) trying to determine the purpose of the PL/SQL code. The logic of the first part of the PL/SQL code seems to be: Logic:

1. If there are rows in the om_db.CDRS table
2. vPMM_DATETIME is set to the date of the earliest START_TIME from om_db.CDRS
3. vEND_DATETIME set to vPMM_DATETIME at 1AM
4. vMAX_PMM_DATETIME is set to the 11:59:59 PM of the latest START_TIME from om_db.CDRS
5. While vPMM_DATETIME <= vMAX_PMM_DATETIME
6. Read into vCALL_SOURCE_REGID the distinct list of CALL_SOURCE_REGID where CALL_SOURCE_REGID is not NULL
7. Reset the counters to 0: vTOTAL_CDRS_CLIENTE, vMIN_VALID_COFETEL, vMIN_INVALID_COFETEL, vMOVILES_VALIDOS, vMOVILES_INVALIDOS, vFIJOS_VALIDOS, vFIJOS_INVALIDOS, vTOTAL_ISDN_CAUSE, vCALL_DURATION_INT
8. vTOTAL_CDRS_CLIENTE is set to the number of records for the vCALL_SOURCE_REGID between vPMM_DATETIME and vMAX_PMM_DATETIME
9. Delete from om_db.CDRS for the vCALL_SOURCE_REGID between vPMM_DATETIME and vMAX_PMM_DATETIME where CALLED_PARTY_ON_DEST LIKE '%* %'
10. v_rows_processed set to the number of rows deleted
11. vMIN_INVALID_COFETEL set to v_rows_processed
12. vMIN_VALID_COFETEL set to the number of rows remaining in om_db.CDRS for the vCALL_SOURCE_REGID
13. vMIN_INVALID_COFETEL set to vTOTAL_CDRS_CLIENTE - vMIN_VALID_COFETEL
14. vMOVILES_VALIDOS set to count of MODALIDAD='CPP' and SUBSTR (A.CALLED_PARTY_ON_DEST, 0,6)='140521' for the vCALL_SOURCE_REGID (possible Cartesian join)
15. vMOVILES_INVALIDOS set to count of MODALIDAD='CPP' and SUBSTR (A.CALLED_PARTY_ON_DEST, 0,6)<>'140521' for the vCALL_SOURCE_REGID (possible Cartesian join)
16. vFIJOS_VALIDOS set to count of MODALIDAD=('FIJO' or 'MPP') and SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5)='14052' for the vCALL_SOURCE_REGID (possible Cartesian join)
17. vFIJOS_INVALIDOS set to count of MODALIDAD=('FIJO' or 'MPP') and SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5)<>'14052' for the vCALL_SOURCE_REGID (possible Cartesian join)
18. om_db.CDRS_FINAL1 table is updated using the above values for the vCALL_SOURCE_REGID and vPMM_DATETIME
19. if no row found in om_db.CDRS_FINAL1 for the vCALL_SOURCE_REGID and vPMM_DATETIME, insert a row using the above values
20. FETCH L_ISDN
21. ...

Steps 5 - 17 might be replaced with a SQL statement something like this (there may be errors in this SQL statement): SELECT

  A.CALL_SOURCE_REGID,
  D.vMIN_INVALID_COFETEL + SUM(1) vTOTAL_CDRS_CLIENTE
  D.vMIN_INVALID_COFETEL,

  SUM(1) vMIN_VALID_COFETEL,
  SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,6),'140521',1,0),0)) vMOVILES_VALIDOS,   SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,6),'140521',0,1),0)) vMOVILES_INVALIDOS,   SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5),'14052',1,0),0))+SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR (A.CALLED_PARTY_ON_DEST,0,5),'14052',1,0),0)) vFIJOS_VALIDOS,   SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST, 0,5),'14052',0,1),0))+SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR (A.CALLED_PARTY_ON_DEST,0,5),'14052',0,1),0)) vFIJOS_INVALIDOS FROM
  om_db.CDRS A,
  om_db.cofetel B,
  (SELECT
    CALL_SOURCE_REGID,
    SUM(SIGN(INSTR(CALLED_PARTY_ON_DEST,'*'))) vMIN_INVALID_COFETEL   FROM
    om_db.CDRS
  WHERE
    CALL_SOURCE_REGID IS NOT NULL) D
WHERE
  TO_NUMBER(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10)) between B.NIR_INICIAL AND B.NIR_FINAL
  AND INSTR(CALLED_PARTY_ON_DEST,'*')=0
  AND A.CALL_SOURCE_REGID=D.CALL_SOURCE_REGID   AND CALL_SOURCE_REGID IS NOT NULL
GROUP BY
  A.CALL_SOURCE_REGID,
  D.vMIN_INVALID_COFETEL;

One of the rules of efficient programs is to use just SQL code (no PL/ SQL) when there is a SQL code solution. Sometimes it is necessary to change the code a little to allow the SQL solution to work: "AND CALLED_PARTY_ON_DEST LIKE '%*%'" in the WHERE clause of a DELETE was changed to
SUM(SIGN(INSTR(CALLED_PARTY_ON_DEST,'*'))) to determine the count of the rows that contain * in the CALLED_PARTY_ON_DEST column, since you need to know the number of rows which contain '*' in the CALLED_PARTY_ON_DEST column.

Instead of deleting the rows, I just specified the following to eliminate those rows from the other counts: INSTR(CALLED_PARTY_ON_DEST,'*')=0 Experiment with just SQL code to see if you are able to simplify the problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Feb 21 2009 - 09:56:40 CST