| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Partition elimination.....
All,
I have two large (100 Million+ rows) tables. I need to join them in the most efficient manner possible. I'm trying partitioning. Here is a describe of the table, and a query I need to tune. The query is still taking forever to complete. Both tables are HASH partitioned on RECORDINSERTDT. If anyone can suggest the best join method, and or a better partition method, I would appreciate it.
Thanks,
rich
SQL> desc richtmp.vehicleprofile;
Name Null? Type ----------------------------------------- -------- ---------------------------- VEHICLEPROFILESKEY NOT NULL NUMBER VEHICLESYSTEMNUMBER NUMBER DIRECTEDVEHICLEIND CHAR(1) DIRECTEDVEHICLEUPDATEDT TIMESTAMP(6) DVSECONDTESTFLAG CHAR(1) DVSECONDTESTFLAGDT TIMESTAMP(6) GROSSPOLLUTERIND CHAR(1) WAIVERINDFLAG CHAR(1) HARDSHIPIND CHAR(1) DVREASONCODE CHAR(1) EFFECTIVESTARTDT NOT NULL TIMESTAMP(6) EFFECTIVEENDDT TIMESTAMP(6) RECORDINSERTDT NOT NULL TIMESTAMP(6) RECORDUPDATEDT TIMESTAMP(6) SYSTEMNM VARCHAR2(50) INSERTEDBY VARCHAR2(30) UPDATEDBY VARCHAR2(30) SQL> desc richtmp.ngetvehicle; Name Null? Type ----------------------------------------- -------- ---------------------------- VEHICLESKEY NOT NULL NUMBER VEHICLESYSTEMNUMBER NOT NULL NUMBER VIN VARCHAR2(20) LICENSESTATE VARCHAR2(20) LICENSEPLATE VARCHAR2(20) MAKE VARCHAR2(50) MODELNM VARCHAR2(50) MODELYEAR NUMBER VEHICLETYPE CHAR(1) VSVLTROWIDNUM VARCHAR2(5) FILENUMBERSTORAGE VARCHAR2(20) GOVERNMENTOWNEDFLAG CHAR(1) MEMBEROFFLEETFLAG CHAR(1) MILITARYOWNEDFLAG CHAR(1) DMVRECORDINSTANCESTATUS CHAR(1) ALLOWUPDTSFLAG CHAR(1) REFEREELABEL VARCHAR2(20) BODYTYPE CHAR(1) ENGINEMAKE VARCHAR2(30) ENGINESIZE NUMBER ENGINEYEAR NUMBER NUMBEROFCYLINDERS VARCHAR2(2) FUELTYPE VARCHAR2(2) TRANSMISSIONTYPE CHAR(1) CERTIFICATIONTYPE CHAR(1) GVWR NUMBER EFFECTIVESTARTDT NOT NULL TIMESTAMP(6) EFFECTIVEENDDT TIMESTAMP(6) RECORDINSERTDT NOT NULL TIMESTAMP(6) RECORDUPDATEDT TIMESTAMP(6) SYSTEMNM VARCHAR2(50) INSERTEDBY VARCHAR2(30) UPDATEDBY VARCHAR2(30)
SQL> SQL> select count(*) from richtmp.ngetvehicle;
COUNT(*)
SQL> select count(*) from richtmp.vehicleprofile;
COUNT(*)
Here is the query:
$ cat bad.sql
SELECT /*+ USE_NL parallel(ngetvehicle, 2) parallel(vehicleprofile, 2)
*/ "NGETVEHICLE"."VEHICLESKEY", "NGETVEHICLE"."VEHICLESYSTEMNUMBER",
"NGETVEHICLE"."VIN", "NGETVEHICLE"."LICENSESTATE",
"NGETVEHICLE"."LICENSEPLATE", "NGETVEHICLE"."MAKE",
"NGETVEHICLE"."MODELNM", "NGETVEHICLE"."MODELYEAR",
"NGETVEHICLE"."VEHICLETYPE",
"NGETVEHICLE"."FILENUMBERSTORAGE",
"NGETVEHICLE"."GOVERNMENTOWNEDFLAG","NGETVEHICLE"."MEMBEROFFLEETFLAG","NGETVEHICLE"."MILITARYOWNEDFLAG",
"NGETVEHICLE"."DMVRECORDINSTANCESTATUS",
"NGETVEHICLE"."ALLOWUPDTSFLAG", "NGETVEHICLE"."BODYTYPE",
"NGETVEHICLE"."ENGINEMAKE",
"NGETVEHICLE"."ENGINESIZE","NGETVEHICLE"."ENGINEYEAR" "ENGINEYEAR$0",
"NGETVEHICLE"."NUMBEROFCYLINDERS", "NGETVEHICLE"."FUELTYPE",
"NGETVEHICLE"."TRANSMISSIONTYPE", "NGETVEHICLE"."CERTIFICATIONTYPE",
"NGETVEHICLE"."GVWR", "NGETVEHICLE"."EFFECTIVESTARTDT",
"NGETVEHICLE"."EFFECTIVEENDDT", "NGETVEHICLE"."RECORDINSERTDT",
"NGETVEHICLE"."RECORDUPDATEDT", "NGETVEHICLE"."SYSTEMNM",
"NGETVEHICLE"."INSERTEDBY", "NGETVEHICLE"."UPDATEDBY",
"VEHICLEPROFILE"."VEHICLEPROFILESKEY",
"VEHICLEPROFILE"."VEHICLESYSTEMNUMBER",
"VEHICLEPROFILE"."DIRECTEDVEHICLEIND",
"VEHICLEPROFILE"."DIRECTEDVEHICLEUPDATEDT","VEHICLEPROFILE"."DVSECONDTESTFLAG",
"VEHICLEPROFILE"."DVSECONDTESTFLAGDT",
"VEHICLEPROFILE"."GROSSPOLLUTERIND", "NGETVEHICLE"."REFEREELABEL",
"VEHICLEPROFILE"."WAIVERINDFLAG",
"VEHICLEPROFILE"."HARDSHIPIND", "VEHICLEPROFILE"."DVREASONCODE",
"VEHICLEPROFILE"."EFFECTIVESTARTDT", "VEHICLEPROFILE"."EFFECTIVEENDDT",
"VEHICLEPROFILE"."RECORDINSERTDT", "VEHICLEPROFILE"."RECORDUPDATEDT",
"VEHICLEPROFILE"."SYSTEMNM", "VEHICLEPROFILE"."INSERTEDBY",
"VEHICLEPROFILE"."UPDATEDBY"
FROM richtmp.NGETVEHICLE, richtmp.VEHICLEPROFILE "VEHICLEPROFILE"
WHERE ( "NGETVEHICLE"."VEHICLESYSTEMNUMBER" =
"VEHICLEPROFILE"."VEHICLESYSTEMNUMBER" )
AND ("NGETVEHICLE"."RECORDINSERTDT" >=
to_date('06/24/2006','mm/dd/yyyy') OR "NGETVEHICLE"."RECORDUPDATEDT" >=
to_date('06/24/2006','mm/dd/yyyy')
OR "VEHICLEPROFILE"."RECORDINSERTDT" >=
to_date('06/24/2006','mm/dd/yyyy') OR "VEHICLEPROFILE"."RECORDUPDATEDT"
>= to_date('06/24/2006','mm/dd/yyyy') );
Explain Plan:
PLAN_TABLE_OUTPUT
| Id | Operation | Name |Rows | Bytes |TempSpc| Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | |
4975K| 2552M| | 43579 | | | | |
|
| 1 | CONCATENATION | |
| | | | | | 94,05 | P->S | QC
(RAND) |
| 2 | MERGE JOIN | |
97808 | 17M| | | | | 94,05 | PCWP |
|
| 3 | SORT JOIN | |
97970 | 50M| | | | | 94,05 | PCWP |
|
| 4 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| NGETVEHICLE |
227K| 77M| | 5 | 1 | 12 | 94,05 | PCWP |
|
| 6 | INDEX FULL SCAN | NGETVEHICLE_IDX_03 |
401 | | | 1 | 1 | 12 | 94,05 | PCWP |
|
PLAN_TABLE_OUTPUT
| 7 | SORT JOIN | |
97808 | 17M| | | | | 94,05 | PCWP |
|
| 8 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| VEHICLEPROFILE |
97808 | 17M| | 419 | 1 | 12 | 94,01 | P->P |
BROADCAST |
| 10 | INDEX RANGE SCAN | VEHICLEPROFILE_IDX_3 |
39M| | | 19 | 1 | 12 | 94,01 | PCWP |
|
| 11 | MERGE JOIN | |
97808 | 17M| | | | | 94,05 | PCWP |
|
| 12 | SORT JOIN | |
97970 | 50M| | | | | 94,05 | PCWP |
|
| 13 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID| NGETVEHICLE |
227K| 77M| | 5 | 1 | 12 | 94,05 | PCWP |
|
| 15 | INDEX FULL SCAN | NGETVEHICLE_IDX_03 |
401 | | | 1 | 1 | 12 | 94,05 | PCWP |
|
| 16 | SORT JOIN | |
97808 | 17M| | | | | 94,05 | PCWP |
|
| 17 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
PLAN_TABLE_OUTPUT
| 18 | TABLE ACCESS BY LOCAL INDEX ROWID| VEHICLEPROFILE |
97808 | 17M| | 419 | 1 | 12 | 94,02 | P->P |
BROADCAST |
| 19 | INDEX RANGE SCAN | VEHICLEPROFILE_IDX_1 |
39M| | | 19 | 1 | 12 | 94,02 | PCWP |
|
| 20 | HASH JOIN | |
97970 | 50M| 187M| 5425 | | | 94,05 | PCWP |
|
| 21 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,00 | S->P | HASH
|
| 22 | TABLE ACCESS FULL | VEHICLEPROFILE |
97808 | 17M| | 419 | 1 | 12 | | |
|
| 23 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
| 24 | TABLE ACCESS BY LOCAL INDEX ROWID | NGETVEHICLE |
227K| 77M| | 5 | 1 | 12 | 94,03 | P->P | HASH
|
| 25 | INDEX RANGE SCAN | NGETVEHICLE_IDX_02 |
401 | | | 1 | 1 | 12 | 94,03 | PCWP |
|
| 26 | MERGE JOIN | |
97970 | 50M| | 5425 | | | 94,05 | PCWP |
|
| 27 | SORT JOIN | |
97808 | 17M| | 837 | | | 94,05 | PCWP |
|
| 28 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
PLAN_TABLE_OUTPUT
| 29 | TABLE ACCESS BY LOCAL INDEX ROWID| VEHICLEPROFILE |
97808 | 17M| | 419 | 1 | 12 | 94,04 | P->P |
BROADCAST |
| 30 | INDEX FULL SCAN | VEHICLEPROFILE_IDX_2 |
39M| | | 19 | 1 | 12 | 94,04 | PCWP |
|
| 31 | SORT JOIN | |
227K| 77M| 187M| 4588 | | | 94,05 | PCWP |
|
| 32 | PARTITION HASH ALL | |
| | | | 1 | 12 | 94,05 | PCWP |
|
| 33 | TABLE ACCESS BY LOCAL INDEX ROWID| NGETVEHICLE |
227K| 77M| | 5 | 1 | 12 | 94,05 | PCWP |
|
| 34 | INDEX RANGE SCAN | NGETVEHICLE_IDX_01 |
401 | | | 1 | 1 | 12 | 94,05 | PCWP |
|
Note: cpu costing is off, PLAN_TABLE' is old version
42 rows selected.
SQL> Received on Mon Jul 03 2006 - 20:53:10 CDT
 |
 |