Results 1 to 4 of 4

Thread: Over partition

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Over partition

    Hello, actually I'm using over partition clause on my queries in my OLTP oracle database ?? is there any disadvantage (performance) or problem of using It?

    Example:

    SELECT manager_id, last_name, hire_date, salary,
    AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
    FROM employees;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When saying that, what do you exactly have in mind? Do you have another query which does the same? If so, the easiest way to measure which one is faster is viewing their explain plan.

    Without any documentation references or measuring results, I don't expect your code to have a negative performance influence.

    Nice people from Oracle corp. made such stuff in order to make our (developer's) lives less complicated. For example, why would you use this
    Code:
    SELECT ROWNUM rb, v.empno, v.ename
    FROM (SELECT e.empno, e.ename FROM EMP e ORDER BY ename) v;
    when you can use this:
    Code:
    SELECT row_number() over (ORDER BY ename) rb, empno, ename 
    FROM EMP;

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    Hello, I still have some doubts about Over partition usage, I have the next query (I'm going to Implement It on a production system) and I don't know which one is better for performance (all of them do the same):

    /********************************/
    /************WAY #1**************/
    /********************************/

    SELECT DISTINCT NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    NE.CDSUCURSAL,
    COUNT(NE.DNI_EMPLEADO) OVER( PARTITION BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL ORDER BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL) NMAFILIADOS,
    SUM(NVL(PTINGRESO_IBC_ESPERADO,0)) OVER( PARTITION BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL ORDER BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL) PTINGRESO_IBC,
    SUM(NVL(PTCOTIZACION_ESPERADO,0)) OVER( PARTITION BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL ORDER BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA,NE. CDSUCURSAL) PTCOTIZACION,
    SUM(NVL(PTCOTIZACION_ESPERADO,0)) OVER( PARTITION BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA ORDER BY NE.NMPERIODO,NE.NPOLIZA,NE.CDSUCURSAL_PAGADORA) TOTAL_COT_CTP
    FROM T094_NOVEDADES_ESPERADAS NE
    WHERE NE.NMPERIODO = '200602'
    ;


    /********************************/
    /************WAY #2**************/
    /********************************/

    SELECT CTP.NMPERIODO,
    CTP.NPOLIZA,
    CTP.CDSUCURSAL_PAGADORA,
    CT.NMAFILIADOS,
    CT.PTINGRESO_IBC,
    CT.PTCOTIZACION,
    CTP.TOTAL_COT_CTP
    FROM ( SELECT NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    SUM(NVL(PTCOTIZACION_ESPERADO,0)) TOTAL_COT_CTP
    FROM T094_NOVEDADES_ESPERADAS NE
    WHERE NE.NMPERIODO = '200602'
    GROUP BY NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA ) CTP,
    (SELECT NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    NE.CDSUCURSAL,
    COUNT(NE.DNI_EMPLEADO) NMAFILIADOS,
    SUM(NVL(PTINGRESO_IBC_ESPERADO,0)) PTINGRESO_IBC,
    SUM(NVL(PTCOTIZACION_ESPERADO,0)) PTCOTIZACION
    FROM T094_NOVEDADES_ESPERADAS NE
    WHERE NE.NMPERIODO = '200602'
    GROUP BY NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    NE.CDSUCURSAL ) CT
    WHERE CT.CDSUCURSAL_PAGADORA = CTP.CDSUCURSAL_PAGADORA
    AND CT.NPOLIZA = CTP.NPOLIZA
    AND CT.NMPERIODO = CTP.NMPERIODO ;

    /********************************/
    /************WAY #3**************/
    /********************************/

    SELECT NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    NE.CDSUCURSAL,
    COUNT(NE.DNI_EMPLEADO) NMAFILIADOS,
    SUM(NVL(PTINGRESO_IBC_ESPERADO,0)) PTINGRESO_IBC,
    SUM(NVL(PTCOTIZACION_ESPERADO,0)) PTCOTIZACION,
    (SELECT SUM(NVL(PTCOTIZACION_ESPERADO,0))
    FROM T094_NOVEDADES_ESPERADAS NEE
    WHERE NEE.CDSUCURSAL_PAGADORA = NE.CDSUCURSAL_PAGADORA
    AND NEE.NPOLIZA = NE.NPOLIZA
    AND NEE.NMPERIODO = NE.NMPERIODO ) TOTAL_COT_CTP
    FROM T094_NOVEDADES_ESPERADAS NE
    WHERE NE.NMPERIODO = '200602'
    GROUP BY NE.NMPERIODO,
    NE.NPOLIZA,
    NE.CDSUCURSAL_PAGADORA,
    NE.CDSUCURSAL ;

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I'm afraid the only person who can answer your question is yourself. Oracle performance tuning is based on so may variables i.e. you hardware, OS, oracle version, init.ora params, your tables and indexes, data distribution etc. that the only way to tune is to stick tracing on and see how much work your database has to do for each of your queries. Remember to execute each query atleast twice to elimanate the buffer cache on your PRODUCTION system (unless your test system is identical in every way).

    Also dont forget to look at what indexes you have, ensure the stats are upto date and watch out for inaccurate stats.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •