oracle - vacio - SELECCIONAR COUNT(*) frente a ir a buscar dos veces con un cursor explícito
recorrer cursor oracle (8)
Dependiendo de la base de datos, puede haber una tabla de sistema que almacena un recuento aproximado y puede consultarse en tiempo constante. Útil si quiere saber si la tabla tiene 20 filas o 20,000 o 20,000,000.
He leído un libro cuyo título es "Oracle PL SQL Programming" (2nd ed.) Por Steven Feuerstein y Bill Pribyl. En la página 99, hay un punto sugerido
No "SELECCIONE CUENTA (*)" de una tabla a menos que realmente necesite saber la cantidad total de "aciertos". Si solo necesita saber si hay más de una coincidencia, simplemente busque dos veces con un cursor explícito.
¿Podría alguien explicarme más este punto al dar un ejemplo? Gracias.
Actualizar:
Como Steven Feuerstein y Bill Pribyl nos recomiendan que no usemos SELECT COUNT () para verificar si existen o no registros en una tabla, ¿alguien podría ayudarme a editar el código a continuación para evitar usar SELECT COUNT (*) usando un cursor explícito? Este código está escrito en el procedimiento almacenado de Oracle.
Tengo una tabla emp (emp_id, emp_name, ...), para verificar la correlación de ID de empleado proporcionada o no:
CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
...
SELECT COUNT(*) INTO v_rows
FROM emp
WHERE emp_id = emp_id_in;
IF v_rows > 0 THEN
/* do sth */
END;
/* more statements */
...
END do_sth;
Servidor SQL:
if 2 = (
select count(*) from (
select top 2 * from (
select T = 1 union
select T = 2 union
select T = 3 ) t) t)
print ''At least two''
Además, nunca uses cursores. Si crees que realmente los necesitas, golpéate con una pala hasta que cambies de opinión. Deja que las reliquias de un pasado antiguo sigan siendo reliquias de un pasado antiguo.
Si dos es todo lo que le interesa, intente
SELECT ''THERE ARE AT LEAST TWO ROWS IN THE TABLE''
FROM DUAL
WHERE 2 =
(
SELECT COUNT(*)
FROM TABLE
WHERE ROWNUM < 3
)
Tomará menos código que haciendo el método de cursor manual, y es probable que sea más rápido.
El truco de rownum significa dejar de buscar filas una vez que tiene dos de ellas.
Si no pone algún tipo de límite en el recuento (*), podría tomar un tiempo largo para terminar, dependiendo de la cantidad de filas que tenga. En ese caso, usar un bucle de cursor para leer 2 filas de la tabla manualmente sería más rápido.
Si desea obtener el número de filas en una tabla, no use count (*), le sugiero contar (0) que 0 es el índice de columna de su columna de clave principal.
Antes de tomar las sugerencias de Steven Feuerstein demasiado en serio, solo haga un pequeño punto de referencia. ¿Es el recuento (*) notablemente más lento que el cursor explícito en su caso? ¿No? Entonces mejor use la construcción que permite un código simple y legible. Que, en la mayoría de los casos, sería "select count (*) into v_cnt ... if v_cnt> 0 then ..."
PL / SQL permite programas muy legibles. No desperdicies eso solo para nanoptimizar.
Hay una serie de razones por las cuales los desarrolladores pueden realizar seleccionar COUNT (*) de una tabla en un programa PL / SQL:
1) Realmente necesitan saber cuántas filas hay en la tabla.
En este caso, no hay otra opción: seleccione COUNT (*) y espere el resultado. Esto será bastante rápido en muchas tablas, pero podría tomar un tiempo en una gran mesa.
2) Solo necesitan saber si existe una fila o no.
Esto no garantiza el conteo de todas las filas en la tabla. Varias técnicas son posibles:
a) Método de cursor explícito:
DECLARE
CURSOR c IS SELECT ''1'' dummy FROM mytable WHERE ...;
v VARCHAR2(1);
BEGIN
OPEN c;
FETCH c INTO v;
IF c%FOUND THEN
-- A row exists
...
ELSE
-- No row exists
...
END IF;
END;
b) método SELECT INTO
DECLARE
v VARCHAR2(1);
BEGIN
SELECT ''1'' INTO v FROM mytable
WHERE ...
AND ROWNUM=1; -- Stop fetching if 1 found
-- At least one row exists
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- No row exists
END;
c) SELECCIONAR COUNT (*) con el método ROWNUM
DECLARE
cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM mytable
WHERE ...
AND ROWNUM=1; -- Stop counting if 1 found
IF cnt = 0 THEN
-- No row found
ELSE
-- Row found
END IF;
END;
3) Necesitan saber si existe más de 1 fila.
Variaciones en las técnicas para (2) trabajo:
a) Método de cursor explícito:
DECLARE
CURSOR c IS SELECT ''1'' dummy FROM mytable WHERE ...;
v VARCHAR2(1);
BEGIN
OPEN c;
FETCH c INTO v;
FETCH c INTO v;
IF c%FOUND THEN
-- 2 or more rows exists
...
ELSE
-- 1 or 0 rows exist
...
END IF;
END;
b) método SELECT INTO
DECLARE
v VARCHAR2(1);
BEGIN
SELECT ''1'' INTO v FROM mytable
WHERE ... ;
-- Exactly 1 row exists
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- No row exists
WHEN TOO_MANY_ROWS THEN
-- More than 1 row exists
END;
c) SELECCIONAR COUNT (*) con el método ROWNUM
DECLARE
cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM mytable
WHERE ...
AND ROWNUM <= 2; -- Stop counting if 2 found
IF cnt = 0 THEN
-- No row found
IF cnt = 1 THEN
-- 1 row found
ELSE
-- More than 1 row found
END IF;
END;
El método que usas es en gran medida una cuestión de preferencia (¡y un poco de fanatismo religioso!) Steven Feuerstein siempre ha favorecido los cursores explícitos sobre los loops implícitos (SELECT INTO y cursor FOR); Tom Kyte favorece los cursores implícitos (y estoy de acuerdo con él).
El punto importante es que seleccionar COUNT (*) sin restringir el ROWCOUNT es costoso y, por lo tanto, solo debe hacerse cuando realmente se necesita un recuento.
En cuanto a su pregunta complementaria sobre cómo volver a escribir esto con un cursor explícito:
CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
...
SELECT COUNT(*) INTO v_rows
FROM emp
WHERE emp_id = emp_id_in;
IF v_rows > 0 THEN
/* do sth */
END;
/* more statements */
...
END do_sth;
Eso sería:
CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
CURSOR c IS SELECT 1
FROM emp
WHERE emp_id = emp_id_in;
v_dummy INTEGER;
BEGIN
...
OPEN c;
FETCH c INTO v_dummy;
IF c%FOUND > 0 THEN
/* do sth */
END;
CLOSE c;
/* more statements */
...
END do_sth;
Pero realmente, en su ejemplo, no es mejor o peor, ya que está seleccionando la clave principal y Oracle es lo suficientemente inteligente como para saber que solo necesita recuperar una vez.
Esto proviene de programadores que escriben código similar al siguiente (¡esto es código psuedo!).
Desea verificar si el cliente tiene más de un pedido:
if ((select count(*) from orders where customerid = :customerid) > 1)
{
....
}
Esa es una manera terriblemente ineficiente de hacer cosas. Como diría Mark Brady , si quieres saber si un frasco contiene monedas de un centavo, ¿contarías todos los centavos en el frasco o solo te asegurarías de que haya 1 (o 2 en tu ejemplo)?
Esto podría escribirse mejor como:
if ((select 1 from (select 1 from orders where customerid = :customerid) where rownum = 2) == 1)
{
....
}
Esto evita el dilema de "contar todas las monedas", ya que Oracle buscará 2 filas y luego finalizará. El ejemplo anterior provocaría que Oracle escaneara (un índice o una tabla) para TODAS las filas y luego finalizara.
Quiere decir abrir un cursor y buscar no solo el primer registro sino el segundo, y entonces sabrá que hay más de uno.
Como nunca parece necesitar saber que SELECT COUNT(*) es >= 2 , no tengo idea de por qué esta es una expresión útil en cualquier variante de SQL. O bien no hay registros o al menos uno, claro, pero no dos o más. Y de todos modos, siempre hay EXISTS .
Eso, y el hecho de que el optimizador de Oracle parece ser bastante pobre ... - cuestionaría la relevancia de la técnica.
Para abordar los comentarios de TheSoftwareJedi:
WITH CustomersWith2OrMoreOrders AS (
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) >= 2
)
SELECT Customer.*
FROM Customer
INNER JOIN CustomersWith2OrMoreOrders
ON Customer.CustomerID = CustomersWith2OrMoreOrders.CustomerID
Asignado adecuadamente, nunca he tenido problemas de rendimiento incluso con consultas de todo el universo como esta en SQL Server. Sin embargo, me he encontrado constantemente con comentarios sobre los problemas del optimizador de Oracle aquí y en otros sitios.
Mi propia experiencia con Oracle no ha sido buena .
El comentario de OP parece indicar que el COUNT(*) de las tablas no está bien manejado por el optimizador. es decir:
IF EXISTS (SELECT COUNT(*) FROM table_name HAVING COUNT(*) >= 2)
BEGIN
END
(que, cuando existe una clave principal, puede reducirse a una exploración de índice simple; en un caso de optimización extrema, uno puede simplemente consultar los metadatos de índice en sysindexes.rowcnt, para encontrar el número de entradas, todo sin un cursor) es generalmente se evita a favor de:
DECLARE CURSOR c IS SELECT something FROM table_name;
BEGIN
OPEN c
FETCH c INTO etc. x 2 and count rows and handle exceptions
END;
IF rc >= 2 THEN BEGIN
END
Eso, para mí resultaría en un código menos legible, menos portátil y menos sostenible.