- 3,734,267 Users
- 2,246,933 Discussions
- 7,857,216 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 438 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.8K SQL Developer
- 291.3K Development
- 6 Developer Projects
- 117 Programming Languages
- 288.1K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 149 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 22 JavaScript - Nashorn
- Programs
- 180 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Is APEX optimization behaves differently than SQL Developer?
Hi All,
I have come across one issue for one of my SQL queries.
When I run that page using LEVEL9 debugging, it gives me explain plan and in that one of table is showing as FULL SCAN and when I run same query with same parameters in SQL Developer, it uses the index and gives results in milliseconds.
Has someone faced similar issue or is there any way/steps to follow so the query uses proper index in APEX?
I am using APEX 18.2 and I have attached examples of explain plan from both APEX and SQL Dev.
From the screenshot you can see that in APEX it is showing cost as 3190 and in SQL Developer it is showing 24. I further found by checking each step of explain plan that one of my database table is doing a full scan in APEX and using proper index in SQL Developer so I am getting confused why would such thing happen.
I tried rebuilding all indexes of that table but it has same issue in APEX.
Can someone suggest what can I do to resolve this issue.
Answers
-
I have come across one issue for one of my SQL queries.
When I run that page using LEVEL9 debugging, it gives me explain plan and in that one of table is showing as FULL SCAN and when I run same query with same parameters in SQL Developer, it uses the index and gives results in milliseconds.
Has someone faced similar issue or is there any way/steps to follow so the query uses proper index in APEX?
I am using APEX 18.2 and I have attached examples of explain plan from both APEX and SQL Dev.
From the screenshot you can see that in APEX it is showing cost as 3190 and in SQL Developer it is showing 24.
I can't see any useful information there and visually impaired users can see nothing at all. DO NOT post inaccessible screenshots of textual information.
Post the complete explain plans as suitably formatted text.
I further found by checking each step of explain plan that one of my database table is doing a full scan in APEX and using proper index in SQL Developer so I am getting confused why would such thing happen.
I tried rebuilding all indexes of that table but it has same issue in APEX.
Can someone suggest what can I do to resolve this issue.
It depends. Where, when, and why is the query being used? If it is used as a region source, state the region type. If it is a report region, provide the pagination settings.
Post the full query as entered, and that shown in the debug trace at runtime as it is likely to have been wrapped in additional SQL to support APEX UI features and internal processing.
-
These kind of questions regularly pop up, and it's usually one of these "issues":
- SQLDeveloper does not take paging into account (rows x-y)
- SQLDeveloper does not take total number of rows into account (rows x-y of z)
- SQLDeveloper does not take LOV lookups into account
- SQLDeveloper does not take an order by into account
-
The query appears to be incomplete and there is no SQL Developer execution plan for comparison.
-
Still not complete. The posted query is missing the termination of the APEX-generated
dandiinline views.You don't appear to be comparing the same thing. Specifically, the SQL Developer execution plan is missing the
WINDOW NOSORT STOPKEYoperation we would expect an APEX pagination filter to produce.Get a SQL Developer plan for the actual query executed by APEX as shown in the debug log, not the region source entered.
