sqloracleoptimizationquery-optimizationsql-tuning
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
- PL/SQL arabic displayed as?
- Tuple matching using SQLAlchemy
- How do I copy data from one column to another in the same table?
- Insert leading spaces, then the value to a varchar(10) column
- BigQuery groupby, add row with count of 0 for groupby variable if no rows found
- Calculating difference of dates In Postgresql
- Count / Sum JSON variant attribute Flatten
- MySQL: How to convert seconds to mm:ss format?
- Get mySQL MONTH() to use leading zeros?
- How to use order by with union all in sql?
- Create date periods based on two tables
- Unable to install SQL Server(setup.exe) Exit code decimal: -2061893606
- SQL Server TRIM character
- No PK or FK when exporting SQL Server database
- SQL Server SELECT LAST N Rows
- Oracle SQL query using regexp_substr to get string between 2 strings
- How to select Users who have not received notifications using SQL?
- Fuzzy phrase similarity in SQL Server
- Union two selects removing duplicates base on some columns (not the full row)
- SQL fill gaps with hold
- BigQuery - Transaction is aborted due to concurrent update against table
- SQL one to one relationship vs. single table
- SQL query to fetch persons with more than one postcode address via join table
- php scrape to write to mysql database error
- php mysql connect and create table with no error but no data
- Create login and logout rows from single row
- Alternatives to sliding window frames with RANGE on snowflake
- Snowflake SQL Range between interval
- Cascading Soft Delete
- ERROR: extra data after last expected column on postgresql