Re: double linked list

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 05 Feb 2003 21:33:25 -0800
Message-ID: <3E41F3A5.E43A25D5@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 05 2003 - 23:33:25 CST