View and Table Column Size mismatch due to UNION

Question

I have created View using unioned CVAS:

CREATE
OR REPLACE VIEW SAMPLEVIEW AS
SELECT
  PT.CREDITPARTYACCOUNT AS PT_CREDITPARTYACCOUNT,
  PT.DEBITPARTYACCOUNT AS PT_DEBITPARTYACCOUNT,
...
...
FROM
  accountingevent AE
  LEFT OUTER JOIN paymenttransaction PT ON (
    AE.PAYMENTTRANSACTIONKEY = PT.PAYMENTTRANSACTIONKEY
  )
  OR (
    AE.PAYMENTINSTRUCTIONKEY = PT.INCOMINGINSTRUCTIONKEY
  )
WHERE
  ...
UNION ALL
SELECT
  PT.CREDITPARTYACCOUNT AS PT_CREDITPARTYACCOUNT,
  PT.DEBITPARTYACCOUNT AS PT_DEBITPARTYACCOUNT,
...
...
FROM
  accountingevent AE
  LEFT OUTER JOIN paymenttransaction PT ON (
    AE.PAYMENTTRANSACTIONKEY = PT.PAYMENTTRANSACTIONKEY
  )
WHERE
    ...
ORDER BY 1;

Result of

DESC SAMPLEVIEW;
PT_CREDITPARTYACCOUNT   VARCHAR2(1024 CHAR)
PT_DEBITPARTYACCOUNT    VARCHAR2(1024 CHAR)
...
...

AND

DESC PAYMENTTRANSACTION;
CREDITPARTYACCOUNT  VARCHAR2(256 CHAR)
DEBITPARTYACCOUNT   VARCHAR2(256 CHAR)
...

Data Sizes I get are 4 times the size of table column sizes. If we remove the UNION ALL and go with single CVAS than sizes becomes equal in table and view. This view and tables are part of large scripts so something in scripts is off which is causing this behavior. This odd behavior is only visible with char and varchar2 datatypes. I also checked the result of the following query:

select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
NLS_CHARACTERSET    AL32UTF8

Kindly guide me on what could be wrong here which is causing this behavior.

Thanks

yes, should the view column sizes not be equal to table column sizes? — coming out of void, Apr 13 '20 at 11:36
I mean, does this reported size difference cause an actual problem? Views do not occupy any real space, they are just stored select statements. I don't know why Oracle gives this size, but it won't do any harm that I can imagine. — Tony Andrews, Apr 13 '20 at 11:38
Actually there is one consumer system that needs to allocate sizes for each column. They are expecting table sizes in view. — coming out of void, Apr 13 '20 at 11:41

score 0 · Answer 1 · answered Apr 13 '20 at 12:19

Which version are you testing? Here's what I get on my DB (20.2):

SQL> desc tukc
Name        Null? Type               
----------- ----- ------------------ 
OBJECT_NAME       VARCHAR2(128 CHAR) 
OBJECT_ID         NUMBER             
SQL> 
SQL> create or replace view tv as
  2  select t.object_name
  3  from tukc t join all_objects o on (t.object_id=o.object_id)
  4  union all
  5  select t.object_name
  6  from tukc t join all_objects o on (t.object_id=o.object_id)
  7  ;

View TV created.

SQL> desc tv
Name        Null? Type               
----------- ----- ------------------ 
OBJECT_NAME       VARCHAR2(128 CHAR)

I am using Oracle 12.2.0.1.0 – coming out of void Apr 13 '20 at 12:20 — coming out of void, Apr 13 '20 at 12:20
Your view has just 2 parts (i.e. just one union all)? – gsalem Apr 13 '20 at 13:33 — gsalem, Apr 13 '20 at 13:33

View and Table Column Size mismatch due to UNION

1 Answers1