Updatability and Analytic Functions

Not really a question, more an annoyance: Oracle won’t correctly determine which columns – if any- in a view are updatable once analytics are involved.

A table will not be regarded as key-preserved by Oracle in a view if analytic functions are invoked on it in a sub-select:

SQL> drop table analytic_test_a;

Table dropped

SQL> drop table analytic_test_b;

Table dropped

SQL> drop view analytic_test_view_1;

View dropped

SQL> drop view analytic_test_view_2;

View dropped

SQL> drop view analytic_test_view_1s;

View dropped

SQL> drop view analytic_test_view_2s;

View dropped

SQL> create table analytic_test_a as
2 select o.object_id, o.object_name, o.created
3 from user_objects o
4 where o.object_type in (‘TABLE’,'VIEW’);

Table created

SQL> alter table ANALYTIC_TEST_A
2 add constraint ANALYTIC_TEST_A_PK primary key (OBJECT_NAME);

Table altered

SQL> create table analytic_test_b as
2 select t.table_name, t.tablespace_name, t.pct_free
3 from all_tables t;

Table created

SQL> create view analytic_test_view_1 as
2 select *
3 from analytic_test_a a,
4 analytic_test_b b
5 where b.table_name = a.object_name;

View created

SQL> create view analytic_test_view_2 as
2 select a.*, b.*, min(OBJECT_ID) over (partition by TABLESPACE_NAME) min_id
3 from analytic_test_a a,
4 analytic_test_b b
5 where b.table_name = a.object_name;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_1′;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1 PCT_FREE YES YES YES

6 rows selected

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_2′;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 PCT_FREE YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_2 MIN_ID NO NO NO

7 rows selected

SQL> create view analytic_test_view_1s as
2 select *
3 from analytic_test_view_1;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_1s’;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S TABLE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S TABLESPACE_NAME YES YES YES
BARTOJ_DEV ANALYTIC_TEST_VIEW_1S PCT_FREE YES YES YES

6 rows selected

SQL> create view analytic_test_view_2s as
2 select *
3 from analytic_test_view_2;

View created

SQL> select *
2 from user_updatable_columns c
3 where lower(c.table_name) = ‘analytic_test_view_2s’;

OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
—————————— —————————— —————————— ——— ———- ———
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S OBJECT_ID NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S OBJECT_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S CREATED NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S TABLE_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S TABLESPACE_NAME NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S PCT_FREE NO NO NO
BARTOJ_DEV ANALYTIC_TEST_VIEW_2S MIN_ID NO NO NO

7 rows selected

SQL>