Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Yisus.

Asked: July 31, 2018 - 9:39 pm UTC

Last updated: August 02, 2018 - 3:49 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Good Afternoon,
I have a table for the generation of a report by year and week but when I execute the query a TAF is marked.
I tried to force the indexes but the execution plan ignores it.
What can I do to take the index?, Is it necessary to change the index?

my index is composed in the fields (FIANIO, FISEMANA)

CREATE TABLE PFPROFIN.TAFTREP
( 
 FIIDSUCURSAL       NUMBER(10)    NOT NULL
 FIANIO             NUMBER(4), 
 FISEMANA           NUMBER(2), 
 FIIDPUESTO         NUMBER(38),
 CUOTA              NUMBER(15,2)
 SUMADEUDOR         NUMBER(15,2)
);


SELECT * 
FROM PFPROFIN.TAFTREP 
 WHERE  FISEMANA = :PA_SEMANA 
  AND    FIANIO = :PA_ANIO 

SELECT STATEMENT ALL_ROWS Cost: 223 Bytes: 2,732,729 Cardinality: 40,787 
1 TABLE ACCESS FULL TABLE PFPROFIN.TAFTREP Cost: 223 Bytes: 2,732,729 Cardinality: 40,787 


One example of data:

FIIDSUCURSAL       FIANIO    FISEMANA   FIIDPUESTO    CUOTA   SUMADEUDOR                           
1                   2018       29         25            200      1500
1                   2018       29         24            200      15000
1                   2018       29         25            200      1500
1                   2018       29         25            200      1500
13                  2018       28         25            100      1000
13                  2018       28         25            100      1000


Thanks and greetings!!

and Chris said...

There are many possible reasons why the optimizer hasn't chosen the index. This could be out-of-date or missing stats.

Or maybe a full scan is faster. As Tom discusses here:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=blocks-read-using-index-vs-full-table-scan

If you want to know more about why the database chooses an index (or not), watch this video and the others in the series:



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.