Re: Partition elimination.....

"oracle_man" <oracle_man_at_yahoo.com> wrote in message news:1151977990.739015.127090_at_75g2000cwc.googlegroups.com...
> 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>
>

It doesn't look as if there will be an efficient execution method for this query. As it stands, Oracle has converted the join into 4 separate join operations, using concatenation, because you have two columns with an OR at both ends of the join.

This means:

    you have to try to join ALL of table B to the     rows in table A which meet one of two date     conditions.
and

    you have to try to join ALL of table A to the     rows in table B which meet one of two date     conditions.

If you are going to try hash partitions, then you should be hashing on the join columns - not one of the date columns - so that Oracle can do a partition-wise join.

You may find that the join runs more quickly if you block the concatenation transformation by using the hint /*+ NO_EXPAND */ If you do this, then one of the two tables will have to be completely hashed into memory. So you will have to work out a suitable number of partitions to make that possible one partition at a time, and then work out the degree of parallelism that goes with that number of partitions.

Done properly, the query should run in about the time it takes to do a single full tablescan of each table.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html