Tables and materialized views

[Walid CHAIB]

Hi guys,

Today while exploring my database objects I noticed that oracle let you create a table and materialized view with the same name which It is not the case for postgresql.

It's weird from oracle, what do you think ?

[Walid CHAIB]

[Lukas Eder]

You can't name your own logical table the same name as a materialized view or vice versa:

create table t (i int);

create materialized view t refresh with rowid as select * from t;

SQL Error [955] [42000]: ORA-00955: name is already used by an existing object

However, it does seem that Oracle stores the materialized view in a system table by the same name but in a different namespace. Do this instead:

create table t (i int);

create materialized view v refresh with rowid as select * from t;

select object_name, object_id, data_object_id, object_type, namespace

from all_objects

where owner = 'TEST'

and object_name in ('T', 'V');

To get

|OBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE      |NAMESPACE|
|-----------|---------|--------------|-----------------|---------|
|T          |158110   |158110        |TABLE            |1        |
|V          |157780   |157780        |TABLE            |1        |
|V          |158112   |              |MATERIALIZED VIEW|19       |

Now, is that causing any jOOQ-specific issues?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4XxjfNzeSmhnxN4361NmKH74V-LyR63nN23xZ%2BD43nYDC8w%40mail.gmail.com.

[Walid CHAIB]

Thank you so much Lukas.
Until now, it doesn't cause issues with Jooq.

Just when  we migrated the database (tables, FK,PK, indexes, views ...) with FullConvert, I was surprised that Oracle allow that and  PostgreSQL didn't accept that which is normal.

But I have another question which is related to Jooq: Does the jooq parser influence the indexes behaviour like for example in the screenshot, the index is on the upper(convert()), and the function convert will be modified because it's not supported by postgresql.

Should i modify the index with the parsed function ?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5E-N0isky%2Bvw3OfQgO9W8%3DvMm%2BeEJmU01rv0Z3aZTGAw%40mail.gmail.com.

[Lukas Eder]

You can use function based indexes as well in PostgreSQL. I think you're parsing the CONVERT function using a ParseListener? In that case, you're in full control of what is being generated by your translation, and you can put an index on that.

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4XxjLN8bhUKg9QsSS-r%2B8CFxKyoNvdpK5mgfDOPfe4Z-Grw%40mail.gmail.com.