Re: (Non-)Use of indexes

From: Mark <mark.harris_at_spam.begone.ukonline.co.uk>
Date: Fri, 7 Jun 2002 14:46:00 +0100
Message-ID: <3d00b914$0$1626$afc38c87@news.easynet.co.uk>

> Lets see the plan, the create indexes, the create table.

Sorry... in hindsight I would've included this in my original post. :-/

The table definitions are *huge*. I'll just include the relevant details here...

uu_failure_emails
AGREEMENT_ID NUMBER
FAILURE_DATE DATE
STATUS_FLAG VARCHAR2(1)

me_details
ME_USER_KEY  VARCHAR2(12)
ME_SECOND_KEY  VARCHAR2(12)

member_details

MEMBER_ID NUMBER(38)
MEMBER_TYPE VARCHAR2(1) Indexes

UU_FAILURE_EMAILS
Index name: UU_FAIL_STATUS_AGREEMENT_IND Indexed column: STATUS_FLAG

ME_DETAILS
Index name: ME_DETAILS_SECOND_KEY_IND
Indexed column: ME_SECOND_KEY

MEMBER_DETAILS
Index name: MEMDET_PK
Indexed column: MEMBER_ID

SQL statement

SELECT ...

FROM    member_details     umd
,       me_details           mmd
,       uu_failure_emails  uufe
WHERE   TO_CHAR(uufe.agreement_id) = mmd.me_second_key
AND     TO_NUMBER(mmd.me_user_key)   = umd.member_id
AND     uufe.status_flag  = pv_status
AND     uufe.failure_date < (sysdate - pi_grace_period)
AND     umd.member_type   = 'U';

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3356 Card=17135610 Bytes=1336577580)

   1 0 MERGE JOIN (Cost=3356 Card=17135610 Bytes=1336577580)

   2    1     SORT (JOIN)
   3    2       NESTED LOOPS (Cost=3 Card=18974 Bytes=1100492)
   4    3         TABLE ACCESS (BY ROWID) OF 'UU_FAILURE_EMAILS' (Cost=1
Card=1 Bytes=42)
   5    4           INDEX (RANGE SCAN) OF 'UU_FAIL_STATUS_AGREEMENT_IND'
(NON-UNIQUE)
   6    3         TABLE ACCESS (BY ROWID) OF 'ME_DETAILS' (Cost=3504
Card=1897379 Bytes=30358064)
   7    6           INDEX (RANGE SCAN) OF 'ME_DETAILS_SECOND_KEY_IND'
(NON-UNIQUE)
   8    1     SORT (JOIN)
   9    8       TABLE ACCESS (FULL) OF 'MEMBER_DETAILS' (Cost=2800

Card=90311 Bytes=1806220)

The index which isn't being used is "MEMDET_PK" - MEMBER_DETAILS is having a full table scan upon it.

If I remove the uu_failure_emails table from the SQL, the index on member_details(MEMDET_PK) is used.

Mark Received on Fri Jun 07 2002 - 08:46:00 CDT