All Forums Database
ramnathjk 1 post Joined 03/14
15 Apr 2014
Optimizer confidence level and understanding explain plan

Hi Guys,
 
I have a doubt regarding the confidence levels of the optimizer in the explain plan.
 
Below is the scenario that i created:
1. Created 2 tables Location and Employee with unique primary indexes on EmpNo column
 


CREATE SET TABLE TDUSER.LOCATION ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      EmpNo INTEGER,
      Loc VARCHAR(25) CHARACTER SET LATIN CASESPECIFIC)
UNIQUE PRIMARY INDEX ( EmpNo );

CREATE SET TABLE TDUSER.employee ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      EmpNo INTEGER,
      Name VARCHAR(12) CHARACTER SET LATIN CASESPECIFIC,
      DeptNo SMALLINT,
      JobTitle VARCHAR(12) CHARACTER SET LATIN CASESPECIFIC,
      Salary DECIMAL(8,2))
UNIQUE PRIMARY INDEX ( EmpNo );

 
2. Inserted sample rows in both the tables with 12 rows
3. Collected both column level and table level statistics
4. When i check the explain plan it shows as below :
 
……the AMPs.  The size of Spool 1 is estimated with low confidence to
be 10 rows (125 bytes).  The estimated time for this step is 0.04……
 
Can someone please help me to understand why is the explain plan showing low confidence instead of high confidence even though the statistics was collected ?
Thanks in Advance.
 
Regards,
 
Ramanth

Santanu84 122 posts Joined 04/13
16 Apr 2014

Hi
If you are joining both the tables, kindly share the query. Also share the collect stats statements you executed and if possible the entire explain plan.
Thanking You
Santanu

Qaisar Kiani 337 posts Joined 11/05
16 Apr 2014

If the joining is based on the PI and the stats are refreshed like you said, then the query on PI column join should give the explain plan with high confidence as both the joining rows would be on the same AMP.
Please share the SQL query which you are executing...

You must sign in to leave a comment.