Topic
  • 3 replies
  • Latest Post - ‏2013-10-10T13:34:06Z by krmilligan
Ajay Kulkarni
Ajay Kulkarni
23 Posts

Pinned topic SYSCOLUMNSTAT to find out CARDINALITY?

‏2013-10-07T15:33:10Z |

 

Hello ,  I was analysing SYSCOLUMNSTAT to find out CARDINALITY of a column., but i was not able to find the column for the table i was investigating (DDS based PF). What forces a column in SYSCOLUMNSTAT?

Regards

AJAY

Updated on 2013-10-07T15:33:29Z at 2013-10-07T15:33:29Z by Ajay Kulkarni
  • krmilligan
    krmilligan
    450 Posts

    Re: SYSCOLUMNSTAT to find out CARDINALITY?

    ‏2013-10-08T14:24:10Z  

    Column Stats are only collected for a column when an SQL statement is processed by SQE and the query optimizer didn't have an index available to provide statistics.

  • Ajay Kulkarni
    Ajay Kulkarni
    23 Posts

    Re: SYSCOLUMNSTAT to find out CARDINALITY?

    ‏2013-10-09T19:22:53Z  

    Column Stats are only collected for a column when an SQL statement is processed by SQE and the query optimizer didn't have an index available to provide statistics.

     

    Kent, I am not sure that i understand.

    Suppose say we have a table F4211. it has around 240+ columns. But when i check the SYSCOLUMN stat i see fewer fields. I am sure that queries go via SQE (atleast for queries i check in SQL plan cache).

    Columns which are not part of the stat catalogue are part of some index? OR Columns which are not in stat catalogue were part of query which went via CQE?

    Thanks

    AJAY

     

  • krmilligan
    krmilligan
    450 Posts

    Re: SYSCOLUMNSTAT to find out CARDINALITY?

    ‏2013-10-10T13:34:06Z  

     

    Kent, I am not sure that i understand.

    Suppose say we have a table F4211. it has around 240+ columns. But when i check the SYSCOLUMN stat i see fewer fields. I am sure that queries go via SQE (atleast for queries i check in SQL plan cache).

    Columns which are not part of the stat catalogue are part of some index? OR Columns which are not in stat catalogue were part of query which went via CQE?

    Thanks

    AJAY

     

    That sounds normal.  Let's assume COLX is in F4211. 

    COLX is only likely to show up in SYSCOLUMNSTAT if COLX is not the leading field of an index key and COLX was referenced in a selection predicate (COLX>=1), join predicate, grouping clause, or ordering clause.