Re: Question about an explain plan.

<dimitris_at_cs.umb.edu> wrote in message
news:1129941485.565870.288920_at_o13g2000cwo.googlegroups.com...
> 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.
>

I don't think I'd worry about it too much. There have been odd little bugs in explain plan in the past where the wrong number was copied in from the CBO calculations. This looks like you may have done an existence subquery that unnested - with the result that the number of rows in the table appeared as the cardinality in the original single table access path, and then got copied into the index path after the unnest. Does that 28,940,028 match user_tables.num_rows or user_indexes.num_rows, or is it approximately the number of rows in the table that match other constant predicates you may have on the t1 table ?

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available to pre-order.

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005