Menu
I have a query it takes 20 seconds to execute, follow my query:
SELECT MATLIGA.COD_MAT_FAMILIA
FROM
ORCAMENTOS.dbo.OR_1INSUMOS INSUMOS
INNER JOIN ORCAMENTOS.dbo.OR_MAT_GRUPOS GRUPOS ON (GRUPOS.EMPRESA='01' AND GRUPOS.FILIAL='01' AND GRUPOS.CODIGO_INTERNO = 'HOT' )
INNER JOIN ORCAMENTOS.dbo.OR_MATERIAIS MATER ON (MATER.EMPRESA='01' AND MATER.FILIAL='01' AND MATER.CODIGO_GRUPO=GRUPOS.ID AND MATER.ID = INSUMOS.COD_INSUMO_MATER )
INNER JOIN ORCAMENTOS.dbo.OR_MAT_LIGACAO MATLIGA ON (MATLIGA.EMPRESA='01' AND MATLIGA.FILIAL='01' AND MATLIGA.CODIGO_MATERIAL = INSUMOS.COD_INSUMO_MATER)
WHERE INSUMOS.EMPRESA='01' AND INSUMOS.FILIAL='01'
AND INSUMOS.COD_INSUMO_MATER IS NOT NULL
AND INSUMOS.NUMERO=10865812
AND INSUMOS.OPCAO_SIMULACAO=1
AND INSUMOS.CODIGO_MAQUINA = (SELECT COD_MAQ_PROPOSTA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' )
AND INSUMOS.OPCAO_MAQUINA = (SELECT OPCAO_MAQUINA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' )
GROUP BY MATLIGA.COD_MAT_FAMILIA
ORDER BY 1
In these two lines bellow, if I change the equal signal by (IN), ( = ALL ) or ( = ANY ) it reduces the costs to 1 second.
AND INSUMOS.CODIGO_MAQUINA IN (SELECT COD_MAQ_PROPOSTA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' )
AND INSUMOS.OPCAO_MAQUINA IN (SELECT OPCAO_MAQUINA FROM ORCAMENTOS.dbo.OR_1SIMULACOES AS ORC WHERE ORC.NUMERO=10865812 AND ORC.OPCAO_SIMULACAO = 1 AND ORC.EMPRESA='01' AND ORC.FILIAL='01' )
Whats the difference between them?
Tks.
385
The main difference between with clause and a subquery in Oracle is that you can reference a query within the clause multiple times. You can then do some optimizations with it like turning it into a temp table using materialize hint. You can also do recursive queries with it by referencing itself inside a with clause.
Nested Subqueries Versus Correlated Subqueries : With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query.
If the SELECT subquery returns exactly one value, the comparison for inequality should return the result you expect. If the SELECT subquery returns more than one value, you should get an error. In general, NOT IN will return the result you expect when you are testing for non membership in a set.
There is a small semantic difference. The first query must fail if the subquery matches more than one record. So it has to finish the subquery until the end:
where col1 = (select col1 from table2)
The second query can stop once it encounters a match:
where col1 in (select col1 from table2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
