Oracle Optimization - Table Partitions

Oracle optimization: create table partitions and Index-Organized Table. Other lessons, visit http://www.learn-with-video-tutorials...

---

Closed Caption:

lesson 3 partitioning and creating an
index organized table part 1 creating an
index organized table
an index organized table keeps its data
sorted according to the primary key
column values for the table and index
organized table stores its data as if
the entire table was stored in an index
index is served two main purposes to
enforce uniqueness when a primary key or
unique constraint is created
oracle creates an index to enforce the
uniqueness of the indexed columns to
improve performance when a query can use
an index the performance of the query
may dramatically improve see chapter 46
for details on the conditions under
which a query may use an index and index
organized table allows you to store its
entire data in an index a normal index
only stores the indexed columns and
index organized table stores all its
columns in the index
yeah
yeah
yeah
to create an index organized table use
the organization index Clause of the
create table command as shown in the
following example
yeah
yeah
in general and index organized table is
most effective when the primary key
constitutes a large part of the tables
columns
part2 creating a partition table
dividing a Tables data in this manner is
called partitioning the table the table
that is partitioned is called a
partition table and the parts are called
partitions partitioning is useful for
very large tables by splitting a large
table rows across multiple smaller
partitions you accomplish several
important goals the performance of
queries against the tables may improve
because article may have to search only
one partition one part of the table
instead of the entire table to resolve a
query the table may be easier to manage
because the partition tables data is
stored in multiple parts it may be easy
to load and delete data in the
partitions than in the large table
backup and recovery operations may
perform better because the partitions
are smaller than the partition table
you may have more options for backing up
and recovering the partitions than you
would have for a single large table
to create a partition table you specify
how to setup the partitions of the
tables day to as part of the create
table command typically tables are
partitioned by ranges of values known as
range partitioning
to partition the tables records use the
partition by range Clause of the create
table command the ranges will determine
the values stored in each partition the
TB transactions part table will be
partitioned based on the values in the
year column for any years less than 2012
the records will be stored in the
partition named part 1 the part one
partition will be stored in the system
tablespace any other years will be
stored in the part to partition you do
not need to specify a maximum value for
the last partition the max value keyword
tells oracle to use the partition to
store any data that could not be stored
in the earlier partitions
yeah
yeah
ok
part3 creating a hash partition
in addition to range partitions oracle
supports hash partitions are hash
partition determines the physical
placement of data by performing a hash
function on the values of the partition
key in range partitioning consecutive
values of the partition key i usually
stored in the same partition in hash
partitioning consecutive values of the
partition kiana generally stored in the
same partition hash partitioning
distributes a set of records over a
greater set of partitions than range
partitioning does potentially decreasing
the likelihood for i/o contention to
create a hash partition use the
partition by hash clause in place of the
partition by range Claus the number of
table space is specified in the store in
clause does not have to equal the number
of partitions if more partitions that
table spaces are specified the
partitions are assigned to the table
spaces in a round-robin fashion
you can specify named partitions in this
method each partition is given a name
and a tablespace with the option of
using an additional lab or very storage
Claus this method gives you more control
over the location of the partitions with
the added benefit of letting you specify
meaningful names for the partitions
yeah
part4 list partitioning
yeah
yeah
yeah
yeah
you can use lists partitions in place of
range and hash partitioning enlist
partitioning you tell oracle all the
possible values and designate the
partitions into which the corresponding
rows should be inserted
yeah
part5 creating some partitions
you can create some partitions that is
partitions of partitions you can use sub
partitions to combine all types of
partitions range partitions list
partitions and hash partitions for
example you can use hash partitions in
combination with range partitions
creating hash partitions of the range
partitions for very large tables this
composite partitioning may be an
effective way of separating the data
into manageable and tunable divisions
the following example range partitions
the TB transactions part table by the
year column and it has partitions the
year partitions by-product version
values the TB transactions part table
will be range partitioned into two
partitions using the year value ranges
specified for the named partitions each
of those partitions will be hash
partitioned on the product version
column
part6 indexing partitions
when you create a partition table you
should create an index on the table the
index may be partitioned according to
the same range values used to partition
the table in this create index command
no ranges are specified instead the
local keyword tells oracle to create a
separate index for each partition of the
TB transactions part table 2 partitions
were created on TB transactions part
this index will create two separate
index partitions one for each table
partition because there is one index /
partition the index partitions are local
to the partitions the global clause in
this create index command allows you to
create a non partitioned index or to
specify ranges for the index values that
are different from the ranges for the
table partitions local indexes may be
easy to manage than global indexes
however global indexes may perform
uniqueness checks faster than local
partitioned indexes perform them
you cannot create global indexes for
hash partitions or sub partitions do you
want to learn new skills in the fastest
and most effective way visits learn with
video tutorials come