Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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 100+ 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

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions