Re: Performace on VERY simple database... : NEW INFO

From: Stephan van Hoof <test_at_test.nl>
Date: Fri, 3 Jan 2003 16:53:09 +0100
Message-ID: <1041609108.50047@newsreader1.wirehub.nl>

SLOW:

---

select id_district district_id, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

Plan Table

---

---

| Operation | Name | Rows | Bytes| Cost | Pstart|
Pstop |

---

---

| SELECT STATEMENT | | 4 | 92 | 3 | |
|
| HASH JOIN | | 4 | 92 | 3 | |
|
| INDEX FAST FULL SCAN |DISTRICT_ | 4 | 20 | 1 | |
|
| TABLE ACCESS FULL |DISTRICT | 20 | 360 | 1 | |
|

---

---

7 rijen zijn geselecteerd.

SQL> set timing on
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 2203
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 2233
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 2223
SQL>

---

FAST:

---

NOTE: Table district2 is created be means of: select * from district

SQL> ed
file afiedt.buf is weggeschreven.

  1 select id_district district_id, district_name   2 FROM district2, district_street where   3* (district_id=id_district) AND (place_street_id=707) SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 50
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 20
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 20
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 20
SQL> / DISTRICT_ID DISTRICT_NAME

----------- ------------------------------
          1 VLISSINGEN
          3 VLISS. EX. CENTRUM
         71 HUISVUILWIJK 12

 real: 20
SQL>
SQL> @utlxpls

Plan Table

---

---

| Operation | Name | Rows | Bytes| Cost | Pstart|
Pstop |

---

---

| SELECT STATEMENT | | 3 | 105 | 3 | |
|
| HASH JOIN | | 3 | 105 | 3 | |
|
| INDEX FAST FULL SCAN |DISTRICT_ | 4 | 20 | 1 | |
|
| TABLE ACCESS FULL |DISTRICT2 | 82 | 2K| 1 | |
|

---

---

7 rijen zijn geselecteerd.

 real: 20
SQL> Received on Fri Jan 03 2003 - 09:53:09 CST