DA Morgan wrote:
>
> 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
>
What happened to the "avoid WHERE NOT IN" ? I need to try this out.
Frank
Received on Thu Feb 06 2003 - 12:43:26 CST
