Optimize Oracle SQL query
Oracle SQL Performance Tuning
Can someone please help me optimize this query? (Please note that I had multiple subqueries in select statement, I am only providing here 2 subqueries for ease).
I have provided explain plan here and will be adding the details of table too.
(This is the case where I converted a SQL server DTS package into Oracle Procedure. So, all the tables are created by me and there is almost no index present.)
SELECT dlr.PK_DealerCode_vc,
dlr.DealerNameShort_vc,
CASE WHEN mmacode_vc = '' THEN Province_vc
ELSE '' END,
dlr.AREAName_vc,
dlr.MMAName_vc,
gmma.GMMAName_vc,
ZoneName_vc,
to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy'),
NVL((SELECT SUM(VehicleCount_in)
FROM VehicleCountByDA b
INNER JOIN DomesticGeographyData a ON TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc)
WHERE (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 4
AND TRIM(a.COCMACode_vc) = TRIM(dlr.PK_DealerCode_vc)
AND gmprimarycode_vc = 'R'
AND TRIM(gmcustomweightgroupname_vc) = 'NON-TRUCK'), 0) "CarIndY - 4",
NVL((SELECT SUM(VehicleCount_in)
FROM VehicleCountByDA b
INNER JOIN DomesticGeographyData a ON TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc)
WHERE (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3
AND TRIM(a.COCMACode_vc) = TRIM(dlr.PK_DealerCode_vc)
AND gmprimarycode_vc = 'R'
AND TRIM(gmcustomweightgroupname_vc) = 'NON-TRUCK'), 0) "CarIndY - 3"
FROM DealerFile dlr,
(SELECT DISTINCT PK_DealerCode_vc,
GMMAName_vc
FROM DealerGuideFile) gmma
WHERE ((nvl(dlr.ChevFranchise_bt, 0) = 1
AND nvl(dlr.PBGFranchise_bt, 0) = 0))
AND StatusName_vc = 'Active'
AND TRIM(dlr.PK_DealerCode_vc) = TRIM(gmma.PK_DealerCode_vc)
AND DealerName_vc NOT LIKE ('%Study Area%')
AND DealerName_vc NOT LIKE ('%Open Point%')
AND DealerName_vc NOT LIKE ('%Branch%')
AND LENGTH(RTRIM(dlr.PK_DealerCode_vc)) <= 5;
Explain Plan statement is attached here:
Plan hash value: 37211997
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1781 | 142K (2)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 55 | | |
|* 2 | HASH JOIN | | 2833 | 152K| 23748 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | GMSQLDBDOMESTICGEOGRAPHYDATA | 539 | 8085 | 1020 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | GM_TEMP_VEHICLECOUNTBYDA | 525 | 21000 | 22728 (2)| 00:00:02 |
| 5 | SORT AGGREGATE | | 1 | 55 | | |
|* 6 | HASH JOIN | | 2833 | 152K| 23748 (2)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | GMSQLDBDOMESTICGEOGRAPHYDATA | 539 | 8085 | 1020 (1)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | GM_TEMP_VEHICLECOUNTBYDA | 525 | 21000 | 22728 (2)| 00:00:02 |
| 9 | VIEW | VM_NWVW_1 | 13 | 1781 | 16 (7)| 00:00:01 |
| 10 | HASH UNIQUE | | 13 | 1898 | 16 (7)| 00:00:01 |
|* 11 | HASH JOIN | | 13 | 1898 | 15 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL| GMSQLDBGMDEALERFILE | 3 | 342 | 10 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL| MAEDEALERGUIDEFILE | 511 | 16352 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$16C51A37
3 - SEL$16C51A37 / A@SEL$1
4 - SEL$16C51A37 / B@SEL$1
5 - SEL$20B10B79
7 - SEL$20B10B79 / A@SEL$2
8 - SEL$20B10B79 / B@SEL$2
9 - SEL$3E77572C / VM_NWVW_1@SEL$A5893905
10 - SEL$3E77572C
12 - SEL$3E77572C / DLR@SEL$3
13 - SEL$3E77572C / MAEDEALERGUIDEFILE@SEL$6
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRIM("A"."PK_DA_VC")=TRIM("B"."PK_DA_VC"))
3 - filter(TRIM("A"."COCMACODE_VC")=TRIM(:B1))
4 - filter(2020-"B"."REPORTYEAR_IN"=4 AND TRIM("B"."GMCUSTOMWEIGHTGROUPNAME_VC")='NON-TRUC
K' AND "B"."GMPRIMARYCODE_VC"='R')
6 - access(TRIM("A"."PK_DA_VC")=TRIM("B"."PK_DA_VC"))
7 - filter(TRIM("A"."COCMACODE_VC")=TRIM(:B1))
8 - filter(2020-"B"."REPORTYEAR_IN"=3 AND TRIM("B"."GMCUSTOMWEIGHTGROUPNAME_VC")='NON-TRUC
K' AND "B"."GMPRIMARYCODE_VC"='R')
11 - access(TRIM("DLR"."PK_DEALERCODE_VC")=TRIM("PK_DEALERCODE_VC"))
12 - filter(NVL("DLR"."PBGFRANCHISE_BT",0)=0 AND
LENGTH(RTRIM("DLR"."PK_DEALERCODE_VC"))<=5 AND "STATUSNAME_VC"='Active' AND "DEALERNAME_VC"
NOT LIKE '%Study Area%' AND "DEALERNAME_VC" NOT LIKE '%Open Point%' AND "DEALERNAME_VC" NOT
LIKE '%Branch%' AND NVL("DLR"."CHEVFRANCHISE_BT",0)=1 AND "DEALERNAME_VC" IS NOT NULL AND
"DEALERNAME_VC" IS NOT NULL AND "DEALERNAME_VC" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("B"."VEHICLECOUNT_IN")[22]
2 - (#keys=1; rowset=256) "B"."VEHICLECOUNT_IN"[NUMBER,22]
3 - (rowset=256) "A"."PK_DA_VC"[VARCHAR2,8]
4 - (rowset=256) "B"."PK_DA_VC"[VARCHAR2,10], "B"."VEHICLECOUNT_IN"[NUMBER,22]
5 - (#keys=0) SUM("B"."VEHICLECOUNT_IN")[22]
6 - (#keys=1; rowset=256) "B"."VEHICLECOUNT_IN"[NUMBER,22]
7 - (rowset=256) "A"."PK_DA_VC"[VARCHAR2,8]
8 - (rowset=256) "B"."PK_DA_VC"[VARCHAR2,10], "B"."VEHICLECOUNT_IN"[NUMBER,22]
9 - "$vm_col_3"[VARCHAR2,100], "$vm_col_4"[VARCHAR2,2], "$vm_col_5"[VARCHAR2,4],
"$vm_col_6"[VARCHAR2,50], "$vm_col_7"[VARCHAR2,25], "$vm_col_8"[VARCHAR2,30],
"$vm_col_9"[VARCHAR2,25], "$vm_col_10"[VARCHAR2,8]
10 - (#keys=11) ROWID[ROWID,10], "GMMANAME_VC"[VARCHAR2,30],
"PK_DEALERCODE_VC"[VARCHAR2,8], "DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100],
"PROVINCE_VC"[VARCHAR2,2], "MMACODE_VC"[VARCHAR2,4], "DLR"."AREANAME_VC"[VARCHAR2,50],
"DLR"."MMANAME_VC"[VARCHAR2,25], "GMMANAME_VC"[VARCHAR2,30], "ZONENAME_VC"[VARCHAR2,25],
"DLR"."PK_DEALERCODE_VC"[VARCHAR2,8]
11 - (#keys=1) ROWID[ROWID,10], "DLR"."PK_DEALERCODE_VC"[VARCHAR2,8],
"DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100], "PROVINCE_VC"[VARCHAR2,2],
"ZONENAME_VC"[VARCHAR2,25], "MMACODE_VC"[VARCHAR2,4], "DLR"."MMANAME_VC"[VARCHAR2,25],
"DLR"."AREANAME_VC"[VARCHAR2,50], "PK_DEALERCODE_VC"[VARCHAR2,8], "GMMANAME_VC"[VARCHAR2,30]
12 - (rowset=256) ROWID[ROWID,10], "DLR"."PK_DEALERCODE_VC"[VARCHAR2,8],
"DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100], "PROVINCE_VC"[VARCHAR2,2],
"ZONENAME_VC"[VARCHAR2,25], "MMACODE_VC"[VARCHAR2,4], "DLR"."MMANAME_VC"[VARCHAR2,25],
"DLR"."AREANAME_VC"[VARCHAR2,50]
13 - (rowset=256) "PK_DEALERCODE_VC"[VARCHAR2,8], "GMMANAME_VC"[VARCHAR2,30]
CREATE TABLE GMSQLDBDomesticGeographyData (
PK_DA_vc Varchar2(8) NOT NULL ,
FSA_vc Varchar2(3) NULL ,
FSAName_vc Varchar2(100) NULL ,
COCMACode_vc Varchar2(5) NULL ,
COCMAName_vc Varchar2(50) NULL ,
PBGMACode_vc Varchar2(5) NULL ,
PBGMAName_vc Varchar2(50) NULL ,
CADMACode_vc Varchar2(5) NULL ,
CADMAName_vc Varchar2(50) NULL ,
HUMMACode_vc Varchar2(5) NULL ,
HUMMAName_vc Varchar2(50) NULL ,
MDMACode_vc Varchar2(5) NULL ,
MDMAName_vc Varchar2(50) NULL ,
DomesticMMACode_vc Varchar2(4) NULL ,
DomesticMMAName_vc Varchar2(25) NULL ,
ProvinceCode_vc Varchar2(2) NULL ,
ProvinceName_vc Varchar2(50) NULL ,
ReportTerritoryCode_vc Varchar2(2) NULL ,
ReportTerritoryName_vc Varchar2(50) NULL ,
ZoneCode_vc Varchar2(2) NULL ,
ZoneName_vc Varchar2(25) NULL ,
GMMACode_vc Varchar2(2) NULL ,
GMMAName_vc Varchar2(30) NULL ,
AREACode_vc Varchar2(5) NULL ,
AREAName_vc Varchar2(50) NULL ,
OntarioSpecialMarket_vc Varchar2(50) NULL ,
CONSTRAINT PK_GMSQLDBDomesticGeographyData PRIMARY KEY
(
PK_DA_vc
)
);
CREATE TABLE GM_TEMP_VehicleCountByDA (
PK_DA_vc Varchar2(10) NULL ,
VehicleCount_in Number(10) NULL ,
ReportYear_in Number(10) NULL ,
ReportMonth_in Number(10) NULL ,
gmcustomtypecode_vc Varchar2(100) NULL ,
GMCustomDetailedSegmentCode_vc Varchar2(100) NULL ,
gmcustomweightgroupname_vc Varchar2(100) NULL ,
gmcustomeManufacturername_vc Varchar2(50) NULL ,
genericmanufacturername_vc Varchar2(50) NULL ,
GMPrimaryCode_vc Varchar2(1) NULL ,
makedescription_vc Varchar2(50) NULL ,
GMCustomImportDomesticName_vc Varchar2(30) NULL
);
CREATE TABLE MAEDealerGuideFile (
PK_DealerCode_vc Varchar2(8) NOT NULL ,
StatusCode_vc Varchar2(1) NOT NULL ,
OPFlag_vc Varchar2(1) NULL ,
StatusName_vc Varchar2(15) NULL ,
DealerName_vc Varchar2(100) NULL ,
DealerNameShort_vc Varchar2(100) NULL ,
ZoneCode_vc Varchar2(2) NULL ,
ZoneName_vc Varchar2(25) NULL ,
SalesTerritoryCode_vc Varchar2(2) NULL ,
GMMACode_vc Varchar2(2) NULL ,
GMMAName_vc Varchar2(30) NULL ,
GMMALevelCode_vc Varchar2(1) NULL ,
GMMAGroupKey Number(10) DEFAULT (0) NULL ,
OutOfVolume Number(10) DEFAULT (0) NULL ,
Rank Number(10) DEFAULT (0) NULL ,
MMACode_vc Varchar2(4) NULL ,
MMAName_vc Varchar2(25) NULL ,
NationalCode_vc Varchar2(3) DEFAULT ('NAT') NULL ,
NationalName_vc Varchar2(8) DEFAULT ('NATIONAL') NULL ,
NationalAssignedCode_vc Varchar2(1) DEFAULT ('A') NULL ,
NationalAssignedName_vc Varchar2(10) DEFAULT ('ASSIGNED') NULL ,
ReplacedByDealerCode_vc Varchar2(20) NULL ,
ReplacingDealerCode_vc Varchar2(20) NULL ,
FranchiseCodes_vc Varchar2(15) NULL ,
FranchisesCarried Varchar2(30) DEFAULT (' ') NULL ,
FranchisesCarried4Print Varchar2(30) DEFAULT (' ') NULL ,
Chevrolet Varchar2(1) NOT NULL ,
PBG Varchar2(1) NOT NULL ,
Cadillac Varchar2(1) NOT NULL ,
Hummer Varchar2(1) NOT NULL ,
Saturn Varchar2(1) NOT NULL ,
Saab Varchar2(1) NOT NULL ,
Isuzu Varchar2(1) NOT NULL ,
AreaName_vc Varchar2(50) NULL ,
CityCode_vc Varchar2(2) NULL ,
MACode_vc Varchar2(5) NULL ,
MAName_vc Varchar2(20) NULL ,
DealerBACCode_vc Varchar2(11) NULL
);
CREATE TABLE GMSQLDBGMDealerFile (
PK_DealerCode_vc Varchar2(8) NOT NULL ,
StatusCode_vc Varchar2(1) NULL ,
StatusName_vc Varchar2(15) NULL ,
OPFlag_vc Varchar2(1) NULL ,
BusFranchiseFlag_vc Varchar2(1) NULL ,
Language_vc Varchar2(1) NULL ,
FrenchFlag_vc Varchar2(1) NULL ,
DealerName_vc Varchar2(100) NULL ,
DealerNameShort_vc Varchar2(100) NULL ,
Address_vc Varchar2(50) NULL ,
POBox_vc Varchar2(50) NULL ,
City_vc Varchar2(30) NULL ,
Province_vc Varchar2(2) NULL ,
FSALDU_vc Varchar2(7) NULL ,
ZoneCode_vc Varchar2(2) NULL ,
ZoneName_vc Varchar2(25) NULL ,
EWCode_vc Varchar2(1) NULL ,
EWName_vc Varchar2(4) NOT NULL ,
FranchiseCodes_vc Varchar2(15) NULL ,
ChevFranchise_bt Number(1) NULL ,
PBGFranchise_bt Number(1) NULL ,
CadFranchise_bt Number(1) NULL ,
HumFranchise_bt Number(1) NULL ,
SatFranchise_bt Number(1) NULL ,
SaabFranchise_bt Number(1) NULL ,
IsuzuFranchise_bt Number(1) NULL ,
SalesTerritoryCode_vc Varchar2(2) NULL ,
GMMACode_vc Varchar2(2) NULL ,
GMMALevelCode_vc Varchar2(1) NULL ,
MMACode_vc Varchar2(4) NULL ,
MMAName_vc Varchar2(25) NULL ,
TerminationDate_d Varchar2(11) NULL ,
ReplacingDealercode_vc Varchar2(20) NULL ,
ReplacedByDealerCode_vc Varchar2(20) NULL ,
ServiceTerritoryCode_vc Varchar2(2) NULL ,
AreaName_vc Varchar2(50) NULL ,
CityCode_vc Varchar2(2) NULL ,
MACode_vc Varchar2(5) NULL ,
MAName_vc Varchar2(20) NULL ,
DealerBACCode_vc Varchar2(11) NULL
);
Cardinality of columns:
TABLE_NAME COLUMN_NAME NUM_DISTINCT
GM_TEMP_VEHICLECOUNTBYDA PK_DA_VC 54936
GM_TEMP_VEHICLECOUNTBYDA VEHICLECOUNT_IN 660
GM_TEMP_VEHICLECOUNTBYDA REPORTYEAR_IN 6
GM_TEMP_VEHICLECOUNTBYDA REPORTMONTH_IN 2
GM_TEMP_VEHICLECOUNTBYDA GMCUSTOMTYPECODE_VC 2
GM_TEMP_VEHICLECOUNTBYDA GMCUSTOMDETAILEDSEGMENTCODE_VC 72
GM_TEMP_VEHICLECOUNTBYDA GMCUSTOMWEIGHTGROUPNAME_VC 4
GM_TEMP_VEHICLECOUNTBYDA GMCUSTOMEMANUFACTURERNAME_VC 47
GM_TEMP_VEHICLECOUNTBYDA GENERICMANUFACTURERNAME_VC 48
GM_TEMP_VEHICLECOUNTBYDA GMPRIMARYCODE_VC 3
GM_TEMP_VEHICLECOUNTBYDA MAKEDESCRIPTION_VC 71
GM_TEMP_VEHICLECOUNTBYDA GMCUSTOMIMPORTDOMESTICNAME_VC 2
MAEDEALERGUIDEFILE PK_DEALERCODE_VC 511
MAEDEALERGUIDEFILE STATUSCODE_VC 4
MAEDEALERGUIDEFILE OPFLAG_VC 2
MAEDEALERGUIDEFILE STATUSNAME_VC 2
MAEDEALERGUIDEFILE DEALERNAME_VC 502
MAEDEALERGUIDEFILE DEALERNAMESHORT_VC 509
MAEDEALERGUIDEFILE ZONECODE_VC 3
MAEDEALERGUIDEFILE ZONENAME_VC 3
MAEDEALERGUIDEFILE SALESTERRITORYCODE_VC 14
MAEDEALERGUIDEFILE GMMACODE_VC 7
MAEDEALERGUIDEFILE GMMANAME_VC 7
MAEDEALERGUIDEFILE GMMALEVELCODE_VC 9
MAEDEALERGUIDEFILE GMMAGROUPKEY 39
MAEDEALERGUIDEFILE OUTOFVOLUME 24
MAEDEALERGUIDEFILE RANK 1
MAEDEALERGUIDEFILE MMACODE_VC 20
MAEDEALERGUIDEFILE MMANAME_VC 20
MAEDEALERGUIDEFILE NATIONALCODE_VC 1
MAEDEALERGUIDEFILE NATIONALNAME_VC 1
MAEDEALERGUIDEFILE NATIONALASSIGNEDCODE_VC 1
MAEDEALERGUIDEFILE NATIONALASSIGNEDNAME_VC 1
MAEDEALERGUIDEFILE REPLACEDBYDEALERCODE_VC 0
MAEDEALERGUIDEFILE REPLACINGDEALERCODE_VC 279
MAEDEALERGUIDEFILE FRANCHISECODES_VC 88
MAEDEALERGUIDEFILE FRANCHISESCARRIED 7
MAEDEALERGUIDEFILE FRANCHISESCARRIED4PRINT 7
MAEDEALERGUIDEFILE CHEVROLET 2
MAEDEALERGUIDEFILE PBG 2
MAEDEALERGUIDEFILE CADILLAC 2
MAEDEALERGUIDEFILE HUMMER 1
MAEDEALERGUIDEFILE SATURN 1
MAEDEALERGUIDEFILE SAAB 1
MAEDEALERGUIDEFILE ISUZU 1
MAEDEALERGUIDEFILE AREANAME_VC 464
MAEDEALERGUIDEFILE CITYCODE_VC 3
MAEDEALERGUIDEFILE MACODE_VC 3
MAEDEALERGUIDEFILE MANAME_VC 2
MAEDEALERGUIDEFILE DEALERBACCODE_VC 448
GMSQLDBGMDEALERFILE PK_DEALERCODE_VC 556
GMSQLDBGMDEALERFILE STATUSCODE_VC 3
GMSQLDBGMDEALERFILE STATUSNAME_VC 3
GMSQLDBGMDEALERFILE OPFLAG_VC 2
GMSQLDBGMDEALERFILE BUSFRANCHISEFLAG_VC 0
GMSQLDBGMDEALERFILE LANGUAGE_VC 2
GMSQLDBGMDEALERFILE FRENCHFLAG_VC 0
GMSQLDBGMDEALERFILE DEALERNAME_VC 546
GMSQLDBGMDEALERFILE DEALERNAMESHORT_VC 550
GMSQLDBGMDEALERFILE ADDRESS_VC 469
GMSQLDBGMDEALERFILE POBOX_VC 130
GMSQLDBGMDEALERFILE CITY_VC 426
GMSQLDBGMDEALERFILE PROVINCE_VC 13
GMSQLDBGMDEALERFILE FSALDU_VC 468
GMSQLDBGMDEALERFILE ZONECODE_VC 3
GMSQLDBGMDEALERFILE ZONENAME_VC 3
GMSQLDBGMDEALERFILE EWCODE_VC 0
GMSQLDBGMDEALERFILE EWNAME_VC 0
GMSQLDBGMDEALERFILE FRANCHISECODES_VC 100
GMSQLDBGMDEALERFILE CHEVFRANCHISE_BT 2
GMSQLDBGMDEALERFILE PBGFRANCHISE_BT 2
GMSQLDBGMDEALERFILE CADFRANCHISE_BT 2
GMSQLDBGMDEALERFILE HUMFRANCHISE_BT 1
GMSQLDBGMDEALERFILE SATFRANCHISE_BT 1
GMSQLDBGMDEALERFILE SAABFRANCHISE_BT 1
GMSQLDBGMDEALERFILE ISUZUFRANCHISE_BT 1
GMSQLDBGMDEALERFILE SALESTERRITORYCODE_VC 14
GMSQLDBGMDEALERFILE GMMACODE_VC 7
GMSQLDBGMDEALERFILE GMMALEVELCODE_VC 9
GMSQLDBGMDEALERFILE MMACODE_VC 20
GMSQLDBGMDEALERFILE MMANAME_VC 20
GMSQLDBGMDEALERFILE TERMINATIONDATE_D 1
GMSQLDBGMDEALERFILE REPLACINGDEALERCODE_VC 304
GMSQLDBGMDEALERFILE REPLACEDBYDEALERCODE_VC 23
GMSQLDBGMDEALERFILE SERVICETERRITORYCODE_VC 13
GMSQLDBGMDEALERFILE AREANAME_VC 468
GMSQLDBGMDEALERFILE CITYCODE_VC 3
GMSQLDBGMDEALERFILE MACODE_VC 3
GMSQLDBGMDEALERFILE MANAME_VC 2
GMSQLDBGMDEALERFILE DEALERBACCODE_VC 486
GMSQLDBDOMESTICGEOGRAPHYDATA PK_DA_VC 53941
GMSQLDBDOMESTICGEOGRAPHYDATA FSA_VC 1576
GMSQLDBDOMESTICGEOGRAPHYDATA FSANAME_VC 588
GMSQLDBDOMESTICGEOGRAPHYDATA COCMACODE_VC 438
GMSQLDBDOMESTICGEOGRAPHYDATA COCMANAME_VC 436
GMSQLDBDOMESTICGEOGRAPHYDATA PBGMACODE_VC 426
GMSQLDBDOMESTICGEOGRAPHYDATA PBGMANAME_VC 425
GMSQLDBDOMESTICGEOGRAPHYDATA CADMACODE_VC 126
GMSQLDBDOMESTICGEOGRAPHYDATA CADMANAME_VC 125
GMSQLDBDOMESTICGEOGRAPHYDATA HUMMACODE_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA HUMMANAME_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA MDMACODE_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA MDMANAME_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA DOMESTICMMACODE_VC 20
GMSQLDBDOMESTICGEOGRAPHYDATA DOMESTICMMANAME_VC 20
GMSQLDBDOMESTICGEOGRAPHYDATA PROVINCECODE_VC 12
GMSQLDBDOMESTICGEOGRAPHYDATA PROVINCENAME_VC 12
GMSQLDBDOMESTICGEOGRAPHYDATA REPORTTERRITORYCODE_VC 14
GMSQLDBDOMESTICGEOGRAPHYDATA REPORTTERRITORYNAME_VC 14
GMSQLDBDOMESTICGEOGRAPHYDATA ZONECODE_VC 3
GMSQLDBDOMESTICGEOGRAPHYDATA ZONENAME_VC 3
GMSQLDBDOMESTICGEOGRAPHYDATA GMMACODE_VC 7
GMSQLDBDOMESTICGEOGRAPHYDATA GMMANAME_VC 7
GMSQLDBDOMESTICGEOGRAPHYDATA AREACODE_VC 438
GMSQLDBDOMESTICGEOGRAPHYDATA AREANAME_VC 437
GMSQLDBDOMESTICGEOGRAPHYDATA ONTARIOSPECIALMARKET_VC 3
Solution
Few quick suggestions -
I see that you are using trim on every join. Would recommend not to do that wherever possible, at least for the joins based on primary keys for e.g - TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc) if not absolutely necessary. Since, Primary keys have by default index built on them in oracle. If you wrap them with function while joining, the query optimizer won’t be able to use index based on pk while joining which will negatively impact your query performance.
On the query to get “carIndy -4 & ..-3 etc.. you don’t need to repeat the query every time. In select, Just use Sum(case when (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3 then VehicleCount_in else 0 end) as "CarIndY - 3", .... "CarIndY - 4" In the same query and use this query in join rather than select. Since these are essentially counts, this subset will only have one row with all the colums you need so you should be able to cross join it and get all the carIndY - n colums in select of the main query
Also , put the main join conditions first before the filter conditions such as in your query you have -
WHERE ((nvl(dlr.ChevFranchise_bt, 0) = 1 AND nvl(dlr.PBGFranchise_bt, 0) = 0)) AND StatusName_vc = 'Active' AND TRIM(dlr.PK_DealerCode_vc) = TRIM(gmma.PK_DealerCode_vc)
Here, the 4th and condition is the main join criteria and it should be mentioned first before other criteria in where
- Query to find position in a string before keyword and extract data before that position
- SQL: get next relative day of week. (Next Monday, Tuesday, Wed.....)
- #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
- java.sql.SQLException: Column Index out of range, 2 > 1
- Should LIKE 'searchstr%' use an index?
- Multiple STRING_AGG in one query
- Postgres: Add constraint if it doesn't already exist
- UPDATE Statement ignores INNER JOIN
- Execute Stored Procedure Minimal API .Net
- Change default schema for user
- Oracle SQL - Select oldest of 3 dates, but ignore NULLs
- Housing Society management system database structure
- Keep getting error: PLS-00103: Encountered the symbol "CREATE"
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Extract 4 million rows from database in chunks of 1 million rows each
- Aggregate into custom groups
- SQL query for chat application
- SQL basic problem when trying to SELECT 4 atributes and use GROUP BY with only 2
- How to modify query converting DATEDIFF to Oracle accepted syntax?
- Sales data analysis: calculate percentage of each product as per total sales
- Calculate the sum of values for the columns that meet the criteria
- Insert missing rows in time series consumption history
- Oracle database dependencies in PL/SQL
- How to autofill a field with data from another field in the same table?
- In Oracle: How to sort by group/cluster by a column value
- oracle SQL query - add date rows using different date
- How to fix products without cover image in PrestaShop
- IF ROW_COUNT() return error
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?