subquery unnesting (was: double linked list)

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Wed, 12 Feb 2003 12:49:11 -0800
Message-ID: <ywy2a.5$gO5.93@news.oracle.com>

I don't follow. Here is what i did:

CREATE TABLE servers (
  srvr_id NUMBER(10),
  network_id NUMBER(10),
  status CHAR(1),
  latitude FLOAT(20),
  longitude FLOAT(20),
  netaddress VARCHAR2(15))
 PCTFREE 5
 PCTUSED 90
 STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0); ALTER TABLE servers
ADD CONSTRAINT pk_servers PRIMARY KEY (srvr_id) USING INDEX
PCTFREE 5
STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0); begin

    FOR i In 1..100 LOOP
   INSERT INTO SERVERS VALUES

      (i,abs(floor(DBMS_RANDOM.RANDOM/1000000000)),
      CASE WHEN DBMS_RANDOM.RANDOM > 0 THEN 'Y'
         ELSE 'N' END ,DBMS_RANDOM.RANDOM/10000000,
      DBMS_RANDOM.RANDOM/20000000,
      abs(floor(DBMS_RANDOM.RANDOM/10000000)) || '.' ||
      abs(floor(DBMS_RANDOM.RANDOM/10000000)) || '.' ||
      abs(floor(DBMS_RANDOM.RANDOM/10000000)) || '.' ||
      abs(floor(DBMS_RANDOM.RANDOM/10000000))
      );

    END LOOP;
end;

commit

CREATE TABLE serv_inst (
  siid NUMBER(10),
  si_status VARCHAR2(15),
  type VARCHAR2(5),

  installstatus                 CHAR(1),
  location_code                 NUMBER(10),
  custacct_id          VARCHAR2(10),

  srvr_id NUMBER(10),
  ws_id NUMBER(10))
 PCTFREE 5
 PCTUSED 90
 STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0) ALTER TABLE serv_inst
ADD CONSTRAINT pk_serv_inst PRIMARY KEY (siid, srvr_id, ws_id) USING INDEX
PCTFREE 5
STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0) begin

    FOR i In 1..1000 LOOP
   INSERT INTO serv_inst VALUES

      (3490000+i,'Pending','WIN',CASE WHEN DBMS_RANDOM.RANDOM > 0 THEN 'Y'
         ELSE 'N' END ,78161,'03490092',floor(i/10),1);

    END LOOP;
end;

commit

SELECT --+all_rows
DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

10 buffer gets

SELECT --+all_rows
DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

   SELECT srvr_id
   FROM servers
   MINUS
   SELECT --+index(serv_inst pk_serv_inst)    srvr_id
   FROM serv_inst);

500 buffer gets
(without index hint it's 1000 buffer gets, while rule plan is about 1200 buffer gets)

If you look to execution plans, then, the first one has a Nested Loop, while the second one has FILTER. Nested Loop execution is virtully identical to FILTER rowsource Tuple Iteration Semantics. If you look into v$sqlplan_statistics_all then there would be 100 LAST_STARTS for the MINUS rowsource)

Certainly, the subquery is not corellated, so that there is no need to execute subquery 100 times, but I failed to get unnested plan in 9.2.0.2. And this would only prove my point: query unnesting is almost always beneficial.

Could you please publish your plans & execution statistics?

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E41F3A5.E43A25D5_at_exesolutions.com...
>
>
> Mikito Harakiri wrote:
>
> > <snipped>
> > I would be interested to see those. Evaluating subquery for each row of
the
> > outer query (Tuple Iteration Semantics) is essentially Nested Loops.
> > Unnesting subquery is a transformation that makes the join explicit.
> > Combining all joins together into a flat select-project-join query is
> > beneficial because it opens larger space of join orders (For example,
new
> > join orders might be explored, unavailable in the original query, plus
other
> > join methods -- Merge Join and Hash Join are available as well).
>
> Table 1:
> CREATE TABLE servers (
> srvr_id NUMBER(10),
> network_id NUMBER(10),
> status CHAR(1),
> latitude FLOAT(20),
> longitude FLOAT(20),
> netaddress VARCHAR2(15))
> PCTFREE 5
> PCTUSED 90
> STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0)
> TABLESPACE data_sml;
> ALTER TABLE servers
> ADD CONSTRAINT pk_servers PRIMARY KEY (srvr_id)
> USING INDEX
> PCTFREE 5
> STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0)
> TABLESPACE indx_sml;
>
> 100 rows of data modeled like this:
> INSERT INTO SERVERS VALUES

(1,1028,'Y',32.9806,-117.2567,'172.020.130.002');
>
> Table 2:
> CREATE TABLE serv_inst (
> siid NUMBER(10),
> si_status VARCHAR2(15),
> type VARCHAR2(5),
> installstatus CHAR(1),
> location_code NUMBER(10),
> custacct_id VARCHAR2(10),
> srvr_id NUMBER(10),
> ws_id NUMBER(10))
> PCTFREE 5
> PCTUSED 90
> STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)
> TABLESPACE data_sml;
>
> ALTER TABLE serv_inst
> ADD CONSTRAINT pk_serv_inst PRIMARY KEY (siid, srvr_id, ws_id)
> USING INDEX
> PCTFREE 5
> STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)
> TABLESPACE indx_sml;
>
> 1000 rows of data modeled like this:
> INSERT INTO SERV_INST VALUES
> (3490095,'Pending','WIN','Y',78161,'03490092',12,1);
>
> Try these two SQL statements in both 8.1.7 and 9.2. They product the exact
same
> result set from the data.
>
> SQL Statement 1:
> SELECT DISTINCT s.srvr_id
> FROM servers s, serv_inst i
> WHERE s.srvr_id = i.srvr_id;
>
> SQL Statement 2:
> SELECT DISTINCT srvr_id
> FROM servers
> WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
> I'll take the ridiculously convoluted sub-query every time. In fact the
flat
> inner-join is slower than just about anything else you can write to solve
the
> problem other than an INTERSECT.
>
> Daniel Morgan
>
Received on Wed Feb 12 2003 - 14:49:11 CST