Scribd Logo
Upload

Welcome to Scribd!

  • 43 views

    Proposed Outline For Physical Database Design by Wells Fargo Day 1

    Uploaded by

    Arpan Gupta
    This 4-day course from Wells Fargo teaches physical database design on Teradata systems. It covers topics such as data distribution, hashing, indexes, partitioning, statistics collection, and query optimization. The focus is on table design using the CREATE TABLE statement and transforming logical data models into physical implementations on Teradata. Performance considerations for indexing, joins, aggregations and integrity constraints are also addressed. Hands-on exercises reinforce concepts for choosing primary and secondary indexes, creating partitioned tables, and using explain plans and statistics.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Proposed Outline For Physical Database Design by Wells Fargo Day 1

    Uploaded by

    Arpan Gupta
    43 views4 pages
    This 4-day course from Wells Fargo teaches physical database design on Teradata systems. It covers topics such as data distribution, hashing, indexes, partitioning, statistics collection, and query optimization. The focus is on table design using the CREATE TABLE statement and transforming logical data models into physical implementations on Teradata. Performance considerations for indexing, joins, aggregations and integrity constraints are also addressed. Hands-on exercises reinforce concepts for choosing primary and secondary indexes, creating partitioned tables, and using explain plans and statistics.

    Original Description:

    Teradata

    Original Title

    Course Content

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This 4-day course from Wells Fargo teaches physical database design on Teradata systems. It covers topics such as data distribution, hashing, indexes, partitioning, statistics collection, and query optimization. The focus is on table design using the CREATE TABLE statement and transforming logical data models into physical implementations on Teradata. Performance considerations for indexing, joins, aggregations and integrity constraints are also addressed. Hands-on exercises reinforce concepts for choosing primary and secondary indexes, creating partitioned tables, and using explain plans and statistics.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    43 views4 pages

    Proposed Outline For Physical Database Design by Wells Fargo Day 1

    Uploaded by

    Arpan Gupta
    This 4-day course from Wells Fargo teaches physical database design on Teradata systems. It covers topics such as data distribution, hashing, indexes, partitioning, statistics collection, and query optimization. The focus is on table design using the CREATE TABLE statement and transforming logical data models into physical implementations on Teradata. Performance considerations for indexing, joins, aggregations and integrity constraints are also addressed. Hands-on exercises reinforce concepts for choosing primary and secondary indexes, creating partitioned tables, and using explain plans and statistics.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 4

    Proposed Outline for Physical Database Design by Wells Fargo

    Day 1
    1. Building the Database Environment
    2. Data Distribution, Hashing, and Index Aess
    !. "hoosing a #rimary Index
    Day 2
    1. $eondary Index %sage
    2. $eondary Indexes and Further "onsiderations
    !. &o' and (able $trutures
    Day 3
    1. #artitioned #rimary Indexes
    2. Database Integrity
    !. "olleting $tatistis
    Day 4
    1. )oin #roessing
    2. Database Design "hoies
    !. (able Design *uidelines
    Teradata Physical Database Design
    This course is a Customized Course for Wells Fargo to teach how to defines the processes and
    procedures to follow when designing a database for implementation on a Teradata system. It
    explains how to transform a logical data model into an extended data model. The focus of this
    course is on the C!"T! T"#$! statement% including all aspects of table design. It co&ers Teradata
    data distribution% access% and use of deri&ed data. 'imilarities between (oin and aggregation
    processing% and the implementation of eferential Integrity are also discussed. "lso co&ered in the
    course are the steps to optimize space management. ')$ *ueries
    DAY 1 & 2 :
    +odule ,- Physical Database Design .&er&iew
    Describe the stages of database design.
    $ist and describe the input re*uirements for database design.
    $ist and describe the outputs and ob(ecti&es for database design.
    Describe the differences between a $ogical% !xtended% and Physical Data +odel.
    Identify the difference between a $ogical% !xtended% and Physical Data +odel.
    +odule /- Data Distribution% 0ashing% and Index "ccess
    Describe the data distribution form and method.
    Describe 0ashing.
    Describe Primary Index hash mapping.
    Describe the reconfiguration process.
    Describe a #loc1 $ayout.
    Describe File 'ystem ead "ccess.
    +odule 2- File 'ystem Writes
    Describe File 'ystem Write "ccess.
    Describe what happens when Teradata inserts a new row into a table.
    Describe the impact of row inserts on bloc1 sizes.
    Describe how fragmentation affects performance.
    +odule 3- "nalyze Primary Index Criteria
    Identify Primary Index choice criteria.
    Describe uni*ueness and how it affects space utilization.
    !xplain row access% selection% and selecti&ity.
    Choose between single and multiple4column Primary Indexes.
    +odule 5- Partitioned Primary Indexes
    Describe the components that comprise a ow 6ey.
    $ist two ad&antages of implementing a PPI.
    $ist two potential disad&antages of using a PPI.
    Create tables with partitioned primary indexes.
    7se the P"TITI.8 1ey word to display partition information.
    D"9 2 -
    +odule :- 'econdary Index 7sage
    Describe 7'I and 87'I implementations.
    Describe dual 87'I access.
    Describe 87'I bit mapping.
    !xplain 87'I and "ggregate processing.
    Compare 87'I &s. full table scan ;FT'<.
    +odule =- "nalyze 'econdary Index Criteria
    Choose columns as candidate 'econdary Indexes.
    Describe Composite 'econdary Indexes.
    "nalyze Change ating and >alue "ccess.
    +odule ?- "ccess Considerations and Constraints
    "nalyze .ptimizer "ccess scenarios.
    !xplain partial &alue searches and data con&ersions.
    Identify the effects of conflicting data types.
    Determine the cost of [email protected].
    Identify column le&el attributes and constraints.
    Identify table le&el attributes and constraints.
    "dd% modify and drop constraints from tables.
    !xplain how the Identity column allocates new numbers.
    +odule A- eferential Integrity
    'pecify how is the reference index subtable hash distributedB
    'pecify how a reference index row can be mar1ed as Cin&alidD.
    +odule ,E- 'izing
    Determine column sizing re*uirements based on chosen data type.
    Determine physical table row size.
    Determine table sizing re*uirements &ia estimates and empirical e&idence.
    Determine index sizing re*uirements &ia estimates and empirical e&idence.
    +odule ,,- ')$ Parser
    Describe internal% channel% and $"8 parcels.
    !xplain software cache functionality.
    $ist and identify the function of the main components ;phases< of the parser.
    Describe the functionality of e*uest4To4'teps cache.
    +odule ,/- .ptimizer and Collecting 'tatistics
    !xplain how the .ptimizer ac*uires statistics.
    Describe dynamic sampling.
    'tate the method for &iewing statistics.
    Identify the ways to !FP$"I8 +acros.
    Describe how the Teradata 'tatistics Wizard can be used to collect or re4collect
    statistics.
    +odule ,/./- Compiling 'tatistics
    'tatistics optimizer statistics
    andom amp sampling
    Dynamic sampling
    Collect statistics syntax
    Collecting statistics
    >iewing statistics
    'tatistics and une&en nusis
    7sing the sample feature
    'tatistics and ppi tables
    'tatistics summary
    e&iew *uestions
    $ab
    +odule ,2- The !FP$"I8 Facility
    Describe the !xplain Facility.
    Define !xplain terminology.
    Describe the !FP$"I8 output of a C!"T! T"#$! statement.
    +atch new !FP$"I8 terms ;with PPI< to a definition.
    7se !FP$"I8 to determine the number of partitions used in &arious access plans.
    DAY 4
    +odule ,5- Goin Processing "nalysis
    Identify and describe different 1inds of (oin plans.
    Identify (oin plan distribution strategies.
    Describe different types of (oin strategies that may be used with PPI tables.
    +odule ,:- !xplains of Goins and Index Choices
    Interpret the !FP$"I8' of 8ested% +erge% and Product Goins.
    De&elop 7nion solutions.
    !xplain the importance of Goin "ccesses in Index selection.
    7se the Index choice guidelines.
    7se the Teradata Index Wizard to analyze secondary index selections
    +odule ,=- "dditional Index Choices
    $ist the three types of Goin Indexes.
    Describe the situations where a Goin Index can impro&e performance.
    Describe how to create a Csparse indexD.
    Describe the difference between a single4table Goin Index and a 0ash Index.

    You might also like