The most curious thing about the autotrace output for the first plan is the
30,000 recursive calls.
The Rows estimates could have come from something as simple as the statistics
on some of the tables being very misleading - as Stefan pointed out the
optimizer can display numbers that show a dramatic difference between the
number table estimates and index estimates for very odd reasons - but the
recursive calls suggests to me that autotrace is not telling you about the
actual execution plan, and the 31,000 values are a clue that Oracle is doing
something completely different.
Have you tried running the update with statistics_level set to all,
serveroutput off, and then issuing
select * from table(dbms_xplan.display_cursor(null,null,'allstats last');
Couple of other thoughts - are any of the columns virtual columns hiding
functions ?
What are the column types of the date/time columns being compared ?
Having 4 indexes that start with the primary key column is a design error -
more so since two of them overlap on the first 3 columns - such things help the
optimizer find "rare" code paths that may have avoided bug testing for years.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Ram Raman <veeeraman@xxxxxxxxx>
Sent: 05 January 2017 04:25:29
To: ORACLE-L
Subject: SQL query clarification
List
I was trying to tune a query in 11g EE. Here is the link:
https://drive.google.com/open?id=0B9YC82qZ8_3eemF5b3kzTlh0eVU
I was looking at the plan and one thing i could not understand was ids 8 and 9
in the second plan (phv 1362929457). After getting the employeeid from the
TIMESHEEITEM table, the optimizer selects the matching row from the jaids
table. jaids table has PERSONID as primary key. Why would the optimizer say
that it would (or really did) access all the 31.5K rows of JAIDS. Same
question with PERSON table.
Ram.
--
--
//www.freelists.org/webpage/oracle-l
