Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Server/Performance    [ Add a report in this area ]  
Report #:  123745   Status: Reported
SQL Query with left outer join does not choose indexed fields for plan
Project:  InterBase Build #:  11.0.1
Version:    11.0 Submitted By:   Orren Grushkin
Report Type:  Basic functionality failure Date Reported:  4/1/2014 11:34:37 AM
Severity:    Serious / Highly visible problem Last Updated: 4/3/2014 9:49:48 AM
Platform:    Windows 95/98/NT 4.0 on Intel 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
see below query:

select a.store, a.quan, a.extended, a.sold_store, a.reg_mkdwn, b.dept
from receipt c, recline a
left outer join sku b on (a.style = b.style and a.brand = b.brand)
where c.Dte_closed = '01/04/2014'
and c.store = 1
and c.typ in (1,2)
and a.rec_num = c.num and a.store = c.store

PLAN JOIN (JOIN (A NATURAL,B INDEX (RDB$PRIMARY197)),C INDEX (RDB$PRIMARY246))

It chose NATURAL on the largest table rather than indexing receipt on bydteclosedstore_RECEIPT and recline on Rec_numstore_RECLINE.

If you remove the reference to table sku b, the plan does choose the correct indices.

Note the following applicable indices:

Table Recline A:

Create index byskuall_RECLINE on RECLINE (Style,Brand,Ext,Store,siz);
Create index Rec_numstore_RECLINE on RECLINE (Rec_num,Store);

Table Receipt C:

Create index bydteclosedstore_RECEIPT on RECEIPT (Dte_closed,Store);
Primary key is num, store

Table B Primary key is Brand, style
Steps to Reproduce:
see description.

This is on windows 32
Workarounds
None
Attachment
None
Comments

Karol Bieniaszewski at 4/24/2014 12:46:06 AM -
You mix SQL92 and SQL98 join types
try to change query to join instead of comma delimited receipt c, recline a

Look if this change something

Orren Grushkin at 4/28/2014 2:33:27 PM -
Well that worked!! I'm not sure why mixing sql92 and sql98 would matter - but it chose the right plan!

Statement:
select a.store, a.quan, a.extended, a.sold_store, a.reg_mkdwn, b.dept
from receipt c inner join recline a on (a.rec_num = c.num and a.store = c.store)
left outer join sku b on (a.style = b.style and a.brand = b.brand)
where c.Dte_closed = '01/04/2014'
and c.store = 1
and c.typ in (1,2)

PLAN JOIN (JOIN (C INDEX (BYDTECLOSEDSTORE_RECEIPT),A INDEX (REC_NUMSTORE_RECLINE)),B INDEX (RDB$PRIMARY197))

Server Response from: ETNACODE01