This discussion is archived
6 Replies Latest reply: Jul 29, 2013 8:10 AM by Skp_Oracle RSS

Performance Tunning - Collections

Skp_Oracle Newbie
Currently Being Moderated

Problem :

Oracle 11 G.

Need to pull the data from one table , the table is Key-Value pair table .or entity table . In which you store all the column names in one column and its value/values in another columns .

The composite primary key is present in the table .

 

Result:  Get the "Value" from the table , I am creating a procedure with primary keys as input and the out parameter will be  a Type which will hold the values.

 

Table structure as below . (Please ignore the names language)

 

  TABLE  a2000020

(

  COD_CIA           NUMBER(2)                 

  NUM_POLIZA        VARCHAR2(13 BYTE)         

  NUM_SPTO          NUMBER                    

  NUM_APLI          NUMBER                    

  NUM_SPTO_APLI     NUMBER                    

  NUM_RIESGO        NUMBER(7)                

  NUM_PERIODO       NUMBER(2)                 

  TIP_NIVEL         NUMBER(1)                 

  COD_CAMPO         VARCHAR2(30 BYTE)         

  VAL_CAMPO         VARCHAR2(80 BYTE),

  VAL_COR_CAMPO     VARCHAR2(10 BYTE),

  NUM_SECU          NUMBER(4),

  TXT_CAMPO         VARCHAR2(80 BYTE),

  MCA_BAJA_RIESGO   VARCHAR2(1 BYTE)          

  MCA_VIGENTE       VARCHAR2(1 BYTE)         

  MCA_VIGENTE_APLI  VARCHAR2(1 BYTE)          

  COD_RAMO          NUMBER(3)                 

)

 

Primary Key : NUM_POLIZA, COD_CIA, NUM_SPTO, NUM_APLI, NUM_SPTO_APLI, NUM_RIESGO, COD_CAMPO, NUM_PERIODO .

 

COD_campo column  is storing the column names .

 

VAL_CAMPO  and txt_campo  column  is storing its corresponding value.

 

i.e.

  Rows in the table  looks like

 

     PRIMARY_KEY_COLUMNS             COD_CAMPO              VAL_CAMPO     TXT_CAMPO        OTHER COLUMS

<Primary key columns values >            <POLICY_NAME>          < XXXXX >              < ZZZZZZ >

<Primary key columns values >            <POLICY_CODE>           <1234 >               < SSAS>

<Primary key columns values >            <POLICY_SECTOR>       <64789 >               <WWWW>

 

*<> are just to  differentiate.

 

Each Row value I want to assign it to attribute variable of a TYPE (POLICY_OBJECT).and that type would be the OUT parameter for the Procedure .

The Primary key values can be the INPUT parameter for the Procedure.

 

Approach:

 

PROCEDURE p_tunning (p_auto_policy_obj    IN OUT  auto_policy_obj )

IS

  TYPE type_a20 IS TABLE OF a2000020%Rowtype  INDEX BY PLS_INTEGER;

   table_a20 type_a20;

BEGIN

 

SELECT

                  COD_CIA,

                  NUM_POLIZA,

                  NUM_SPTO,

                  NUM_APLI,

                  NUM_SPTO_APLI,

                  NUM_RIESGO,

                  NUM_PERIODO,

                  TIP_NIVEL,

                  COD_CAMPO,

                  VAL_CAMPO,

                  VAL_COR_CAMPO,

                  NUM_SECU,

                  TXT_CAMPO,

                  MCA_BAJA_RIESGO,

                  MCA_VIGENTE,

                  MCA_VIGENTE_APLI,

                  COD_RAMO

  BULK COLLECT INTO table_a20

   FROM (SELECT   COD_CIA,

                  NUM_POLIZA,

                  NUM_SPTO,

                  NUM_APLI,

                  NUM_SPTO_APLI,

                  NUM_RIESGO,

                  NUM_PERIODO,

                  TIP_NIVEL,

                  COD_CAMPO,

                  VAL_CAMPO,

                  VAL_COR_CAMPO,

                  NUM_SECU,

                  TXT_CAMPO,

                  MCA_BAJA_RIESGO,

                  MCA_VIGENTE,

                  MCA_VIGENTE_APLI,

                  COD_RAMO,

                  MAX(num_spto) OVER(PARTITION BY cod_cia, num_poliza, cod_campo) AS max_endrstm

           FROM a2000020

          WHERE  <CONDITIONS>   )

 

  WHERE <CONDITIONS>;

  --

  FOR i IN 1 .. table_a20.count

  LOOP

    CASE table_a20(i).cod_campo

 

    WHEN 'POLICY_NAME' THEN

          p_auto_policy.policy_type_code := table_a20(i).val_campo;

          p_auto_policy.policy_type_name := table_a20(i).txt_campo;

  

WHEN     'POLICY_SECTOR' THEN

          p_auto_policy.policy_sector_code := table_a20(i).val_campo;

          p_auto_policy.policy_sector_name := table_a20(i).txt_campo;

 

<      MORE SIMILAR  CASE STATEMENT AS ABOVE AROUND 70 -80 FOR EACH VAL CAMPO  AND assignment  to the  variables > 

       

  END ;

 

the problem here is  case statements is taking some time to execute and i want to avoid the case .

  can we use index by varchar  cod_campo , and loop in and assign the values ? no case statement would require. this is just

  a thought , i am not sure how to implement it  , any  more suggestions please.

 

Similar things can be achieved by Select statements as well using decode, or write a function which takes as input the val_campo  and a select statement in that function selecting the val_campo,txt_campo.

but the same function needs to be called for each assignment (70-80)  times, I tried this approach and it takes time as 70-80 time I/O .

 

 

  Kindly suggest . Thank you!

  • 1. Re: Performance Tunning - Collections
    34MCA2K2 Journeyer
    Currently Being Moderated
    Select statements as well using decode, or write a function which takes as input the val_campo  and a select statement in that function selecting the val_campo,txt_campo.

     

     

    What about Select statement using Case? You should do it when you are doing the bulk collect itself. No need of running the loop.

     

    Regards,

  • 2. Re: Performance Tunning - Collections
    Skp_Oracle Newbie
    Currently Being Moderated

    34MCA2K2


    Could you please give the example using the above select .

  • 3. Re: Performance Tunning - Collections
    rp0428 Guru
    Currently Being Moderated

    Kindly suggest . Thank you!

    Ok - since you ask so nicely. I will suggest the same thing I suggested in your other thread (which has not yet been marked ANSWERED and is a duplicate).

     

    That code doesn't need 'tuned'; it needs to be thrown away.

     

    You are trying to use PL/SQL when it is likely that SQL will do whatever it is you are needing to do.

     

    Start over and tell us what PROBLEM you are trying to solve. Tell us what RESULT you need.

     

    We CANNOT help  you when we don't know what you are really trying to do. You are posting code that CANNOT possibly be useful for doing whatever it is (unknown to us) that you want to do.

     

    That procedure is processing potentially large numbers of rows, using expensive PGA memory to hold a lot of data and then processing those collections to create other collections that use more expensive PGA memory. And no matter how efficient it does that the data being collected is TOTALLY USELESS for any purpose because the procedure neither uses the data for anything nor does it return the data so the caller can use it.

     

    This is how the procedure is defined:

     

    PROCEDURE p_tunning (p_auto_policy_obj    IN OUT  auto_policy_obj )

    That IN OUT parameter is a SCALAR and will only ever return ONE data item.

     

    As previously suggested, if you want help then START OVER in this thread and tell us, in plain and simple English:

    1. WHAT problem are you trying to solve?

    2. WHY are you querying all of that data?

    3. HOW much data are we even talking about? Tens of rows? Hundreds of rows, Millions of rows?

    4. WHO is going to use the data that you now have in collections in that procedure code?

    5. HOW is the data going to be used? Display on a web page? Produce a report? Export to a file?

     

    Please quit focusing on what you think the solution is and tell us what the problem is.

     

    So far your proposed 'solution' is performing at least TWO asynchronous, serial operations on the SAME data and the not even doing anything with the resulting data.

     

    It simply isn't possible to 'tune' that kind of process without understanding what the ultimate result needs to be. As far as we know you could use a simple SQL process to accomplish your desired result.

     

    People are trying to help you but you have to 'help us help you' by providing us with the BUSINESS requirement you are trying to implement.

  • 4. Re: Performance Tunning - Collections
    Skp_Oracle Newbie
    Currently Being Moderated

    PROCEDURE p_tunning (p_auto_policy_obj    IN   OUT  auto_policy_obj )


    p_auto_policy_obj    is variable of TYPE  auto_policy_obj  which is input as well out .

    the primary key values would be coming as in the p_auto_policy_obj  ,(its attributes ,6  ) . similarly once the  values are read from the table  , those 70-80 different attribute and values will be passed to UI/Another Calling Program in the OUT parameter in the p_auto_policy_obj.


    This is OLTP env , the time taken for current approach , with the case statement is 6 sec. 

    and Reading the data from the table (70-80) rows and getting the result as OUT , 6 sec is problem for us.

    The Indexes on the table are fine and the select statement above does not take time , the bulk collect may not be needed to collect 70 record.however i have used it .


    I have to have pass those attributes(the result ) in the TYPE  auto_policy_obj , i.e i have to use the TYPE as OUT parameter.


    1. WHAT problem are you trying to solve?

    Ans : Execution time for selecting the data from table and passing the result it to a TYPE.

    The logic needs to be in PLSQL procedure so that can be used multiple times.

     

    2. WHY are you querying all of that data?

    Ans :  That is the requirement to read the data , and pass it to UI/Programs.

     

    3. HOW much data are we even talking about? Tens of rows? Hundreds of rows, Millions of rows?

    Ans :

    Sorry for not mentioning this .

    with one input parameter (composite Primary key) , 70-80 rows will be fetched from the table.  in another word the select statement above will always give 70-80 rows for one set of  input parameter.

     

    4. WHO is going to use the data that you now have in collections in that procedure code?

     

    Ans : As to reduce the time for reading the data , i have used collections , and collecting the data in one I/O .

     

    5. HOW is the data going to be used? Display on a web page? Produce a report? Export to a file?

    Ans :as the data will be passed to the TYPE , the TYPE will be used for input to other programs/UI .

     

     

     

     

     

     

     


  • 5. Re: Performance Tunning - Collections
    rp0428 Guru
    Currently Being Moderated

    You have now added an additional requirement that you never mentioned before. That illustrates why you needed to describe the problem instead of impose a solution.

     

    Now you are saying that the query needs to use data from the parameter and mention this:

    (p_auto_policy_obj  ,(its attributes ,6  )

    How is anyone supposed to know just what those attributes are? You didn't provide the type definition. And initially you said that was a SCALAR type

    Each Row value I want to assign it to attribute variable of a TYPE (POLICY_OBJECT).

    but now you say it is a COLLECTION type.

    Reading the data from the table (70-80) rows and getting the result as OUT

     

    Except that collection will only have ONE value that needs to be used in the query

    with one input parameter (composite Primary key)

    Now it seems you are passing some unknown object type that has ONE value to be used in a query but needs to return 70-80 sets of values from the query result.

     

    So the next thing you need to do is provide us with:

    1. the DDL for that object type

     

    2. sample data for 4 rows from the source table: two rows for "cod_campo='POLICY_NAME'" and two rows for 'POLICY_SECTOR'

     

    3. an example of an instance of that object type that shows the 'one input parameter' it contains when the procedure is called.

     

    4. an example of the data the SAME type instance will contain after it is populated.

     

    That will show how the 4 rows of source data are used to populate whatever it is that object type contains.

     

    No code - just simple data sets and explanation. Only the data columns that matter.

  • 6. Re: Performance Tunning - Collections
    Skp_Oracle Newbie
    Currently Being Moderated

    So the next thing you need to do is provide us with:

    1. the DDL for that object type

     

    2. sample data for 4 rows from the source table: two rows for "cod_campo='POLICY_NAME'" and two rows for 'POLICY_SECTOR'

     

    3. an example of an instance of that object type that shows the 'one input parameter' it contains when the procedure is called.

     

    4. an example of the data the SAME type instance will contain after it is populated.

     

    That will show how the 4 rows of source data are used to populate whatever it is that object type contains.

     

    No code - just simple data sets and explanation. Only the data columns that matter.

     

     

    --------------

     

     

    1 )

     

     

      TYPE  "auto_policy_obj"                                         

    (

    UNIQUE_ID                   INTEGER,

    POLICY_NUMBER               VARCHAR2(64),

    COMPANY_CODE                NUMBER,

    COMPANY_NAME                VARCHAR2(128),

    PRODUCT_CODE                NUMBER,

    POLICY_NAME                    VARCHAR(50),

    POLICY_NAME_CODE                    VARCHAR(2),

    PRODUCT_NAME                VARCHAR2(128),

    POLICY_NAME                VARCHAR2(128),

    SECTOR_CODE                 NUMBER,

    SECTOR_NAME                 VARCHAR2(128),

    EFFECTIVE_DATE              DATE, 

    EXPIRATION_DATE             DATE, 

    ---

    similar Data/Varchar2 field  ~20

    ---

    REPORT_ORDER         INTERFACE_ORDER_OBJ, 

    PAYMENT                PAYMENT_OBJ,

    SUMMARY                     POLICY_SUMMARY_OBJ,

    --

    Similar subtypes ~10

    --

    QUESTIONS_ANSWERS        UW_QUESTION_ANSWER_OBJ_TBL,

     

     

    CONSTRUCTOR FUNCTION POLICY_OBJ RETURN SELF AS RESULT

    ) NOT FINAL

    ;

     

     

    2 )

     

     

    Inputs are : COMPANY_CODE   POLICY_NUMBER  MOD_NUMBER     MOD_NUMBER _APP        VEH_NUMBER         PRODUCT_CODE

               

    COMPANY_CODE     POLICY_NUMBER  MOD_NUMBER        MOD_NUMBER _APP    NUM_SPTO_APLI         VEH_NUMBER     NUM_PERIODO   TIP_NIVEL    COD_CAMPO VAL_CAMPO      VAL_COR_CAMPO               NUM_SECU                TXT_CAMPO                PRODUCT_CODE

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    80      8001010000001      0      0      0      0      1      1      POLICY_NAME           1      1      2      OWNED AUTO           101         

    80      8001010000005      0      0      0      0      1      1      POLICY_NAME           1      1      2      OWNED AUTO           102

    80      8001010000001      0      0      0      0      1      1      POLICY_SECTOR       1       1      2      111                            104

    80      8001010000005      0      0      0      0      1      1      POLICY_SECTOR       1       1      2      123                            105    

     

     

     

     

    3 )

    this  6 input  the object POLICY_OBJECT  will be containing  rest of the field will be NULL,

     

     

    COMPANY_CODE   =80

    POLICY_NUMBER =8001010000001

    MOD_NUMBER     =0

    MOD_NUMBER _APP=0

    VEH_NUMBER                  =1 

    PRODUCT_CODE =101

     

    4)

     

     

    Once we retrieve the data from the table ,each field and its subtypes "p_auto_policy_obj "of the will be assigned the values based on the cod_campo (POLICY_NAME etc )

     

    i.e.

    p_auto_policy_obj .POLICY_NAME    =  table_a20(i).txt_campo .  (the text campo field ,as we are seleting it.)

    [so the attribute POLICY_NAME will be having value as OWNED AUTO  ]

    similarly

    p_auto_policy_obj .POLICY_NAME_CODE    =  table_a20(i).val_campo   (the val campo field ,as we are seleting it.)

     

     

    so the Txt_campo  and val_campo for each retrived record will  be assinged to diffrent attributes including the subtypes .i.e. the attributs of the subtypes of p_auto_policy_obj

     

    but in order to do the assingment as below

     

    p_auto_policy_obj.POLICY_NAME    =  table_a20(i).txt_campo

     

     

    First  i need to know table_a20(i).txt_campo is  blongs to row for which i have cod_campo as POLICY_NAME.

     

     

    i.e.

     

     

    CASE when COD_CAMPO='POLICY_NAME' THEN

    p_auto_policy_obj .POLICY_NAME    =  table_a20(i).txt_campo

    p_auto_policy_obj.POLICY_NAME_CODE    =  table_a20(i).val_campo

     

     

    WHEN  COD_CAMPO='POLICY_SECTOR'

     

     

    p_auto_policy_obj .SECTOR_NAME    =  table_a20(i).txt_campo

    p_auto_policy_obj .SECTOR_CODE     =  table_a20(i).val_campo

     

     

    I used CASE  , which i have to avoid.

    Once all the 70 similar assingments are done , end the procedure as p_auto_policy_obj  is out and we have assinged the value the calling program to this procedure would be getting the value of the 70 attributes in the p_auto_policy_obj  out parameter.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points