Re: Explain Plan Weirdness

From: <jcora_at_my-dejanews.com>
Date: Tue, 11 May 1999 22:13:37 GMT
Message-ID: <7haa2f$nsb$1@nnrp1.deja.com>

In article <3737493C.F936F4_at_mci.com>,
  MKB <mohammed.bhatti_at_mci.com> wrote:
> I've got this table T1, as follows:
>
> CYN NOT NULL NUMBER(4)
> CQN NOT NULL NUMBER(1)
> AC NOT NULL CHAR(10)
> PIC NOT NULL CHAR(5)
> JC NOT NULL CHAR(1)
> CC NOT NULL CHAR(1)
>
> with a pk (T1_PK) of all the above columns and an index on T1_IDX1(AC,
> PIC) columns.
>
> I run explain plan on this SQL:
>
> delete T1
> where CYN = 1997
> and CQN = 3
> and AC = '2175322166'
> and PIC = '17464'
>
> and I get this:
>
> Execute Plan
>

---

---

>
> TABLE ACCESS BY ROWID T1
> INDEX RANGE SCAN T1_IDX1
>
> Question:
> Why does the optimizer not use the PK? Would it not be reasonable to
> assume that the PK provides a better access path than the index
> T1_IDX1? Funny thing is, if I use all the columns in the PK for the
> delete, the explain shows use of the PK. But even if I include the
> following hint:
>
> delete /*+ index(T1, T1_PK) */ T1

Here's the problem. In order for the optimizer to understand the index hint you must have proper syntax. Remove the comma between the tablename and the indexname in the hint. THis should be the proper syntax to allow the optimizer to read it.

> ...
> ...
>
> The plan still comes out as above. BTW, this is on Oracle
7.3.2/Solaris
> 2.5.1.
>
> So, can anyone enlighten me as to why the optimizer chooses the index
> and not the pk or am I missing something really basic here.
>
> Thanks
>
> mkb
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 11 1999 - 17:13:37 CDT