oracle_man wrote:
> 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(*)
> ----------
> 106111597
>
> SQL> select count(*) from richtmp.vehicleprofile;
>
> COUNT(*)
> ----------
> 102493635
>
> 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>
Serge is correct. Hash partitioning and your query have nothing to
do with each other. But given that you didn't supply the Oracle
version or the DDL so that we could see what was hashed and into
how many buckets it is hard to tell what you are doing.
Seems to me that the first order of business would be to rebuild
this using RANGE PARTITIONING by date and then, if needed,
subpartitioning by hash.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jul 04 2006 - 11:23:24 CDT
