Bug in `show_index`?

[kadrlica]

I think that there is something wrong with the output of show_index. For each indexed column, the query is repeating all other indexes (but not the index on that column).

Here is the query that easyaccess is using:

SELECT a.table_name, a.column_name, b.index_type, b.index_name, b.ityp_name 
FROM all_ind_columns a, all_indexes b
WHERE a.table_name LIKE 'Y1A1_IMAGE' 
AND a.table_name like b.table_name;

In comparison, here is a query that I modified from Brian Yanny:

SELECT dba_tables.table_name,dba_ind_columns.column_name,dba_indexes.index_name,
FROM dba_tables
JOIN dba_indexes on dba_indexes.table_name = dba_tables.table_name
JOIN dba_ind_columns ON dba_indexes.index_name = dba_ind_columns.index_name
WHERE dba_tables.table_name='Y1A1_EXPOSURE'
ORDER BY dba_tables.table_name,dba_indexes.index_name;

Here is the output of show_index on the Y1A1_EXPOSURE table:

DESDB ~> show_index y1a1_exposure;

30 rows in 0.51 seconds

       TABLE_NAME   COLUMN_NAME INDEX_TYPE                INDEX_NAME ITYP_NAME
1   Y1A1_EXPOSURE        EXPNUM     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
2   Y1A1_EXPOSURE        EXPNUM     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
3   Y1A1_EXPOSURE        EXPNUM     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
4   Y1A1_EXPOSURE        EXPNUM     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
5   Y1A1_EXPOSURE        EXPNUM     NORMAL          Y1A1_EXPOSURE_PK      None
6   Y1A1_EXPOSURE            ID     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
7   Y1A1_EXPOSURE            ID     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
8   Y1A1_EXPOSURE            ID     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
9   Y1A1_EXPOSURE            ID     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
10  Y1A1_EXPOSURE            ID     NORMAL          Y1A1_EXPOSURE_PK      None
11  Y1A1_EXPOSURE  EXPOSURENAME     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
12  Y1A1_EXPOSURE  EXPOSURENAME     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
13  Y1A1_EXPOSURE  EXPOSURENAME     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
14  Y1A1_EXPOSURE  EXPOSURENAME     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
15  Y1A1_EXPOSURE  EXPOSURENAME     NORMAL          Y1A1_EXPOSURE_PK      None
16  Y1A1_EXPOSURE        TELDEC     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
17  Y1A1_EXPOSURE        TELDEC     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
18  Y1A1_EXPOSURE        TELDEC     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
19  Y1A1_EXPOSURE        TELDEC     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
20  Y1A1_EXPOSURE        TELDEC     NORMAL          Y1A1_EXPOSURE_PK      None
21  Y1A1_EXPOSURE         TELRA     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
22  Y1A1_EXPOSURE         TELRA     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
23  Y1A1_EXPOSURE         TELRA     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
24  Y1A1_EXPOSURE         TELRA     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
25  Y1A1_EXPOSURE         TELRA     NORMAL          Y1A1_EXPOSURE_PK      None
26  Y1A1_EXPOSURE          NITE     BITMAP    Y1A1_EXPOSURE_NITE_BMX      None
27  Y1A1_EXPOSURE          NITE     NORMAL  Y1A1_EXPOSURE_EXPNUM_UTX      None
28  Y1A1_EXPOSURE          NITE     NORMAL   Y1A1_EXPOSURE_RADEC_IDX      None
29  Y1A1_EXPOSURE          NITE     NORMAL    Y1A1_EXPOSURE_NAME_BTX      None
30  Y1A1_EXPOSURE          NITE     NORMAL          Y1A1_EXPOSURE_PK      None

Below is the output from the index query I modified from Brian:

 SELECT dba_tables.table_name,dba_ind_columns.column_name,dba_indexes.index_name FROM dba_tables JOIN dba_indexes on dba_indexes.table_name = dba_tables.table_name JOIN dba_ind_columns ON dba_indexes.index_name = dba_ind_columns.index_name WHERE dba_tables.table_name='Y1A1_EXPOSURE' ORDER BY dba_tables.table_name,dba_indexes.index_name;  

6 rows in 0.69 seconds

      TABLE_NAME   COLUMN_NAME                INDEX_NAME
1  Y1A1_EXPOSURE        EXPNUM  Y1A1_EXPOSURE_EXPNUM_UTX
2  Y1A1_EXPOSURE  EXPOSURENAME    Y1A1_EXPOSURE_NAME_BTX
3  Y1A1_EXPOSURE          NITE    Y1A1_EXPOSURE_NITE_BMX
4  Y1A1_EXPOSURE            ID          Y1A1_EXPOSURE_PK
5  Y1A1_EXPOSURE         TELRA   Y1A1_EXPOSURE_RADEC_IDX
6  Y1A1_EXPOSURE        TELDEC   Y1A1_EXPOSURE_RADEC_IDX

[kadrlica]

Here is my suggested modification to the query here:

query_template = """
  SELECT tab.table_name,icol.column_name,idx.index_type,idx.index_name
  FROM dba_tables tab
  JOIN dba_indexes idx on idx.table_name = tab.table_name
  JOIN dba_ind_columns icol ON idx.index_name = icol.index_name
  WHERE tab.table_name='%s'
  ORDER BY icol.column_name,idx.index_name
"""

I've removed idx.ityp_name since I'm not sure what that column is supposed to display (it's output is always None as far as I've seen).

[kadrlica]

Some additional issues with the current (and also the past) implementation:

• This doesn't work for tables owned by other users. For example, querying show_index NSEVILLA.Y1A1_GOLD_1_0_1 returns nothing because the table is named Y1A1_GOLD_1_0_1 while the OWNER is NSEVILLA.
• There is something strange going on with the Y1A1_FINALCUT. It is supposed to be a SYNONYM to Y1A1_OBJECTS, but the indexes of Y1A1_OBJECTS are not displayed when you run show_index Y1A1_FINALCUT.