Calling findById for a JsonView results in a query like this
SELECT student_view_.* FROM "STUDENT_SCHEDULE" student_view_ WHERE (student_view_.DATA."_id" = ?)
which triggers a full table/index scan.
Actual Behaviour
Calling findById for a JsonView should use .numberOnly() (see Oracle Docs)
SELECT student_view_.* FROM "STUDENT_SCHEDULE" student_view_ WHERE (student_view_.DATA."_id".numberOnly() = ?)
which allows the OracleDB to use an unique index lookup.
Steps To Reproduce
Run oracle-jsonview-demo-app with query logging (add <logger name="io.micronaut.data.query" level="DEBUG"/> to logback.xml)
curl localhost:8080/students/1
In SQL*Plus: SET AUTOTRACE ON EXP and execute query logged by micronaut
Result shows INDEX FAST FULL SCAN or TABLE ACCESS FULL and warns:
1 - SEL$A8194D38 / "OUTER_ALIAS0"@"SEL$2"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"ID"
Environment Information
Should currently occur in every environment and with every database which supports JsonViews.
AaronTacke
commented
1 week ago
Expected Behavior
Calling findById for a JsonView results in a query like this
SELECT student_view_.* FROM "STUDENT_SCHEDULE" student_view_ WHERE (student_view_.DATA."_id" = ?)which triggers a full table/index scan.Actual Behaviour
Calling findById for a JsonView should use .numberOnly() (see Oracle Docs)
SELECT student_view_.* FROM "STUDENT_SCHEDULE" student_view_ WHERE (student_view_.DATA."_id".numberOnly() = ?)which allows the OracleDB to use an unique index lookup.Steps To Reproduce
<logger name="io.micronaut.data.query" level="DEBUG"/>to logback.xml)SET AUTOTRACE ON EXPand execute query logged by micronaut Result showsINDEX FAST FULL SCANorTABLE ACCESS FULLand warns:Environment Information
Should currently occur in every environment and with every database which supports
JsonViews.Example Application
https://github.com/oracle-samples/oracle-db-examples/tree/main/java/micronaut-jsonview-demo-app
Version
4.5.0