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 ?
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.
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
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 - 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
1) First, we lock MIM_TBL.RETAIL_SHIPMENT_T in view
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?
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?|