Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Demongo.

Asked: September 23, 2015 - 10:18 am UTC

Last updated: July 14, 2017 - 3:09 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

How do I implement inheritance this way in oracle?

create table requests();
create table requests_new() inherits (requests);
create table requests_old() inherits (requests);


I should be able to query the child tables independently. When querying the parent table, I would like to get all the data in the child tables. Just as Postgres does.

and Chris said...

Table inheritance doesn't currently exist in Oracle.

You can implement views to simulate it, e.g.:

create view requests_all as
  select r.*, n.*, o.*
  from   requests r
  left join requests_new n
  on     r.id = n.id
  left join requests_old o
  on     r.id = o.id;

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partitoning

David Aldridge, September 24, 2015 - 9:28 pm UTC

To a certain extent, PostgreSQL table inheritance is similar to Oracle partitioning.

Partitioned tables have the same columns in Oracle, so they are limited in some ways, but are much more powerful in others (e.g. global constraints and indexes).

Object views?

Duke Ganote, October 01, 2015 - 12:28 pm UTC

Maybe just create an object view?
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1392558436600

Inherited tables functionality exists in Oracle

Debabrata Kabiraj, July 14, 2017 - 1:30 pm UTC

As per below article, inherited table functionality exists in Oracle similar to PostGreSQL :
https://stackoverflow.com/questions/17512871/how-to-create-inherited-table-in-oracle

I have also tested it and it works very well seamlessly with no issues in partitioning the inherited tables or inserting individually without using the object types in insert statement just like any other table.

However, the only concern is that it creates a system unique index to map to the parent object type and in case of partitioned table the unique index is a global index as opposed to local index which can neither be dropped or modified, other than making them invisible. That might be cause of concern in terms of insert performance and additional unnecessary storage usage. The behavior seems to be similar to nested tables with little control on the system generated index.

N.B.: In exadata, indexes scans are mostly redundant due to Storage Index at Exadata Cell level.
Chris Saxon
July 14, 2017 - 3:09 pm UTC

Object tables aren't really the same as regular tables. Yeah, you could do something like this to mimic the behaviour. But I wouldn't recommend it!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.