We help IT Professionals succeed at work.
Get Started
Private
Troubleshooting Question

Plan statistics - Am I reading this correctly???

MariaHalt
MariaHalt asked
on
102 Views
Last Modified: 2021-02-05
Hi Experts, I hope you're looking for a challenge today.  
This is my query.  The values in brackets are dummies to protect the innocent :)  

with 
cte_1 as (
select [a few fields] 
from [a really big table] 
where status = [some status] 
and [date_field]  >= sysdate - 365
), 
cte_2 as (
select [a few fields] 
from [the same really big table] 
where status = [some other status] 
and [date_field]  >= sysdate - 365
)
select /*+ gather_plan_statistics */
[a field], count(*) as rcdcnt
from cte_1 
inner join cte_2 on cte_1.<field xyz> = cte_2.<field xyz>
where cte_2.[date_field]  <  cte_1.[date_field] 
group by [a field]
order by [a field]

This runs painfully s-l-o-w.  The status and date_field are indexed separately.  An extended statistic has been created between [status] and [date field].  

The execution plan, with my commentary, looks like this:

I believe execution starts with lines id 3 and 4, sends those results to line id 2 and then goes to line 5 and sends those back to 2 as well.  And then up the tree from there.  

My first question is how do I know it's using the extended statistic?  Should I have used a hint for that in my query?  By the way, this is what the extended statistic looks like:

My second question is what can I do to get the estimated rows down?  Please feel free to educate me.  
Comment
Watch Question
Software Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 108 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE