Revious
Revious

Reputation: 8146

Oracle: Forcing index usage

I've got this two index:

CREATE INDEX NETATEMP.CAMBI_MEM_ANIMALI_ELF_T2A ON NETATEMP.CAMBI_MEM_ANIMALI_ELF_T2
(TELE_TESTATA_LETTURA_ID, ELF_DATA_FINE_FATTURAZIONE)

CREATE INDEX NETATEMP.LET_TESTATE_LETTURE1A ON NETATEMP.LET_TESTATE_LETTURE1
(TELE_STORICO_ID, TRUNC("TELE_DATA_LETTURA"))


CREATE TABLE NETATEMP.cambi_mem_animali_elf
AS
   SELECT   --/*+ parallel(forn 32)  */
            DISTINCT
            forn_fornitura_id,
            TRUNC (tele.TELE_DATA_LETTURA) TELE_DATA_LETTURA,
            forn.edw_partition,
            DECODE (SUBSTR (forn.TELE_TESTATA_LETTURA_ID, 1, 1), '*', 'MIGRATO', 'INTEGRA') Origine
     FROM   NETATEMP.cambi_mem_animali_elf_t2 forn,
            netatemp.let_testate_letture1 tele
            WHERE forn.tele_testata_lettura_id = tele.tele_storico_id
            --
            AND forn.ELF_DATA_FINE_FATTURAZIONE != TRUNC (tele.TELE_DATA_LETTURA)

It uses two full table scan. I simply can't understand why Oracle doesn't look at both index and makes and index range scan after that. How can I force to do so? enter image description here

Upvotes: 0

Views: 137

Answers (3)

David Aldridge
David Aldridge

Reputation: 52356

If you have the partitioning option then consider hash partitioning the two tables on the join columns. A partition-wise join will greatly reduce the memory requirement and likelihood of the join spilling to disk.

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

You are referencing columns that are not included in the indexes, so even if the join itself would be faster using index, Oracle would anyway have to retrieve all the table blocks for the remaining columns.

For reference: Depending on statistics you may get the index join you are looking for with the first of these two queries because it can be resolved with index only, whereas the second query has to go to the table.

select count(*)
 from netatemp.cambi_mem_animali_elf_t2 forn
     ,netatemp.let_testate_letture1     tele
where forn.tele_testata_lettura_id = tele.tele_storico_id;


select count(*), min(forn.edw_partition)
 from netatemp.cambi_mem_animali_elf_t2 forn
     ,netatemp.let_testate_letture1     tele
where forn.tele_testata_lettura_id = tele.tele_storico_id;

Upvotes: 1

Markus Winand
Markus Winand

Reputation: 8716

It's because HASH joins don't use indexes on the join predicates.

Read this for all the details: http://use-the-index-luke.com/sql/join/hash-join-partial-objects

Upvotes: 3

Related Questions