Re: Question about an explain plan.

dimitris_at_cs.umb.edu wrote:
> Hi,
>
> I got the following explain plan for a query:
>
> Order Execution Step Object Name Object Owner Est Cost Est Row
> Count Est Byte Count
> 7 [-] Select Statement [CHOOSE] 12 1 102
> 6 [-] Nested Loops 12 1 102
> 3 [-] Sort (Unique)
> 2 [-] Table Access (By Index Rowid)
> [ANALYZED] EMAILLATEST XXXXXXXX 5 1 20
> 1 [ ] Index (Range Scan)
> [ANALYZED] XPKEMAILLATEST XXXXXXXX 4 1
> 5 [-] Table Access (By Index Rowid)
> [ANALYZED] EMAIL XXXXXXXX 3 1 82
> 4 [ ] Index (Unique Scan)
> [ANALYZED] XPKEMAIL XXXXXXXX 2 28,940,028
>
>
> One thing I cannot understand in this plan is that the last step,
> which is a unique scan of an index, contains a very large number as
> the estimated row count (28,940,028). Do you know what this number
> would mean for the unique index scan. My understanding is that a unique
> index scan would always return a single rowid.
>
> Thank you,
>
> D.

Your understanding is incorrect:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref977

"Index unique scan is one of the most efficient ways of accessing data. This access method is used for returning the data from B-tree indexes. The optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions."

BTW: An explain plan without version number, etc. is reasonably meaningless. I note you don't have CPU cost in your output. Are you working in an old unsupported version of Oracle or have you not yet learned to use DBMS_XPLAN?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)