slow query - help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

slow query - help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-28 : 08:32:01

dps writes "Can someone suggest a boost to this.

select count(rowid) from account

COUNT(ROWID)
------------
33706

select count(rowid) from tel_nos

COUNT(ROWID)
------------
4121532

from account a, tel_nos t
where t.tel_type = 'S'
AND SYSDATE BETWEEN t.start_date AND NVL(t.end_date, SYSDATE)
and rownum < 1001
and t.rowid <> a.rowid

OPERATION OPTIONS OBJECT_NAME
SELECT STATEMENT
COUNT STOPKEY
NESTED LOOPS
TABLE ACCESS FULL TEL_NOS
TABLE ACCESS FULL ACCOUNT

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 CONNECT BY
3 2 TABLE ACCESS (FULL) OF 'PLAN_TABLE'
4 2 TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE'
5 2 TABLE ACCESS (FULL) OF 'PLAN_TABLE'

If I take off tel_type then its slightly quicker.

Table details

tel_nos
------
TEL_ID NOT NULL NUMBER(10)
TEL_TYPE NOT NULL VARCHAR2(10)
TEL_NO NOT NULL VARCHAR2(19)
SITE_USAGE_ID VARCHAR2(10)
PROSPECT_ID VARCHAR2(14)
START_DATE DATE
END_DATE DATE

-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : IFL07
UNIQUENESS : NONUNIQUE
COLUMN_NAME : CONTACT_SEQ
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : IFL08
UNIQUENESS : NONUNIQUE
COLUMN_NAME : EQUIPMENT_INSTANCE_ID
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : IFL09
UNIQUENESS : NONUNIQUE
COLUMN_NAME : PROSPECT_ID
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : IFL10
UNIQUENESS : NONUNIQUE
COLUMN_NAME : SITE_USAGE_ID
COLUMN_POSITION : 1

INDEX_TYPE : NORMAL
INDEX_NAME : PI17
UNIQUENESS : NONUNIQUE
COLUMN_NAME : PROSPECT_ID
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : PI17
UNIQUENESS : NONUNIQUE
COLUMN_NAME : SITE_USAGE_ID
COLUMN_POSITION : 2
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : PI17
UNIQUENESS : NONUNIQUE
COLUMN_NAME : CONTACT_SEQ
COLUMN_POSITION : 3
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : PI17
UNIQUENESS : NONUNIQUE
COLUMN_NAME : TEL_TYPE
COLUMN_POSITION : 4
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : PL03
UNIQUENESS : UNIQUE
COLUMN_NAME : TEL_ID
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : TEL_IND
UNIQUENESS : NONUNIQUE
COLUMN_NAME : TEL_NO
COLUMN_POSITION : 1
-----------------

INDEX_TYPE : NORMAL
INDEX_NAME : UN06
UNIQUENESS : UNIQUE
COLUMN_NAME : TEL_NO
COLUMN_POSITION : 1
-----------------
INDEX_TYPE : NORMAL
INDEX_NAME : UN06
UNIQUENESS : UNIQUE
COLUMN_NAM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-28 : 08:38:39

Two things:

A. We're not an Oracle site, we focus on SQL Server. You might get a better answer in an Oracle forum.

B. The statement you posted:

from account a, tel_nos t
where t.tel_type = 'S'
AND SYSDATE BETWEEN t.start_date AND NVL(t.end_date, SYSDATE)
and rownum < 1001
and t.rowid <> a.rowid

...is a CROSS JOIN between the two tables, effectively generating close to 138,920,357,592 rows of data. I'm not well-versed in how Oracle optimizes its queries but that could very well be part of your problem. I do know that it used to be extremely fussy about how the parts of the WHERE clause were ordered, and that moving conditions around would generate completely different execution plans, you might want to give that a try if you haven't already.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources