QualityCentral

Public Report

Report From: InterBase/Server/Internal/Optimizer [ Add a report in this area ]

Report #: 47916

Status: Closed

(Pulled) Left outer join not optimized when indexes should be used ------------------------------------------ 6/11/99 [jli] Anot

Project: InterBase

Build #: V5.5.0.742

Version: 8.0

Submitted By: QC<->Raid Replicator Account

Report Type: Basic functionality failure

Date Reported: 6/11/1999 12:00:00 AM

Severity: Serious / Highly visible problem

Last Updated: 7/10/2008 9:10:30 PM

Platform: Windows 95/98/NT 4.0 on Intel

Internal Tracking #: 60128

Resolution: Fixed (Resolution Comments)

Resolved in Build: : 8.1.0.257

Duplicate of: None

Voting and Rating

Overall Rating: No Ratings Yet

0.00 out of 5

Total Votes: None

Description

Left outer join not optimized when indexes should be used

------------------------------------------
6/11/99 [jli]

Another customer is hitting the similiar problem. I've logged bug 61347 before I've learned this bug exists.

Steps to Reproduce:

This query should use indexes for all the inner joins, but it doesn't use any.

SELECT
CUHIST.COMPANY_CODE, CUHIST.DEBTOR_CODE, CUHIST.SALES_PERSON,
CUHIST.TERRITORY, CUHIST.PROD_GROUP, CUHIST.SALES_VALUE_01,
CUHIST.SALES_VALUE_02, CUHIST.SALES_VALUE_03, CUHIST.SALES_VALUE_04,
CUHIST.SALES_VALUE_05, CUHIST.SALES_VALUE_13, CUHIST.SALES_VALUE_14,
CUHIST.SALES_VALUE_15, CUHIST.SALES_VALUE_16, CUHIST.SALES_VALUE_17,
CUHIST.SALES_VALUE_18, CUHIST.SALES_VALUE_19, CUHIST.SALES_VALUE_20,
CUHIST.SALES_VALUE_21, CUHIST.SALES_VALUE_22, CUHIST.SALES_VALUE_23,
CUHIST.SALES_VALUE_24, CUHIST.SALES_QTY_01, CUHIST.SALES_QTY_02,
CUHIST.SALES_QTY_03, CUHIST.SALES_QTY_04, CUHIST.SALES_QTY_05,
CUHIST.SALES_QTY_13, CUHIST.SALES_QTY_14, CUHIST.SALES_QTY_15,
CUHIST.SALES_QTY_16, CUHIST.SALES_QTY_17, CUHIST.SALES_QTY_18,
CUHIST.SALES_QTY_19, CUHIST.SALES_QTY_20, CUHIST.SALES_QTY_21,
CUHIST.SALES_QTY_22, CUHIST.SALES_QTY_23, CUHIST.SALES_QTY_24,
CUHIST.SALES_COST_01, CUHIST.SALES_COST_02, CUHIST.SALES_COST_03,
CUHIST.SALES_COST_04, CUHIST.SALES_COST_05, CUHIST.SALES_COST_13,
CUHIST.SALES_COST_14, CUHIST.SALES_COST_15, CUHIST.SALES_COST_16,
CUHIST.SALES_COST_17, CUHIST.SALES_COST_18, CUHIST.SALES_COST_19,
CUHIST.SALES_COST_20, CUHIST.SALES_COST_21, CUHIST.SALES_COST_22,
CUHIST.SALES_COST_23, CUHIST.SALES_COST_24,
    SAPRGP.PRODUCT_GROUP_NAME,
    ARMAST.DEBTOR_NAME,
    SYSREP.SALES_PERSON_NAME,
    SYTERR.TERRITORY_NAME
FROM
    (((SAHIST CUHIST LEFT OUTER JOIN ARMAST ARMAST ON
        CUHIST.DEBTOR_COMPANY = ARMAST.DEBTOR_COMPANY AND
    CUHIST.DEBTOR_CODE = ARMAST.DEBTOR_CODE)
     INNER JOIN SAPRGP SAPRGP ON
        CUHIST.PROD_GROUP = SAPRGP.PRODUCT_GROUP)
     INNER JOIN SYTERR SYTERR ON
        ARMAST.TERRITORY = SYTERR.TERRITORY)
     INNER JOIN SYSREP SYSREP ON
        ARMAST.SALES_PERSON = SYSREP.SALES_PERSON
WHERE
    CUHIST.COMPANY_CODE = '11' AND
    (CUHIST.PROD_GROUP <> 'ICSLSTAX' AND
    CUHIST.PROD_GROUP <> 'ICGLPOST')

Here is the plan generated on WI-V5.5.0.742
--------------------------------------------------------------------------
PLAN MERGE (SORT (SYSREP NATURAL),SORT (MERGE (SORT (SYTERR NATURAL),
SORT (MERGE (SORT (SAPRGP NATURAL),
SORT (JOIN (CUHIST INDEX (RDB$PRIMARY8),ARMAST INDEX (RDB$PRIMARY2))))))))

Indexes
-------------
RDB$PRIMARY2 UNIQUE INDEX ON ARMAST(DEBTOR_COMPANY, DEBTOR_CODE)
RDB$PRIMARY8 UNIQUE INDEX ON SAHIST(COMPANY_CODE, DEBTOR_CODE, WAREHOUSE_CODE, PRODUCT_CODE)
RDB$PRIMARY9 UNIQUE INDEX ON SAPRGP(PRODUCT_GROUP)
RDB$PRIMARY11 UNIQUE INDEX ON SYSREP(SALES_PERSON)
RDB$PRIMARY12 UNIQUE INDEX ON SYTERR(TERRITORY)

I have put the database and the query in /usr/gds/support/bug_10128

the database is: SA3001.GDB
the query is in: queryplan.sql
the database's metadata is in: test.sql

Workarounds

None

Attachment

Comments

None

Quality Central

Developer Tools

Database Tools