Re: Yes, this is probably a stupid question but...

"Pat" <pat.casey_at_service-now.com> wrote in message news:394e6ddb-24ca-4013-81ba-aa3e2de49491_at_m44g2000hsc.googlegroups.com...
>
> In this case, looks like having a histogram didn't help. The active
> column has two potential values (0 or 1), so it's essentially a
> boolean. Even with a 2 columns histogram on it, the optimizer is still
> going down nested loops.
>
> I may take a crack at putting in an a refreshing materialized view
> though. Need to sit down and do a little match on relative transaction
> frequency though, want to make sure I end up saving cycles rather than
> burning them.
>
> Thanks again!
>
> SQL> analyze table task compute statistics for columns "active" size
> 2;
>
> Table analyzed.
>
> SQL> explain plan for select count(*) from incident inner join task on
> incident."sys_id" = task."sys_id" where task."active" = 1;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 1023873955
>
> -----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> -----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 68 | 32 (7)|
> 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 68 | | |
> | 2 | NESTED LOOPS | | 155K| 10M| 32 (7)|
> 00:00:01 |
> |* 3 | INDEX FAST FULL SCAN| ACTIVE_TASK | 197K| 6755K| 29
> (0)| 00:00:01 |
> |* 4 | INDEX UNIQUE SCAN | INCIDENT_SYSID | 1 | 33 |
> 1 (0)| 00:00:01 |
> -----------------------------------------------------------------------------------------
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - filter("TASK"."active"=1)
> 4 -
> access(NLSSORT(INTERNAL_FUNCTION("sys_id"),'nls_sort=''BINARY_CI''')=NLSSO
>
> RT(INTERNAL_FUNCTION("TASK"."sys_id"),'nls_sort=''BINARY_CI'''))
>
> 18 rows selected.

Pat,

The costing in this plan are a little surprising - but there are two possibilities that I can think of might explain them

What's your value for optimizer_index_caching - has it been set to something close to 100 ?

Is this (very specifically) running 10.2.0.1 ?

I guess it's is also possible that the NLS index is introducing a side effect that I've not come across before - can you show us the plan you get (including costs and predicates as above) when you hint the hash join suggested by Charles Hooper.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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