Remember that the INCLUDE_COLUMN of the USER_INDEXES view gives you the last column included in the index and so in the top segment, so joining this view with the USER_TAB_COLUMNS you can separate the columns that are in the top segment from those that are in the overflow one as with the following statement:
SQL> SELECT c.table_name, c.column_name,
2 CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment
3 FROM user_tab_columns c, user_indexes i
4 WHERE i.table_name = c.table_name
5 ORDER by table_name, column_id;
TABLE_NAME COLUMN_NAME SEGMENT
------------------------------ ------------------------------ --------
MY_IOT ID TOP
MY_IOT VALUE TOP
MY_IOT COMMENTS OVERFLOW
No comments:
Post a Comment