This will not necessarily return the same result as the original query. Depends on whether nro_facturacion can be null or not. mvg/regards Jo Juan Cachito Reyes Pacheco <jreyes@xxxxxxxxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 03/11/2004 15:51 Please respond to oracle-l To: oracle-l@xxxxxxxxxxxxx cc: Subject: Re: Oracle Performance And is better to do a SELECT COUNT(*) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1' ----- Original Message ----- From: Huascar Espinoza To: oracle-l@xxxxxxxxxxxxx Cc: VChoque Sent: Thursday, March 11, 2004 9:35 AM Subject: RE: Oracle Performance To realize the test, we have the basic following structure (similar on Oracle 9i and SQL Server 7.0): · Table "SC_FACTURACION" have the following indexes: COD_IMPORTACION NRO_FACTURACION COD_FACTURA_ESTIMADA COD_FACTURA_REFACTURADA COD_MOTIVO_ESTIMACION COD_NIVEL_CALIDAD. · Table "GE_HISTORICO_IMPORTACION" has 30 registers. · Table "SC_FACTURACION" has 1,885,618 registers. On 'SC_FACTURACION' we execute the following SQL sentences: 1. SELECT COUNT(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1' 2. SELECT MAX(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1' 3. SELECT MIN(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1' The results are: Sentence Performance ORACLE 9i Performance MS SQL Server 7.0 1 2 minutes 20 secs. 2 2 minutes 1 sec. 3 50 secs. 1 secs. The servers are the followins features: ORACLE 9i Server Processor Intel Pentium IV - 2.4 GHz RAM Memory 512 MB S SQL Server 7.0 Server Processor Intel Celeron 2.4 GHz RAM Memory 380 MB Please let me know, why the performance of Oracle is so low. Thank you! Huáscar Espinoza -----Mensaje original----- De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En nombre de DENNIS WILLIAMS Enviado el: Jueves 11 de Marzo de 2004 9:16 Para: 'oracle-l@xxxxxxxxxxxxx' Asunto: RE: Oracle Performance Huascar Normal? No. In order to help diagnose your situation, please post more details. Like your query, the sizes of your tables, etc. Have you analyzed the tables in Oracle? Dennis Williams DBA Lifetouch, Inc. dwilliams@xxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Huascar Espinoza Sent: Thursday, March 11, 2004 7:04 AM To: oracle-l@xxxxxxxxxxxxx Subject: Oracle Performance ¿Is it normal that a SQL sentence: MAX or MIN, execute over 1.5 minutes on Oracle 9i, and in 1 sec. on SQL Server 7.0 (2 millions of registers), using equals data structures and servers? ¿Why occur this situation? Thank you, Huascar Espinoza ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------