Oracle Live SQL - Script: NL with ambiguous order by

Statement 1

DROP TABLE rau_companyowner

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

DROP TABLE rau_owner

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 3

DROP TABLE rau_address

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 4

DROP TABLE rau_company

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 5

CREATE TABLE rau_company ( 
id NUMBER CONSTRAINT pk_rau_company PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_company_p ON rau_company(id)) 
)

Table created.

Statement 6

CREATE TABLE rau_owner ( 
id NUMBER CONSTRAINT pk_rau_owner PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_owner_p ON rau_owner(id)), 
name varchar2(1000) 
)

Table created.

Statement 7

CREATE TABLE rau_companyowner ( 
company_id NUMBER, 
owner_id NUMBER, 
CONSTRAINT pk_rau_companyowner PRIMARY KEY (company_id, owner_id) USING INDEX (CREATE UNIQUE INDEX idx_rau_companyowner_p ON rau_companyowner(company_id, owner_id)), 
CONSTRAINT fk_companyowner_company FOREIGN KEY (company_id) REFERENCES rau_company(id), 
CONSTRAINT fk_companyowner_owner FOREIGN KEY (owner_id) REFERENCES rau_owner(id) 
)

Table created.

Statement 8

CREATE TABLE rau_address ( 
id NUMBER CONSTRAINT pk_rau_address PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_address_p ON rau_address(id)), 
company_id NUMBER, 
prio NUMBER NOT NULL, 
street varchar2(1000), 
CONSTRAINT fk_address_company FOREIGN KEY (company_id) REFERENCES rau_company(id) 
)

Table created.

Statement 9

DECLARE 
   TYPE t_address IS TABLE OF rau_address%rowtype INDEX BY pls_integer; 
   address t_address; 
   TYPE t_owner IS TABLE OF rau_owner%rowtype INDEX BY pls_integer; 
   owner t_owner; 
   TYPE t_companyowner IS TABLE OF rau_companyowner%rowtype INDEX BY pls_integer; 
   companyowner t_companyowner; 
   ii pls_integer; 
   company_id pls_integer := 1; 
   test_count PLS_INTEGER := 10000; 
   --test_count PLS_INTEGER := 50; 
BEGIN 
   --rau_company 
   INSERT INTO rau_company VALUES (company_id); 
   --rau_owner,rau_companyowner 
   FOR ii IN 1 .. test_count 
   LOOP 
      owner(ii).id:=ii; 
      owner(ii).name:='N'||to_char(ii); 
      companyowner(ii).company_id:=company_id; 
      companyowner(ii).owner_id:=ii; 
   END LOOP; 
   forall ii IN owner.FIRST .. owner.LAST 
      INSERT INTO rau_owner VALUES (owner(ii).id, owner(ii).name); 
   forall ii IN companyowner.FIRST .. companyowner.LAST 
      INSERT INTO rau_companyowner VALUES (companyowner(ii).company_id, companyowner(ii).owner_id); 
   --rau_address 
   FOR ii IN 1 .. test_count 
   LOOP 
      address(ii).id:=ii; 
      address(ii).company_id:=company_id; 
      address(ii).prio:=1; 
      address(ii).street:='S'||to_char(ii); 
   END LOOP; 
   forall ii IN address.FIRST .. address.LAST 
      INSERT INTO rau_address VALUES (address(ii).id, address(ii).company_id, address(ii).prio, address(ii).street); 
   COMMIT; 
END; 
 
-- check testdata 
SELECT 'rau_company' tab, COUNT(*) count FROM rau_company 
UNION all 
SELECT 'rau_owner', COUNT(*) FROM rau_owner 
UNION all 
SELECT 'rau_companyowner', COUNT(*) FROM rau_companyowner 
UNION all 
SELECT 'rau_address', COUNT(*) FROM rau_address; 
 
-- the sql, NL with address as inner loop enforced 
-- ‘order BY prio’ is ambiguous because all addresses have the same prio 
--   => the single row in ad could be any row 
   SELECT /*+ leading(hh hhoo oo ad) use_hash(hhoo oo) USE_NL(hh ad) */ 
          hh.id company, 
          ad.street, 
--          LISTAGG(oo.name || ', ') within group (order by oo.name), 
          count(oo.id) owner_count 
     FROM rau_company hh 
LEFT JOIN rau_companyowner hhoo ON hh.id = hhoo.company_id 
LEFT JOIN rau_owner oo ON hhoo.owner_id = oo.id 
LEFT JOIN ( 
          SELECT * 
            FROM ( 
                 SELECT company_id, street, 
                        row_number() over ( partition by company_id order BY prio asc ) as row_num                        
                   FROM rau_address                      
                 ) 
           WHERE row_num = 1               
          ) ad ON hh.id = ad.company_id   
 GROUP BY hh.id, 
          ad.street;

ORA-06550: line 41, column 1:
PLS-00103: Encountered the symbol "SELECT"

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 10

DROP TABLE rau_companyowner

Table dropped.

Statement 11

DROP TABLE rau_owner

Table dropped.

Statement 12

DROP TABLE rau_address

Table dropped.

Statement 13

DROP TABLE rau_company

Table dropped.

Statement 14

CREATE TABLE rau_company ( 
id NUMBER CONSTRAINT pk_rau_company PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_company_p ON rau_company(id)) 
)

Table created.

Statement 15

CREATE TABLE rau_owner ( 
id NUMBER CONSTRAINT pk_rau_owner PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_owner_p ON rau_owner(id)), 
name varchar2(1000) 
)

Table created.

Statement 16

CREATE TABLE rau_companyowner ( 
company_id NUMBER, 
owner_id NUMBER, 
CONSTRAINT pk_rau_companyowner PRIMARY KEY (company_id, owner_id) USING INDEX (CREATE UNIQUE INDEX idx_rau_companyowner_p ON rau_companyowner(company_id, owner_id)), 
CONSTRAINT fk_companyowner_company FOREIGN KEY (company_id) REFERENCES rau_company(id), 
CONSTRAINT fk_companyowner_owner FOREIGN KEY (owner_id) REFERENCES rau_owner(id) 
)

Table created.

Statement 17

CREATE TABLE rau_address ( 
id NUMBER CONSTRAINT pk_rau_address PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_address_p ON rau_address(id)), 
company_id NUMBER, 
prio NUMBER NOT NULL, 
street varchar2(1000), 
CONSTRAINT fk_address_company FOREIGN KEY (company_id) REFERENCES rau_company(id) 
)

Table created.

Statement 18

DECLARE 
   TYPE t_address IS TABLE OF rau_address%rowtype INDEX BY pls_integer; 
   address t_address; 
   TYPE t_owner IS TABLE OF rau_owner%rowtype INDEX BY pls_integer; 
   owner t_owner; 
   TYPE t_companyowner IS TABLE OF rau_companyowner%rowtype INDEX BY pls_integer; 
   companyowner t_companyowner; 
   ii pls_integer; 
   company_id pls_integer := 1; 
   test_count PLS_INTEGER := 10000; 
   --test_count PLS_INTEGER := 50; 
BEGIN 
   --rau_company 
   INSERT INTO rau_company VALUES (company_id); 
   --rau_owner,rau_companyowner 
   FOR ii IN 1 .. test_count 
   LOOP 
      owner(ii).id:=ii; 
      owner(ii).name:='N'||to_char(ii); 
      companyowner(ii).company_id:=company_id; 
      companyowner(ii).owner_id:=ii; 
   END LOOP; 
   forall ii IN owner.FIRST .. owner.LAST 
      INSERT INTO rau_owner VALUES (owner(ii).id, owner(ii).name); 
   forall ii IN companyowner.FIRST .. companyowner.LAST 
      INSERT INTO rau_companyowner VALUES (companyowner(ii).company_id, companyowner(ii).owner_id); 
   --rau_address 
   FOR ii IN 1 .. test_count 
   LOOP 
      address(ii).id:=ii; 
      address(ii).company_id:=company_id; 
      address(ii).prio:=1; 
      address(ii).street:='S'||to_char(ii); 
   END LOOP; 
   forall ii IN address.FIRST .. address.LAST 
      INSERT INTO rau_address VALUES (address(ii).id, address(ii).company_id, address(ii).prio, address(ii).street); 
   COMMIT; 
END; 
 
-- check testdata 
SELECT 'rau_company' tab, COUNT(*) count FROM rau_company 
UNION all 
SELECT 'rau_owner', COUNT(*) FROM rau_owner 
UNION all 
SELECT 'rau_companyowner', COUNT(*) FROM rau_companyowner 
UNION all 
SELECT 'rau_address', COUNT(*) FROM rau_address; 
 
-- the sql, NL with address as inner loop enforced 
-- ‘order BY prio’ is ambiguous because all addresses have the same prio 
--   => the single row in ad could be any row 
   SELECT /*+ leading(hh hhoo oo ad) use_hash(hhoo oo) USE_NL(hh ad) */ 
          hh.id company, 
          ad.street, 
--          LISTAGG(oo.name || ', ') within group (order by oo.name), 
          count(oo.id) owner_count 
     FROM rau_company hh 
LEFT JOIN rau_companyowner hhoo ON hh.id = hhoo.company_id 
LEFT JOIN rau_owner oo ON hhoo.owner_id = oo.id 
LEFT JOIN ( 
          SELECT * 
            FROM ( 
                 SELECT company_id, street, 
                        row_number() over ( partition by company_id order BY prio asc ) as row_num                        
                   FROM rau_address                      
                 ) 
           WHERE row_num = 1               
          ) ad ON hh.id = ad.company_id   
 GROUP BY hh.id, 
          ad.street;

ORA-06550: line 41, column 1:
PLS-00103: Encountered the symbol "SELECT"

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 19

DROP TABLE rau_companyowner

Table dropped.

Statement 20

DROP TABLE rau_owner

Table dropped.

Statement 21

DROP TABLE rau_address

Table dropped.

Statement 22

DROP TABLE rau_company

Table dropped.

Statement 23

CREATE TABLE rau_company ( 
id NUMBER CONSTRAINT pk_rau_company PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_company_p ON rau_company(id)) 
)

Table created.

Statement 24

CREATE TABLE rau_owner ( 
id NUMBER CONSTRAINT pk_rau_owner PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_owner_p ON rau_owner(id)), 
name varchar2(1000) 
)

Table created.

Statement 25

CREATE TABLE rau_companyowner ( 
company_id NUMBER, 
owner_id NUMBER, 
CONSTRAINT pk_rau_companyowner PRIMARY KEY (company_id, owner_id) USING INDEX (CREATE UNIQUE INDEX idx_rau_companyowner_p ON rau_companyowner(company_id, owner_id)), 
CONSTRAINT fk_companyowner_company FOREIGN KEY (company_id) REFERENCES rau_company(id), 
CONSTRAINT fk_companyowner_owner FOREIGN KEY (owner_id) REFERENCES rau_owner(id) 
)

Table created.

Statement 26

CREATE TABLE rau_address ( 
id NUMBER CONSTRAINT pk_rau_address PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX idx_rau_address_p ON rau_address(id)), 
company_id NUMBER, 
prio NUMBER NOT NULL, 
street varchar2(1000), 
CONSTRAINT fk_address_company FOREIGN KEY (company_id) REFERENCES rau_company(id) 
)

Table created.

Statement 27

DECLARE 
   TYPE t_address IS TABLE OF rau_address%rowtype INDEX BY pls_integer; 
   address t_address; 
   TYPE t_owner IS TABLE OF rau_owner%rowtype INDEX BY pls_integer; 
   owner t_owner; 
   TYPE t_companyowner IS TABLE OF rau_companyowner%rowtype INDEX BY pls_integer; 
   companyowner t_companyowner; 
   ii pls_integer; 
   company_id pls_integer := 1; 
   test_count PLS_INTEGER := 10000; 
   --test_count PLS_INTEGER := 50; 
BEGIN 
   --rau_company 
   INSERT INTO rau_company VALUES (company_id); 
   --rau_owner,rau_companyowner 
   FOR ii IN 1 .. test_count 
   LOOP 
      owner(ii).id:=ii; 
      owner(ii).name:='N'||to_char(ii); 
      companyowner(ii).company_id:=company_id; 
      companyowner(ii).owner_id:=ii; 
   END LOOP; 
   forall ii IN owner.FIRST .. owner.LAST 
      INSERT INTO rau_owner VALUES (owner(ii).id, owner(ii).name); 
   forall ii IN companyowner.FIRST .. companyowner.LAST 
      INSERT INTO rau_companyowner VALUES (companyowner(ii).company_id, companyowner(ii).owner_id); 
   --rau_address 
   FOR ii IN 1 .. test_count 
   LOOP 
      address(ii).id:=ii; 
      address(ii).company_id:=company_id; 
      address(ii).prio:=1; 
      address(ii).street:='S'||to_char(ii); 
   END LOOP; 
   forall ii IN address.FIRST .. address.LAST 
      INSERT INTO rau_address VALUES (address(ii).id, address(ii).company_id, address(ii).prio, address(ii).street); 
   COMMIT; 
END;

Statement processed.

Statement 28

SELECT 'rau_company' tab, COUNT(*) count FROM rau_company 
UNION all 
SELECT 'rau_owner', COUNT(*) FROM rau_owner 
UNION all 
SELECT 'rau_companyowner', COUNT(*) FROM rau_companyowner 
UNION all 
SELECT 'rau_address', COUNT(*) FROM rau_address


TAB COUNT
rau_company 1
rau_owner 10000
rau_companyowner 10000
rau_address 10000

4 rows selected.

TAB	COUNT
rau_company	1
rau_owner	10000
rau_companyowner	10000
rau_address	10000

Statement 29

   SELECT /*+ leading(hh hhoo oo ad) use_hash(hhoo oo) USE_NL(hh ad) */ 
          hh.id company, 
          ad.street, 
--          LISTAGG(oo.name || ', ') within group (order by oo.name), 
          count(oo.id) owner_count 
     FROM rau_company hh 
LEFT JOIN rau_companyowner hhoo ON hh.id = hhoo.company_id 
LEFT JOIN rau_owner oo ON hhoo.owner_id = oo.id 
LEFT JOIN ( 
          SELECT * 
            FROM ( 
                 SELECT company_id, street, 
                        row_number() over ( partition by company_id order BY prio asc ) as row_num                        
                   FROM rau_address                      
                 ) 
           WHERE row_num = 1               
          ) ad ON hh.id = ad.company_id   
 GROUP BY hh.id, 
          ad.street


COMPANY STREET OWNER_COUNT
1 S1095 10000

NL with ambiguous order by

Additional Information