Re: double linked list

Frank wrote:

> 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
> >

>

These are seven of my demo queries for EXPLAIN PLAN. All seven produce the exact same result set.

Q1: Intersect
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

Q2: Simple IN
SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT srvr_id
   FROM serv_inst);

Q3: IN with Inner Join
SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT i.srvr_id
   FROM serv_inst i, servers s
   WHERE i.srvr_id = s.srvr_id);

Q4: Simple Inner Join
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

Q5: NOT IN with MINUS
SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

   SELECT srvr_id
   FROM servers
   MINUS
   SELECT srvr_id
   FROM serv_inst);

Q6: EXISTS
SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

Q7: Reversed EXISTS
SELECT srvr_id
FROM serv_inst i
WHERE EXISTS (
   SELECT srvr_id
   FROM servers s
   WHERE s.srvr_id = i.srvr_id);

Have fun. If you come up with others that produce the same result set please send them to me and I'll add them to my repetoire. Plenty more exist so have at it.

Daniel Morgan Received on Thu Feb 06 2003 - 14:25:27 CST