Thanks for the question, Sundaram.
Asked: June 03, 2024 - 1:56 pm UTC
Last updated: June 06, 2024 - 7:01 am UTC
Version: 10g
Viewed 1000+ times
You Asked
Hello Experts,
For an upcoming audit requirement, am working on a query. I am facing performance issues in getting the data.
In one table gl_import_references the transactional references are stored in reference columns. There is a index in this table for columns 6,2,3 in order. I want to leverage the index to get the accounting information from Ledger. I tried using the Index Hint as mentioned below.
SELECT /*+ INDEX (gl_import_references GL_IMPORT_REFERENCES_N3) */
/*+ INDEX (gl_import_references REFERENCE_6, REFERENCE_2, REFERENCE_3) */
When I execute the query for a month or three months, I could see for the access predicate all the three indexed columns are considered and the results are returned in few seconds. When I execute the query for a year, the access predicate considers only Reference_6 and in the filter predicate the other columns are there. Considering the volume of data, the executes for hours.
Can you guys suggest what could be wrong.
Thanks
Sundaram
and Connor said...
We need to see some execution statistics.
Run the query with the GATHER_PLAN_STATISTICS hint and then check the output of
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST +PEEKED_BINDS'))
Add that as a comment and we'll work from there
Is this answer out of date? If it is, please let us know via a Comment