Re: ACCELERATING STORE PROCEDURE
From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Thu, 19 Feb 2009 14:57:03 -0800 (PST)
Message-ID: <acb9b085-9029-4671-ac8c-b48e2ba86358_at_n2g2000vbl.googlegroups.com>
On 19 feb, 15:31, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 19, 2:29 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
>
>
>
> > Hi everyone,
>
> > Hoping you are ok, i need your help to solve this query in order to
> > make faster an stored procedure, it goes likes this:
>
> > Tables Sources -> SP procesing some data -> Tables Destiny (Results)
>
> > So my source's tables are
>
> > OM_DB.CDRS (86 fields and 30,000 rows average)
> > OM_DB.COFETEL (8 fields and 62,000 rows average)
> > OM_DB.CDRS_LISTED_ISDN_CAUSE_CODES (4 fields, 54 rows and this is a
> > catalog, no index)
>
> > Those source tables with this indexes
>
> > CREATE INDEX PSA.CDRS_IX
> > ON OM_DB.CDRS(START_TIME,CALLED_PARTY_ON_DEST,CALL_SOURCE_REGID)
>
> > CREATE INDEX PSA.SX_CDRS
> > ON OM_DB.CDRS(ISDN_CAUSE_CODE,CALL_DURATION_INT)
>
> > CREATE INDEX PSA.COFETEL_IX
> > ON OM_DB.COFETEL(MODALIDAD)
>
> > CREATE INDEX PSA.COFETEL_SX
> > ON OM_DB.COFETEL(NIR_INICIAL,NIR_FINAL)
>
> > The destiny's tables are
>
> > om_db.CDRS_FINAL1 (9 fields, 5 rows per hour)
> > om_db.CDRS_ISDN_CAUSE_CODE (5 fields, 50 rows per hour)
>
> > Those destiny tables with this indexes
>
> > CREATE UNIQUE INDEX PSA.IX_CDRS_FINAL1
> > ON OM_DB.CDRS_FINAL1(PMM_DATETIME,CALL_SOURCE_REGID)
>
> > CREATE UNIQUE INDEX PSA.IX_CDRS_ISDN_CAUSE_CODE
> > ON OM_DB.CDRS_ISDN_CAUSE_CODE
> > (PMM_DATETIME,CALL_SOURCE_REGID,ISDN_CAUSE_CODE)
>
> (Snip)
>
> I agree with Ed - don't do in PL/SQL what may be done with just SQL.
> Let's take a look at part of the code (note that there *may* be
> problems with my PL/SQL code):
> This part of the code is doing much more work than necessary:
> -- Obtener MIN_VALID_COFETEL
> SELECT COUNT(*) INTO vMIN_VALID_COFETEL FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);
>
> -- Calcular el MIN_INVALID_COFETEL
> vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE -
> vMIN_VALID_COFETEL;
>
> -- Obtener los MOVILES_VALIDOS
> SELECT COUNT(*) INTO vMOVILES_VALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) = '140521'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD = 'CPP');
>
> -- Obtener los MOVILES_INVALIDOS
> SELECT COUNT(*) INTO vMOVILES_INVALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) <> '140521'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD = 'CPP');
>
> -- Obtener los FIJOS_VALIDOS
> SELECT COUNT(*) INTO vFIJOS_VALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) = '14052'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD IN('FIJO','MPP'));
>
> -- Obtener los FIJOS_INVALIDOS
> SELECT COUNT(*) INTO vFIJOS_INVALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) <> '14052'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD IN('FIJO','MPP'));
> --------------------
> The above may be done in a single SQL statement, something like this
> (note: the first character position in a string is 1, not 0 as it is
> in C++):
> SELECT
> SUM(1) INTO vMIN_VALID_COFETEL,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',0,1),0)) INTO 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)) INTO 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)) INTO vFIJOS_INVALIDOS
> FROM
> om_db.CDRS A,
> om_db.cofetel B
> WHERE
> A.START_TIME BETWEEN vPMM_DATETIME AND vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0))
> between B.NIR_INICIAL AND B.NIR_FINAL
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);
>
> -- Calcular el MIN_INVALID_COFETEL
> vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE - vMIN_VALID_COFETEL;
>
> In the above, SUM and DECODE are used to essentially eliminate 4
> additional passes through the tables.
>
> You have an index declared like this:
> CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
> START_TIME,
> CALLED_PARTY_ON_DEST,
> CALL_SOURCE_REGID)
>
> Due to the CAST statement used on the A.CALLED_PARTY_ON_DEST column,
> the above index probably would not be used by the query. A function
> based index might be able to help, for example:
> CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
> START_TIME,
> CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0)),
> CALL_SOURCE_REGID);
>
> Since the above set of 5 SQL statements have been decreased to a
> single SQL statement, you might then experiment with using the SQL
> statement to directly update the om_db.CDRS_FINAL1 table with the
> single SQL statement. I will leave that for someone else to explain.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Date: Thu, 19 Feb 2009 14:57:03 -0800 (PST)
Message-ID: <acb9b085-9029-4671-ac8c-b48e2ba86358_at_n2g2000vbl.googlegroups.com>
On 19 feb, 15:31, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 19, 2:29 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
>
>
>
> > Hi everyone,
>
> > Hoping you are ok, i need your help to solve this query in order to
> > make faster an stored procedure, it goes likes this:
>
> > Tables Sources -> SP procesing some data -> Tables Destiny (Results)
>
> > So my source's tables are
>
> > OM_DB.CDRS (86 fields and 30,000 rows average)
> > OM_DB.COFETEL (8 fields and 62,000 rows average)
> > OM_DB.CDRS_LISTED_ISDN_CAUSE_CODES (4 fields, 54 rows and this is a
> > catalog, no index)
>
> > Those source tables with this indexes
>
> > CREATE INDEX PSA.CDRS_IX
> > ON OM_DB.CDRS(START_TIME,CALLED_PARTY_ON_DEST,CALL_SOURCE_REGID)
>
> > CREATE INDEX PSA.SX_CDRS
> > ON OM_DB.CDRS(ISDN_CAUSE_CODE,CALL_DURATION_INT)
>
> > CREATE INDEX PSA.COFETEL_IX
> > ON OM_DB.COFETEL(MODALIDAD)
>
> > CREATE INDEX PSA.COFETEL_SX
> > ON OM_DB.COFETEL(NIR_INICIAL,NIR_FINAL)
>
> > The destiny's tables are
>
> > om_db.CDRS_FINAL1 (9 fields, 5 rows per hour)
> > om_db.CDRS_ISDN_CAUSE_CODE (5 fields, 50 rows per hour)
>
> > Those destiny tables with this indexes
>
> > CREATE UNIQUE INDEX PSA.IX_CDRS_FINAL1
> > ON OM_DB.CDRS_FINAL1(PMM_DATETIME,CALL_SOURCE_REGID)
>
> > CREATE UNIQUE INDEX PSA.IX_CDRS_ISDN_CAUSE_CODE
> > ON OM_DB.CDRS_ISDN_CAUSE_CODE
> > (PMM_DATETIME,CALL_SOURCE_REGID,ISDN_CAUSE_CODE)
>
> (Snip)
>
> I agree with Ed - don't do in PL/SQL what may be done with just SQL.
> Let's take a look at part of the code (note that there *may* be
> problems with my PL/SQL code):
> This part of the code is doing much more work than necessary:
> -- Obtener MIN_VALID_COFETEL
> SELECT COUNT(*) INTO vMIN_VALID_COFETEL FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);
>
> -- Calcular el MIN_INVALID_COFETEL
> vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE -
> vMIN_VALID_COFETEL;
>
> -- Obtener los MOVILES_VALIDOS
> SELECT COUNT(*) INTO vMOVILES_VALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) = '140521'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD = 'CPP');
>
> -- Obtener los MOVILES_INVALIDOS
> SELECT COUNT(*) INTO vMOVILES_INVALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) <> '140521'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD = 'CPP');
>
> -- Obtener los FIJOS_VALIDOS
> SELECT COUNT(*) INTO vFIJOS_VALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) = '14052'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD IN('FIJO','MPP'));
>
> -- Obtener los FIJOS_INVALIDOS
> SELECT COUNT(*) INTO vFIJOS_INVALIDOS FROM (
> SELECT A.*, B.*
> FROM om_db.CDRS A, om_db.cofetel B
> WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
> vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
> NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
> AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) <> '14052'
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
> AND B.MODALIDAD IN('FIJO','MPP'));
> --------------------
> The above may be done in a single SQL statement, something like this
> (note: the first character position in a string is 1, not 0 as it is
> in C++):
> SELECT
> SUM(1) INTO vMIN_VALID_COFETEL,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',0,1),0)) INTO 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)) INTO 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)) INTO vFIJOS_INVALIDOS
> FROM
> om_db.CDRS A,
> om_db.cofetel B
> WHERE
> A.START_TIME BETWEEN vPMM_DATETIME AND vEND_DATETIME
> AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0))
> between B.NIR_INICIAL AND B.NIR_FINAL
> AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);
>
> -- Calcular el MIN_INVALID_COFETEL
> vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE - vMIN_VALID_COFETEL;
>
> In the above, SUM and DECODE are used to essentially eliminate 4
> additional passes through the tables.
>
> You have an index declared like this:
> CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
> START_TIME,
> CALLED_PARTY_ON_DEST,
> CALL_SOURCE_REGID)
>
> Due to the CAST statement used on the A.CALLED_PARTY_ON_DEST column,
> the above index probably would not be used by the query. A function
> based index might be able to help, for example:
> CREATE INDEX PSA.CDRS_IX ON OM_DB.CDRS(
> START_TIME,
> CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0)),
> CALL_SOURCE_REGID);
>
> Since the above set of 5 SQL statements have been decreased to a
> single SQL statement, you might then experiment with using the SQL
> statement to directly update the om_db.CDRS_FINAL1 table with the
> single SQL statement. I will leave that for someone else to explain.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Oh hello thank you so much for your help, i do this but i have a problem with the DECODE function, i'm receiving this error:
16:47:37.329 DBMS nmsnoc-db -- Error: PL/SQL: SQL Statement ignored,
Batch 1 Line 80 Col 17
16:47:37.407 DBMS nmsnoc-db -- Error: PL/SQL: ORA-00934: group
function is not allowed here, Batch 1 Line 82 Col 19
i probe with this (into the sp):
SELECT
SUM(1) INTO vMIN_VALID_COFETEL,
SUM(decode(B.MODALIDAD,'CPP',decode(SUBSTR(A.CALLED_PARTY_ON_DEST,
0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS
FROM om_db.CDRS A, om_db.cofetel B;
Decode function, could be used inside of an Store Procedure?
Regards Received on Thu Feb 19 2009 - 16:57:03 CST