Hi All,
II 10.0.0 (a64.lnx/132)NPTL + patch14122
I have a database in which the attempt to optimize or statdump a specific table will fail with the message:
E_OP0930 optimizedb: database 't_ldb15', table 'cryovial_store' is not a base table and will be ignored.
Typically this message is associated with attempting to optimize/statdump something that is not a table.
In this case the table in question is defined as btree, unique, nocompression…it is a table.
The table name is unique in the entire database…select count(*) from iitables for the table_name shows only 1 row and its table_type=’T’.
The only thing unusual about this table is that the table_owner has defined a view upon the table and that one user (not the table owner) has registered a synonym for the view and called that synonym the same name as the table in question.
Sadly when I attempt to set this situation up with a dummy database the error will not reproduce.
Has anyone got any idea what else might be wrong?
Martin Bowes
Hi All,
Playing with II_EMED_SET has shown that the optimizer will issue something like:
select t.table_owner, t.num_rows, t.table_type,
t.table_reltid, t.table_reltidx, t.number_pages, t.table_stats, t.overflow_pages, t.row_width
from(iitables t left join iisynonyms s on t.table_name=s.table_name)
where(t.table_owner='lust' )and((t.table_name='cryovial_store' )or(s.synonym_name= 'cryovial_store' ))
Executing . . .
┌────────────────────────────────┬─────────────┬──────┬─────────────┬─────────────┬─────────────┬──────┬─────────────┬─────────────┐
│table_owner │num_rows │table_│table_reltid │table_reltidx│number_pages │table_│overflow_page│row_width │
├────────────────────────────────┼─────────────┼──────┼─────────────┼─────────────┼─────────────┼──────┼─────────────┼─────────────┤
│lust │ 0│V │ 629│ 0│ 3│N │ 0│ 58│
│lust │ 99147│T │ 278│ 0│ 1706│Y │ 0│ 58│
│lust │ 0│V │ 630│ 0│ 3│N │ 0│ 58│
└────────────────────────────────┴─────────────┴──────┴─────────────┴─────────────┴─────────────┴──────┴─────────────┴─────────────┘
I think the system is actually expecting a single row back and is only processing the last row. It sees it as a view and bitchs.
I believe the query used is wrong and should put table owner details into the join condition and use s.table_name not s.synonym_name in the where clause.
Marty
Ø I believe the query used is wrong and should put table owner details into the join condition and use s.table_name not s.synonym_name in the where clause.
Actually on reflection I’ll change that to say: I believe the query used is wrong and should put table owner details into the join condition.