oracle database 12c new features for...

© Oren Nakdimon

1

Seminar:

Oracle Database Objects Internals

Presenter:

Oren Nakdimon www.db-oriented.com

[email protected]

054-4393763

@DBoriented

© Oren Nakdimon

© Oren Nakdimon

2

Who Am I? • Chronology by “Oracle years”

• Today: – Freelance database expert

• Database Administration • Development • Courses

When What Where

Oracle 6/7 1991-1997 Developer IAF

Oracle 8 1997-1998 Server group manager Golden Screens

Oracle 8i/9i 1998-2003 DBA group manager TELEknowledge

Oracle 10g/11g

2004-2011 VP R&D and Israel Site Manager

Olista

“An expert is a person

who has made all the

mistakes that can be

made in a very narrow

field” Niels Bohr (1885-1962)

© Oren Nakdimon

3

Agenda

• Database objects and segments

• The storage hierarchy

• Most common segment types

• Other types of tables

• Other types of indexes

• Partitioning

• Advanced features

© Oren Nakdimon

4

Segments The Building Blocks

© Oren Nakdimon

5

Segments • Segments are objects that consume storage • Segment types

– Table – Table partition – Index – Undo – Temporary – And more…

• A segment may be created as soon as the corresponding object is created (IMMEDIATE) or when it gets data for the first time (DEFERRED, new in version 11.2)

@seg

© Oren Nakdimon

6

Oracle Storage Hierarchy The Building Blocks

© Oren Nakdimon

7

Storage Hierarchy

• A database contains tablespaces

• Tablespace

– A logical container of segments

– Composed of one or more data files

© Oren Nakdimon

8

Storage Hierarchy • A segment is composed of one or more extents • Extent

– A logically contiguous space in a data file – Composed of blocks

• Block – The smallest unit of space allocation in Oracle – Block size is usually 2KB, 4KB, 8KB, 16KB or 32KB – All the blocks within a specific tablespace have the

same size

© Oren Nakdimon

9

Storage Hierarchy

© Oren Nakdimon

10

Block Structure • Block overhead

– Header • Block type

• Transaction information

• Block address

– Table directory

– Row directory

• Data

• Free space

© Oren Nakdimon

11

Heap Organized Tables Most Common Segment Types

© Oren Nakdimon

12

Tables

• A table may contain up to 1000 columns and virtually infinite number of rows

• Data (rows) are stored in blocks

• Controlling block usage

– PCTFREE

– PCTUSED

– FREELISTS

– INITRANS @ssm @pct @fl @itl

© Oren Nakdimon

13

Tables

• There are 9 major types of tables

• The most common type is Heap Organized Table

© Oren Nakdimon

14

Heap Organized Tables

• The default table type

• The location of rows is “random”

• Data is returned in “random” order

• Good for Insert/Update/Delete operations

• What about data retrieval?

@heap

© Oren Nakdimon

15

B*Tree Indexes Most Common Segment Types

© Oren Nakdimon

16

Indexes

• Supportive objects aimed to quickly locate the data

• There are different types of indexes

• The most common type is B*Tree

© Oren Nakdimon

17

B*Tree Indexes

© Oren Nakdimon

18

B*Tree Indexes

• 2 kinds of blocks: Branch and Leaf

• Always balanced

• The leaf blocks are doubly linked list

• Controlling block usage

– PCTFREE (relevant only to index creation)

– FREELISTS

– COMPRESS

@ind

© Oren Nakdimon

19

Let’s See Some Use Cases Other Types of Tables

© Oren Nakdimon

20

Use Case 1a

• PROJECT_EMPLOYEES

– Classic many-to-many relationship

– Usually retrieving all the employees of a specific project

@uc1a1

© Oren Nakdimon

21

Index Organized Tables

• IOT is a table stored in an index structure

• Data is sorted by the Primary Key

@uc1a2

© Oren Nakdimon

22

Use Case 1b


– With additional columns

© Oren Nakdimon

23


• Some (or all) of the non-primary-key columns may be located in a different segment

– OVERFLOW

• PCTTHRESHOLD

• INCLUDING

@uc1b

© Oren Nakdimon

24

Use Case 1c


– Querying from both sides

© Oren Nakdimon

25


• Secondary indexes may be defined

– Using logical rowid

@uc1c

© Oren Nakdimon

26

Use Case 2

• PROJ_PROGRESS

– Rows for the same project are added over time

– All rows for the same project are retrieved together

@uc2

© Oren Nakdimon

27

Index Organized Tables • IOT is advantageous when

– All the columns are in the primary key

– The table is always accessed by (the leading part of) the primary key

– Co-location of data that arrive randomly is needed

• Disadvantages – Maintaining a B*Tree is more expensive than

maintaining a Heap

– Secondary indexes are somewhat less efficient than on Heap Organized Tables

© Oren Nakdimon

28

Use Case 3

• DEPT and EMP

– Department and its employees are usually retrieved together

© Oren Nakdimon

29

Cluster Segments

• A cluster stores related data, from one or more tables, in the same block

© Oren Nakdimon

30

Cluster Segments

• The storage parameters are defined in the cluster level, not the tables’ level

• SIZE defines the expected size of each cluster key value

• Blocks are chained if the data of a single cluster key value requires more than one block

• Note: in a cluster, rows of different tables may have the same ROWID

© Oren Nakdimon

31

Cluster Segments

• Limitations

– No TRUNCATE TABLE

– No partitioning

• There are 3 types of clustered tables

– Index Clustered Tables

– Hash Clustered Tables

– Sorted Hash Clustered Tables

© Oren Nakdimon

32

Index Clustered Tables

• The cluster key is indexed by a B*Tree (it’s like the cluster’s primary key)

• The creation order is:

– CREATE CLUSTER with key column(s)

– CREATE INDEX ON CLUSTER

– CREATE TABLE in the cluster

– Optionally, CREATE additional TABLEs in the cluster

@uc3

© Oren Nakdimon

33

Index Clustered Tables

• Advantageous when

– Data is mostly read, via indexes, joined frequently

• Disadvantages

– Full table scans read more data than needed

– Wrong SIZE estimation will lead to inefficient storage and/or bad performance

© Oren Nakdimon

34

Use Case 4

• PRODUCTS

– Maximum number of products is known

– A product is usually accessed by its ID

© Oren Nakdimon

35

Hash Clustered Tables

• The cluster key is hashed to determine the data location

• You can write your own hash function, or use the default one

• Advantages

– Less I/O to get to the data (no index!)

@uc4

© Oren Nakdimon

36

Hash Clustered Tables • Restrictions:

– Access by hash works only for equality, not range – Number of hash keys must be set when creating the

cluster (HASHKEYS) – Space is pre-allocated

[HASHKEYS/trunc(blocksize/SIZE) blocks]

• Advantageous when – Maximum number of final rows is known, not many

INSERTs, no UPDATEs of the hash key, access mainly by the hash key value

– For example: lookup tables

© Oren Nakdimon

37

Use Case 2b

• PROJ_PROGRESS

– Rows for the same project are added over time

– All rows for the same project are retrieved together ordered by day

© Oren Nakdimon

38

Sorted Hash Clustered Tables

• Data is clustered by one key

• Within each cluster key, the data is stored ordered by some other key


– Data is usually retrieved by one column value and ordered by another column

– Data arrives sorted over time (by the SORTed column)

@uc2b

© Oren Nakdimon

39

Global Temporary Tables Other Types of Tables

© Oren Nakdimon

40

Temporary Tables • Hold intermediate resultsets

• Defined once, not in runtime

• The lifetime of the data is either a transaction or a session – ON COMMIT PRESERVE ROWS

– ON COMMIT DELETE ROWS

• The data is visible only to the current session

• Each session has its own temporary segment

@gtt1

© Oren Nakdimon

41

Temporary Tables • Temporary tables can have:

– Indexes – Check constraints – Triggers

• Temporary tables cannot: – Have foreign key constraints – Have NESTED TABLE columns – Be Index Organized Tables – Be in a cluster – Be partitioned

© Oren Nakdimon

42

Temporary Undo (12c)

• In 12c, undo for temporary tables can be stored in the temporary tablespace

• As a result:

– No redo for undo of DML on temporary tables

– Increased “real” undo retention

– Ability to use temporary tables for read and write in read-only Active Data Guard

• TEMP_UNDO_ENABLED = true | false @utmp

© Oren Nakdimon

43

Session Level Sequences (12c)

• A new type of sequence

• Returns a range of numbers which is unique within a session

• Non persistent

@seq

© Oren Nakdimon

44

External Tables Other Types of Tables

© Oren Nakdimon

45

External Tables

• External tables allow querying from flat files (outside the database)

• The CREATE TABLE statement:

– Defines how to read and parse the file

– Maps the file structure to table columns

• Can be SELECTed just like a regular table (including joins, aggregation, sorting, etc.)

• As of 11g, a preprocessor can be defined @ext1

© Oren Nakdimon

46

SQL*Loader Express Mode (12c)

• No need to create a control file

• A log file is generated for future use, including:

– Control file

– CREATE EXTERNAL TABLE statement

– INSERT statement

• Data types of all table columns should be numeric, string or datetime

© Oren Nakdimon

47

Bitmap Indexes Other Types of Indexes

© Oren Nakdimon

48

Bitmap Indexes • Each index key stores pointers to multiple rows

• Each row is represented as a bit in every indexed value

• Efficient bitwise operations


– Low DML activity

– Low/medium cardinality

– High ad-hoc query activity

@bmi

© Oren Nakdimon

49

Bitmap-Join Indexes Other Types of Indexes

© Oren Nakdimon

50

Bitmap-Join Indexes

• Allows to index one table using columns from another table

@bmj

© Oren Nakdimon

51

Partitioning Overview Partitioning

© Oren Nakdimon

52

Partitioning

• Partitioning allows to physically break a table or index into many smaller, more manageable pieces

• There is no segment for the table/index itself – each partition has a segment

• Partition Key – used to segregate data based on its values

© Oren Nakdimon

53

Partitioning – Advantages

• Increased data availability

• Easier administration of large objects

• Performance improvement of certain queries

• Improved scalability in certain cases

© Oren Nakdimon

59


• Composite – 2-level partitioning:

– Range-Hash (8i)

– Range-List (9i)

– The other combinations (11g)

• There is no segment for the partition itself – each subpartition has a segment

© Oren Nakdimon

62

Index Partitioning

• Local index

– Each table partition has a corresponding index partition

– Unique local indexes must include the partitioning key

© Oren Nakdimon

63

Index Partitioning

• Globally partitioned index

– The index is partitioned by range or hash

– Each index partition may point to several table partitions

© Oren Nakdimon

64

Index Partitioning

• Global (non-partitioned) indexes

– May be defined on partitioned tables as well

© Oren Nakdimon

66

New Partitioning Features in 12c

• New scheme: reference + Interval

• Asynchronous global index maintenance

• Truncate/exchange partition cascade

• Multiple (sub)partitions in single DDL operations

• Partial indexes

© Oren Nakdimon

68

Virtual Columns

• Virtual columns are not stored on disk

• Their values are derived on demand by computing expressions or functions

• Can be used in queries and DML statements, but cannot be set in INSERT and UPDATE

• Can be indexed (function based index)

• Can be used as partitioning key

@vc

© Oren Nakdimon

70

Invisible Columns (12c)

• Invisible columns are invisible, unless explicitly specified

• For example, they will not appear in:

– SELECT * FROM…

– INSERT with no column list

– %ROWTYPE

© Oren Nakdimon

71

Invisible Columns

• May be created as invisible

• May be modified from visible to invisible and vice versa

• When an invisible column becomes visible, it appears as the last column of the table

@icol1

© Oren Nakdimon

72

Invisible Columns • In SQL*Plus

– SET COLINVISIBLE controls inclusion or exclusion of invisible columns in DESCRIBE

• Invisible columns can be also: – Virtual – Partitioning key

• Cannot contain invisible columns: – External tables – Temporary tables – Clustered tables

@icol2

© Oren Nakdimon

74

Virtual Indexes

• A virtual index:

– Has no segment

– Is not maintained by DML

– Is invisible to the optimizer

• Unless “_use_nosegment_indexes” is true

© Oren Nakdimon

76

Invisible Indexes

• An invisible index is:

– Maintained by DML

– Invisible to the optimizer

• Unless optimizer_use_invisible_indexes is true

@iind

oracle database 12c new features for...

Documents