Forum Stats

  • 3,728,698 Users
  • 2,245,675 Discussions
  • 7,853,706 Comments

Discussions

adf View Criteria full access table

moi9082234
moi9082234 Member Posts: 35
edited June 2017 in JDeveloper and ADF

Hi,

I created a view based on entity, and I also created a view criteria based on  indexes ,but when I traced all queries in database I found that adf  scan in full table.

I want here to optimise my database access to oblige adf use indexes because I have huge tables.

for example :

SELECT  * FROM (SELECT 

T_V_TEST.ID, 

T_V_TEST.ABRV,

T_V_TEST.LIB, 

T_V_TEST.ID_COM 


  FROM

T_V_TEST) QRSLT  WHERE ( ( (ID_COM = :Par_Com ) ) )

the explain plan contains this line

TABLE ACCESS FULL T_V_TEST

such as I have an index in id_com. how to oblige adf use this index in view criteria.

any suggestions

thanks

Tagged:
moi9082234

Answers

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 36,976 Red Diamond
    edited June 2017

    User, tell us your jdev version, please!

    I testet this with the HR DB schema and the query

    select * from (select * from employees) qrslt where department_id = :pID;

    To get

    pastedImage_6.png

    and using

    select * from employees where department_id = :pID;

    I  get

    pastedImage_15.png

    So it's using the index in both cases. May be the optimizer has choosen to not use the index, but this is not ADFs fault.

    Timo


    moi9082234moi9082234
  • moi9082234
    moi9082234 Member Posts: 35
    edited June 2017

    Hello Timo,

    you are right I have a mistake of choice of index.

    thanks

This discussion has been closed.