Oracle Live SQL - Script: Session 06

Statement 1

DROP TABLE AM_TEMP

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE AM_TEMP(				  
DEALER_MAP_CD	NUMBER(5)			,  
LOC_CD	VARCHAR2(3)			,  
COMP_FA	VARCHAR2(8)			,  
PARENT_GROUP	VARCHAR2(5)			,  
INV_NUM	VARCHAR2(12)			,  
INV_DATE	DATE			,  
ORDER_NUM	VARCHAR2(12)			,  
ORDER_DATE	DATE			,  
SALES_TYPE	VARCHAR2(5)			,  
CUST_NAME_OLD	VARCHAR2(100)			,  
MODEL_CD	VARCHAR2(10)			,  
VARIANT_CD	VARCHAR2(8)			,  
ECOLOR_CD	VARCHAR2(4)			,  
BUY_ID	VARCHAR2(15)			,  
REG_NUM	VARCHAR2(20)			,  
INV_STATUS	VARCHAR2(1)			,  
CANCEL_DATE	DATE			,  
CREATED_BY	VARCHAR2(8)			,  
CREATED_DATE	DATE			,  
MODIFIED_BY	VARCHAR2(10)			,  
MODIFIED_DATE	DATE			,  
REGION_CD	VARCHAR2(5)			,  
STATE_CD	VARCHAR2(5)			,  
CITY_CD	VARCHAR2(5)			,  
PIN_CODE_OLD	VARCHAR2(8)			,  
MOBILE_OLD	VARCHAR2(10)			,  
PAN_OLD	VARCHAR2(15)			,  
FI_CD	VARCHAR2(8)			,  
FIN_CASH	VARCHAR2(1)			,  
FIN_AMT	NUMBER(10,2)			,  
EMI_AMT	NUMBER(10,2)			,  
TENURE	NUMBER(3)			,  
DISCOUNT	NUMBER(21,4)			,  
POC_DSE_CD	VARCHAR2(8)			,  
RECD_AMT	NUMBER(21,4)			,  
BRANCH	VARCHAR2(100)			,  
EXCHANGE_FLAG	VARCHAR2(1)			,  
EXCH_CANC_DATE	DATE			,  
EXCH_CANC_TYPE	VARCHAR2(100)			,  
EXCH_CANC_BY	VARCHAR2(8)			,  
EXCH_BUY_ID	VARCHAR2(15)			,  
EXCH_REG_NUM	VARCHAR2(20)			,  
DOR	DATE			,  
RC_STATUS	VARCHAR2(10)			,  
UNIT_SELL_PRICE	NUMBER(10,2)			,  
SELLING_MILEAGE	NUMBER			,  
INVOICE_AMT	NUMBER(10,2)			,  
VIN	VARCHAR2(20)			,  
RC_COMITTED_DATE	DATE			,  
CURR_RC_COM_DATE	DATE			,  
CURR_RC_STATUS	VARCHAR2(10)			,  
REG_DATE	DATE			,  
CHASSIS_NUM	VARCHAR2(20)			,  
ENGINE_NUM	VARCHAR2(20)			,  
BROKER_CD	VARCHAR2(14)			,  
EXCH_RELATION	VARCHAR2(5)			,  
TESM_REMARKS	VARCHAR2(300)			,  
MAKE_CD	VARCHAR2(5)			,  
YOM	NUMBER(5)			,  
CATEGORY	VARCHAR2(5)			,  
VEH_SELL_PRICE	NUMBER(10,2)			,  
CAN_NUM	VARCHAR2(12)			,  
PRICE_FLAG	VARCHAR2(2)	DEFAULT	'B'	,  
GSTN_DEALER	VARCHAR2(15)	DEFAULT	'GSTUNREGISTERED'	,  
HSN	VARCHAR2(11)	DEFAULT	'NOHSN'	,  
GSTN_CUST	VARCHAR2(15)	DEFAULT	'GSTUNREGISTERED'	,  
CUST_NAME	VARCHAR2(100)			,  
MOBILE	VARCHAR2(10)			,  
PIN_CODE	VARCHAR2(8)			,  
PAN	VARCHAR2(15)			,  
SVOC_ID	NUMBER			)

Table created.

Statement 3

ALTER TABLE AM_TEMP ADD CONSTRAINT PK_SH_POC_INV PRIMARY KEY (PARENT_GROUP, DEALER_MAP_CD, LOC_CD, COMP_FA, INV_NUM)

Table altered.

Statement 4

CREATE INDEX 	IDX_POC_BUYID	ON AM_TEMP	(	BUY_ID	)

Index created.

Statement 5

CREATE INDEX 	IDX_SH_POC_INVOICE_ORDNUM	ON AM_TEMP	(	DEALER_MAP_CD, LOC_CD, ORDER_NUM	)

Index created.

Statement 6

CREATE INDEX 	IDX_SH_POC_INV_CRT_DT	ON AM_TEMP	(	CREATED_DATE	)

Index created.

Statement 7

CREATE INDEX 	IDX_SH_POC_INV_MOD_DT	ON AM_TEMP	(	MODIFIED_DATE	)

Index created.

Statement 8

CREATE INDEX 	IDX_VIN_POCINV	ON AM_TEMP	(	VIN	)

Index created.

Statement 9

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50299, 'DMR', 'PLA', 'PLA', 'INVP16000154', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000154', to_date('06-06-2016', 'dd-mm-yyyy'), 'RTC', 'ROHIRA KL', 'V', 'VRFXSC', '26U', 'B15000538654', 'TN29L1091', 'I', null, 'POC02705', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02705', null, null, null, null, null, null, null, null, null, 'TGC', 175000.00, 76850, 175000.00, 'ST91-IN518777', to_date('23-06-2016', 'dd-mm-yyyy'), null, null, to_date('25-07-2002', 'dd-mm-yyyy'), '518777', '2259419', null, null, null, null, 2002, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'ROHIRA KL', '9443279510', null, null, 107467023)

1 row(s) inserted.

Statement 10

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50425, 'SHN', 'SUMI', 'SUMIT', 'INVP16000069', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000069', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'MOHD AYUB', 'H', 'H00045', 'M', 'B16000850451', 'UP31X3136', 'I', null, 'POC02095', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02095', null, null, null, null, null, null, null, null, null, 'TGC', 170000.00, 110215, 170000.00, null, to_date('06-06-2016', 'dd-mm-yyyy'), null, null, to_date('22-02-2012', 'dd-mm-yyyy'), '180559', '284022', null, null, null, null, 2011, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'MOHD AYUB', '8896774930', null, null, 147158879)

1 row(s) inserted.

Statement 11

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50324, 'CTK', 'RAJL', 'RAJLK', 'INVP16000093', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000093', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'SRIDHAR', 'H', 'H00045', 'M', 'B16000830619', 'TN20AC9001', 'I', null, 'POC02524', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02524', null, null, null, null, null, null, null, null, null, 'STR', 40000.00, 45821, 40000.00, null, to_date('12-07-2016', 'dd-mm-yyyy'), null, null, to_date('02-06-2006', 'dd-mm-yyyy'), '872105', '842650', null, null, null, null, 2006, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'SRIDHAR', '9840022425', null, null, 147158880)

1 row(s) inserted.

Statement 12

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50324, 'CTK', 'RAJL', 'RAJLK', 'INVP16000094', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000094', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'RAMA LINGHAM', 'N', 'N00106', 'M', 'B16000840747', 'TN10T4201', 'I', null, 'POC02524', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02524', null, null, null, null, null, null, null, null, null, 'STR', 50000.00, 41515, 50000.00, null, to_date('12-07-2016', 'dd-mm-yyyy'), null, null, to_date('14-11-2007', 'dd-mm-yyyy'), '101541', '2965529', null, null, null, null, 2007, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'RAMA LINGHAM', '9884560055', null, null, 135074875)

1 row(s) inserted.

Statement 13

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50321, 'DWT', 'PRIM', 'PRIMC', 'INVP16000081', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000081', to_date('06-06-2016', 'dd-mm-yyyy'), 'RTC', 'MANOJ DUBEY', 'A', 'AR4XSF', '26U', 'B16000600422', 'MP04CB8462', 'I', null, 'POC02520', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02520', null, null, null, null, null, null, null, null, null, 'TGC', 55000.00, 92000, 55000.00, 'MA3EYD81S01041149', to_date('21-07-2016', 'dd-mm-yyyy'), null, null, to_date('18-01-2008', 'dd-mm-yyyy'), '1041149', '4209028', null, null, null, null, 2007, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'MANOJ DUBEY', '9425400231', null, null, 125974256)

1 row(s) inserted.

Statement 14

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50002, 'CSR', 'ACPL', 'AADHI', 'INVP16000241', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000241', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'SENTHILKUMAR', 'J', 'J00007', 'M', 'B16000690175', 'TN37CA7692', 'I', null, 'POC02046', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), 'TV001', to_date('12-07-2016 13:25:12', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC02046', null, null, null, null, null, null, null, null, null, 'STR', 327000.00, 82000, 327000.00, null, to_date('11-06-2016', 'dd-mm-yyyy'), to_date('12-07-2016', 'dd-mm-yyyy'), 'TGC', to_date('19-12-2012', 'dd-mm-yyyy'), '47347', '47347', null, null, null, null, 2012, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'SENTHILKUMAR', '9865840521', null, null, 145628174)

1 row(s) inserted.

Statement 15

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50320, 'KNR', 'PMPL', 'PREMS', 'INVP16000113', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000113', to_date('06-06-2016', 'dd-mm-yyyy'), 'RTC', 'MANOJ  JAISWAL', 'C', 'CR4XSP', '67M', 'B16000860786', 'BIN8321', 'I', null, 'POC01647', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC01647', null, null, null, null, null, null, null, null, null, 'STR', 10728.00, 102346, 10728.00, null, to_date('06-06-2016', 'dd-mm-yyyy'), null, null, to_date('02-02-1989', 'dd-mm-yyyy'), '272911', '375518', null, null, null, null, 1989, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'MANOJ JAISWAL', '9470564896', null, null, 108461159)

1 row(s) inserted.

Statement 16

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50425, 'SHN', 'SUMI', 'SUMIT', 'INVP16000070', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000070', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'RAJEEV GUPTA', 'H', 'H00045', 'NM', 'B16000836785', 'UP27P1005', 'I', null, 'POC01130', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC01130', null, null, null, null, null, null, null, null, null, 'TGC', 140000.00, 78242, 140000.00, null, to_date('06-06-2016', 'dd-mm-yyyy'), null, null, to_date('26-10-2010', 'dd-mm-yyyy'), '299623', '327825', null, null, null, null, 2010, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'RAJEEV GUPTA', '9044950270', null, null, 136622403)

1 row(s) inserted.

Statement 17

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50281, 'PGR', 'PASC', 'PASCO', 'INVP16000825', to_date('06-06-2016', 'dd-mm-yyyy'), 'ORDP16000825', to_date('06-06-2016', 'dd-mm-yyyy'), 'WTB', 'SURESH KASHYAP', 'G', 'G00005', 'M', 'B16000856583', 'DL3CAE0416', 'I', null, 'POC03476', to_date('02-07-2016 12:01:40', 'dd-mm-yyyy hh24:mi:ss'), 'TV001', to_date('17-07-2018 17:53:54', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, null, null, null, null, 'C', null, null, null, null, 'POC03476', null, null, null, null, null, null, null, null, null, 'STR', 42000.00, 98586, 42000.00, null, to_date('31-08-2016', 'dd-mm-yyyy'), to_date('17-07-2018', 'dd-mm-yyyy'), 'TGC', to_date('18-06-2004', 'dd-mm-yyyy'), '228452', '3229', null, null, null, null, 2004, 'N', null, null, 'B', 'GSTUNREGISTERED', 'NOHSN', 'GSTUNREGISTERED', 'SURESH KASHYAP', '9650698744', null, null, 143568683)

1 row(s) inserted.

Statement 18

commit

Statement processed.

Statement 19

insert into am_temp (DEALER_MAP_CD, LOC_CD, COMP_FA, PARENT_GROUP, INV_NUM, INV_DATE, ORDER_NUM, ORDER_DATE, SALES_TYPE, CUST_NAME_OLD, MODEL_CD, VARIANT_CD, ECOLOR_CD, BUY_ID, REG_NUM, INV_STATUS, CANCEL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, REGION_CD, STATE_CD, CITY_CD, PIN_CODE_OLD, MOBILE_OLD, PAN_OLD, FI_CD, FIN_CASH, FIN_AMT, EMI_AMT, TENURE, DISCOUNT, POC_DSE_CD, RECD_AMT, BRANCH, EXCHANGE_FLAG, EXCH_CANC_DATE, EXCH_CANC_TYPE, EXCH_CANC_BY, EXCH_BUY_ID, EXCH_REG_NUM, DOR, RC_STATUS, UNIT_SELL_PRICE, SELLING_MILEAGE, INVOICE_AMT, VIN, RC_COMITTED_DATE, CURR_RC_COM_DATE, CURR_RC_STATUS, REG_DATE, CHASSIS_NUM, ENGINE_NUM, BROKER_CD, EXCH_RELATION, TESM_REMARKS, MAKE_CD, YOM, CATEGORY, VEH_SELL_PRICE, CAN_NUM, PRICE_FLAG, GSTN_DEALER, HSN, GSTN_CUST, CUST_NAME, MOBILE, PIN_CODE, PAN, SVOC_ID) 
values (50039, 'MLM', 'AMM', 'AMMOT', 'INV/17002548', to_date('31-03-2018 15:09:26', 'dd-mm-yyyy hh24:mi:ss'), 'ORDP17003642', to_date('31-03-2018 15:08:27', 'dd-mm-yyyy hh24:mi:ss'), 'RTC', 'VISHNU C', 'DM', 'DMRDCD3', 'ZHJ', 'B17003904591', 'KL52K4856', 'I', null, 'TV001', to_date('31-03-2018 15:09:26', 'dd-mm-yyyy hh24:mi:ss'), null, null, null, 'KL', 'A44', null, '9746894243', 'F60', null, 'C', null, null, null, null, 'POC03486', 5000.0000, null, null, null, null, null, null, null, null, 'TGC', 463000.00, 78818, 464560.00, 'MA3FJEB1S00986676', to_date('31-03-2018', 'dd-mm-yyyy'), null, null, to_date('29-09-2016', 'dd-mm-yyyy'), '986676', '2887232', null, null, null, 'M', 2016, 'T', 463000.00, null, 'B', '32AAHFA8699Q1ZK', '87033191', 'GSTUNREGISTERED', 'VISHNU C', '9746894243', null, null, 138509903)

1 row(s) inserted.

Statement 20

commit

Statement processed.

Statement 21

select * from am_temp WHERE vin ='MA3EYD81S01041149'


DEALER_MAP_CD LOC_CD COMP_FA PARENT_GROUP INV_NUM INV_DATE ORDER_NUM ORDER_DATE SALES_TYPE CUST_NAME_OLD MODEL_CD VARIANT_CD ECOLOR_CD BUY_ID REG_NUM INV_STATUS CANCEL_DATE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE REGION_CD STATE_CD CITY_CD PIN_CODE_OLD MOBILE_OLD PAN_OLD FI_CD FIN_CASH FIN_AMT EMI_AMT TENURE DISCOUNT POC_DSE_CD RECD_AMT BRANCH EXCHANGE_FLAG EXCH_CANC_DATE EXCH_CANC_TYPE EXCH_CANC_BY EXCH_BUY_ID EXCH_REG_NUM DOR RC_STATUS UNIT_SELL_PRICE SELLING_MILEAGE INVOICE_AMT VIN RC_COMITTED_DATE CURR_RC_COM_DATE CURR_RC_STATUS REG_DATE CHASSIS_NUM ENGINE_NUM BROKER_CD EXCH_RELATION TESM_REMARKS MAKE_CD YOM CATEGORY VEH_SELL_PRICE CAN_NUM PRICE_FLAG GSTN_DEALER HSN GSTN_CUST CUST_NAME MOBILE PIN_CODE PAN SVOC_ID
50321 DWT PRIM PRIMC INVP16000081 06-JUN-16 ORDP16000081 06-JUN-16 RTC MANOJ DUBEY A AR4XSF 26U B16000600422 MP04CB8462 I  - POC02520 02-JUL-16  -  -  -  -  -  -  -  -  - C  -  -  -  - POC02520  -  -  -  -  -  -  -  -  - TGC 55000 92000 55000 MA3EYD81S01041149 21-JUL-16  -  - 18-JAN-08 1041149 4209028  -  -  -  - 2007 N  -  - B GSTUNREGISTERED NOHSN GSTUNREGISTERED MANOJ DUBEY 9425400231  -  - 125974256

Statement 22

Create table PLAN_TABLE (  
        statement_id       varchar2(30),  
        plan_id            number,  
        timestamp          date,  
        remarks            varchar2(4000),  
        operation          varchar2(30),  
        options            varchar2(255),  
        object_node        varchar2(128),  
        object_owner       varchar2(30),  
        object_name        varchar2(30),  
        object_alias       varchar2(65),  
        object_instance    numeric,  
        object_type        varchar2(30),  
        optimizer          varchar2(255),  
        search_columns     number,  
        id                 numeric,  
        parent_id          numeric,  
        depth              numeric,  
        position           numeric,  
        cost               numeric,  
        cardinality        numeric,  
        bytes              numeric,  
        other_tag          varchar2(255),  
        partition_start    varchar2(255),  
        partition_stop     varchar2(255),  
        partition_id       numeric,  
        other              long,  
        distribution       varchar2(30),  
        cpu_cost           numeric,  
        io_cost            numeric,  
        temp_space         numeric,  
        access_predicates  varchar2(4000),  
        filter_predicates  varchar2(4000),  
        projection         varchar2(4000),  
        time               numeric,  
        qblock_name        varchar2(30),  
        other_xml          clob  
)

Table created.

Statement 23

delete from plan_table

0 row(s) deleted.

Statement 24

commit

Statement processed.

Statement 25

EXPLAIN PLAN    
    SET STATEMENT_ID = 'WITHOUT_OR'   
    FOR   
    SELECT sh.inv_date,  
                    sh.sales_type,    
                    sh.invoice_amt,  
                    'TV' channel, 
                    sh.fin_amt, 
                    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
                    sh.exch_reg_num, 
                    sh.tenure, 
                    sh.fi_cd, 
                    sh.parent_group, 
                    sh.dealer_map_cd, loc_CD 
               FROM am_temp sh 
              WHERE sh.vin = 'MA3FJEB1S00986676' 
              /*((sh.vin = 'MA3FJEB1S00986676') OR 
                     (sh.model_cd = 'DM' AND 
                       substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.engine_num = '2887232') OR 
                     (substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.reg_num = 'KL52K4856') 
                     )*/ 
                AND sh.inv_status = 'I'   
                AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019'

Statement processed.

Statement 26

SELECT * 
    FROM plan_table    
    START WITH id = 0 AND statement_id = 'Tokyo'   
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Tokyo'

no data found

Statement 27

EXPLAIN PLAN    
    SET STATEMENT_ID = 'WITH_OR'   
    FOR   
    SELECT sh.inv_date,  
                    sh.sales_type,    
                    sh.invoice_amt,  
                    'TV' channel, 
                    sh.fin_amt, 
                    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
                    sh.exch_reg_num, 
                    sh.tenure, 
                    sh.fi_cd, 
                    sh.parent_group, 
                    sh.dealer_map_cd, loc_CD 
               FROM am_temp sh 
              WHERE  
              ((sh.vin = 'MA3FJEB1S00986676') OR 
                     (sh.model_cd = 'DM' AND 
                       substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.engine_num = '2887232') OR 
                     (substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.reg_num = 'KL52K4856') 
                     ) 
                AND sh.inv_status = 'I'   
                AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019'

Statement processed.

Statement 28

EXPLAIN PLAN    
    SET STATEMENT_ID = 'Force_Index'   
    FOR   
    SELECT /* +index (sh IDX_VIN_POCINV) */ sh.inv_date,  
                    sh.sales_type,    
                    sh.invoice_amt,  
                    'TV' channel, 
                    sh.fin_amt, 
                    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
                    sh.exch_reg_num, 
                    sh.tenure, 
                    sh.fi_cd, 
                    sh.parent_group, 
                    sh.dealer_map_cd, loc_CD 
               FROM am_temp sh 
              WHERE  
              ((sh.vin = 'MA3FJEB1S00986676') OR 
                     (sh.model_cd = 'DM' AND 
                       substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.engine_num = '2887232') OR 
                     (substr(sh.chassis_num,-6) = substr('986676',-6) AND 
                       sh.reg_num = 'KL52K4856') 
                     ) 
                AND sh.inv_status = 'I'   
                AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019'

Statement processed.

Statement 29

SELECT * 
    FROM plan_table    
    START WITH id = 0 AND statement_id ='WITH_OR'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'WITH_OR'


STATEMENT_ID PLAN_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_ALIAS OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID DEPTH POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME OTHER_XML
WITH_OR 44040 12-AUG-19  - SELECT STATEMENT  -  -  -  -  -  -  - ALL_ROWS  - 0  - 0 3 3 1 149  -  -  -  -  -  - 48212 3  -  -  -  - 1  -  - 
WITH_OR 44040 12-AUG-19  - TABLE ACCESS FULL  - SQL_QOPCWOMHGHBNAFLPBCVSFTNCD AM_TEMP "SH"@"SEL$1" 1 TABLE  -  - 1 0 1 1 3 1 149  -  -  -  -  -  - 48212 3  -  - "SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("SH"."VIN"='MA3FJEB1S00986676' OR "SH"."REG_NUM"='KL52K4856' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676' OR "SH"."MODEL_CD"='DM' AND "SH"."ENGINE_NUM"='2887232' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676') "SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22] 1 SEL$1 <other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2251623430</info><info type="plan_hash">445253272</info><info type="plan_hash_2">2251623430</info><stats type="compilation"><stat name="bg">23</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>

2 rows selected.

Statement 30

SELECT * 
    FROM plan_table    
    START WITH id = 0 AND statement_id ='WITHOUT_OR'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'WITHOUT_OR'


STATEMENT_ID PLAN_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_ALIAS OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID DEPTH POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME OTHER_XML
WITHOUT_OR 44039 12-AUG-19  - SELECT STATEMENT  -  -  -  -  -  -  - ALL_ROWS  - 0  - 0 1 1 1 106  -  -  -  -  -  - 7321 1  -  -  -  - 1  -  - 
WITHOUT_OR 44039 12-AUG-19  - TABLE ACCESS BY INDEX ROWID BATCHED  - SQL_QOPCWOMHGHBNAFLPBCVSFTNCD AM_TEMP "SH"@"SEL$1" 1 TABLE  -  - 1 0 1 1 1 1 106  -  -  -  -  -  - 7321 1  -  - "SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') "SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22] 1 SEL$1 <other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2000447992</info><info type="plan_hash">965741078</info><info type="plan_hash_2">2000447992</info><stats type="compilation"><stat name="bg">24</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "SH"@"SEL$1" ("AM_TEMP"."VIN"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>
WITHOUT_OR 44039 12-AUG-19  - INDEX RANGE SCAN  - SQL_QOPCWOMHGHBNAFLPBCVSFTNCD IDX_VIN_POCINV "SH"@"SEL$1"  - INDEX ANALYZED 1 2 1 2 1 1 1  -  -  -  -  -  -  - 7321 1  - "SH"."VIN"='MA3FJEB1S00986676'  - "SH".ROWID[ROWID,10] 1 SEL$1  - 

3 rows selected.

Statement 31

SELECT * 
    FROM plan_table    
    START WITH id = 0 AND statement_id ='Force_Index'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Force_Index'


STATEMENT_ID PLAN_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_ALIAS OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID DEPTH POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME OTHER_XML
Force_Index 44041 12-AUG-19  - SELECT STATEMENT  -  -  -  -  -  -  - ALL_ROWS  - 0  - 0 3 3 1 149  -  -  -  -  -  - 48212 3  -  -  -  - 1  -  - 
Force_Index 44041 12-AUG-19  - TABLE ACCESS FULL  - SQL_QOPCWOMHGHBNAFLPBCVSFTNCD AM_TEMP "SH"@"SEL$1" 1 TABLE  -  - 1 0 1 1 3 1 149  -  -  -  -  -  - 48212 3  -  - "SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("SH"."VIN"='MA3FJEB1S00986676' OR "SH"."REG_NUM"='KL52K4856' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676' OR "SH"."MODEL_CD"='DM' AND "SH"."ENGINE_NUM"='2887232' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676') "SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22] 1 SEL$1 <other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2251623430</info><info type="plan_hash">445253272</info><info type="plan_hash_2">2251623430</info><stats type="compilation"><stat name="bg">10</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>

2 rows selected.

STATEMENT_ID	PLAN_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_ALIAS	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	DEPTH	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER	DISTRIBUTION	CPU_COST	IO_COST	TEMP_SPACE	ACCESS_PREDICATES	FILTER_PREDICATES	PROJECTION	TIME	QBLOCK_NAME	OTHER_XML
WITH_OR	44040	12-AUG-19	-	SELECT STATEMENT	-	-	-	-	-	-	-	ALL_ROWS	-	0	-	0	3	3	1	149	-	-	-	-	-	-	48212	3	-	-	-	-	1	-	-
WITH_OR	44040	12-AUG-19	-	TABLE ACCESS	FULL	-	SQL_QOPCWOMHGHBNAFLPBCVSFTNCD	AM_TEMP	"SH"@"SEL$1"	1	TABLE	-	-	1	0	1	1	3	1	149	-	-	-	-	-	-	48212	3	-	-	"SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("SH"."VIN"='MA3FJEB1S00986676' OR "SH"."REG_NUM"='KL52K4856' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676' OR "SH"."MODEL_CD"='DM' AND "SH"."ENGINE_NUM"='2887232' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676')	"SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22]	1	SEL$1	<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2251623430</info><info type="plan_hash">445253272</info><info type="plan_hash_2">2251623430</info><stats type="compilation"><stat name="bg">23</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>

STATEMENT_ID	PLAN_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_ALIAS	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	DEPTH	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER	DISTRIBUTION	CPU_COST	IO_COST	TEMP_SPACE	ACCESS_PREDICATES	FILTER_PREDICATES	PROJECTION	TIME	QBLOCK_NAME	OTHER_XML
WITHOUT_OR	44039	12-AUG-19	-	SELECT STATEMENT	-	-	-	-	-	-	-	ALL_ROWS	-	0	-	0	1	1	1	106	-	-	-	-	-	-	7321	1	-	-	-	-	1	-	-
WITHOUT_OR	44039	12-AUG-19	-	TABLE ACCESS	BY INDEX ROWID BATCHED	-	SQL_QOPCWOMHGHBNAFLPBCVSFTNCD	AM_TEMP	"SH"@"SEL$1"	1	TABLE	-	-	1	0	1	1	1	1	106	-	-	-	-	-	-	7321	1	-	-	"SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')	"SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22]	1	SEL$1	<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2000447992</info><info type="plan_hash">965741078</info><info type="plan_hash_2">2000447992</info><stats type="compilation"><stat name="bg">24</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "SH"@"SEL$1" ("AM_TEMP"."VIN"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>
WITHOUT_OR	44039	12-AUG-19	-	INDEX	RANGE SCAN	-	SQL_QOPCWOMHGHBNAFLPBCVSFTNCD	IDX_VIN_POCINV	"SH"@"SEL$1"	-	INDEX	ANALYZED	1	2	1	2	1	1	1	-	-	-	-	-	-	-	7321	1	-	"SH"."VIN"='MA3FJEB1S00986676'	-	"SH".ROWID[ROWID,10]	1	SEL$1	-

STATEMENT_ID	PLAN_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_ALIAS	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	DEPTH	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER	DISTRIBUTION	CPU_COST	IO_COST	TEMP_SPACE	ACCESS_PREDICATES	FILTER_PREDICATES	PROJECTION	TIME	QBLOCK_NAME	OTHER_XML
Force_Index	44041	12-AUG-19	-	SELECT STATEMENT	-	-	-	-	-	-	-	ALL_ROWS	-	0	-	0	3	3	1	149	-	-	-	-	-	-	48212	3	-	-	-	-	1	-	-
Force_Index	44041	12-AUG-19	-	TABLE ACCESS	FULL	-	SQL_QOPCWOMHGHBNAFLPBCVSFTNCD	AM_TEMP	"SH"@"SEL$1"	1	TABLE	-	-	1	0	1	1	3	1	149	-	-	-	-	-	-	48212	3	-	-	"SH"."INV_STATUS"='I' AND "SH"."INV_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SH"."INV_DATE"<=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("SH"."VIN"='MA3FJEB1S00986676' OR "SH"."REG_NUM"='KL52K4856' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676' OR "SH"."MODEL_CD"='DM' AND "SH"."ENGINE_NUM"='2887232' AND SUBSTR("SH"."CHASSIS_NUM",(-6))='986676')	"SH"."DEALER_MAP_CD"[NUMBER,22], "LOC_CD"[VARCHAR2,3], "SH"."PARENT_GROUP"[VARCHAR2,5], "SH"."INV_DATE"[DATE,7], "SH"."SALES_TYPE"[VARCHAR2,5], "SH"."FI_CD"[VARCHAR2,8], "SH"."FIN_AMT"[NUMBER,22], "SH"."TENURE"[NUMBER,22], "SH"."EXCHANGE_FLAG"[VARCHAR2,1], "SH"."EXCH_REG_NUM"[VARCHAR2,20], "SH"."INVOICE_AMT"[NUMBER,22]	1	SEL$1	<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0</info><info type="parse_schema"><![CDATA["SQL_QOPCWOMHGHBNAFLPBCVSFTNCD"]]></info><info type="dynamic_sampling" note="y">2</info><info type="plan_hash_full">2251623430</info><info type="plan_hash">445253272</info><info type="plan_hash_2">2251623430</info><stats type="compilation"><stat name="bg">10</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SH]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "SH"@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>

Session 06

Additional Information