Looking for Performance with a Where In Clause

From: Rayb <ray.basque2_at_gnb.ca>
Date: 6 Dec 2005 08:17:58 -0800
Message-ID: <1133885878.495523.241900@g49g2000cwa.googlegroups.com>

I have two pieces of SQL that as very similar, but are treated very differently by oracle (9.2.0.1 by the way).

This sql was originally in a package.proc. The real version selects from more than just the voter table, I have removed the extra columns for simplicity.

The SQL statements and explain plans are at bottom of post.

SQL 1 runs in .02 seconds while SQL 2 takes 8 or so seconds. Any thoughts as to why? My first thought was a binding issue, but that does not apprear to be the case. If I just select from the voter table, both queries return in .02 or .03 seconds. If it was not binding and doing a full table scan of voter, it would take about one or two seconds.

I would like to improve the performance of SQL 2 while still using the STR2TBL function (or some other method of dynamically selecting the voter_ids). Suggestions would be appreciated.

Rows per table.
VOTER_INFO 600k
HOUSEHOLD_INFO 300k
ADDRESS_INFO 300k

Thanks
Ray

SQL 1
SELECT VI.VOTER_INFO_ID

      FROM VOTER_INFO VI
      JOIN HOUSEHOLD_INFO HI
        ON HI.HOUSEHOLD_ID = VI.HOUSEHOLD_ID
      JOIN ADDRESS_INFO AI
      	ON AI.ADDRESS_INFO_ID = HI.ADDRESS_INFO_ID
      JOIN PROV_DISTRICT_POLL PDP
        ON PDP.PROV_DISTRICT_ID = AI.PROV_DISTRICT_ID
       AND PDP.PROV_DISTRICT_POLL_ID = AI.PROV_DISTRICT_POLL_ID
      JOIN PROVINCIAL_DISTRICT PD
        ON PD.PROV_DISTRICT_ID = PDP.PROV_DISTRICT_ID
      JOIN COMMUNITY_911 C
        ON C.COMMUNITY_911_ID = AI.COMMUNITY_911_ID
      LEFT OUTER JOIN STREET_DIRECTION SD
        ON SD.STREET_DIRECTION_ID = AI.STREET_DIRECTION_ID
      LEFT OUTER JOIN STREET_TYPE ST
        ON ST.STREET_TYPE_ID = AI.STREET_TYPE_ID
      JOIN GENDER G
        ON G.GENDER_ID = VI.GENDER_ID
       WHERE (VI.VOTER_INFO_ID IN (1201343,1201344,1201345,1201346));

Plan Table

| SELECT STATEMENT | | 4
| 212 | 12 |
| NESTED LOOPS | | 4
| 212 | 12 |
| NESTED LOOPS | | 4
| 204 | 12 |
| NESTED LOOPS | | 4
| 184 | 12 |
| NESTED LOOPS OUTER | | 4
| 172 | 12 |
| NESTED LOOPS OUTER | | 4
| 160 | 12 |
| NESTED LOOPS | | 4
| 152 | 12 |
| NESTED LOOPS | | 4
| 84 | 8 |
| NESTED LOOPS | | 4
| 52 | 4 |
| INLIST ITERATOR | |
| | |
| TABLE ACCESS BY INDEX ROWI|VOTER_INFO | 4
| 44 | 4 |
| INDEX RANGE SCAN |VOTER_INFO_PK | 4
| | 3 |
| INDEX UNIQUE SCAN |GENDER_PK | 1
| 2 | |
| TABLE ACCESS BY INDEX ROWID |HOUSEHOLD_INFO | 1
| 8 | 1 |
| INDEX UNIQUE SCAN |HOUSEHOLD_INFO_PK | 1
| | |
| TABLE ACCESS BY INDEX ROWID |ADDRESS_INFO | 1
| 17 | 1 |
| INDEX UNIQUE SCAN |ADDRESS_INFO_PK | 1
| | |
| INDEX UNIQUE SCAN |STREET_DIRECTION_PK | 1
| 2 | |
| INDEX UNIQUE SCAN |STREET_TYPE_PK | 1
| 3 | |
| INDEX UNIQUE SCAN |COMMUNITY_PK | 1
| 3 | |
| INDEX UNIQUE SCAN |PROV_DISTRICT_POLL_PK | 1
| 5 | |
| INDEX UNIQUE SCAN |PROVINCIAL_DISTRICT_PK | 1
| 2 | |

SQL 2
select VI.VOTER_INFO_ID

      FROM VOTER_INFO VI
      JOIN HOUSEHOLD_INFO HI
        ON HI.HOUSEHOLD_ID = VI.HOUSEHOLD_ID
      JOIN ADDRESS_INFO AI
      	ON AI.ADDRESS_INFO_ID = HI.ADDRESS_INFO_ID
      JOIN PROV_DISTRICT_POLL PDP
        ON PDP.PROV_DISTRICT_ID = AI.PROV_DISTRICT_ID
       AND PDP.PROV_DISTRICT_POLL_ID = AI.PROV_DISTRICT_POLL_ID
      JOIN PROVINCIAL_DISTRICT PD
        ON PD.PROV_DISTRICT_ID = PDP.PROV_DISTRICT_ID
      JOIN COMMUNITY_911 C
        ON C.COMMUNITY_911_ID = AI.COMMUNITY_911_ID
      LEFT OUTER JOIN STREET_DIRECTION SD
        ON SD.STREET_DIRECTION_ID = AI.STREET_DIRECTION_ID
      LEFT OUTER JOIN STREET_TYPE ST
        ON ST.STREET_TYPE_ID = AI.STREET_TYPE_ID
      JOIN GENDER G
        ON G.GENDER_ID = VI.GENDER_ID
       WHERE (VI.VOTER_INFO_ID IN (SELECT *
                                   FROM THE ( SELECT CAST(
STR2TBL('1201343,1201344,1201345,1201346') AS MYTABLETYPE )
                                                FROM DUAL )));

Plan Table

| SELECT STATEMENT | | 1
| 66 | 1396 |
| NESTED LOOPS | | 1
| 66 | 1396 |
| NESTED LOOPS | | 1
| 64 | 1396 |
| NESTED LOOPS | | 1
| 59 | 1396 |
| NESTED LOOPS OUTER | | 1
| 56 | 1396 |
| NESTED LOOPS OUTER | | 1
| 53 | 1396 |
| NESTED LOOPS | | 1
| 51 | 1396 |
| NESTED LOOPS | | 1
| 34 | 1395 |
| HASH JOIN SEMI | | 1
| 26 | 1394 |
| HASH JOIN | |
544K| 6M| 824 |
| INDEX FULL SCAN |GENDER_PK | 2
| 4 | 1 |
| TABLE ACCESS FULL |VOTER_INFO |
544K| 5M| 806 |
| VIEW |VW_NSO_1 |

8K|  103K|     11 |


|           COLLECTION ITERATOR PICKLE|STR2TBL                  |

|      |        |

|            TABLE ACCESS FULL        |DUAL                     |

8K|      |     11 |


|         TABLE ACCESS BY INDEX ROWID |HOUSEHOLD_INFO           |     1

|    8 |      1 |

|          INDEX UNIQUE SCAN          |HOUSEHOLD_INFO_PK        |     1

|      |        |

|        TABLE ACCESS BY INDEX ROWID  |ADDRESS_INFO             |     1

|   17 |      1 |

|         INDEX UNIQUE SCAN           |ADDRESS_INFO_PK          |     1

|      |        |

|       INDEX UNIQUE SCAN             |STREET_DIRECTION_PK      |     1

|    2 |        |

|      INDEX UNIQUE SCAN              |STREET_TYPE_PK           |     1

|    3 |        |

|     INDEX UNIQUE SCAN               |COMMUNITY_PK             |     1

|    3 |        |

|    INDEX UNIQUE SCAN                |PROV_DISTRICT_POLL_PK    |     1

|    5 |        |

|   INDEX UNIQUE SCAN                 |PROVINCIAL_DISTRICT_PK   |     1

|    2 |        |

-----------------------------------------------------------------------------------------

If you have not seen the STR2TBL proc, it comes from Tom Kyte's site. see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061 for details. Received on Tue Dec 06 2005 - 10:17:58 CST