Re: Explain Plan Weirdness

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 10 May 1999 23:31:28 +0200
Message-ID: <926371761.14446.0.pluto.d4ee154e@news.demon.nl>

First question:
which optimizer are you using. Probably the rule based optimizer: no statistics on the tables and optimizer_goal in init<sid>.ora = choose. In this case the RBO will be used and hints will be -AFAIK- ignored. Secondly, the typical RBO behavior is to take the take with the lowest number of columns satisfying the where clause. So yes, if your delete statement is the way it is, RBO won't use the PK. Of course you can trick it to do that (which is more difficult with CBO), by simply writing AND CYN > 0.
IMO you should do some measurements if you make this change, to see whether the PK is really more efficient. I doubt it very much, as there is no equality on the leading column of the index... Equality always has priority over range.

Hth,

Sybrand Bakker, Oracle DBA
MKB wrote in message <3737493C.F936F4_at_mci.com>...
>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
>...
>...
>
>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
>
>
Received on Mon May 10 1999 - 16:31:28 CDT