| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sql query
A copy of this was sent to hpcheong_at_ncs.com.sg
(if that email address didn't require changing)
On Mon, 05 Apr 1999 10:35:32 GMT, you wrote:
>Hi,
>
>The below SQL statement is what my program is trying to execute:
>
>Each table abt 2 million rows.
>All fields that are joined/queried on are INDEXED. We verified that.
>
Full scan of tb_easy_house followed by a full scan of tb_easy_re for each row in tb_easy_house.
NULLS are not indexed in conventional b*tree indexes. Therefore, no indexes can be used on the following query. You are reading 2million rows 2million times....
even if nulls were indexed, the AND'ed OR's would make it extremely difficult to use. You are saying "if (a = b) or ( a = somevalue and b = somevalue )". Cannot use an index for that logic either.
>This took > 3 hours to execute & it is not completed yet
> when my Telnet session timed-out
>
>Previously this query executed in less than half an hour in the MainFrame.
>
>Any clue on why Oracle is doing so slowly ?
>
>Thanks evelyn.
>-----------------------------------------------------------------------------
>--------------------------------------------------------------------------
>
>SELECT DISTINCT count(*)
>FROM tb_EASY_HOUSE, tb_EASY_RE
>WHERE (
>(tb_EASY_HOUSE.STREET_CODE=tb_EASY_RE.STREET_CODE OR
> (tb_EASY_HOUSE.STREET_CODE IS NULL AND tb_EASY_RE.STREET_CODE IS NULL) )
>AND
>(tb_EASY_HOUSE.BLOCK=tb_EASY_RE.BLOCK OR
> (tb_EASY_HOUSE.BLOCK IS NULL AND tb_EASY_RE.BLOCK IS NULL) )
>AND
>(tb_EASY_HOUSE.MAIN_HSE=tb_EASY_RE.MAIN_HSE OR
> (tb_EASY_HOUSE.MAIN_HSE IS NULL AND tb_EASY_RE.MAIN_HSE IS NULL) )
>AND
>(tb_EASY_HOUSE.SUB_HSE=tb_EASY_RE.SUB_HSE OR
> (tb_EASY_HOUSE.SUB_HSE IS NULL AND tb_EASY_RE.SUB_HSE IS NULL) )
>);
>
>
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |