"From" clauses in Create database query.

Database

"From" clauses in Create database query.

 

Hi I'm biginner to teradata.

 

I want to ask u that 

while we creating a new database in teradata why we use "from" clauses in that query.

The query is

CREATE DATABASE personnel

FROM administration

AS PERMANENT = 5000000 BYTES,

FALLBACK,

BEFORE JOURNAL,

DUAL AFTER JOURNAL,

DEFAULT JOURNAL TABLE = personnel.fin_cop;

here we use "from" administration which is a another database.

why we use 'from' here

thanks

3 REPLIES
Senior Apprentice

Re: "From" clauses in Create database query.

In Teradata there's a hierarchy of databases/users, "dbc" being the root.

When FROM is not specified it defaults to the logged in user.

As most customers use a single admin user (e.g. "sysdba") for creation of all databases/users, FROM is needed to create/maintain that hierarchy.

Dieter

Enthusiast

Re: "From" clauses in Create database query.

Hi dnoeth,

i have a query like as follows

SELECT c.unit_num

,c.trans_end_dt

,c.Coupon_cd AS Coupon_cd

,COUNT(DISTINCT Loyalty_Member_Id ) AS "Customer count"

,SUM(Net_Sales_Amt) AS Sales

,COUNT(Wgt_Item_Cnt) AS Units

,COUNT (DISTINCT c.rtl_trans_id) AS Transactions

,SUM(Discount_Amt) Disc_Amt

,SUM(coupon_qty) AS "Coupon quantity"

 FROM

           (SELECT

                a.rtl_trans_id  AS rtl_trans_id     

               ,a.Trans_End_Dt AS Trans_End_Dt    

               ,a. Mfr_Cpn_Scan_Cd AS Coupon_cd

               ,b.unit_num

               ,SUM(a.Mfr_Cpn_Amt ) AS Discount_Amt

               ,COUNT(DISTINCT rtl_trans_id) AS coupon_qty

              FROM     Merch_AV.RtlSaleMfrCpn a

                                                                    JOIN merch_av.FlatOrgHierarchyAsIs b

              ON a.org_id =b.store_id

                          AND Mfr_Cpn_Scan_Cd = 110100360000   

                          AND trans_end_dt BETWEEN 1120717 AND 1120811

                          AND trans_type_cd = 0

              WHERE   org_level_03_num=84 AND org_hierarchy_id=203     /*  farm fresh          */                          

                  GROUP  BY 1,2,3,4)c

        INNER JOIN merch_av.Rtlsale d

        ON c.Rtl_Trans_Id = d.Rtl_Trans_Id

        AND c.Trans_End_Dt = d.Trans_End_Dt

        AND trans_type_cd=0

    GROUP BY 1,2,3

    ORDER BY 1;

and explain plan as follows for this is as follows

/---------------------------

Explain SELECT c.unit_num

,c.trans_end_dt

,c.Coupon_cd AS Coupon_cd

,COUNT(DISTINCT Loyalty_Member_Id ) AS "Customer count"

,SUM(Net_Sales_Amt) AS Sales

,COUNT(Wgt_Item_Cnt) AS Units

,COUNT (DISTINCT c.rtl_trans_id) AS Transactions

,SUM(Discount_Amt) Disc_Amt

,SUM(coupon_qty) AS "Coupon quantity"

 FROM

           (SELECT

                a.rtl_trans_id  AS rtl_trans_id     

               ,a.Trans_End_Dt AS Trans_End_Dt    

               ,a. Mfr_Cpn_Scan_Cd AS Coupon_cd

               ,b.unit_num

               ,SUM(a.Mfr_Cpn_Amt ) AS Discount_Amt

               ,COUNT(DISTINCT rtl_trans_id) AS coupon_qty

              FROM     Merch_AV.RtlSaleMfrCpn a

                                                                    JOIN merch_av.FlatOrgHierarchyAsIs b

              ON a.org_id =b.store_id

                          AND Mfr_Cpn_Scan_Cd = 110100360000   

                          AND trans_end_dt BETWEEN 1120717 AND 1120811

                          AND trans_type_cd = 0

              WHERE   org_level_03_num=84 AND org_hierarchy_id=203     /*  farm fresh          */                          

                  GROUP  BY 1,2,3,4)c

        INNER JOIN merch_av.Rtlsale d

        ON c.Rtl_Trans_Id = d.Rtl_Trans_Id

        AND c.Trans_End_Dt = d.Trans_End_Dt

        AND trans_type_cd=0

    GROUP BY 1,2,3

    ORDER BY 1;

  1) First, we lock SVUEDW_T.RtlSaleMfrCpn for access, we lock

     SVUEDW_T.RtlSale in view merch_av.Rtlsale for access, and we lock

     SVUEDW_A.FlatOrgHierarchyAsIs for access.

  2) Next, we do an all-AMPs JOIN step from

     SVUEDW_A.FlatOrgHierarchyAsIs by way of a RowHash match scan with

     a condition of ("(SVUEDW_A.FlatOrgHierarchyAsIs.Org_Level_03_Num =

     84) AND ((SVUEDW_A.FlatOrgHierarchyAsIs.Org_Hierarchy_Id = 203)

     AND ((NOT (SVUEDW_A.FlatOrgHierarchyAsIs.Org_Level_03_Desc LIKE

     '%SAVE-A-LOT%')) AND (NOT

     (SVUEDW_A.FlatOrgHierarchyAsIs.Org_Level_03_Desc LIKE '%SAVE A

     LOT%'))))"), which is joined to SVUEDW_A.FlatOrgHierarchyAsIs by

     way of a RowHash match scan with a condition of (

     "(SVUEDW_A.FlatOrgHierarchyAsIs.Org_Hierarchy_Id = 203) AND ((NOT

     (SVUEDW_A.FlatOrgHierarchyAsIs.Org_Level_03_Desc LIKE

     '%SAVE-A-LOT%')) AND (NOT

     (SVUEDW_A.FlatOrgHierarchyAsIs.Org_Level_03_Desc LIKE '%SAVE A

     LOT%')))") locking SVUEDW_A.FlatOrgHierarchyAsIs for access.

     SVUEDW_A.FlatOrgHierarchyAsIs and SVUEDW_A.FlatOrgHierarchyAsIs

     are joined using a merge join, with a join condition of (

     "(SVUEDW_A.FlatOrgHierarchyAsIs.Org_Hierarchy_Id =

     SVUEDW_A.FlatOrgHierarchyAsIs.Org_Hierarchy_Id) AND

     (SVUEDW_A.FlatOrgHierarchyAsIs.Store_Id =

     SVUEDW_A.FlatOrgHierarchyAsIs.Store_Id)").  The result goes into

     Spool 4 (all_amps) (compressed columns allowed), which is

     duplicated on all AMPs.  The size of Spool 4 is estimated with low

     confidence to be 34,125 rows (989,625 bytes).  The estimated time

     for this step is 0.02 seconds.

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

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

     SVUEDW_T.RtlSaleMfrCpn with a condition of (

     "(SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt >= DATE '2012-07-17') AND

     ((SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt <= DATE '2012-08-11') AND

     ((SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd = 110100360000.

    ) AND (SVUEDW_T.RtlSaleMfrCpn.Trans_Type_Cd = 0 )))").  Spool 4 and

     SVUEDW_T.RtlSaleMfrCpn are joined using a dynamic hash join, with

     a join condition of ("(SVUEDW_T.RtlSaleMfrCpn.Org_Id = Store_Id)

     AND (SVUEDW_T.RtlSaleMfrCpn.Org_Id = Store_Id)").  The input table

     SVUEDW_T.RtlSaleMfrCpn will not be cached in memory.  The result

     goes into Spool 3 (all_amps) (compressed columns allowed), which

     is built locally on the AMPs.  The size of Spool 3 is estimated

     with low confidence to be 16,240 rows (893,200 bytes).  The

     estimated time for this step is 0.09 seconds.

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

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

     SVUEDW_T.RtlSaleMfrCpn.Rtl_Trans_Id

     ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

     ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd

     ,SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num

     ,SVUEDW_T.RtlSaleMfrCpn.Rtl_Trans_Id).  Aggregate Intermediate

     Results are computed locally, then placed in Spool 6.  The size of

     Spool 6 is estimated with low confidence to be 16,240 rows (

     1,770,160 bytes).  The estimated time for this step is 0.01

     seconds.

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

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

     SVUEDW_T.RtlSaleMfrCpn.Rtl_Trans_Id

     ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

     ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd

     ,SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 8.  The size

     of Spool 8 is estimated with low confidence to be 16,240 rows (

     1,705,200 bytes).  The estimated time for this step is 0.02

     seconds.

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

     an all-rows scan into Spool 1 (used to materialize view, derived

     table or table function c) (all_amps) (compressed columns allowed),

     which is built locally on the AMPs.  The size of Spool 1 is

     estimated with low confidence to be 16,240 rows (1,250,480 bytes).

     The estimated time for this step is 0.01 seconds.

  7) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

     an all-rows scan with a condition of ("NOT (c.RTL_TRANS_ID IS NULL)")

     into Spool 13 (all_amps) (compressed columns allowed), which is

     redistributed by the rowkey of (

     SVUEDW_T.RtlSaleMfrCpn.Rtl_Trans_Id,

     SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt) to all AMPs.  Then we do a

     SORT to partition Spool 13 by rowkey.  The size of Spool 13 is

     estimated with low confidence to be 16,240 rows (1,120,560 bytes).

     The estimated time for this step is 0.01 seconds.

  8) We do an all-AMPs JOIN step from SVUEDW_T.RtlSale in view

     merch_av.Rtlsale by way of a RowHash match scan with a condition

     of ("SVUEDW_T.RtlSale in view merch_av.Rtlsale.Trans_Type_Cd = 0"),

     which is joined to Spool 13 (Last Use) by way of a RowHash match

     scan.  SVUEDW_T.RtlSale and Spool 13 are joined using a

     rowkey-based merge join, with a join condition of ("(TRANS_END_DT

     = SVUEDW_T.RtlSale.Trans_End_Dt) AND (RTL_TRANS_ID =

     SVUEDW_T.RtlSale.Rtl_Trans_Id)").  The input table

     SVUEDW_T.RtlSale will not be cached in memory, but it is eligible

     for synchronized scanning.  The result goes into Spool 14

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (SVUEDW_T.RtlSale.Org_Id, 203) to all AMPs.  Then

     we do a SORT to order Spool 14 by row hash.  The size of Spool 14

     is estimated with low confidence to be 16,081 rows (1,350,804

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

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

     RowHash match scan, which is joined to

     SVUEDW_A.FlatOrgHierarchyAsIs in view merch_av.Rtlsale by way of a

     RowHash match scan with a condition of (

     "(SVUEDW_A.FlatOrgHierarchyAsIs in view

     merch_av.Rtlsale.Org_Hierarchy_Id = 203) AND ((NOT

     (SVUEDW_A.FlatOrgHierarchyAsIs in view

     merch_av.Rtlsale.Org_Level_03_Desc LIKE '%SAVE-A-LOT%')) AND (NOT

     (SVUEDW_A.FlatOrgHierarchyAsIs in view

     merch_av.Rtlsale.Org_Level_03_Desc LIKE '%SAVE A LOT%')))")

     locking SVUEDW_A.FlatOrgHierarchyAsIs for access.  Spool 14 and

     SVUEDW_A.FlatOrgHierarchyAsIs are joined using a merge join, with

     a join condition of ("Org_Id =

     SVUEDW_A.FlatOrgHierarchyAsIs.Store_Id").  The result goes into

     Spool 12 (all_amps) (compressed columns allowed), which is built

     locally on the AMPs.  The size of Spool 12 is estimated with low

     confidence to be 15,370 rows (1,244,970 bytes).  The estimated

     time for this step is 0.01 seconds.

 10) We do an all-AMPs SUM step to aggregate from Spool 12 by way of an

     all-rows scan , grouping by field1 (

     SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num

     ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

     ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd

     ,SVUEDW_T.RtlSaleMfrCpn.Rtl_Trans_Id).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 19.  The size

     of Spool 19 is estimated with low confidence to be 15,370 rows (

     1,767,550 bytes).  The estimated time for this step is 0.02

     seconds.

 11) We do an all-AMPs SUM step to aggregate from Spool 19 (Last Use)

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

     SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num

     ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

     ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 21.  The size

     of Spool 21 is estimated with low confidence to be 2,157 rows (

     243,741 bytes).  The estimated time for this step is 0.02 seconds.

 12) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from Spool 21 (Last Use) by

         way of an all-rows scan into Spool 16 (all_amps) (compressed

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

         size of Spool 16 is estimated with low confidence to be 2,157

         rows (183,345 bytes).  The estimated time for this step is

         0.01 seconds.

      2) We do an all-AMPs SUM step to aggregate from Spool 12 (Last

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

         SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num

         ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

         ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd

         ,SVUEDW_T.RtlSale.Loyalty_Member_Id).  Aggregate Intermediate

         Results are computed globally, then placed in Spool 25.  The

         size of Spool 25 is estimated with low confidence to be 15,370

         rows (1,060,530 bytes).  The estimated time for this step is

         0.02 seconds.

 13) We do an all-AMPs SUM step to aggregate from Spool 25 (Last Use)

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

     SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num

     ,SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt

     ,SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd).  Aggregate Intermediate

     Results are computed globally, then placed in Spool 27.  The size

     of Spool 27 is estimated with low confidence to be 2,157 rows (

     131,577 bytes).  The estimated time for this step is 0.02 seconds.

 14) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from Spool 27 (Last Use) by

         way of an all-rows scan into Spool 15 (all_amps) (compressed

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

         size of Spool 15 is estimated with low confidence to be 2,157

         rows (97,065 bytes).  The estimated time for this step is 0.01

         seconds.

      2) We do an all-AMPs RETRIEVE step from Spool 16 (Last Use) by

         way of an all-rows scan into Spool 29 (all_amps) (compressed

         columns allowed), which is redistributed by the hash code of (

         SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num,

         SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt,

         SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd) to all AMPs.  Then we

         do a SORT to order Spool 29 by row hash.  The size of Spool 29

         is estimated with low confidence to be 2,157 rows (166,089

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

 15) We do an all-AMPs RETRIEVE step from Spool 15 (Last Use) by way of

     an all-rows scan into Spool 30 (all_amps) (compressed columns

     allowed), which is redistributed by the hash code of (

     SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num,

     SVUEDW_T.RtlSaleMfrCpn.Trans_End_Dt,

     SVUEDW_T.RtlSaleMfrCpn.Mfr_Cpn_Scan_Cd) to all AMPs.  Then we do a

     SORT to order Spool 30 by row hash.  The size of Spool 30 is

     estimated with low confidence to be 2,157 rows (79,809 bytes).

     The estimated time for this step is 0.01 seconds.

 16) We do an all-AMPs JOIN step from Spool 29 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 30 (Last Use) by way

     of a RowHash match scan.  Spool 29 and Spool 30 are joined using a

     merge join, with a join condition of ("(COUPON_CD = COUPON_CD) AND

     ((TRANS_END_DT = TRANS_END_DT) AND (UNIT_NUM = UNIT_NUM ))").  The

     result goes into Spool 10 (group_amps), which is built locally on

     the AMPs.  Then we do a SORT to order Spool 10 by the sort key in

     spool field1 (SVUEDW_A.FlatOrgHierarchyAsIs.Unit_Num).  The size

     of Spool 10 is estimated with low confidence to be 1,967 rows (

     175,063 bytes).  The estimated time for this step is 0.01 seconds.

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

     in processing the request.

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

     of statement 1.  The total estimated time is 0.47 seconds.

-----------/

in this plan in step2 it is get duplicated in all amps i want to know why it is happening like this and how to convert this from low confidence to high confidence?

Thanks in advance

Ram

Re: "From" clauses in Create database query.

Hi denoth

Thanks for your response.