How I can determine whether table, index or column statistics are missing?
During its statistics runs, BRCONNECT checks only the tables and index tables. This means that missing column statistics can no longer be created over a long period of time. In addition, up until Release 6.20, BRCONNECT created statistics only for indexed columns. In the meantime, however, it is advisable to create statistics for all columns (Note 723894). The following SQL statements allow you to determine the missing statistics manually: * Indexes with missing statistics: SELECT T.TABLE_NAME, I.INDEX_NAME FROM DBA_TABLES T, DBA_INDEXES I WHERE I.OWNER = T.OWNER AND I.TABLE_NAME = T.TABLE_NAME AND T.LAST_ANALYZED IS NOT NULL AND I.LAST_ANALYZED IS NULL AND INDEX_TYPE != ‘LOB’; * Indexed columns with missing statistics: SELECT SUBSTR(T.OWNER, 1, 15) OWNER, SUBSTR(T.TABLE_NAME, 1, 30) TABLE_NAME, SUBSTR(TC.COLUMN_NAME, 1, 15) COLUMN_NAME, T.NUM_ROWS NUM_ROWS FROM DBA_TABLES T, DBA_TAB_COLUMNS TC, DBA_IND_COLUMNS IC WHERE T.OWNER = IC.TABLE_OWNER AND TC.OWNER = IC.TABLE_OWNER AND T.TABLE
Related Questions
- I followed the example in Help, but I couldn get a table sorted by the Client Name, for example. Maybe, Im missing something in the Secondary Index field at the Database Manager?
- I have a table partitioned on column timestamp, with a primary key on column id. How do I create a locally partitioned index for this PK ?
- See Note 806554.28. How I can determine whether table, index or column statistics are missing?
