sql - usar - uso de indices en oracle
Oracle 12c: ¿el índice en una columna de ''número'' se ejecuta más rápido que el índice en la columna ''varchar''? (1)
[TL; DR] Use fechas para almacenar fechas, números para almacenar números y cadenas para almacenar cadenas.
¿Qué tal el uso de recursos?
Oracle almacena el tipo de datos NUMBER
como 1 byte por 2 dígitos.
Oracle almacena el tipo de datos CHAR
como 1 byte por carácter ASCII (UTF-8 y otras codificaciones pueden tomar más para los caracteres en conjuntos extendidos) y rellenará la cadena con caracteres de espacio para que las cadenas tengan exactamente la misma longitud.
Oracle almacena el tipo de datos VARCHAR2
como 1 byte por carácter ASCII más una pequeña sobrecarga (1 o 2 bytes) para la longitud de la cadena.
Oracle almacena el tipo de datos DATE
como 7 bytes (2 por año y 1 por cada mes, día, hora, minuto, segundo).
Según su pregunta anterior , parece que está almacenando year
y quarter
y suponiendo que siempre tendrá años de 4 dígitos y trimestres de 1 dígito, entonces:
-
NUMBER(5,0)
tomaría 3 bytes; -
CHAR(5 CHARACTER)
tomaría 5 bytes; -
VARCHAR2(5 CHARACTER)
tomaría 6 bytes; y -
DATE
tomaría 7 bytes.
Entonces, solo teniendo en cuenta la memoria, un NUMBER(5,0)
sería el más eficiente.
sin embargo
Tan pronto como comienzas a hacer aritmética en el año / trimestres almacenados como números / cadenas, entres en problemas de rendimiento:
Por ejemplo, obtener el próximo trimestre:
- Si
quarter
es un tipo de datosNUMBER
, entonces puede usar:CASE WHEN MOD(quarter,10) = 4 THEN quarter + 7 ELSE quarter + 1 END
pero esto no funciona cuando desea agregar 5 trimestres o comenzar a restar trimestres y luego la lógica comienza a ser mucho más complicada. - Si
quarter
es un tipo de datosCHAR
, puede convertirlo a un número o una fecha y usar cualquiera de esos métodos (la manipulación de cadenas probablemente no sea efectiva). - Si
quarter
es unDATE
, solo necesita usarADD_MONTHS( quarter, 3 )
.
El método DATE
es autodocumentado y ya existe, mientras que el método NUMBER
se convertiría en una función personalizada para su aproximación de un tipo de datos QUARTER
y una vez que implemente todas las funciones de comparación y manipulación que necesita, habrá reescrito efectivamente el tipo de datos DATE
como un UDT para trimestres y esas funciones serán menos eficaces que las funciones de fecha optimizadas.
No use tipos de datos inapropiados, solo almacene las fechas como fechas; números como números; y cuerdas como cuerda.
Digamos que tengo una tabla en Oracle 12c con columnas:
create table t1 (
a number (5,0),
b varchar (5,0)
d ...
e ...
);
Luego inserto 100,000,000 registros en ambas columnas que tienen los mismos valores, por ejemplo
20151 and ''20152'' ... (for a first record)
20152 and ''20152'' ... (for a second record)
20153 and ''20153'' ... (for a third record)
...
Luego agrego el índice 1 en la columna ''a''
y el índice 2 en la columna ''b''.
La pregunta es: ¿la consulta funcionaría igual de rápido al ejecutar contra la columna ''a''
que en la columna ''b''
(por ejemplo, join
consulta con otra tabla basada en una columna ''a''
o basada en una columna ''b''
o cláusula WHERE
en cualquiera de columnas)?
Además, ¿usaría index en una columna '' varchar
''? ¿ varchar
más CPU que usando index en una columna ''number''?
Gracias.