Index traversal forbidding the use of Partitions

Database

Index traversal forbidding the use of Partitions

I have a 8 billion record Shipments table which is joined to couple other tables. The Shipments tables has some indexes defined and those index columns are being used as filters in my query. The Shipments table is also partitioned on SHIPMENT_DT.

When executing the query Explain plan shows Index Traversal but hitting all the partitions of the Shipments Table which is slowing down the query. Is it a rule or something that when Indexes are used then the Parttitions are not used ?

Please advise

Thanks

Manik

11 REPLIES
N/A

Re: Index traversal forbidding the use of Partitions

Hi Manik,

SHIPMENT_DT is used in the WHERE-condition, too?

Is partition elimination working as expected when the NUSI-conditions are removed?

The optimizer decides (based on existing stats) which is more efficient, NUSIs or partition elimination.

Dieter

Re: Index traversal forbidding the use of Partitions

Yes Dieter,

Shipment_Dt is used in the Where condition as well. And it uses the Partition elimination when the Condition commented below is removed from the query.  I don't understand why Prod_Id has an impact of whether Partition should be used or not.

RETAIL_SHIPMENT has NUSI on PROD_ID and SHIPMENT_DT as well 

Select

S.rtl_id,

mim.Retail_Extn.Key_Acct_id,

mim.Retail.Lvl_1_Mgmt_Acct_nm ChainName, 

mim.Retail.Region_id,

mim.Retail.Section_Id,

mim.Retail.District_Id,

mim.Retail.Unit_Id,

mim.Retail.Territory_Id

FROM   mim.RETAIL_SHIPMENT S  

JOIN mim.Product P      

ON S.Prod_ID =  P.Prod_ID 

and P.CATG_ID = 3 

and P.Company_ID = 59 

and P.Brand_Family_ID = 1489 

/*and P.Prod_ID = 7683  */

And s.shipment_dt  in    ('2012-02-18','2013-02-16')    

JOIN mim.RETAIL  

ON S.RTL_ID = mim.Retail.RTL_ID   

Inner Join mim.Retail_Extn  

ON mim.Retail_Extn.RTL_ID = mim.Retail.RTL_ID   

Group By 1,2,3,4,5,6,7,8

Thanks

N/A

Re: Index traversal forbidding the use of Partitions

Hi Manik,

could you show the different explains?

Which release are you running?

Dieter

Re: Index traversal forbidding the use of Partitions

I am on TD 13

Given below are the Explains without and with the PROD_ID Filters. Can you please help me understand why Teradata is creating a Bit map even in the cases where I am using just one filter in my query( Not this one but in another query). BMSMS is taking a lot of time.

EXPLAIN without the PROD_ID Filter

1) First, we lock MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT for access, we lock MIM_TBL.RETAIL_T in view

     mim.RETAIL for access, we lock MIM_TBL.RETAIL_EXTN_T in view

     mim.Retail_Extn for access, and we lock MIM_TBL.PRODUCT_T in view

     mim.Product for access. 

  2) Next, we do an all-AMPs RETRIEVE step from MIM_TBL.PRODUCT_T in

     view mim.Product by way of an all-rows scan with a condition of (

     "(MIM_TBL.PRODUCT_T in view mim.Product.Brand_Family_Id = 1489)

     AND ((MIM_TBL.PRODUCT_T in view mim.Product.Company_Id = 59) AND

     (MIM_TBL.PRODUCT_T in view mim.Product.Catg_Id = 3 ))") into Spool

     8 (all_amps) (compressed columns allowed), which is duplicated on

     all AMPs.  The size of Spool 8 is estimated with high confidence

     to be 3,960 rows (67,320 bytes).  The estimated time for this step

     is 0.02 seconds. 

  3) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an

     all-rows scan, which is joined to 2 partitions of

     MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT with a

     condition of ("(MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT.Shipment_Dt = DATE '2013-02-16') OR

     (MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Shipment_Dt

     = DATE '2012-02-18')").  Spool 8 and MIM_TBL.RETAIL_SHIPMENT_T are

     joined using a single partition hash_ join, with a join condition

     of ("MIM_TBL.RETAIL_SHIPMENT_T.Prod_Id = Prod_Id").  The input

     table MIM_TBL.RETAIL_SHIPMENT_T will not be cached in memory.  The

     result goes into Spool 9 (all_amps) (compressed columns allowed),

     which is built locally on the AMPs.  Then we do a SORT to order

     Spool 9 by the hash code of (MIM_TBL.RETAIL_SHIPMENT_T.Rtl_Id). 

     The size of Spool 9 is estimated with low confidence to be 499,367

     rows (8,489,239 bytes).  The estimated time for this step is 3.36

     seconds. 

  4) We do an all-AMPs JOIN step from MIM_TBL.RETAIL_T in view

     mim.RETAIL by way of a RowHash match scan with no residual

     conditions, which is joined to Spool 9 (Last Use) by way of a

     RowHash match scan.  MIM_TBL.RETAIL_T and Spool 9 are joined using

     a merge join, with a join condition of ("Rtl_Id =

     MIM_TBL.RETAIL_T.Rtl_Id").  The result goes into Spool 10

     (all_amps) (compressed columns allowed), which is built locally on

     the AMPs.  The size of Spool 10 is estimated with low confidence

     to be 499,367 rows (29,962,020 bytes).  The estimated time for

     this step is 0.18 seconds. 

  5) We do an all-AMPs JOIN step from MIM_TBL.RETAIL_EXTN_T in view

     mim.Retail_Extn by way of a RowHash match scan with no residual

     conditions, which is joined to Spool 10 (Last Use) by way of a

     RowHash match scan.  MIM_TBL.RETAIL_EXTN_T and Spool 10 are joined

     using a merge join, with a join condition of ("(Rtl_Id =

     MIM_TBL.RETAIL_EXTN_T.Rtl_Id) AND (MIM_TBL.RETAIL_EXTN_T.Rtl_Id =

     Rtl_Id)").  The result goes into Spool 7 (all_amps) (compressed

     columns allowed), which is built locally on the AMPs.  The size of

     Spool 7 is estimated with low confidence to be 499,367 rows (

     33,956,956 bytes).  The estimated time for this step is 0.56

     seconds. 

  6) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by

     way of an all-rows scan , grouping by field1 (

     MIM_TBL.RETAIL_SHIPMENT_T.Rtl_Id

     ,MIM_TBL.RETAIL_EXTN_T.Key_Acct_Id

     ,MIM_TBL.RETAIL_T.Lvl_1_Mgmt_Acct_Nm ,MIM_TBL.RETAIL_T.Region_Id

     ,MIM_TBL.RETAIL_T.Section_Id ,MIM_TBL.RETAIL_T.District_Id

     ,MIM_TBL.RETAIL_T.Unit_Id ,MIM_TBL.RETAIL_T.Territory_Id). 

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 5.  The size of Spool 5 is estimated with no confidence

     to be 484,812 rows (81,448,416 bytes).  The estimated time for

     this step is 0.49 seconds. 

  7) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 5 are sent back to the user as the result of

     statement 1.  The total estimated time is 4.61 seconds. 

Explain with the PROD_ID Filter

1) First, we lock MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT for access, we lock MIM_TBL.RETAIL_T in view

     mim.RETAIL for access, and we lock MIM_TBL.RETAIL_EXTN_T in view

     mim.Retail_Extn for access. 

  2) Next, we execute the following steps in parallel. 

       1) We do a single-AMP RETRIEVE step from MIM_TBL.PRODUCT_T in

          view mim.Product by way of the unique primary index

          "MIM_TBL.PRODUCT_T in view mim.Product.Prod_Id = 7683" with a

          residual condition of ("(MIM_TBL.PRODUCT_T in view

          mim.Product.Brand_Family_Id = 1489) AND ((MIM_TBL.PRODUCT_T

          in view mim.Product.Company_Id = 59) AND (MIM_TBL.PRODUCT_T

          in view mim.Product.Catg_Id = 3 ))") locking row for access

          into Spool 9 (all_amps) (compressed columns allowed), which

          is duplicated on all AMPs.  The size of Spool 9 is estimated

          with high confidence to be 72 rows (1,224 bytes).  The

          estimated time for this step is 0.01 seconds. 

       2) We do a BMSMS (bit map set manipulation) step that builds a

          bit map for MIM_TBL.RETAIL_SHIPMENT_T in view

          mim.RETAIL_SHIPMENT by way of a traversal of index # 8 which

          is placed in Spool 8.  The estimated time for this step is

          0.27 seconds. 

  3) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an

     all-rows scan, which is joined to MIM_TBL.RETAIL_SHIPMENT_T in

     view mim.RETAIL_SHIPMENT by way of index # 4

     "MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Prod_Id =

     7683" and the bit map in Spool 8 (Last Use) with a residual

     condition of ("(MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT.Shipment_Dt = DATE '2013-02-16') OR

     (MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Shipment_Dt

     = DATE '2012-02-18')").  Spool 9 and MIM_TBL.RETAIL_SHIPMENT_T are

     joined using a product join, with a join condition of (

     "MIM_TBL.RETAIL_SHIPMENT_T.Prod_Id = Prod_Id").  The input table

     MIM_TBL.RETAIL_SHIPMENT_T will not be cached in memory, but it is

     eligible for synchronized scanning.  The result goes into Spool 10

     (all_amps) (compressed columns allowed), which is built locally on

     the AMPs.  Then we do a SORT to order Spool 10 by the hash code of

     (MIM_TBL.RETAIL_SHIPMENT_T.Rtl_Id).  The size of Spool 10 is

     estimated with low confidence to be 30,868 rows (524,756 bytes). 

     The estimated time for this step is 0.26 seconds. 

  4) We do an all-AMPs JOIN step from MIM_TBL.RETAIL_EXTN_T in view

     mim.Retail_Extn by way of a RowHash match scan with no residual

     conditions, which is joined to Spool 10 (Last Use) by way of a

     RowHash match scan.  MIM_TBL.RETAIL_EXTN_T and Spool 10 are joined

     using a merge join, with a join condition of ("Rtl_Id =

     MIM_TBL.RETAIL_EXTN_T.Rtl_Id").  The result goes into Spool 11

     (all_amps) (compressed columns allowed), which is built locally on

     the AMPs.  The size of Spool 11 is estimated with low confidence

     to be 30,868 rows (956,908 bytes).  The estimated time for this

     step is 0.48 seconds. 

  5) We do an all-AMPs JOIN step from MIM_TBL.RETAIL_T in view

     mim.RETAIL by way of a RowHash match scan with no residual

     conditions, which is joined to Spool 11 (Last Use) by way of a

     RowHash match scan.  MIM_TBL.RETAIL_T and Spool 11 are joined

     using a merge join, with a join condition of ("(Rtl_Id =

     MIM_TBL.RETAIL_T.Rtl_Id) AND (Rtl_Id = MIM_TBL.RETAIL_T.Rtl_Id)"). 

     The result goes into Spool 7 (all_amps) (compressed columns

     allowed), which is built locally on the AMPs.  The size of Spool 7

     is estimated with low confidence to be 30,868 rows (2,099,024

     bytes).  The estimated time for this step is 0.13 seconds. 

  6) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by

     way of an all-rows scan , grouping by field1 (

     MIM_TBL.RETAIL_SHIPMENT_T.Rtl_Id

     ,MIM_TBL.RETAIL_EXTN_T.Key_Acct_Id

     ,MIM_TBL.RETAIL_T.Lvl_1_Mgmt_Acct_Nm ,MIM_TBL.RETAIL_T.Region_Id

     ,MIM_TBL.RETAIL_T.Section_Id ,MIM_TBL.RETAIL_T.District_Id

     ,MIM_TBL.RETAIL_T.Unit_Id ,MIM_TBL.RETAIL_T.Territory_Id). 

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 5.  The size of Spool 5 is estimated with low confidence

     to be 30,868 rows (5,185,824 bytes).  The estimated time for this

     step is 0.05 seconds. 

  7) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 5 are sent back to the user as the result of

     statement 1.  The total estimated time is 1.19 seconds. 

N/A

Re: Index traversal forbidding the use of Partitions

Hi Manik,

according to explain the 2nd plan is much more efficient.

So what's the actual runtime? Do you have DBQL stepinfo enabled to see the resource usage of each step?

BMSMS might be used by the optimizer when more than singe NUSI is used in WHERE.

Dieter

Re: Index traversal forbidding the use of Partitions

DIeter - Plan wise, second one more efficient but its taking 4 secs without PROD_ID filter whereas the first one is takinf almost 6 mins.

Unfortunately I can not access DBQL tables.

Regarding BMSMS. both the below queries has a BMSMS even if one query has just one filter clause. Table has a NUSI on Shipment_Dt as well as Prod_Id. Not sure why in the second query its still creating a BMSMS

Select *

FROM   mim.RETAIL_SHIPMENT S  

WHere Prod_ID = 7683

And shipment_dt  in    ('2012-02-18','2013-02-16')    

 1) First, we lock MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT for access. 

  2) Next, we do a BMSMS (bit map set manipulation) step that builds a

     bit map for MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT

     by way of a traversal of index # 8 which is placed in Spool 3. 

     The estimated time for this step is 0.14 seconds. 

  3) We do an all-AMPs RETRIEVE step from MIM_TBL.RETAIL_SHIPMENT_T in

     view mim.RETAIL_SHIPMENT by way of index # 4

     "MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Prod_Id =

     7683" and the bit map in Spool 3 (Last Use) with a residual

     condition of ("(MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT.Shipment_Dt = DATE '2013-02-16') OR

     (MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Shipment_Dt

     = DATE '2012-02-18')") into Spool 2 (all_amps), which is built

     locally on the AMPs.  The input table will not be cached in memory,

     but it is eligible for synchronized scanning.  The size of Spool 2

     is estimated with high confidence to be 19,732 rows (2,841,408

     bytes).  The estimated time for this step is 0.03 seconds. 

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 2 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.17 seconds. 

Select *

FROM   mim.RETAIL_SHIPMENT S  

WHere Prod_ID = 7683

1) First, we lock MIM_TBL.RETAIL_SHIPMENT_T in view

     mim.RETAIL_SHIPMENT for access. 

  2) Next, we do a BMSMS (bit map set manipulation) step that builds a

     bit map for MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT

     by way of a traversal of index # 8 which is placed in Spool 3. 

     The estimated time for this step is 0.00 seconds. 

  3) We do an all-AMPs RETRIEVE step from MIM_TBL.RETAIL_SHIPMENT_T in

     view mim.RETAIL_SHIPMENT by way of index # 4

     "MIM_TBL.RETAIL_SHIPMENT_T in view mim.RETAIL_SHIPMENT.Prod_Id =

     7683" and the bit map in Spool 3 (Last Use) with no residual

     conditions into Spool 2 (all_amps), which is built locally on the

     AMPs.  The input table will not be cached in memory, but it is

     eligible for synchronized scanning.  The size of Spool 2 is

     estimated with high confidence to be 753,379 rows (108,486,576

     bytes).  The estimated time for this step is 1.20 seconds. 

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 2 are sent back to the user as the result of

     statement 1.  The total estimated time is 1.20 seconds. 

N/A

Re: Index traversal forbidding the use of Partitions

Hi Manik,

strange, but it's two different indexes, #4 and #8, could you do a HELP INDEX to get details?

And show the partitioning/index definition, i'm a bit confuded, you said the table is partitioned by shipment_dt and also there's a NUSI on shipment_dt?

Dieter

Re: Index traversal forbidding the use of Partitions

Yes. Shipment date is both Partitioned and It has a NUSI as well. Is it a right way or it dosen't makes sense  as even I was bit surprised to see this but hard to convince all the colleagues.

Here is the Help Index result










Unique? Primary or Secondary? Column Names Index Id Approximate Count Index Name Ordered or Partitioned?
N P Rtl_Id 1 5,889,846,312.00 ? P
N S Prod_Id 4 13,778.00 ? H
N S Rtl_Id,Prod_Id,Shipment_Dt 8 55,088,622.00 ? H
N S Rtl_Id,Shipment_Dt 12 362,584.00 ? H
N S Dstbr_Id 16 12,600.00 ? H
N S Shipment_Dt 20 9,892.00 ? H

Thanks

Manik

Re: Index traversal forbidding the use of Partitions

Forgot this:)

PARTITION BY RANGE_N(Shipment_Dt  BETWEEN DATE '2007-01-01' AND DATE '2020-12-31' EACH INTERVAL '7' DAY )

INDEX ( Prod_Id )

INDEX ( Rtl_Id ,Prod_Id ,Shipment_Dt )

INDEX ( Rtl_Id ,Shipment_Dt )

INDEX ( Dstbr_Id )

INDEX ( Shipment_Dt );