Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.

Public Report
Report From: InterBase/Server/Internal/Optimizer    [ Add a report in this area ]  
Report #:  116685   Status: Reported
PLAN NATURAL but primary key should be used
Project:  InterBase Build #:  WI-V10.0.5.595
Version:    10.0 Submitted By:   Karol Bieniaszewski
Report Type:  Basic functionality failure Date Reported:  6/19/2013 4:58:19 AM
Severity:    Commonly encountered problem Last Updated: 7/16/2013 11:52:35 PM
Platform:    Not OS or platform specific Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
PLAN NATURAL but primary key should be used

follow [STEPS]

Steps to Reproduce:
Create database with attached script - this is simplified script

then do this query

SELECT V.*, T3.*, T1.* FROM
VLAST_TEST1_FOR_TEST3 V
INNER JOIN TEST3 T3 ON T3.ID=V.ID3
LEFT JOIN TEST1 T1 ON T1.ID=V.ID1


PLAN SORT ((T2 INDEX (RDB$FOREIGN31)))
PLAN SORT ((T2 INDEX (RDB$FOREIGN31)))
PLAN JOIN (JOIN (V T3 NATURAL,T3 INDEX (RDB$PRIMARY30)),T1 NATURAL)


As you can see that for table T1 there is natural scan - my real tables have big count of records
i try to change this plan and store it in query but when i try do this
PLAN JOIN (JOIN (V T3 NATURAL,T3 INDEX (RDB$PRIMARY30)),T1 INDEX(RDB$PRIMARYXXX))

optimizer tell me that this index can not be used in this plan
Workarounds
None
Attachment
problematyczny_plan_zapytania.zip
Comments

Karol Bieniaszewski at 6/21/2013 1:18:47 AM -
I update script (missing colons) then you can use it simplier

Server Response from: ETNACODE01