If you inherit a database and are asked to maintain and manage it, typically you will inspect the contents of the data dictionary to determine the physical structure of the database and see what events are currently transacting. Besides figuring out what you inherited, these views help to automate processes and troubleshoot problems. Toward this end, Oracle provides two general categories of read-only data dictionary views:

•     The contents of your database, such as users, tables, indexes, constraints, privileges, and other objects. These are sometimes referred to as the static CDB/DBA/ALL/USER data dictionary views, and they are based on internal tables stored in the SYSTEM tablespace. The term static, in this sense, means that the information within these views changes only as you make changes to your database, such as adding a user, creating a table, or modifying a column.

•     A real-time view of activity in the database, such as users connected to the database, SQL currently executing, memory usage, locks, and I/O statistics. These views are based on virtual memory tables and are referred to as the dynamic performance views. The information in these views is continuously updated by Oracle as events take place within the database. The views are also sometimes called the V$ or GV$ views. GV$ views are global views across all nodes in the database system and normally have an additional column to let you know which node they are referring to.

These types of data dictionary views are described in further detail in the next two sections.

Static Views

Oracle refers to a subset of the data dictionary views as static and based on the physical tables maintained internally by Oracle. The term static can sometimes be a misnomer. For example, the DBA_SEGMENTS and DBA_EXTENTS views change dynamically as the amount of data in your database grows and shrinks. Regardless, Oracle has made the distinction between static and dynamic, and it is important to understand this architecture nuance when querying the data dictionary. There are four levels of static views:
• USER

• ALL

• DBA

• CDB
The USER views contain information available to the current user. For example, the USER_TABLES view contains information about tables owned by the current user. No special privileges are required to select from the USER-level views.

At the next level are the ALL static views. The ALL views show you all object information the current user has access to. For example, the ALL_TABLES view displays all database tables on which the current user can perform any type of DML operation. No special privileges are required to query from the ALL-level views.

Next are the DBA static views. The DBA views contain metadata describing all objects in the database (regardless of ownership or access privilege). To access the DBA views, a DBA role or SELECT_CATALOG_ROLE must be granted to the current user.

The CDB-level views provide information about all pluggable databases within a container database. The CDB-level views report across all containers (root, seed, and all pluggable databases) in a CDB. For instance, if you wanted to view all users within a CDB database, you would do so from the root container, by querying CDB_USERS.

You will notice that many of the static data dictionary and dynamic performance views have a new column, CON_ID. This column uniquely identifies each pluggable database within a container database. The root container has a CON_ID of 1. The seed has a CON_ID of 2. Each new pluggable database created within the CDB is assigned a unique sequential
container ID.

The static views are based on internal Oracle tables, such as USER$, TAB$, and IND$. If you have access to the SYS schema, you can view underlying tables directly via SQL. For most situations, you need to access only the static views that are based on the underlying internal tables.

The data dictionary tables (such as USER$, TAB$, and IND$) are created during the execution of the CREATE DATABASE command. As part of creating a database, the sql. bsq file is executed, which builds these internal data dictionary tables.

The sql.bsq file is generally located in the ORACLE_HOME/rdbms/admin directory; you can view it via an OS editing utility (such as vi, in Linux/Unix, or Notepad in Windows).

The static views are created when you run the catalog.sql script (usually, you run this script once the CREATE DATABASE operation succeeds). The catalog.sql script is located in the ORACLE_HOME/rdbms/admin directory. Figure 10-1 shows the process of creating the static data dictionary views.

Figure 10–1. Creating the static data dictionary views

The two most common configurations used are as follows:

•     Creating complete refresh MVs that are refreshed on demand

•     Creating fast refresh MVs that are refreshed on demand

It is important to understand these basic configurations. They lay the foundation for everything else you do with the MV feature.

The SALES table will be used as the basis for the MV examples.

SQL> create table sales( sales_id number primary key , sales_amt number, region_id number , sales_dtt date);

SQL> insert into sales values (1,101,10,sysdate-10), (2,511,20,sysdate-20),(3,11,30,sysdate-30); commit;

Keep in mind the performance of these queries, which are being executed thousands/millions of times a day and consuming a large amount of database resources. These examples will not be able to simulate that but will show how to create and maintain these views.

For creating an MV, you need both the CREATE MATERIALIZED VIEW and CREATE TABLE system privileges. If a user creating MVs doesn’t own the base table, then SELECT access on the base table is also required to perform an ON COMMIT REFRESH.

Suppose you wanted to create an MV that reports on daily sales. Use the CREATE MATERIALIZED VIEW…AS SELECT statement to do this. The following statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:

Note using an alias with GROUP BY is a new 23c feature.

Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Run this query:

SQL> select mview_name, refresh_method, refresh_mode , build_mode, fast_refreshable from user_mviews where mview_name = ‘SALES_DAILY_MV’;

MVIEW_NAME   REFRESH_ REFRES BUILD_MOD FAST_REFRESHABLE

————— ——– —— ——— —————–SALES_DAILY_MV COMPLETE DEMAND IMMEDIATE DIRLOAD_LIMITEDDML

If new data is inserted, this MV will be refreshed on demand only. To initiate a fast refresh of the MV, use the REFRESH procedure of the DBMS_MVIEW package.

This example passes two parameters to the REFRESH procedure: the name and the refresh method. C is for complete, and F is for fast, and to run a fast refresh, the MV needs to have a MV log.

Now, you attempt to initiate a fast refresh of the MV, using the REFRESH procedure of the DBMS_MVIEW package. This example passes two parameters to the REFRESH procedure: the name and the refresh method. The name is SALES_DAILY_MV, and the parameter is F (for fast):

SQL> exec dbms_mview.refresh(‘SALES_DAILY_MV’,’F’);

Because this MV was not created in conjunction with an MV log, a fast refresh is not possible. The following error is thrown:

ORA-23413: table “MV_MAINT”.”SALES” does not have a materialized view log

Instead, a complete refresh is initiated. The parameter passed in is C (for complete):

SQL> exec dbms_mview.refresh(‘SALES_DAILY_MV’,’C’);

The output indicates success:

PL/SQL procedure successfully completed.

To make this MV fast refreshable, a log needs to be created. Here is the criteria for a fast refreshable MV:

1. A base table recommended with primary (rowid can be used if no primary key)

2. Create an MV log on the base table.

3. Create an MV as fast refreshable.

SQL> create materialized view log on sales with primary key; SQL> create materialized view sales_rep_mv segment creation immediate refresh with primary key fast on demand as select sales_id ,sales_amt,trunc(sales_dtt) sales_dtt from sales;

First, when an MV log is created, a corresponding table is also created that stores the rows in the base table that changed and how they changed (insert, update, or delete). The MV log table name follows the format MLOG$_<base table name>.

A table is also created with the format RUPD$_<base table name>. Oracle automatically creates this RUPD$ table when you create a fast refreshable MV, using a primary key.

The table is there to support the updatable MV feature. You do not have to worry about this table unless you are dealing with updatable MVs (see the Oracle Advanced Replication Guide for more details on updatable MVs). If you’re not using the updatable MV feature, then you can ignore the RUPD$ table.

Oracle creates an index with the format <base table name>_PK1. This index is automatically created for primary key-based MVs and is based on the primary key column(s) of the base table. If this is a ROWID instead of a primary key, then the index name has the format I_SNAP$_<table_name> and is based on the ROWID. If you do not explicitly name the primary key index on the base table, then Oracle gives the MV table primary key index a system-generated name, such as SYS_C008780.

Now that you understand the underlying architectural components, let’s look at the data in the MV:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2;Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023 101 20-jan-2023 127 30-jan-2023

Let’s add two records to the base SALES table:

SQL> insert into sales values (6, 99, 20, sysdate-6), (7, 127, 30, sysdate-7);

SQL> commit;

At this point, it is instructional to inspect the M$LOG table. You should see two records that identify how the data in the SALES table have changed:

SQL> select count(1) from mlog$_sales;

There are two records:

Next, let’s refresh the MV. This MV is fast refreshable, so you call the REFRESH procedure of the DBMS_MVIEW package with the F (for fast) parameter:

SQL> exec dbms_mview.refresh(‘SALES_REP_MV’,’F’);A quick inspection of the MV shows two new records:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2; Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023101 20-jan-2023 127 23-jan-2023 99 24-jan-2023 127 30-jan-2023

Additionally, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those records are no longer required:

SQL> select count(1) from mlog$_sales;

Here is the output:

COUNT(1)

———-0

You can verify the last method whereby an MV was refreshed by querying the USER_ MVIEWS view:

SQL> select mview_name, last_refresh_type, last_refresh_date from user_mviews order by 1,3; Here is some sample output:

MVIEW_NAME             LAST_REF    LAST_REFR

SALES_DAILY_MV COMPLETE 30-JAN-23

SALES_REP_MV        FAST    30-JAN-23

This topic fits into our data management coverage and preparing the data for reporting, other applications, and data services. Materialized views are valuable tools to use in your database environment.

An MV allows you to execute a SQL query at a point in time and store the result set in a table (either locally or in a remote database). After the MV is initially populated, you can later rerun the MV query and store the fresh results in the underlying table. There are ways to automate refreshes as well as real-time MVs.

There are three main uses for MVs:

•     Replicating of data to offload query workloads to separate reporting databases

•     Improving performance of queries by periodically computing and storing the results of complex aggregations of data, which lets users query point-in-time results

•     Stopping the query from executing if the query rewrite does not happen

The MV can be a query based on tables, views, and other MVs. The base tables are often referred to as master tables. When you create an MV, Oracle internally creates atable (with the same name as the MV) as well as an MV object (visible in DBA/ALL/USER_ OBJECTS).

MV Terminology

There are many terms related to refreshing MVs. You should be familiar with these terms before implementing the features. Table 9-1 defines the various terms relevant to MVs.

Table9-1.MVTerminology

This table will serve as a good reference as you read the rest of the chapter. The examples will further explain these terms and concepts.

Just like with other objects in the database and what we saw with JSON duality views, there are data dictionary views that are helpful when working with MVs. Table 9-2 describes the MV-related data dictionary views.

Table9-2.MVDataDictionaryViewDefinitions

DBA/ALL/USER_REFRESH DBA_RGROUP

DBA_RCHILD

You might see this as a developer topic inserted into a database administration book, but this idea of having relational tables and delivering data in different formats while maintaining data consistency and performance is too important to keep just to the developers or just to DBAs. You can use SQL, graph syntax, PL/SQL, JavaScript, or your favorite programming language to access data from the database.

JSON relational duality views make it easy to maintain the JSON documents without needing back and forth to the database for ID or updates against other documents to make sure you have a data consistency maintained across the board. JSON relational duality views leverage the relational tables by providing JSON documents using the data you have as part of other applications or the relational tables that you use for transactions.

Also, if you have gotten into the practice of providing data services from the database through APIs or views, this is going to show you how easy it is to use JSON in the Oracle Database and provide JSON documents for applications to read, insert, and modify.

The first part of this chapter was demonstrating the basic view concepts with a few examples to implement, create, and maintain views as database objects. Now let’s dive into how we can create JSON duality views on some of the same types of tables. Here are the create table statements for these examples:

SQL> create table emp ( emp_id number primary key , emp_name varchar2(30), emp_email varchar2(30), job_profile varchar2(30));

You can also simply select from the table in JSON formatting:

Now, we can create a simple duality view on just the emp table:

Here we used the SQL syntax to create the view, and the insert, update, and delete operations allow us to perform those actions against the view. The relational table will be updated as a result.

Select from the view to see the JSON format:

You will notice the emp_id is the primary key and additional fields of metadata have been added. The etag can be used for optimistic locking, and it represents the current state of the object. The asof metadata is the system change number (SCN) for consistent reads. If you update the emp, the state of the object has changed for those updated records, and the etag would then change. The etag will provide the checks and validations for the updates with no optimistic locking. It really makes it easy to work withhigh concurrency systems and scales very nicely.

From the select statement, you can see every row is one employee, and each employee is a JSON document in the view.

Now let’s look at a duality view on multiple tables. Also, notice that this is using the graph syntax that is built into the Oracle Database. This is another way of querying the data. You can also build the view with SQL syntax like we did for the emp_v.

The JSON document is being made up from different entities, and the data is shared in the views. With the insert/update/delete, you can perform these actions on the view, and that update or insert will occur in the relational tables that populate these views.

This eliminates managing all of the JSON documents to update every document with the change. Also, having everything available in the JSON document will avoid making more round-trips to the database to fetch IDs as things change because it is based on the relational table data.

The JSON duality view has all of the data that is needed available, including the changes, as data is modified through the view to the tables. As you can see, I excluded a couple of columns from the view, and not all columns from the table are required in the duality view.

So, what does this view look like (the first regular SQL statement is just to return the rows, and then it appears in pretty print so the JSON data is more readable):

Now let’s insert a row into the emp_v for the new employee:

Now let’s insert a row into the emp_dept_v with a new team name:

Selecting from the emp table will show another entry:

Selecting from the teams table inserts the row with the department and manager:

Finally, the JSON duality view shows us the JSON data:

Not all of the fields were inserted, but we also didn’t provide that data through the view. Depending on the application and how the data is being used, those are areas that can be handled programmatically or through triggers or just by including the data in the insert.

There are also data dictionary views that provide metadata about the duality views: DBA/ALL/USER_JSON_DUALITY_VIEWS, DBA/ALL/USER_JSON_DUALITY_VIEW_TABS, DBA/ALL/USER_JSON_DUALITY_VIEW_TAB_COLS, and DBA/ALL/USER_JSON_DUALITY_ VIEW_LINKS.

SQL> select view_name from user_json_duality_views; VIEW_NAME

——————————————–EMP_V

EMP_DEPT_V

With Oracle Database 23c, there are simplified ways to handle JSON documents. There are JSON data types and JSON schemas along with the functions to view the JSON format easier. It is easier to use SQL or graph syntax for easier-to-read JSON; and now JSON duality views give applications ways to get and put data, read data, and modify it when necessary while sharing the same data source and avoiding costly integrations and data consistency issues.

If you need to modify the SQL query on which a view is based, then either drop and re-create the view or use the CREATE or REPLACE syntax, as in the previous examples.

For instance, say you add a REGION column to the SALES table:

SQL> alter table sales add (region varchar2(30)); SQL> create or replace view sales_rockies as select sales_id, amnt, state, region from sales where state in (‘CO’, ‘UT’, ‘WY’, ‘ID’, ‘AZ’) with read only;

The advantage of using the CREATE OR REPLACE method is that you do not have to reestablish access to the view for users with previously granted permissions.

Also, you don’t have to re-create the view if you do not want to include the new column in the view.

However, if you remove a column that the view is using, the view will compile with errors, and you will have to re-create the view without the column.

Renaming a view is also possible with the RENAME statement.

SQL> rename sales_rockies to sales_rockies_old;

You should see this message:

Table renamed.

It would make more sense if it said “View renamed,” but the message in this case does not exactly match the operation.

Dropping a view makes sense if you are no longer using it.

SQL> drop view sales_rockies_old;

Keep in mind that when you drop a view, any dependent views, materialized views, and synonyms become invalid. Additionally, any grants associated with the dropped view are also removed.

The previous example had inserts and data modification on a view with one table defined in the FROM clause of the SQL query, but this is also possible with multiple tables defined. This is known as an updatable join view.

For reference purposes, here are the CREATE TABLE statements for the two tables used in the examples in this section:

SQL> create table dept ( dept_id number primary key , dept_name varchar2(15)); —

SQL> create table emp ( emp_id number primary key , emp_name varchar2(15), dep_id number, constraint emp_dept_fk foreign key (dept_id) references dept(dept_id));And let’s seed some data for the two tables:

SQL> insert into dept values (1, ‘HR’), (2, ‘IT’),(3, ‘SALES’);

SQL> insert into emp values (10, ‘John’, 2), (20, ‘George’, 1),

(30, ‘Fred’, 2), (40, ‘Craig’, 1), (50, ‘Linda’, 2), (60, ‘Carrie’, 3);

Here is an example of an updatable join view, based on the two prior base tables:

SQL> create or replace view emp_dept_v asselect a.emp_id, a.emp_name, b.dept_name, b.dept_id from emp a, dept bwhere a.dept_id = b.dept_id;

Underlying tables can be updated only if the following conditions are true:

•     The DML statement must modify only one underlying table.

•     The view must be created without the READ ONLY clause.

•     The column being updated belongs to the key-preserved table in the join view.

An underlying table in a view is key preserved if the table’s primary key can also be used to uniquely identify rows returned by the view. An example with data will help illustrate whether an underlying table is key preserved. In this scenario, the primary key of the EMP table is the EMP_ID column; the primary key of the DEPT table is the DEPT_ID column. Here is some sample data returned by querying the view:

As you can see from the output of the view, the EMP_ID column is always unique. Therefore, the EMP table is key preserved, and its columns can be updated. In contrast, the view’s output shows that it is possible for the DEPT_ID column to not be unique. Therefore, the DEPT table is not key preserved, and its columns can’t be updated.

When you update the view, modifications that result in columns that map to the underlying EMP table should be allowed because the EMP table is key preserved in this view.

SQL> update emp_dept_v set emp_name = ‘Jon’ where emp_id = 10;

A basic view is really a SQL statement stored in the database as an object. Conceptually, when you select from a view, Oracle looks up the view definition in the data dictionary, executes the query the view is based on, and returns the results.

In addition to selecting from a view, in some scenarios it is possible to execute INSERT, UPDATE, and DELETE statements against the view, which results in modifications to the underlying table data. So, in this sense, instead of simply describing a view as a stored SQL statement, it is more accurate to conceptualize a view as a logical table built on other tables or views, or both.

Here are some common uses for views:

•     Create an efficient method of storing a SQL query for reuse

•     Provide an interface layer between an application and physical tables

•     Hide the complexity of a SQL query from an application

•     Report to a user only a subset of columns or rows, or both

You should be able to start to see how useful views are for application APIs and how they can provide the needed data sets to applications and users.

Creating a View

You can create views on tables, materialized views, or other views. To create a view, your user account must have the CREATE VIEW system privilege. If you want to create a view in another user’s schema, then you must have the CREATE ANY VIEW privilege.

For reference, the view creation example in this section depends on the following base table:

SQL> create table sales ( sales_id number primary key , amnt number, state varchar2(2), sales_person_id number); Also assume that the table has the following data initially inserted into it:

SQL> insert into sales values(1, 222, ‘CO’, 8773), (20,827, ‘FL’, 9222); The CREATE VIEW statement is used to create a view.

The following code creates a view (or replaces it if the view already exists) that selects a subset of columns and rows from the SALES table:

SQL> create or replace view sales_rockies as select sales_id, amnt, state from sales where state in (‘CO’,’UT’,’WY’,’ID’,’AZ’); CREATE OR REPLACE VIEW is useful to modify a view if it exists or create a new view without having to verify if it already exists. If you don’t want to overwrite existing views, then use CREATE VIEW statements.

When you select from SALES_ROCKIES, it executes the view query and returns data from the SALES table as appropriate:

SQL> select * from sales_rockies; Given the view query, it is intuitive that the output shows only the following columns and one row:

SALES_ID    AMNT                    ST ———- ———- –1              222                    CO

What is not as apparent is that you can also issue UPDATE, INSERT, and DELETE statements against a view, which results in modification of the underlying table data. For example, the following insert statement against the view results in the insertion of a record in the SALES table:

SQL> insert into sales_rockies ( sales_id, amnt, state) Values (2, 100, ‘CO’);

Additionally, as the owner of the table and view (or as a DBA), you can grant DML privileges to other users on the view.

For instance, you can grant SELECT, INSERT, UPDATE, and DELETE privileges on the view to another user, which will allow the user to select and modify data referencing the view to another user, which will allow the user to select and modify data referencing the view. However, having privileges on the view does not give the user direct SQL access to the underlying tables.

Any users granted privileges on the view will be able to manipulate data through the view but not issue SQL against the object the view is based on.

If you create the view using the WITH READ ONLY clause, users cannot perform INSERT, UPDATE, or DELETE operations on the view.

This is useful if you use views for reporting and never intend for the view to be used as a mechanism for modifying the underlying table’s data; then you should always create the views with the WITH READ ONLY clause. Doing so prevents accidental modifications to the underlying tables through a view that was never intended to be used to modify data.

Here is a simple example that demonstrates the locking issue when foreign key columns are not indexed. First, create two tables (DEPT and EMP) and associate them with a foreign key constraint:

SQL> create table emp(emp_id number primary key, dept_id number); SQL> create table dept(dept_id number primary key);

SQL> alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);

Next, insert some data:

SQL> insert into dept values(10), (20),(30);

SQL> insert into emp values(1,10), (2,20),(3,10);

SQL> commit; Open two terminal sessions. From one, delete one record from the child table (do not commit):

SQL> delete from emp where dept_id = 10; Then, attempt to delete from the parent table some data not affected by the child table delete:

SQL> delete from dept where dept_id = 30; The delete from the parent table hangs until the child table transaction is committed.

Without a regular B-tree index on the foreign key column in the child table, any time you attempt to insert or delete in the child table, a table-wide lock is placed on the parent table; this prevents deletes or updates in the parent table until the child table transaction completes.

Now, run the prior experiment, except this time, additionally create an index on the foreign key column of the child table:

SQL> create index emp_fk1 on emp(dept_id);

You should be able to run the prior two delete statements independently. When you have a B-tree index on the foreign key columns, if deleting from the child table, Oracle will not excessively lock all rows in the parent table.

Indexes are important tools for performance when querying the data. There is some cost involved when using them and creating, but with analyzing the SQL statements and planning the storage and maintenance indexes allow for faster data access.

Say you have a requirement that every record in the ADDRESS table be assigned a corresponding CUST_ID column from the CUST table. To enforce this relationship, you create the ADDRESS table and a foreign key constraint, as follows:

SQL> create table address(address_id number ,cust_address varchar2(2000),cust_id number); —

SQL> alter table address add constraint addr_fk1 foreign key (cust_id) references cust(cust_id);

Note a foreign key column must reference a column in the parent table that has a primary key or unique key constraint defined on it. Otherwise, you will receive the error ORA-02270: no matching unique or primary key for this column-list.

You realize that the foreign key column is used extensively when joining the CUST and ADDRESS tables and that an index on the foreign key column will increase performance. In this situation, you have to create an index manually. For instance, a regular B-tree index is created on the foreign key column of CUST_ID in the ADDRESS table.

SQL> create index addr_fk1 on address(cust_id);

You do not have to name the index the same name as the foreign key (as I did in these lines of code). It is a personal preference as to whether you do that. I feel it is easier to maintain environments when the constraint and corresponding index have the same name.

When creating an index, if you do not specify the tablespace name, Oracle places the index in the user’s default tablespace. It is usually a good idea to explicitly specify which tablespace the index should be placed in; for example,

SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index;

Determining Whether Foreign Key Columns Are Indexed

If you are creating an application from scratch, it is fairly easy to create the code and ensure that each foreign key constraint has a corresponding index. However, if you have inherited a database, it is prudent to check if the foreign key columns are indexed.

You can use data dictionary views to verify if all columns of a foreign key constraint have a corresponding index. The task is not as simple as it might first seem. For example, here is a query that gets you started in the right direction:

SQL> SELECT DISTINCT

a.owner              owner

,a.constraint_name cons_name

,a.table_name ,tab_name

b.column_name cons_column

NVL(c.column_name,’***Check index****’) ind_column FROM dba_constraints a,dba_cons_columns b ,dba_ind_columns c

WHERE constraint_type = ‘R’

AND a.owner = UPPER(‘&&user_name’) AND a.owner = b.owner

AND a.constraint_name = b.constraint_name AND b.column_name = c.column_name(+)

AND b.table_name = c.table_name(+) AND b.position = c.column_position(+) ORDER BY tab_name, ind_column;

This query, while simple and easy to understand, does not correctly report on unindexed foreign keys for all situations.

For example, in the case of multicolumn foreign keys, it does not matter if the constraint is defined in an order different from that of the index columns, as long as the columns defined in the constraint are in the leading edge of the index.

In other words, for a constraint on (COL1, COL2), an index will work on either (COL1,COL2) or (COL2,COL1);the order of the same set of columns doesn’t matter. Also, an index on (COL1,COL2,COL3) will also work, because the extra index column is OK as long as the leading columns match.

SQL> create index column_test_idx on table1 (col2, col1);

SQL> create index column_test_idx on table1 (col1, col2, col3);

Another issue is that a B-tree index protects you from locking issues, but a bitmap index does not. In this situation, the query should also check the index type.

In these scenarios, you will need a more sophisticated query to detect indexing issues related to foreign key columns. The query checks index type and finds the related tables and their indexes to show what the index columns are with the tables and indexes.

The following example is a more complex query that uses the LISTAGG analytic function to compare columns (returned as a string in one row) in a foreign key constraint with corresponding indexed columns:

SQL> SELECT

CASE WHEN ind.index_name IS NOT NULL THEN CASE WHEN ind.index_type IN (‘BITMAP’) THEN ‘** Bitmp idx **’

ELSE ‘indexed’ END

ELSE

‘** Check idx **’ END checker ,ind.index_type,cons.owner, cons.table_name, ind.index_name, cons.constraint_name, cons.cols

FROM (SELECT

c.owner, c.table_name, c.constraint_name

, LISTAGG(cc.column_name, ‘,’ ) WITHIN GROUP (ORDER BY cc.column_name) cols FROM dba_constraints c

,dba_cons_columns cc WHERE c.owner = cc.owner

AND c.owner = UPPER(‘&&schema’)

AND c.constraint_name = cc.constraint_name AND c.constraint_type = ‘R’

GROUP BY c.owner, c.table_name, c.constraint_name) cons LEFT OUTER JOIN

(SELECT

table_owner, table_name, index_name, index_type, cbr ,LISTAGG(column_name, ‘,’ ) WITHIN GROUP (ORDER BY column_name) cols FROM (SELECT

ic.table_owner, ic.table_name, ic.index_name

,ic.column_name, ic.column_position, i.index_type ,CONNECT_BY_ROOT(ic.column_name) cbr

FROM dba_ind_columns ic ,dba_indexes i

WHERE ic.table_owner = UPPER(‘&&schema’) AND ic.table_owner = i.table_owner

AND ic.table_name = i.table_name AND ic.index_name = i.index_name

CONNECT BY PRIOR ic.column_position-1 = ic.column_position AND PRIOR ic.index_name = ic.index_name)

GROUP BY table_owner, table_name, index_name, index_type, cbr) ind ON cons.cols = ind.cols

AND cons.table_name = ind.table_name AND cons.owner = ind.table_owner

ORDER BY checker, cons.owner, cons.table_name;

This query will prompt you for a schema name and then will display all foreign key constraints that do not have corresponding indexes. This query also checks for the index type; as previously stated, bitmap indexes may exist on foreign key columns but do not prevent locking issues.

Suppose you have identified an index that is not being used and are considering dropping it. In earlier releases of Oracle, you could mark the index UNUSABLE and then later drop indexes that you were certain weren’t being used. If you later determined that you needed an unusable index, the only way to re-enable the index was to rebuild it. For large indexes, this could take a great amount of time and database resources.

Making an index invisible has the advantage of telling only the optimizer not to use the index. The invisible index is still maintained as the underlying table has records inserted, updated, and deleted. If you decide that you later need the index, there is no need to rebuild it; you simply make it visible again.

You can create an index as invisible or alter an existing index to be invisible; for example,

SQL> create index cust_idx2 on cust(first_name) invisible; SQL> alter index cust_idx1 invisible;

You can verify the visibility of an index via this query:

SQL> select index_name, status, visibility from user_indexes;

Here is some sample output:

INDEX_NAME

——————–CUST_IDX1

CUST_IDX2

USERS_IDX1

STATUS

——–VALID VALID

VALID

VISIBILITY

———-INVISIBLE INVISIBLE

VISIBLE

Use the VISIBLE clause to make an invisible index visible to the optimizer again:

SQL> alter index cust_idx1 visible;

Note If you have a B-tree index on a foreign key column and you decide to make it invisible, Oracle can still use the index to prevent certain locking issues. Before you drop an index on a column associated with a foreign key constraint, ensure that it is not used by Oracle to prevent locking issues. See the section “Indexing Foreign Key Columns,” later in this chapter, for details.

Guaranteeing Application Behavior Is Unchanged When You Add an Index

You can also use an invisible index when you are working with third-party applications. Often, third-party vendors do not support customers adding their own indexes to an application. However, there may be a scenario in which you are certain you can increase a query’s performance without affecting other queries in the application.

You can create the index as invisible and then use the OPTIMIZER_USE_INVISIBLE_ INDEXES parameter to instruct the optimizer to consider invisible indexes. This parameter can be set at the system or session level. Here is an example:

SQL> create index cust_idx1 on cust(cust_id) invisible;

Now, set the OPTIMIZER_USE_INVISIBLE_INDEXES database parameter to TRUE. This instructs the optimizer to consider invisible indexes for the currently connected session:

SQL> alter session set optimizer_use_invisible_indexes=true;

You can verify that the index is being used by setting AUTOTRACE to on and running the SELECT statement:

SQL> set autotrace trace explain;

SQL> select cust_id from cust where cust_id = 3;

Here is some sample output, indicating that the optimizer chose to use the invisible index:

—————————————————————————| Id | Operation               | Name           | Rows | Bytes | Cost (%CPU)| Time —-| —————————————————————————| 0 | SELECT STATEMENT |                    | 1        | 5          | 1 (0)           | 00:00:01 | |* 1 | INDEX RANGE SCAN| CUST_IDX1 | 1                      | 5                                          | 1 (0)         | 00:00:01 | —————————————————————————

Keep in mind that invisible index simply means an index the optimizer cannot see. Just like any other index, an invisible index consumes space and resources during DML statements.