registros - oracle pivot subquery

¿Cómo insertar una columna en una posición específica en oracle sin soltar y recrear la tabla?

Question

Tengo un escenario específico en el que tengo que insertar dos nuevas columnas en una tabla existente en Oracle. No puedo hacer caer y recrear la tabla. Entonces, ¿puede lograrse por cualquier medio?

Answer 1

Amit-

No creo que pueda agregar una columna en cualquier lugar, pero al final de la tabla una vez que se crea la tabla. Una solución podría ser intentar esto:

CREATE TABLE MY_TEMP_TABLE AS
SELECT *
FROM TABLE_TO_CHANGE;

Suelte la tabla a la que desea agregar columnas:

DROP TABLE TABLE_TO_CHANGE;

Es en el punto en el que puede reconstruir la tabla existente desde cero agregando las columnas donde desee. Supongamos que para este ejercicio desea agregar las columnas denominadas "COL2 y COL3".

Ahora inserte los datos de nuevo en la nueva tabla:

INSERT INTO TABLE_TO_CHANGE (COL1, COL2, COL3, COL4) 
SELECT COL1, ''Foo'', ''Bar'', COL4
FROM MY_TEMP_TABLE;

Cuando los datos se insertan en la tabla "new-old", puede eliminar la tabla temporal.

DROP TABLE MY_TEMP_TABLE;

Esto es a menudo lo que hago cuando quiero agregar columnas en una ubicación específica. Obviamente, si este es un sistema de producción en línea, entonces probablemente no sea práctico, sino solo una idea potencial.

-CJ

Answer 2

Aunque esto es algo antiguo, me gustaría agregar una versión ligeramente mejorada que realmente cambie el orden de las columnas. Aquí están los pasos (asumiendo que tenemos una tabla TAB1 con las columnas COL1, COL2, COL3):

1. Agregar nueva columna a la tabla TAB1:

alter table TAB1 add (NEW_COL number);

1. "Copie" la tabla al nombre temporal mientras cambia el orden de las columnas Y renombra la nueva columna:

create table tempTAB1 as select NEW_COL as COL0, COL1, COL2, COL3 from TAB1;

1. soltar la tabla existente:

drop table TAB1;

1. renombrar temp nombre de tabla a solo nombre de tabla eliminado:

rename tempTAB1 to TAB1;

Answer 3

En 12c puede hacer uso del hecho de que las columnas que se configuran de invisible a visible se muestran como la última columna de la tabla: Consejos y trucos: columnas invisibles en la base de datos Oracle 12c

Tal vez ese es el ''truco'' que @ jeffrey-kemp estaba hablando en su comentario, pero el enlace no funciona más.

Ejemplo:

ALTER TABLE my_tab ADD (col_3 NUMBER(10));
ALTER TABLE my_tab MODIFY (
  col_1 invisible,
  col_2 invisible
);
ALTER TABLE my_tab MODIFY (
  col_1 visible,
  col_2 visible
);

Ahora col_3 se mostrará primero en una SELECT * FROM my_tab .

Nota: Esto no cambia el orden físico de las columnas en el disco, pero en la mayoría de los casos no es lo que quiere hacer de todos modos. Si realmente desea cambiar el orden físico, puede usar el paquete DBMS_REDEFINITION.

Answer 4

Usted (aún) no puede elegir la posición de la columna utilizando ALTER TABLE: solo se puede agregar al final de la tabla. Obviamente, puede seleccionar las columnas en el orden que desee, de modo que, a menos que esté utilizando SELECT * FROM, el orden de las columnas no debería ser un gran problema.

Si realmente debe tenerlos en un orden particular y no puede eliminar y volver a crear la tabla, entonces podría ser capaz de eliminar y volver a crear columnas:

Primero copia la tabla

CREATE TABLE my_tab_temp AS SELECT * FROM my_tab;

Luego suelte las columnas que desea que estén después de la columna que insertará

ALTER TABLE my_tab DROP COLUMN three;

Ahora agregue la nueva columna (dos en este ejemplo) y las que eliminó.

ALTER TABLE my_tab ADD (two NUMBER(2), three NUMBER(10));

Por último, vuelva a agregar los datos para las columnas recreadas.

UPDATE my_tab SET my_tab.three = (SELECT my_tab_temp.three FROM my_tab_temp WHERE my_tab.one = my_tab_temp.one);

Obviamente, su actualización probablemente será más compleja y tendrá que manejar índices y restricciones y no podrá usar esto en algunos casos (columnas LOB, etc.). Además, esta es una forma bastante horrible de hacer esto, pero la tabla siempre existirá y terminará con las columnas en el orden que desee. Pero, ¿el orden de las columnas realmente importa tanto?