Timeline for Extraneous Unique Index in Oracle DB DDL
Current License: CC BY-SA 4.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 18 at 7:05 | comment | added | Wernfried Domscheit |
How did you generate the DDL? It could be set by options. Better check with SELECT TABLE_NAME, COLUMN_NAME, IN_ROW FROM USER_LOBS. Also be aware "enable" means the LOB can be stored inside the table. If the LOB is larger than 4k, then it is stored externally and the index is needed.
|
|
| Jan 9 at 15:13 | vote | accept | Nathan | ||
| Jan 6 at 15:50 | comment | added | Nathan | When I generate the DDL, I don't see STORAGE IN ROW or STORAGE IN ROW in the DDL for either DB. In fact, the DDL is identical for the table. What am I missing? | |
| Jan 6 at 12:17 | comment | added | Wernfried Domscheit |
@Nathan Oracle can store LOB inside the table or outside in a dedicated table. See LOB_parameters: ENABLE STORAGE IN ROW: If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information. This is the default. If you disable storage in row, then the LOB value is stored outside of the row - in this case the index is needed.
|
|
| Jan 5 at 21:55 | comment | added | Nathan | Thank you. I do have a LOB. I have 2 Oracle DBs with the same exact schema. One DB has this LOB index and the other DB does not. Why does the LOB index exist in one but not the other? Does it have to do with the table's data? | |
| Jan 5 at 21:48 | history | answered | Balazs Papp | CC BY-SA 4.0 |