CODEWITHSUNDEEP
sqloracle-database
alexherm
alexherm

Reputation: 1362

Why is this not using index?

I have this basic query:

SELECT B.sap_adjustment_id,b.adjustment_type,'PROCESSED'
FROM mtl_material_transactions a
    RIGHT JOIN xxcb_RTL_adjustments_iface b ON a.attribute1=b.sap_adjustment_id
WHERE b.interface_status='NEW'
    AND b.adjustment_type='ADJUSTMENT'

As you Oracle e-Business Suite users know, mtl_material_transactions table is generally one of the biggest tables in the database.

I am joining to it on attribute1 from a custom table.

mtl_material_transactions has a number indexes, but I created one on attribute1 in an attempt to speed up the run time.

INFO    INDEX_NAME          UNIQUE  LOGGING DEGREE  COLUMN_NAME     POSITION    ORDER   OWNER
PLAIN   XXCB_MTL_TRX_ATTR1  N       YES     1       ATTRIBUTE1                  Asc     APPS

But when I run explain plan, I still get full table scans and index is not used.

Plan                                                                Cost    
SELECT STATEMENT  ALL_ROWS                                          442,861 
    3 HASH JOIN OUTER                                               442,861 
        1 TABLE ACCESS FULL TABLE APPS.XXCB_RTL_ADJUSTMENTS_IFACE   35  
        2 TABLE ACCESS FULL TABLE INV.MTL_MATERIAL_TRANSACTIONS     442,715

How should I change the index so that its used by this query?

Upvotes: 2

Views: 161

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Why is this not using index?

Because MTL_MATERIAL_TRANSACTIONS.ATTRIBUTE1 is a VARCHAR2 field and, judging by its name, the XXCB_RTL_ADJUSTMENTS_IFACE.SAP_ADJUSTMENT_ID is probably a NUMBER field.

That means, Oracle is doing an implicit type conversion to process the query and, so, is actually evaluating this join condition instead of the one you wrote:

ON TO_NUMBER(a.attribute1)=b.sap_adjustment_id

The TO_NUMBER() function that Oracle is implicitly wrapping around the column prevents it from using the index on that column.

Instead, try writing it this way:

ON a.attribute1=TO_CHAR(b.sap_adjustment_id)

Upvotes: 4

Related Questions