Partition Pruning in HANA

[Former Member]

I am fairly new to analyzing visual plans in HANA, but I am experiencing something I cannot quite explain.  We have a small fact table of about 3 billion rows.  We have range partitioned this table by what I will call Fiscal Week (SALES_DATE_FISCAL_WEEK), which is a year and week concatenated (i.e '201441', etc.), and grouped the partitioning so that 1 Fiscal Month is contained within a partition (4 or 5 fiscal weeks).  We have this table spread across 3 nodes.

When I visualize the explain against this table for one fiscal month:

SELECT *

FROM FACT TABLE

WHERE SALES_DATE_FISCAL_WEEK BETWEEN '201449' AND '201452'

AND LOCATION = '0171'

I see a search on one single partition, partition 85, as I would expect. 

When I visualize this plan, I see a search on all partitions:

SELECT *

FROM FACT TABLE

WHERE SALES_DATE BETWEEN '20150104' AND '20150131'

AND LOCATION = '0171'

Now the confusing part.   We have a complex model created that I won't detail here, but this is the gist of what we are doing.  We are computing 3 dates from our Time dimension, a current month, Last year current month, and Last Last year current month, i.e. TY, LY, and LLY. This is being supplied as SALES_DATE BETWEEN X AND Y.  I see this in each of the between predicates, for each of the cases above, TY, LY, and LLY.  For example:

Summary: SALES_DATE BETWEEN '20150104' AND '20150131'

What I see however, is ONLY 3 PARTITIONS INVOLVED.  The predicate is NOT on Partitioning Column, but on the SALES_DATE.  It appears I get Partition Pruning, but we are not using the column I would have thought I needed to be involved based on the results above. 

Is this the result of some transitive property in HANA?  Am I missing something obvious?  In the actual Join Operator for one of these 3 joins, I see 4 dimension tables being joined to the central fact table, but I cannot see how to view the sql from this join.  At some point I assume the the Calendar Table is being joined to the fact table on this SALES_DATE.

Any help would be appreciated.

[lbreddemann · Accepted solution]

That's a lot of theory and only little data.

Please show the table DDL, your statements and the planviz files/output. EXPLAIN PLAN does not show you which partitions are touched.

To investigate partition pruning behavior you might also trace it. Check my book for a detailed description on how to do that.

[Former Member · Accepted solution]

I ran some detailed traces today, and I will post here only the pruning part.  It appears that partition pruning is not being selected, despite what I see in the query plan visualization.  As I said, the model is complicated, with many nested views and several tables.

About half way down, it seems the supplied predicate cannot be reconciled with the partition specification:

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810826 d partitioning     Pruning.cpp(00057) : Pruning entries are ((20121230 <= <UNKNOWN> >= 20130202)).

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810830 d partitioning     Pruning.cpp(00063) : Pruning entries were created by converting Query Entires.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810832 d partitioning     Pruning.cpp(00063) : Pruning entries were created by converting Query Entires.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810834 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810837 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810838 d partitioning     Pruning.cpp(00080) : For the given partition specification, no filtering according to a Range Restriction will take place.

From the final statements in the pruning section, namely:

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812003 i partitioning     Pruning.cpp(00094) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773): all parts have to be considered.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812076 i join_eval        Estimator.cpp(00505) : Partition spec based pruning didn't remove any parts.

Especially:

"Partition spec based pruning didn't remove any parts".

I take this to mean that no filtering occurred based on partition.

Please correct me if this is in error.

I cannot post the entire trace, as it is quite long, about 22,000 lines in text format. This is the first time I have ever seen one.

I post DDL for both Calendar and Fact Table at the end.

Thanks.

CALENDAR

Example of constraints:

[88908]{232661}[322/-1] 2015-11-10 14:59:29.790696 d join_eval        MetaModel.cpp(00810) : TABLE ZCAR:HT_JAS_CALENDAR (-1)(1000001)

[87407]{232661}[322/-1] 2015-11-10 14:59:29.790730 d join_eval        MetaModel.cpp(00810) :    CONSTRAINT ( DATE:["20150104" TO "20150131"] ) AND ( LLY_DATE:["20121230" TO "20130202"] )

FACT TABLE

[87407]{232661}[322/-1] 2015-11-10 14:59:29.790590 d join_eval        MetaModel.cpp(00810) : TABLE ZCAR:HT_SALES_TXNLINE (-1)(1000021)

[88908]{232661}[322/-1] 2015-11-10 14:59:29.790630 d join_eval        MetaModel.cpp(00810) :    CONSTRAINT ( SALES_DATE:["20150104" TO "20150131"] )

Joins for Calendar, Fact:

subgraph mySG_2 {

  edge[dir=none, label="pm_jc_2"];

  "ZCAR:HT_SALES_TXNLINE (87773)":"SALES_DATE" -> "ZCAR:HT_JAS_CALENDAR (87711)":"DATE" ;

subgraph mySG_2 {

  edge[dir=none, label="pm_jc_2"];

  "ZCAR:HT_SALES_TXNLINE (87773)":"SALES_DATE" -> "ZCAR:HT_JAS_CALENDAR (87711)":"LLY_DATE" ;

  }

subgraph mySG_2 {

  edge[dir=none, label="pm_jc_2"];

  "ZCAR:HT_SALES_TXNLINE (87773)":"SALES_DATE" -> "ZCAR:HT_JAS_CALENDAR (87711)":"LY_DATE" ;

  }

Partition Pruning Section

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810797 d partitioning     Pruning.cpp(00052) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773).

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810798 d partitioning     Pruning.cpp(00052) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773).

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810808 d partitioning     Pruning.cpp(00054) : Partition spec is RANGE SALES_DATE_FISCAL_WEEK 000000-200801,200801-200805,200805-200810,200810-200814,200814-200818,200818-200823,200823-200827,200827-200831,200831-200836,200836-200840,200840-200844,200844-200849,200849-200901,200901-200905,200905-200910,200910-200914,200914-200918,200918-200923,200923-200927,200927-200931,200931-200936,200936-200940,200940-200944,200944-200949,200949-201001,201001-201005,201005-201010,201010-201014,201014-201018,201018-201023,201023-201027,201027-201031,201031-201036,201036-201040,201040-201044,201044-201049,201049-201101,201101-201105,201105-201110,201110-201114,201114-201118,201118-201123,201123-201127,201127-201131,201131-201136,201136-201140,201140-201144,201144-201149,201149-201201,201201-201205,201205-201210,201210-201214,201214-201218,201218-201223,201223-201227,201227-201231,201231-201236,201236-201240,201240-201244,201244-201249,201249-201301,201301-201305,201305-201310,201310-201314,201314-201318,201318-201323,201323-201327,201327-201331,201331-201336,201336-20

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810810 d partitioning     Pruning.cpp(00054) : Partition spec is RANGE SALES_DATE_FISCAL_WEEK 000000-200801,200801-200805,200805-200810,200810-200814,200814-200818,200818-200823,200823-200827,200827-200831,200831-200836,200836-200840,200840-200844,200844-200849,200849-200901,200901-200905,200905-200910,200910-200914,200914-200918,200918-200923,200923-200927,200927-200931,200931-200936,200936-200940,200940-200944,200944-200949,200949-201001,201001-201005,201005-201010,201010-201014,201014-201018,201018-201023,201023-201027,201027-201031,201031-201036,20

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810808 d partitioning     Pruning.cpp(00054) : 1340,201340-201344,201344-201349,201349-201401,201401-201405,201405-201410,201410-201414,201414-201418,201418-201423,201423-201427,201427-201431,201431-201436,201436-201440,201440-201444,201444-201449,201449-201501,201501-201505,201505-201510,201510-201514,201514-201518,201518-201523,201523-201527,201527-201531,201531-201536,201536-201540,201540-201544,201544-201549,201549-201601,201601-201605,201605-201610,201610-201614,201614-201618,201618-201623,201623-201627,201627-201631,201631-201636,201636-201640,201640-201644,201644-201649,201649-201701,201701-201705,201705-201710,201710-201714,201714-201718,201718-201723,201723-201727,201727-201731,201731-201736,201736-201740,201740-201744,201744-201749,201749-201801,201801-201805,201805-201810,201810-201814,201814-201818,201818-201823,201823-201827,201827-201831,201831-201836,201836-201840,201840-201844,201844-201849,201849-201901,201901-201905,201905-201910,201910-201914,201914-201918,201918-201923,201923-201927,201927-201931,201931-201936,201936-201940,201940-20

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810810 d partitioning     Pruning.cpp(00054) : 1036-201040,201040-201044,201044-201049,201049-201101,201101-201105,201105-201110,201110-201114,201114-201118,201118-201123,201123-201127,201127-201131,201131-201136,201136-201140,201140-201144,201144-201149,201149-201201,201201-201205,201205-201210,201210-201214,201214-201218,201218-201223,201223-201227,201227-201231,201231-201236,201236-201240,201240-201244,201244-201249,201249-201301,201301-201305,201305-201310,201310-201314,201314-201318,201318-201323,201323-201327,201327-201331,201331-201336,201336-20

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810808 d partitioning     Pruning.cpp(00054) : 1944,201944-201949,201949-202001,202001-202005,202005-202010,202010-202014,202014-202018,202018-202023,202023-202027,202027-202031,202031-202036,202036-202040,202040-202044,202044-202049,202049-202101,*.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810810 d partitioning     Pruning.cpp(00054) : 1340,201340-201344,201344-201349,201349-201401,201401-201405,201405-201410,201410-201414,201414-201418,201418-201423,201423-201427,201427-201431,201431-201436,201436-201440,201440-201444,201444-201449,201449-201501,201501-201505,201505-201510,201510-201514,201514-201518,201518-201523,201523-201527,201527-201531,201531-201536,201536-201540,201540-201544,201544-201549,201549-201601,201601-201605,201605-201610,201610-201614,201614-201618,201618-201623,201623-201627,201627-201631,201631-201636,201636-201640,201640-201644,201644-201649,201649-201701,201701-201705,201705-201710,201710-201714,201714-201718,201718-201723,201723-201727,201727-201731,201731-201736,201736-201740,201740-201744,201744-201749,201749-201801,201801-201805,201805-201810,201810-201814,201814-201818,201818-201823,201823-201827,201827-201831,201831-201836,201836-201840,201840-201844,201844-201849,201849-201901,201901-201905,201905-201910,201910-201914,201914-201918,201918-201923,201923-201927,201927-201931,201931-201936,201936-201940,201940-201944,201944-201949,201949-202001,202001-202005,202005-202010,202010-202014,202014-202018,202018-202023,202023-202027,202027-202031,202031-202036,202036-202040,202040-202044,202044-202049,202049-202101,*.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810826 d partitioning     Pruning.cpp(00057) : Pruning entries are ((20140105 <= <UNKNOWN> >= 20140201)).

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810826 d partitioning     Pruning.cpp(00057) : Pruning entries are ((20121230 <= <UNKNOWN> >= 20130202)).

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810830 d partitioning     Pruning.cpp(00063) : Pruning entries were created by converting Query Entires.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810832 d partitioning     Pruning.cpp(00063) : Pruning entries were created by converting Query Entires.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810834 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810837 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810838 d partitioning     Pruning.cpp(00080) : For the given partition specification, no filtering according to a Range Restriction will take place.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810840 d partitioning     Pruning.cpp(00080) : For the given partition specification, no filtering according to a Range Restriction will take place.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810842 d partitioning     Pruning.cpp(00083) : Pruning was called by JoinEngine

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810843 d partitioning     Pruning.cpp(00083) : Pruning was called by JoinEngine

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810847 d partitioning     PruningRuntime.cpp(00047) : Pruning based on pruning entries will be performed.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810847 d partitioning     PruningRuntime.cpp(00047) : Pruning based on pruning entries will be performed.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810877 d partitioning     PruningRuntime.cpp(00066) : Parts based on Pruning Entries: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810877 d partitioning     PruningRuntime.cpp(00066) : Parts based on Pruning Entries: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810887 d partitioning     PruningRuntime.cpp(00078) : Final set of parts: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810888 d partitioning     PruningRuntime.cpp(00078) : Final set of parts: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810890 i partitioning     Pruning.cpp(00094) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773): all parts have to be considered.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810892 i partitioning     Pruning.cpp(00094) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773): all parts have to be considered.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810948 i join_eval        Estimator.cpp(00505) : Partition spec based pruning didn't remove any parts.

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810956 i join_eval        Estimator.cpp(00505) : Partition spec based pruning didn't remove any parts.

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810954 d join_eval        Estimator.cpp(00241) : Partition spec based pruning for index 'ZCAR:HT_SALES_TXNLINEen' returned the pruning bit vector size 158 set 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 1

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810962 d join_eval        Estimator.cpp(00241) : Partition spec based pruning for index 'ZCAR:HT_SALES_TXNLINEen' returned the pruning bit vector size 158 set 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 1

[87407]{232661}[322/-1] 2015-11-10 14:59:29.810954 d join_eval        Estimator.cpp(00241) : 02, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, ..., numSet 158

[89164]{232661}[322/-1] 2015-11-10 14:59:29.810962 d join_eval        Estimator.cpp(00241) : 02, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, ..., numSet 158

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811881 d partitioning     Pruning.cpp(00052) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773).

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811921 d partitioning     Pruning.cpp(00054) : Partition spec is RANGE SALES_DATE_FISCAL_WEEK 000000-200801,200801-200805,200805-200810,200810-200814,200814-200818,200818-200823,200823-200827,200827-200831,200831-200836,200836-200840,200840-200844,200844-200849,200849-200901,200901-200905,200905-200910,200910-200914,200914-200918,200918-200923,200923-200927,200927-200931,200931-200936,200936-200940,200940-200944,200944-200949,200949-201001,201001-201005,201005-201010,201010-201014,201014-201018,201018-201023,201023-201027,201027-201031,201031-201036,201036-201040,201040-201044,201044-201049,201049-201101,201101-201105,201105-201110,201110-201114,201114-201118,201118-201123,201123-201127,201127-201131,201131-201136,201136-201140,201140-201144,201144-201149,201149-201201,201201-201205,201205-201210,201210-201214,201214-201218,201218-201223,201223-201227,201227-201231,201231-201236,201236-201240,201240-201244,201244-201249,201249-201301,201301-201305,201305-201310,201310-201314,201314-201318,201318-201323,201323-201327,201327-201331,201331-201336,201336-201340,201340-201344,201344-201349,201349-201401,201401-201405,201405-201410,201410-201414,201414-201418,201418-201423,201423-201427,201427-201431,201431-201436,201436-201440,201440-201444,201444-201449,201449-201501,201501-201505,201505-201510,201510-201514,201514-201518,201518-201523,201523-201527,201527-201531,201531-201536,201536-201540,201540-201544,201544-201549,201549-201601,201601-201605,201605-201610,201610-201614,201614-201618,201618-201623,201623-201627,201627-201631,201631-201636,201636-201640,201640-201644,201644-201649,201649-201701,201701-201705,201705-201710,201710-201714,201714-201718,201718-201723,201723-201727,201727-201731,201731-201736,201736-201740,201740-201744,201744-201749,201749-201801,201801-201805,201805-201810,201810-201814,201814-201818,201818-201823,201823-201827,201827-201831,201831-201836,201836-201840,201840-201844,201844-201849,201849-201901,201901-201905,201905-201910,201910-201914,201914-201918,201918-201923,201923-201927,201927-201931,201931-201936,201936-201940,201940-201944,201944-201949,201949-202001,202001-202005,202005-202010,202010-202014,202014-202018,202018-202023,202023-202027,202027-202031,202031-202036,202036-202040,202040-202044,202044-202049,202049-202101,*.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811935 d partitioning     Pruning.cpp(00057) : Pruning entries are ((20150104 <= <UNKNOWN> >= 20150131)).

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811937 d partitioning     Pruning.cpp(00063) : Pruning entries were created by converting Query Entires.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811941 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811943 d partitioning     Pruning.cpp(00080) : For the given partition specification, no filtering according to a Range Restriction will take place.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811952 d partitioning     Pruning.cpp(00083) : Pruning was called by JoinEngine

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811968 d partitioning     PruningRuntime.cpp(00047) : Pruning based on pruning entries will be performed.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.811992 d partitioning     PruningRuntime.cpp(00066) : Parts based on Pruning Entries: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812001 d partitioning     PruningRuntime.cpp(00078) : Final set of parts: 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812003 i partitioning     Pruning.cpp(00094) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773): all parts have to be considered.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812076 i join_eval        Estimator.cpp(00505) : Partition spec based pruning didn't remove any parts.

[88908]{232661}[322/-1] 2015-11-10 14:59:29.812082 d join_eval        Estimator.cpp(00241) : Partition spec based pruning for index 'ZCAR:HT_SALES_TXNLINEen' returned the pruning bit vector size 158 set 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, ..., numSet 158

DDL

CREATE COLUMN TABLE "ZCAR"."HT_JAS_CALENDAR" ("DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FISCAL_WEEK" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "FISCAL_MONTH" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "FISCAL_QUARTER" NVARCHAR(5) DEFAULT '' NOT NULL ,

  "FISCAL_YEAR" NVARCHAR(4) DEFAULT '' NOT NULL ,

  "FW_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FW_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FM_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FM_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FQ_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FQ_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FY_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "FY_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "DAY_NAME" NVARCHAR(9) DEFAULT '' NOT NULL ,

  "DAY_NUMBER" NVARCHAR(1) DEFAULT '' NOT NULL ,

  "WEEK_IN_MONTH" NVARCHAR(1) DEFAULT '' NOT NULL ,

  "LY_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FISCAL_WEEK" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LY_FISCAL_MONTH" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LY_FISCAL_QUARTER" NVARCHAR(5) DEFAULT '' NOT NULL ,

  "LY_FISCAL_YEAR" NVARCHAR(4) DEFAULT '' NOT NULL ,

  "LY_FW_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FW_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FM_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FM_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FQ_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FQ_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FY_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_FY_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LY_WEEK_IN_MONTH" NVARCHAR(1) DEFAULT '' NOT NULL ,

  "LLY_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FISCAL_WEEK" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LLY_FISCAL_MONTH" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LLY_FISCAL_QUARTER" NVARCHAR(5) DEFAULT '' NOT NULL ,

  "LLY_FISCAL_YEAR" NVARCHAR(4) DEFAULT '' NOT NULL ,

  "LLY_FW_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FW_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FM_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FM_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FQ_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FQ_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FY_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_FY_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLY_WEEK_IN_MONTH" NVARCHAR(1) DEFAULT '' NOT NULL ,

  "IS_CURRENT" NVARCHAR(1) DEFAULT '' NOT NULL ,

  "LLLY_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FISCAL_WEEK" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LLLY_FISCAL_MONTH" NVARCHAR(6) DEFAULT '' NOT NULL ,

  "LLLY_FISCAL_QUARTER" NVARCHAR(5) DEFAULT '' NOT NULL ,

  "LLLY_FISCAL_YEAR" NVARCHAR(4) DEFAULT '' NOT NULL ,

  "LLLY_FW_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FW_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FM_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FM_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FQ_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FQ_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FY_START_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_FY_END_DATE" NVARCHAR(8) DEFAULT '' NOT NULL ,

  "LLLY_WEEK_IN_MONTH" NVARCHAR(1) DEFAULT '' NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE

;

CREATE COLUMN TABLE "ZCAR"."HT_SALES_TXNLINE" ("TXNSEQ_NUMBER" NVARCHAR(26) NOT NULL ,

  "SALES_DATE" NVARCHAR(8) NOT NULL ,

  "SITE" NVARCHAR(4) NOT NULL ,

  "REGISTER_NUMBER" NVARCHAR(5) NOT NULL ,

  "TRANSACTION_NUMBER" NVARCHAR(5) NOT NULL ,

  "MDSE_SEQUENCE_NUMBER" NVARCHAR(4) NOT NULL ,

  "TXN_NUMBER" NVARCHAR(22) NOT NULL ,

  "ORDER_NUMBER" NVARCHAR(20) NOT NULL ,

  "SAP_ORDER_NUMBER" NVARCHAR(10) NOT NULL ,

  "SAP_RETURN_ORDER_NUMBER" NVARCHAR(10) NOT NULL ,

  "SALES_DATE_FISCAL_WEEK" NVARCHAR(6) NOT NULL ,

  "SALES_DATE_FISCAL_MONTH" NVARCHAR(6) NOT NULL ,

  "SALES_DATE_FISCAL_YEAR" NVARCHAR(4) NOT NULL ,

  "SALES_DATE_FISCAL_QUARTER" NVARCHAR(5) NOT NULL ,

  "POSTING_DATE" NVARCHAR(8) NOT NULL ,

  "POSTING_DATE_FISCAL_WEEK" NVARCHAR(6) NOT NULL ,

  "POSTING_DATE_FISCAL_MONTH" NVARCHAR(6) NOT NULL ,

  "POSTING_DATE_FISCAL_YEAR" NVARCHAR(4) NOT NULL ,

  "POSTING_DATE_FISCAL_QUARTER" NVARCHAR(5) NOT NULL ,

  "TAX_INDICATOR" NVARCHAR(1) NOT NULL ,

  "ARTICLE_SCAN_INDICATOR" NVARCHAR(1) NOT NULL ,

  "ARTICLE" NVARCHAR(18) NOT NULL ,

  "CHAIN_STATUS" NVARCHAR(2) NOT NULL ,

  "WEB_STATUS" NVARCHAR(2) NOT NULL ,

  "VENDOR_TXN" NVARCHAR(10) NOT NULL ,

  "ABC_INDICATOR_TXN" NVARCHAR(1) NOT NULL ,

  "CHAIN_SUPPLY_SOURCE_TXN" NVARCHAR(1) NOT NULL ,

  "STORE_RP_TYPE_TXN" NVARCHAR(2) NOT NULL ,

  "FASHION_BASIC_TYPE_CODE_TXN" NVARCHAR(2) NOT NULL ,

  "FASHION_BASIC_TYPE_TXN" NVARCHAR(1) NOT NULL ,

  "FACTORY_TXN" NVARCHAR(10) NOT NULL ,

  "COUNTRY_OF_ORIGIN_TXN" NVARCHAR(3) NOT NULL ,

  "EANUPC_TXN" NVARCHAR(18) NOT NULL ,

  "PROMOTION_STORES" NVARCHAR(10) NOT NULL ,

  "PROMOTION_WEB" NVARCHAR(60) DEFAULT '' NOT NULL ,

  "PRICING_RULE" NVARCHAR(10) NOT NULL ,

  "CUTTING_STANDARD_INDICATOR" NVARCHAR(1) NOT NULL ,

  "SPECIAL_ORDER_INDICATOR" NVARCHAR(1) NOT NULL ,

  "SALES_TIME" NVARCHAR(6) NOT NULL ,

  "SALES_HOUR" NVARCHAR(2) NOT NULL ,

  "SALES_EMPLOYEE" NVARCHAR(8) NOT NULL ,

  "TEAM_MEMBER_NUMBER" NVARCHAR(10) NOT NULL ,

  "FEDERAL_SALES_TAX" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "STATE_SALES_TAX" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "LOCAL_SALES_TAX" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "ENTRY_DATE1" NVARCHAR(8) NOT NULL ,

  "ENTRY_TIME1" NVARCHAR(6) NOT NULL ,

  "ORIGINAL_TRANS_NUMBER" NVARCHAR(22) NOT NULL ,

  "COMP_STORE_IND_TXN" NVARCHAR(1) NOT NULL ,

  "SELLING_STORE" NVARCHAR(4) NOT NULL ,

  "SHIP_TO_STORE" NVARCHAR(4) NOT NULL ,

  "OMNICHANNEL_INDICATOR" NVARCHAR(1) NOT NULL ,

  "SUPPLYING_DC_TXN" NVARCHAR(4) NOT NULL ,

  "ORDER_ORIGIN" NVARCHAR(7) NOT NULL ,

  "GIFT_CARD_NUMBER" NVARCHAR(30) NOT NULL ,

  "STOCK_PLANNER_TXN" NVARCHAR(3) NOT NULL ,

  "ARTICLE_SITE_STATUS_TXN" NVARCHAR(2) NOT NULL ,

  "LISTED_TXN" NVARCHAR(1) NOT NULL ,

  "AVL_TIER_TXN" NVARCHAR(1) NOT NULL ,

  "CUSTOMER_FRAMING_IND_TXN" NVARCHAR(1) NOT NULL ,

  "EDUCATION_STORE_IND_TXN" NVARCHAR(1) NOT NULL ,

  "VIKING_STORE_IND_TXN" NVARCHAR(1) NOT NULL ,

  "SCHEMA_GROUP_TXN" NVARCHAR(2) NOT NULL ,

  "MERCHCAT_TXN" NVARCHAR(9) NOT NULL ,

  "CUSTOMER_ID" NVARCHAR(10) NOT NULL ,

  "PRIMARY_MAILER_CODE" NVARCHAR(6) NOT NULL ,

  "GK_XREF_NUMBER" NVARCHAR(14) NOT NULL ,

  "LINE_COUNTER" DECIMAL(12,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "SALES_QTY" DECIMAL(17,

  3) CS_FIXED DEFAULT 0 NOT NULL ,

  "EXTENDED_REG_RETAIL" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "MARKDOWN" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "PREDISCOUNT_SALES" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "COUPON_DISCOUNT" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "OTHER_DISCOUNT" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "TOTAL_DISCOUNT" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "SALES" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "COGS" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "MARGIN" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "REG_UNIT_PRICE" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "GROSS_UNIT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "FIRST_LANDED_UNIT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "LANDED_UNIT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "GROSS_EXT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "FIRST_LANDED_EXT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "LANDED_EXT_COST" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "DEMAND_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "GROSS_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "RETURN_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "NET_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "FREIGHT_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "GIFT_CARD_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "GREEN_BAG_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "POST_VOID_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "SALES_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "PROMO_LINE_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "PROMO_BASKET_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "COUPON_LINE_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "COUPON_BASKET_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "SPECIAL_ORDER_LINE_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "SPECIAL_ORDER_BASKET_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "EMPLOYEE_SALES_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "REMNANT_SALES_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "REMNANT_BASKET_FLAG" TINYINT CS_INT DEFAULT 0 NOT NULL ,

  "STATE" NVARCHAR(2) NOT NULL ,

  "ZIP_CODE" NVARCHAR(10) NOT NULL ,

  "SHIPPING_ZIP_CODE" NVARCHAR(10) NOT NULL ,

  "SHIPPING_COUNTRY" NVARCHAR(3) NOT NULL ,

  "BILL_TO_COUNTRY" NVARCHAR(3) NOT NULL ,

  "WEB_SOURCE" NVARCHAR(1) NOT NULL ,

  "SALES_CHANNEL" NVARCHAR(1) NOT NULL ,

  "FULFILLER" NVARCHAR(20) NOT NULL ,

  "MARKETING_SOURCE" NVARCHAR(60) NOT NULL ,

  "MEDIUM" NVARCHAR(20) NOT NULL ,

  "CUSTOMER_EMAIL_ADDRESS" NVARCHAR(60) NOT NULL ,

  "SHIPPING_METHOD" NVARCHAR(2) NOT NULL ,

  "AGENT_ID" NVARCHAR(12) NOT NULL ,

  "EMPLOYEE_ID" NVARCHAR(8) NOT NULL ,

  "OVERRIDE_REASON_CODE" NVARCHAR(40) NOT NULL ,

  "PRICE_OVERRIDE_PRICE" NVARCHAR(40) NOT NULL ,

  "PRICE_OVERRIDE_VALUE" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  "MANAGER_ID" NVARCHAR(8) NOT NULL ,

  "SHOW_CODE" NVARCHAR(20) NOT NULL ,

  "TOTAL_TAX" DECIMAL(17,

  2) CS_FIXED DEFAULT 0 NOT NULL ,

  PRIMARY KEY ("SALES_DATE_FISCAL_WEEK",

  "TXNSEQ_NUMBER")) UNLOAD PRIORITY 5 AUTO MERGE PARTITION BY 'RANGE SALES_DATE_FISCAL_WEEK 000000-200801,200801-200805,200805-200810,200810-200814,200814-200818,200818-200823,200823-200827,200827-200831,200831-200836,200836-200840,200840-200844,200844-200849,200849-200901,200901-200905,200905-200910,200910-200914,200914-200918,200918-200923,200923-200927,200927-200931,200931-200936,200936-200940,200940-200944,200944-200949,200949-201001,201001-201005,201005-201010,201010-201014,201014-201018,201018-201023,201023-201027,201027-201031,201031-201036,201036-201040,201040-201044,201044-201049,201049-201101,201101-201105,201105-201110,201110-201114,201114-201118,201118-201123,201123-201127,201127-201131,201131-201136,201136-201140,201140-201144,201144-201149,201149-201201,201201-201205,201205-201210,201210-201214,201214-201218,201218-201223,201223-201227,201227-201231,201231-201236,201236-201240,201240-201244,201244-201249,201249-201301,201301-201305,201305-201310,201310-201314,201314-201318,201318-201323,201323-201327,201327-201331,201331-201336,201336-201340,201340-201344,201344-201349,201349-201401,201401-201405,201405-201410,201410-201414,201414-201418,201418-201423,201423-201427,201427-201431,201431-201436,201436-201440,201440-201444,201444-201449,201449-201501,201501-201505,201505-201510,201510-201514,201514-201518,201518-201523,201523-201527,201527-201531,201531-201536,201536-201540,201540-201544,201544-201549,201549-201601,201601-201605,201605-201610,201610-201614,201614-201618,201618-201623,201623-201627,201627-201631,201631-201636,201636-201640,201640-201644,201644-201649,201649-201701,201701-201705,201705-201710,201710-201714,201714-201718,201718-201723,201723-201727,201727-201731,201731-201736,201736-201740,201740-201744,201744-201749,201749-201801,201801-201805,201805-201810,201810-201814,201814-201818,201818-201823,201823-201827,201827-201831,201831-201836,201836-201840,201840-201844,201844-201849,201849-201901,201901-201905,201905-201910,201910-201914,201914-201918,201918-201923,201923-201927,201927-201931,201931-201936,201936-201940,201940-201944,201944-201949,201949-202001,202001-202005,202005-202010,202010-202014,202014-202018,202018-202023,202023-202027,202027-202031,202031-202036,202036-202040,202040-202044,202044-202049,202049-202101,*'

;

[lbreddemann · Accepted solution]

Hmm... ok, what I think is happening here is that the filter range condition is provided as an numeric value (e.g. Integer as it seems).

However, the partitioning definition works on the NVARCHAR defined SALES_DATE_FISCAL_WEEK column.

This type conversion doesn't work with partitioning.

It's required to specific the conditions that should trigger partition pruning with the exact correct data types.

Can you check if this is correctly done in the information model you are using here?

i build a test with the given DDL and achieved correct partition pruning when the WHERE clause specified the condition with the correct data type.

[Former Member · Accepted solution]

Hello Lars,

I replicated what you did to see what a positive case would look like.  The model is supplying a varchar, but not the right varchar, i.e. partitioning column.  They are using SALES_DATE, a varchar (8), not the varchar (6) of the partitioning SALES_DATE_FISCAL_WEEK.  This will be changed.  By the way, even in the positive case, the output in the trace says, "no filtering according to a range restriction will take place".  What does that mean exactly?

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050242 d partitioning     Pruning.cpp(00052) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773).

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050491 d partitioning     Pruning.cpp(00054) : Partition spec is RANGE SALES_DATE_FISCAL_WEEK 000000-200801,200801-200805,200805-200810,200810-200814,200814-200818,200818-200823,200823-200827,200827-200831,200831-200836,200836-200840,200840-200844,200844-200849,200849-200901,200901-200905,200905-200910,200910-200914,200914-200918,200918-200923,200923-200927,200927-200931,200931-200936,200936-200940,200940-200944,200944-200949,200949-201001,201001-201005,201005-201010,201010-201014,201014-201018,201018-201023,201023-201027,201027-201031,201031-201036,201036-201040,201040-201044,201044-201049,201049-201101,201101-201105,201105-201110,201110-201114,201114-201118,201118-201123,201123-201127,201127-201131,201131-201136,201136-201140,201140-201144,201144-201149,201149-201201,201201-201205,201205-201210,201210-201214,201214-201218,201218-201223,201223-201227,201227-201231,201231-201236,201236-201240,201240-201244,201244-201249,201249-201301,201301-201305,201305-201310,201310-201314,201314-201318,201318-201323,201323-201327,201327-201331,201331-201336,201336-201340,201340-201344,201344-201349,201349-201401,201401-201405,201405-201410,201410-201414,201414-201418,201418-201423,201423-201427,201427-201431,201431-201436,201436-201440,201440-201444,201444-201449,201449-201501,201501-201505,201505-201510,201510-201514,201514-201518,201518-201523,201523-201527,201527-201531,201531-201536,201536-201540,201540-201544,201544-201549,201549-201601,201601-201605,201605-201610,201610-201614,201614-201618,201618-201623,201623-201627,201627-201631,201631-201636,201636-201640,201640-201644,201644-201649,201649-201701,201701-201705,201705-201710,201710-201714,201714-201718,201718-201723,201723-201727,201727-201731,201731-201736,201736-201740,201740-201744,201744-201749,201749-201801,201801-201805,201805-201810,201810-201814,201814-201818,201818-201823,201823-201827,201827-201831,201831-201836,201836-201840,201840-201844,201844-201849,201849-201901,201901-201905,201905-201910,201910-201914,201914-201918,201918-201923,201923-201927,201927-201931,201931-201936,201936-201940,201940-201944,201944-201949,201949-202001,202001-202005,202005-202010,202010-202014,202014-202018,202018-202023,202023-202027,202027-202031,202031-202036,202036-202040,202040-202044,202044-202049,202049-202101,*.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050711 d partitioning     Pruning.cpp(00057) : Pruning entries are ((201414 <= <UNKNOWN> >= 201547)).

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050715 d partitioning     Pruning.cpp(00060) : Pruning entries were created by the SQL Layer.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050724 d partitioning     Pruning.cpp(00069) : No Range Restriction present.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050726 d partitioning     Pruning.cpp(00080) : For the given partition specification, no filtering according to a Range Restriction will take place.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050739 d partitioning     Pruning.cpp(00083) : Pruning was called during SQL Plan Generation

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050750 d partitioning     PruningRuntime.cpp(00047) : Pruning based on pruning entries will be performed.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050803 d partitioning     PruningRuntime.cpp(00066) : Parts based on Pruning Entries: 00000000000000000000000000000000000000000000000000000000000000000000000000001111111111111111111100000000000000000000000000000000000000000000000000000000000001

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050812 d partitioning     PruningRuntime.cpp(00078) : Final set of parts: 00000000000000000000000000000000000000000000000000000000000000000000000000001111111111111111111100000000000000000000000000000000000000000000000000000000000001

[49381]{501658}[133/-1] 2015-11-11 14:15:35.050834 i partitioning     Pruning.cpp(00109) : Pruning for index ZCAR:HT_SALES_TXNLINE (87773): considering part(s) 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 158 only.

[49381]{501658}[133/-1] 2015-11-11 14:15:35.148181 i SQLCompile       query.cc(02913) : (compile) row-slot image result passing: is_applicable=0, isCSTable=1, table_count=

[lbreddemann · Accepted solution]

 "no filtering according to a range restriction will take place".  What does that mean exactly?

This debug trace output refers to a SAP internal feature used with data ageing in ECC on HANA and S/4 HANA.

You can safely ignore this for your use case.

By the way: partitioning by calendar week is likely a mistake. If the data between any two weeks is not tremendously different in the type and volume of entries, then choosing this many partitions likely will give you a much worse compression than necessary. And that of course will lead to higher memory consumption - which I assume was the reason for looking into partitioning in the first place.

Coming back to the original topic: if I got you right, then the filter specification in the model was done on a different column (SALES_DATE). This is something that SAP HANA cannot figure out itself.

If you want to exploit partition pruning but also want to allow users to provide filters based on other time columns you have to perform a mapping to an explicit filter in your model.

[Former Member · Accepted solution]

This is off topic, but it relates to the statement you made above about inefficient compression. 

We will probably move to the SALES_DATE for our partitioning column since this is how the model

is currently designed, and the designers tell me that whatever date related filtering is plugged in,

it should translate to SALES_DATE.  There is no hash partitioning here.  1 level Range only. 

But your comment about compression concerns me. Currently, we had 4/5 fiscal weeks/partition (comprising a month). For 5 years of data, this gives us 12 partitions/year, and 60 partitions in total with actual data, with 50-70 million rows of data/partition.  We had this partitioned out into the future and past so that we could just drop partitions as they roll off. Therefore we also had a number of empty ones as well.  All this spread across 3 nodes. 

With SALES_DATE, if we go with the same month logic, that is 28/35 days/month, 364 days/year with the same number of partitions.

Do you mean that applying the general rule of thumb that the fewer the number of distinct values,

the better the compression, that similar data spread across more partitions means more compression dictionaries and higher memory utilization?  I can understand that.  I suppose in that case, fewer partitions would be better.  It seems difficult to judge the balance between reducing the number of rows touched and degree of parallelism vs. size of structures in memory.  By the SAP recommendation of 250+ million/partition, we could go with quarterly divisions.  But conventional wisdom seems to suggest that scanning fewer rows across more partitions is faster.  We were not necessarily thinking in terms of memory consumptiuon. 

With regard to data distribution, although we sell a large number of core items, part of our business is

seasonal in nature.  Data will be somewhat skewed per month in terms of the number of records, in that this is transactional data and we sell a lot more in the last quarter of the year.  But we can balance the year/months out across the nodes.  Data for some of the columns would be somewhat skewed across partitions for the seasonal part of our business, like sku number, vendor number, etc. I would admit that in terms of raw numbers, I do not know the extent of that skew.  I would think that the bulk of the column(s) would be more evenly distributed.

Your book has helped me understand some of this a bit better as it relates to HANA.

Thanks.

[lbreddemann · Accepted solution]

Glad you liked the book. We really did try hard to relate it to SAP HANA

Your understanding concerning how too many partitions affect compression factors sounds about right. It is indeed the value dictionaries that tend to take roughly the same amount of space then for each partition, while the actual valueID vectors often are RLE or block compressed, which means there is no linear growth of storage requirement compared to stored data.

Other factors that come into play is that during join processing more partitions (with more dictionaries) mean more translation structures are required to match up values across different tables. So not only the static space requirement increases, but also the processing time memory requirement.

And that's also the reason where what you called 'conventional wisdom' goes wrong: the scan operation is not in any way row oriented.

Instead it is implemented to work on whole blocks of data at the same time (SIMD instructions on CPU level) - many times this even works with the compressed data representation.

This makes scanning through 10.000 rows pretty much the same as scanning through 10.000.000 rows - depending on the compression and the total number of distinct values.

Our rule of thumb recommendation works well (that is, it strikes a balance between potential issues and compression) for many cases. I would recommend to start of which this and see how that works for the situation.

Something that might be handy in this regards is the Data Distribution Optimizer from the Data Warehousing Foundation option .