Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 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
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Optimization of pl/sql

Hi All ,
This is my query it takes 30 second to process . I need to optimize it pls provide me some solution. I also heard about EXPLAIN PLAN what is that?
Select unique api_WEEKLY.country From api_WEEKLY INNER JOIN USERPROFILE
ON api_WEEKLY.REGION = NVL(USERPROFILE.REGION,api_WEEKLY.REGION)
AND api_WEEKLY.Category = NVL(USERPROFILE.SECTOR,api_WEEKLY.Category)
AND api_WEEKLY.MRP_DRP_CONTROLLER = NVL(USERPROFILE.MRP_DRP_CONTROLLER,api_WEEKLY.MRP_DRP_CONTROLLER)
AND api_WEEKLY.TDC_VAL = NVL(USERPROFILE.TDC_VAL,api_WEEKLY.TDC_VAL)
AND api_WEEKLY.PLANT = NVL(USERPROFILE.PLANT,api_WEEKLY.PLANT)
AND api_WEEKLY.MATERIAL = NVL(USERPROFILE.MATERIAL,api_WEEKLY.MATERIAL)
AND api_WEEKLY.SUBSECTOR = NVL(USERPROFILE.SUBSECTOR,api_WEEKLY.SUBSECTOR)
AND api_WEEKLY.COUNTRY = NVL(USERPROFILE.COUNTRY,api_WEEKLY.COUNTRY)
WHERE USERPROFILE.USER_ID = 'sheikh.a' ;
and this is my User table
Answers
-
I also heard about EXPLAIN PLAN what is that?
Have you heard about Google? This was the first hit I got when I searched for "Explain Plan": Using EXPLAIN PLAN
-
Your query can't use any indexes because you are applying a function (NVL) to each of the where clause predicates. So it will be doing a full table scan.
-
What about...
alter session set optimizer_guess_op_meaning = true; Select unique api_WEEKLY.country From api_WEEKLY INNER JOIN USERPROFILE ON api_WEEKLY.REGION = NVL(USERPROFILE.REGION,api_WEEKLY.REGION) AND api_WEEKLY.Category = NVL(USERPROFILE.SECTOR,api_WEEKLY.Category) AND api_WEEKLY.MRP_DRP_CONTROLLER = NVL(USERPROFILE.MRP_DRP_CONTROLLER,api_WEEKLY.MRP_DRP_CONTROLLER) AND api_WEEKLY.TDC_VAL = NVL(USERPROFILE.TDC_VAL,api_WEEKLY.TDC_VAL) AND api_WEEKLY.PLANT = NVL(USERPROFILE.PLANT,api_WEEKLY.PLANT) AND api_WEEKLY.MATERIAL = NVL(USERPROFILE.MATERIAL,api_WEEKLY.MATERIAL) AND api_WEEKLY.SUBSECTOR = NVL(USERPROFILE.SUBSECTOR,api_WEEKLY.SUBSECTOR) AND api_WEEKLY.COUNTRY = NVL(USERPROFILE.COUNTRY,api_WEEKLY.COUNTRY) WHERE USERPROFILE.USER_ID = 'sheikh.a' ;
no rows selected. Elapsed Time for Script Execution: 518 msecs.
At least it's faster...
Gerard
-
What you have posted is akin to saying your car is slow, it is painted green, uses unleaded fuel, and runs on 4 tires.
In other words, nothing about WHAT is actually the problem. Going slow (in a car or a query) is a symptom at best - not a problem definition.
See - from the FAQ (link right at the top of this forum space's home page).
-
Since you have a row where all the columns (other than user_id) are null then the query is equivalent to:
select distinct
wek.country
from
api_weekly wek
where
wek.region is not null
and wek.category is not null
and wek.mrp_drp_controller is not null
and wek.tdc_val is not null
and wek.plant is not null
and wek.material is not null
and wek.subsector is not null
and wek.country is not null
;
Regards
Jonathan Lewis