Re: SQL query clarification
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 5 Jan 2017 05:40:59 -0500
On 01/04/2017 11:25 PM, Ram Raman wrote:
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.
--
Ram, Oracle cost based optimizer is a very complex beast, chock full of
bugs and anomalies. It is quickly getting so complex that human beings,
even highly trained DBA personnel like you, have a hard time managing
it. Have you considered clustering TIMESHEETITEM and JAID tables on the
PERSONID column? Clustering would put the rows of the both tables
sharing the common PERSONID value into the same blocks. The declarative
nature of the construct would also aid the optimizer with the proper plan.
Regards
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
Other related posts:
