Re: Index help
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 24 Jul 2004 13:29:09 -0600
Now it's my turn...I missed the PK (focused on the row and value counts). Not
enough scotch...
Since this is using bind variables, there must be something about the first
parse that is causing this problem. Any chance you are using stored outlines?
In rereading the post, it is noted that an index was added and it is using the
index. But the output from tkprof is still reflecting a full table scan and not
the index. I think you need to run a 10046 and submit the stat lines from the
actual execution.
Regards,
Daniel
Terry Sutton wrote:
> OK, we've established that I wasn't awake enough to notice there were 29
> executions, but I'm still confused about the "primary key" thing. You say
> ida3a5 is the primary key. That means you have an index on it. Unless I'm
> still reading wrong, your query has a "(A0.idA3A5 = :1)" predicate. Seems
> to me that would indicate 1 index lookup and 1 row lookup. As Daniel points
> out, most of your executions return no rows, so the other predicates aren't
> satisfied. But I don't see how you would ever get a full scan instead of a
> primary key lookup.
>
> --Terry
>
> ----- Original Message -----
> I may be blind (no coffee yet), but you're doing a select with the primary
> key in the where clause and you get 4 rows? Something's wrong here.
>
> --Terry
>
> ----- Original Message -----
> From: "M.Godlewski" <mcgodlewski@xxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Friday, July 23, 2004 8:43 AM
> Subject: RE: Index help
>
> row count for table 5108
> primary key on table (ida3a5) has 5108 distinct_keys
> codec5 has 3 distinct values.
> scheduletime has 4781 distinct values.
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
