ORACLE Optimize queries with not exists and dblink

Question

ORACLE Optimize queries with not exists and dblink

Navigation

#1 by (1 votes)

1

With the not exists it takes 1 min.

    SELECT
    PED.NFCH         "NFCH",
    PED.NFCHF         "NFCHF",   
    PED.CNL         "CNL",
    PED.CLI         "CLI",
    PED.GRC       "GRC"
    FROM
    PE@DBV PED
    WHERE 
    PED.NFCH =  20160201 AND  ---P_FECHA
    PED.CNL = 1 and  -- P_CEDI
    PED.CLI <> 0
    and NOT EXISTS(SELECT 1 FROM CLIENTE CLI WHERE CLI.IN_INTER = 'AG' AND CLI.CODIGO = PED.CLI) 
    GROUP BY 
    PED.NFCH ,
    PED.NFCHF ,      
    PED.CNL,
    PED.CLI ,        
    PED.GRC 

    Pero sin el  not exists se demora 3 segundos

      SELECT
    PED.NFCH         "NFCH",
    PED.NFCHF         "NFCHF",   
    PED.CNL         "CNL",
    PED.CLI         "CLI",
    PED.GRC       "GRC"
    FROM
    PE@DBV PED
    WHERE 
    PED.NFCH =  20160201 AND  ---P_FECHA
    PED.CNL = 1 and  -- P_CEDI
    PED.CLI <> 0
    --and NOT EXISTS(SELECT 1 FROM CLIENTE CLI WHERE CLI.IN_INTER = 'AG' AND CLI.CODIGO = PED.CLI) 
    GROUP BY 
    PED.NFCH ,
    PED.NFCHF ,      
    PED.CNL,
    PED.CLI ,        
    PED.GRC

oracle

asked by Alejandro Soler 02.02.2016 в 15:25

source

1 answer

Entity Framework can not find 'System.Data.SqlClient' Problem with the property date of an object that changed from AJAX to a Web Service

score 1 · Accepted Answer

To optimize the not exists, do it using a left join and an is null filter. as follows.

SELECT
    PED.NFCH         "NFCH",
    PED.NFCHF         "NFCHF",   
    PED.CNL         "CNL",
    PED.CLI         "CLI",
    PED.GRC       "GRC"
    FROM
    PEDIDO@DBVNSLLNG PED, CLI CLI
    WHERE 
    PED.CLI = CLI.CODIGO (+) AND
    PED.NFCH =  20160201 AND  ---P_FECHA
    PED.CNL = 1 and  -- P_CEDI
    PED.CLI <> 0 AND
    CLI.CODIGO IS NULL
    GROUP BY 
    PED.NFCH ,
    PED.NFCHF ,      
    PED.CNL,
    PED.CLI ,        
    PED.GRC