i'm struggling to understand what the TABLE clause does, per oracle docs:

it transforms a collection like a nested table into a table which could be used in an sql statement.

which seems clear enough but i don't know how it works in practice.

these are the relevant types and tables;

create type movies_type as Table of ref movie_type;

create type actor_type under person_type

(

starring movies_type

) Final;

create table actor of actor_type

NESTED TABLE starring STORE AS starring_nt;

i want to list actors and movies they starred in, this works

select firstname, lastname, value(b).title

from actor2 a, table(a.starring) b;

but i don't understand why. why isn't this

actor2 a, table(a.starring) b

a Cartesian product?

also, why does value(b) work here?, since it's table of refs, it should be deref, but that doesn't work.

my question is:

why does this query work as intended? i would expect it to list every actor with every movie (Cartesian product) as there are no specified conditions on how to join, and why does value(b) work here?, since it's table of refs, it should be deref, but that doesn't work.

i don't have a mental model for oracle sql, help is very much appreciated on how to learn properly.

thank you very much.

CodePudding user response：

It’s not a Cartesian product because table(a.starring) is correlated by a: For each row in a it is running the table function against its starring nested table.

This is not a very common way of data modelling in Oracle, usually you would use a junction table to allow for a properly normalised model (which usually is much easier to query and allows for better for performance)