Optimizer confidence level and understanding explain plan

Database

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

2 REPLIES
Enthusiast

Re: Optimizer confidence level and understanding explain plan

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

Enthusiast

Re: Optimizer confidence level and understanding explain plan

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...