trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Owhadi <eric.owh...@esgyn.com>
Subject RE: NUMERIC? NUMERIC!
Date Tue, 29 Mar 2016 17:07:00 GMT
Hi Pierre,
This is off topic, but looking at the ddl you provided, I see that you are
creating indexes that may not be needed.
Given that your primary key is :
PRIMARY KEY (WORK_EFFORT_ID ASC, PRODUCT_ID ASC,

       WORK_EFFORT_GOOD_STD_TYPE_ID ASC, FROM_DATE ASC)

     )

The data on the main table will be sorted by WORK_EFFORT_ID ASC then
PRODUCT_ID ASC then

       WORK_EFFORT_GOOD_STD_TYPE_ID ASC then FROM_DATE ASC

Trafodion has a feature called MDAM (Multi Dimentional Access Method), that
will be able to leverage this sort order on main table to efficiently
retrieve the data you need, even when you are missing predicates on the left
most key columns.

This is a very cool feature that greatly limit your needs for indexes and
associated overhead.

In order to maximize benefit from MDAM, you want to order the key columns so
that the lower cardinality column that could be missing in predicates shows
up at the left most part of the key.

So given the current schema, you should only need index on status_ID.
(disclaimer, this depends on cardinality of the key columns...).
Hope this helps,
Eric


-----Original Message-----
From: Pierre Smits [mailto:pierre.smits@gmail.com]
Sent: Tuesday, March 29, 2016 11:29 AM
To: dev@trafodion.incubator.apache.org
Subject: Re: NUMERIC? NUMERIC!

Hi Dave,

Thanks for the tip regarding showddl.

So in the earlier mentioned ESTIMATED_COST example, these are the findings:


   - The defintion in OFBiz in the table definition for
   WorkEffortGoodStandard for the field is

   <field name="estimatedCost" type="currency-amount"></field>
   -

   The data type definition for 'currency-amount' is

   <field-type-def type="currency-amount" sql-type="NUMERIC(18,2)" java-type
   ="java.math.BigDecimal"/>
   -

   The example in my earlier posting is an excerpt of the OFBiz log
   -

   showddl table OFBIZ.WORK_EFFORT_GOOD_STANDARD returns

   >>showddl table OFBIZ.WORK_EFFORT_GOOD_STANDARD;


   CREATE TABLE TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD

     (

       WORK_EFFORT_ID                   VARCHAR(20) CHARACTER SET ISO88591
   COLLATE

         DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED

     , PRODUCT_ID                       VARCHAR(20) CHARACTER SET ISO88591
   COLLATE

         DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED

     , WORK_EFFORT_GOOD_STD_TYPE_ID     VARCHAR(20) CHARACTER SET ISO88591
   COLLATE

         DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED

     , FROM_DATE                        TIMESTAMP(6) NO DEFAULT NOT NULL NOT

         DROPPABLE NOT SERIALIZED

     , THRU_DATE                        TIMESTAMP(6) DEFAULT NULL NOT
   SERIALIZED

     , STATUS_ID                        VARCHAR(20) CHARACTER SET ISO88591
   COLLATE

         DEFAULT DEFAULT NULL SERIALIZED

     , ESTIMATED_QUANTITY               DOUBLE PRECISION DEFAULT NULL NOT

         SERIALIZED

     , ESTIMATED_COST                   NUMERIC(18, 2) DEFAULT NULL
   SERIALIZED

     , LAST_UPDATED_STAMP               TIMESTAMP(6) DEFAULT NULL NOT
   SERIALIZED

     , LAST_UPDATED_TX_STAMP            TIMESTAMP(6) DEFAULT NULL NOT
   SERIALIZED

     , CREATED_STAMP                    TIMESTAMP(6) DEFAULT NULL NOT
   SERIALIZED

     , CREATED_TX_STAMP                 TIMESTAMP(6) DEFAULT NULL NOT
   SERIALIZED

     , PRIMARY KEY (WORK_EFFORT_ID ASC, PRODUCT_ID ASC,

       WORK_EFFORT_GOOD_STD_TYPE_ID ASC, FROM_DATE ASC)

     )

   ;


   CREATE INDEX WKEFF_GDSTD_PROD ON
   TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD

     (

       PRODUCT_ID ASC

     )

   ;


   CREATE INDEX WKEFF_GDSTD_STTS ON
   TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD

     (

       STATUS_ID ASC

     )

   ;


   CREATE INDEX WKEFF_GDSTD_TYPE ON
   TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD

     (

       WORK_EFFORT_GOOD_STD_TYPE_ID ASC

     )

   ;


   CREATE INDEX WKEFF_GDSTD_WEFF ON
   TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD

     (

       WORK_EFFORT_ID ASC

     )

   ;

It seems to me that everything is ok underneath, but that the return message
is misinterpreted by the OFBiz functions.

Best regards,


Pierre Smits

ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services

OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/

On Tue, Mar 29, 2016 at 5:59 PM, Dave Birdsall <dave.birdsall@esgyn.com>
wrote:

> Hi Pierre,
>
> Can you say more?
>
> Trafodion does support NUMERIC(18,6) as a distinct data type, for example:
>
> >>create table t1 (a int not null, b largeint not null, c
> >>numeric(18,6) not null
> +> , primary key (a));
>
> --- SQL operation complete.
> >>insert into t1 values (1,2,3.8);
>
> --- 1 row(s) inserted.
> >>insert into t1 values (4,5,6.712345);
>
> --- 1 row(s) inserted.
> >>insert into t1 values (7,8,9.000000001);
>
> --- 1 row(s) inserted.
> >>select * From t1;
>
> A            B                     C
> -----------  --------------------  ---------------------
>
>           1                     2               3.800000
>           4                     5               6.712345
>           7                     8               9.000000
>
> --- 3 row(s) selected.
> >>
>
> It might be useful to do a "showddl" on the underlying Trafodion table
> to see what the column data types are. There might, for example, be a
> problem in DDL generation so the wrong data type gets created. Or
> perhaps the DDL is correct but something happens on the way back out,
> say for a "describe" of a statement.
>
> Dave
>
> -----Original Message-----
> From: Pierre Smits [mailto:pierre.smits@gmail.com]
> Sent: Tuesday, March 29, 2016 5:11 AM
> To: dev@trafodion.incubator.apache.org
> Subject: NUMERIC? NUMERIC!
>
> Hi all,
>
> While trying to marry Apache OFBiz with Trafodion, I found this oddity:
>
> In OFBiz we type some fields as NUMERIC, e.g:
>
>     <field-type-def type="fixed-point" sql-type="NUMERIC(18,6)"
> java-type= "java.math.BigDecimal"/>
>
> However, when looking at the feedback we get from Trafodion after a
> restart we get:
>
> Column [ESTIMATED_COST] of table [OFBIZ.WORK_EFFORT_GOOD_STANDARD] of
> entity [WorkEffortGoodStandard] is of type [BIGINT] in the database,
> but is defined as type [NUMERIC] in the entity definition.
>
> Is something wrong inside Trafodion with respect to mapping field types?
>
> Best regards,
>
> Pierre Smits
>
> ORRTIZ.COM <http://www.orrtiz.com>
> OFBiz based solutions & services
>
> OFBiz Extensions Marketplace
> http://oem.ofbizci.net/oci-2/
>

Mime
View raw message