10 Managing Schema Objects

What are schema objects in Oracle?

Schema objects are database objects owned by a user within their schema, including tables, views, indexes, and sequences.

Where is metadata about tables (like columns and data types) stored in Oracle?

In the system-defined tables known as the data dictionary, which Oracle manages automatically.

How are new columns added with ALTER TABLE stored in relation to existing columns?

They are added at the end of the list of columns in the table's structure.

What is a constraint in Oracle databases?

A rule on a table restricting the values that can be added to columns, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.

What happens if a SQL statement violates a table constraint?

The entire SQL statement fails with an execution error.

What is a view in Oracle?

A stored SELECT statement with a name, acting like a table but storing no data, only the query definition. You can describe.

How does an index work conceptually?

It is like a book index: it stores presorted unique values from table columns and their row addresses to speed lookups. It is own object, stored separately from the table and it’s constantly maintained with every INSERT, UPDATE and DELETE performed on the table. Can be created for only one table.

What is the syntax to create a view?

CREATE [OR REPLACE] VIEW view_name [(col1, col2)] AS SELECT …

Why might you use a view for security?

To expose only certain columns of a table while hiding sensitive information from general users.

What is the OR REPLACE option in CREATE VIEW?

It overwrites an existing view without warning and replaces it with a new definition.

When must column aliases or names be specified when creating a view?

When the SELECT statement includes complex expressions, since views require valid column names. Or use syntax CREATE VIEW vw_name (COL_N2, COL_N1) AS SELECT …

What is an updatable view?

A view that allows INSERT, UPDATE, or DELETE if it contains enough information to satisfy all constraints of underlying tables.

What prevents a view from being updatable?

Use of aggregations, GROUP BY, DISTINCT, alias columns, set operators, missing NOT NULL columns, FROM clause that references more than one table - that is subqueries in the SELECT or most joins.

What is an inline view?

A subquery in the FROM clause of a SQL statement, replacing a table reference.

What is the difference between a normal subquery and an inline view?

Inline views can be nested without limit, while regular subqueries have a nesting limit (255).

How can inline views solve ROWNUM ordering issues?

By placing ORDER BY inside the inline view so that ROWNUM in the outer query reflects the sorted order.

What does ALTER VIEW … COMPILE do?

It recompiles an invalid view after changes to underlying tables; if recompilation fails, the view remains invalid.

Can ALTER VIEW change a view's SELECT statement?

No, you must drop and recreate the view.

What are invisible columns in Oracle tables?

Columns marked INVISIBLE are hidden from DESCRIBE and wildcard SELECT, but can still be used if named explicitly, even for manipulating data like INSERT, DELETE or UPDATE.

How do invisible columns interact with views?

Wildcard (*) in a view ignores invisible columns, but explicitly naming them includes them in the view.

What is the purpose of a sequence?

To generate sequential numbers, commonly for populating primary key values.

What are the pseudocolumns of a sequence?

NEXTVAL (increments and returns next value) and CURRVAL (returns current value after NEXTVAL has been called within a given session).

What are key options in CREATE SEQUENCE?

• INCREMENT BY [integer] - default is 1 and a negative number is for descending

• START WITH [integer] - default is MINVALUE for ascending or MAXVALUE for desc unless NOMINVALUE or NOMAXVALUE are specified in which case default is 1

• MAXVALUE [integer] - default is NOMAXVALUE

• MINVALUE [integer] - default is NOMINVALUE, unless a MINVALUE is required by the presence of CYCLE in which case the default is 1

• NOMAXVALUE

• NOMINVALUE

• CYCLE - when the generator reaches one end of its range, restart at the other end.

• NOCYCLE - when the generator reaches the end of its range, stop generating numbers. NOCYCLE is the default. If no range is specified, NOCYCLE has no effect.

Each separated by spaces

What happens if an INSERT using NEXTVAL fails?

The sequence still advances; the value is consumed even if the DML fails. Even ROLLBACK won’t reset it back.

Can sequences be tied automatically to a table?

No, they are independent objects. Developers must explicitly use them.

What restrictions exist for using NEXTVAL or CURRVAL?

They cannot be used in DEFAULT clauses, CHECK constraints, WHERE clauses, set operators, or in subqueries of CREATE VIEW.

What must be the first reference to a sequence in a session?

NEXTVAL must be referenced before CURRVAL can be used within a given session.

What is the purpose of an index?

To improve performance of queries by storing presorted column values with row addresses. For WHERE and ORDER BY clauses.

Can indexes be created on LOB or RAW columns?

No, indexes cannot be created on LOB or RAW data types.

When are indexes created implicitly?

When PRIMARY KEY or UNIQUE constraints are defined on a table.

What is a composite index?

An index built on two or more columns, sorted first by the first column, then by the next, and so on.

CREATE INDEX ix_name ON table_name (col1, col2, …, coln);

What is skip scanning in composite indexes?

A feature allowing the index to be used even if the WHERE clause does not reference the leading column, by scanning once per unique value in the leading column. However the benefit is not as so good, but better than nothing.

What is a unique index?

An index that enforces uniqueness on a column or set of columns.

CREATE UNIQUE INDEX ix_name ON table_name (col1);

What is the difference between UNIQUE constraint and unique index?

Both enforce uniqueness, but a constraint is more self-documenting; Oracle recommends unique indexes for performance.

Constraint is a logical rule at the schema level, part of the relational model (shows business logic)

Index is a physical (implementation) database object, speeds lookups, can exist without being tied to a constraint

What is an invisible index?

An index ignored by the optimizer when creating a query plan but still maintained during DML operations. The invisible index is still updated for consistency when there’s UPDATE, DELETE or INSERT performed.

When might you use an invisible index?

For testing index usefulness or tuning performance without dropping the index.

Can multiple indexes exist on the same column set? (1 or more columns)

Yes, if they differ by:

• uniqueness (one index is a unique index and other is not)

• index type (B-tree vs bitmap [CREATE BITMAP INDEX … ])

• partitioning (type - hash or range, local versus global partitioning, partitioning vs nonpartitioning).

Only one may be visible at a time - others must be invisible when creating a new index on the same column set.

What are Flashback operations in Oracle?

Features that allow access to past states of data or objects including ones that have already been dropped, including Flashback Table, Flashback Drop, and Flashback Query.

What is FLASHBACK TABLE used for?

To recover a previously dropped table or restore an existing table to a past state.

What happens to a dropped table before it is purged?

It is placed in the recycle bin, from which it can be recovered.

What is the effect of PURGE TABLE?

It permanently removes a table from the recycle bin so it cannot be recovered with FLASHBACK TABLE.

What are the ways to specify a flashback point in time?

Using a TIMESTAMP, SCN (System Change Number), or RESTORE POINT.

What is ROW MOVEMENT in Flashback?

A table option that must be enabled to allow flashback of existing tables to a prior state.

What are SCNs?

System Change Numbers automatically incremented with every commit; they uniquely mark points in database time.

What functions convert between SCN and TIMESTAMP?

SCNTOTIMESTAMP(scn) and TIMESTAMPTOSCN(timestamp).

What is a RESTORE POINT?

A named object representing a specific SCN or TIMESTAMP for later flashback use.

In what order are columns of tables stored?

In order of their creation but it’s not true at all times

Where are data stored in the database?

In tables

Is a constraint a database object?

No, but it is listed in the data dictionary and can be named

Can you create a constraint on a view?

Yes, however Oracle doesn’t enforce them without special configuration

What happens if you insert new data to a view that omits some of the NOT NULL columns?

It fails, but DELETE and UPDATE can still be executed

What is a ROWNUM?

It’s a pseudocolumn containing a row number that is assigned automatically to each row result of a query

Why is ROWNUM a challenge?

Because it’s assigned before the ORDER BY clause is processed

What can ALTER VIEW do?

It can create, modify, drop constraints on a view or. recompile an invalid view

What is the syntax for creating a sequence?

CREATE SEQUENCE seq_name [sequence_options]

What’s the places where it’s forbidden to use CURRVAL or NEXTVAL?

• In the DEFAULT clause of a CREATE TABLE or ALTER TABLE

• In the subquery of a CREATE VIEW or of a SELECT, UPDATE, or DELETE statement

• In a SELECT, you cannot combine CURRVAL or NEXTVAL with a DISTINCT operator

• In the WHERE clause of a SELECT

• In a CHECK constraint

• Set operators UNION, INTERSECT, and MINUS

• You can call a sequence pseudocolumn from anywhere within a SQL statement that you can use any expression

Is there some engine that determines what actions should be executed first for a particular SQL statement?

Yes, Oracle Database Optimizer creates an execution plan for processing the statement. This plan can be viewed by EXPLAIN PLAN.

In which tables are created index stored?

You can query the stored indexes in USER_INDEXES and also in USER_IND_COLUMNS

How to create an index?

CREATE INDEX ix_name ON table_name(col1) [INVISIBLE/VISIBLE];

visible is the default

When is it more likely for the optimizer to use an index?

When the values of the columns of the index have less repetitive or unique values (higher degree if selectivity).

What are the guidelines for more likely usage of the index?

Query specifies indexed column in the WHERE clause or ORDER BY clause.

• For best results, the indexed column should be specified in a comparison of equality

• A greater than or some other comparison may work

• A not equals will not invoke an index

• LIKE may invoke an index as long as the wildcard character is not in the leading position

• A unction on a column will prevent the use of an index - unless the index is a function-based index

How to drop an index?

DROP INDEX index_name;

If you drop a table the index is automatically dropped as well. If you re-create the table, you need to re-create the index as well,

How to modifie an existing index to be visible/invisible?

ALTER INDEX index_name INVISIBLE/VISIBLE;

VISIBILITY of an index is stored in the data dictionary view USER_INDEXES.

The data dictionary uses uppercase letters to record rhetorical names of all tables, columns, views, and other objects you create, unless you enclose the name in double quotes when you create the object.

What can Flashback operations do?

• Recover complete tables that have been dropped

• Recover data changes within one or more tables resulting from a series of DML statements

• Performing data analysis on data that’s been changed over periods of time

• Comparing data that existed in one point of time to data from other time

• Performing queries as of a prior time period

What’s the relation with user sessions with Flashback operations?

Multiple user sessions can access historical data dynamically on any table (including the same tables) at the same time, with each user session potentially accessing different points in the history of the table simultaneously, all while the database is up and running in full operational mode.