una - manejo de indices en sql
¿Necesito crear índices en claves externas? (6)
Como con cualquier cosa relacionada con el rendimiento, depende de muchos factores y no hay una bala de plata, por ejemplo, en un entorno de actividad muy alto, el mantenimiento de un índice puede ser inaceptable.
Aquí lo más sobresaliente parece ser la selectividad: si los valores en el índice estuvieran altamente duplicados, entonces podría ofrecer un mejor rendimiento para soltar el índice (si es posible) y permitir un escaneo de tabla.
Tengo una tabla A y una tabla B A tiene una clave externa a B en la clave primaria de B , B_ID .
Por alguna razón (sé que hay razones legítimas), no estoy usando un índice cuando uniera estas dos tablas en la clave.
¿Debo crear un índice por A.B_ID en A.B_ID o debería existir la existencia de una clave externa?
La creación de una clave externa no crea automáticamente un índice en A.B_ID. Por lo tanto, desde una perspectiva de rendimiento de la consulta, en general tendría sentido crear un índice separado en A.B_ID.
Si alguna vez elimina filas en B, definitivamente quiere que A.B_ID sea indexado. De lo contrario, Oracle tendrá que realizar un escaneo completo de la tabla en A cada vez que elimine una fila de B para asegurarse de que no haya registros huérfanos (según la versión de Oracle, también puede haber implicaciones de bloqueo adicionales, pero éstas disminuyen) en versiones más recientes de Oracle).
La restricción de clave externa por sí sola no proporciona el índice; se debe (y se debe) crear.
Por razones de rendimiento, se debe crear un índice. Se utiliza en operaciones de eliminación en la tabla principal (para verificar que el registro que está eliminando no se utiliza) y en combinaciones que generalmente involucran una clave externa. Solo algunas tablas (no las creo en registros) podrían ser que no necesitan el índice, pero probablemente, en este caso, probablemente tampoco necesites la restricción de clave externa.
PERO
Hay algunas bases de datos que ya crean automáticamente índices en claves extranjeras. Jet Engine (Archivos de acceso de Microsoft) Firebird MySQL
SIN LUGAR A DUDA
SQL Server Oracle
NO
SQL Server nunca ha puesto índices en columnas de claves externas de forma automática; echa un vistazo a la excelente publicación de blog de Kim Tripp sobre los antecedentes y la historia de este mito urbano.
Sin embargo, generalmente es una buena idea indexar las columnas de clave externa, así que sí, recomendaría asegurar que cada columna de FK esté respaldada por un índice; no necesariamente en esa sola columna, tal vez tenga sentido crear un índice en dos o tres columnas con la columna FK como la primera en ese lugar. Depende de su escenario y sus datos.
Solo para obtener más información: Oracle no crea un índice automáticamente (como lo hace para las restricciones únicas) porque (a) no es necesario para hacer cumplir la restricción, y (b) en algunos casos no necesita uno.
La mayoría de las veces, sin embargo, querrá crear un índice (de hecho, en Oracle Apex hay un informe de "claves externas no indexadas").
Siempre que la aplicación necesite poder eliminar una fila en la tabla padre, o actualizar el valor PK (que es más raro), el DML sufrirá si no existe un índice, ya que tendrá que bloquear toda la tabla hija.
Un caso en el que generalmente elijo no agregar un índice es cuando el FK está en una tabla de "datos estáticos" que define el dominio de una columna (por ejemplo, una tabla de códigos de estado), donde las actualizaciones y eliminaciones nunca se hacen directamente por la aplicación. Sin embargo, si al agregar un índice en la columna se obtienen beneficios para consultas importantes en la aplicación, el índice seguirá siendo una buena idea.