Managing Indexes and Synonyms in SQL: Everything You Need to Know.

6.Create a descending index on column tDate from table Transactions.
CREATE INDEX idx_tdate_desc ON Transactions (tDate DESC);
It will create an index called idx_tdate_desc on the tDate column of the Transactions table in descending order. Descending indexes are handy when queries always order or filter by date in descending order like retrieving the latest transactions first. The database can then fetch results ordered by date with minimal additional sorting that happened since the index was built in this order which does quickly speed up date based searches or reports particularly where you seek the latest records.

7.Create an index that has columns TransactionType and Amount of the table Transactions.
CREATE INDEX idx_txn_type_amount ON Transactions (TransactionType, Amount);
This query creates a composite index named idx_txn_type_amount on the columns TransactionType and Amount of the table Transactions. Composite indexes are useful to optimize selects that filter on more than one column. For instance, suppose that there are many queries that often filter on transactions by both TransactionType and Amount in them. This composite index could reduce the time taken to fetch the results markedly. It helps to improve performance by fetching the data faster when the column combination occurs in the query.

8.Disable an index on the Phone column in the Customers table (Oracle only)
ALTER INDEX idx_phone UNUSABLE;
This Oracle specific query makes the index idx_phone on the Phone column of the Customers table unusable to the query optimizer. The index still exists but is not used in query planning, which allows testing how queries perform without it. If there is no performance degradation the index might be redundant and could be dropped. This is a useful strategy for evaluating the necessity of an index without permanently removing it, offering a non destructive way to test its impact on query performance.

9.Rebuild an index on the Balance column in the Accounts table.
ALTER INDEX idx_balance REBUILD;
This query rebuilds the index idx_balance on the column Balance of the Accounts table. Since updates, deletions, and insertions are frequent sometimes an index becomes fragmented. When an index gets fragmented, it leads to poor usage of storage and bad performance. Rebuilding an index rebuilds the structure of the database because the database consolidates the fragmented pages and optimizes access. This restores performance because the time needed to look up or filter records based on the indexed column is saved.

10.Index the Name column in the Customers table for faster search.
CREATE INDEX idx_name ON Customers (Name);
This index idx_name is based on the Customers table column Name. Indexing the column Name may greatly speed up search operations if you happen to be trying to find customers by their names. Without creating an index on Name you'd do a full scan of probably a very huge customer table when you are trying to locate a record by the customers' names. That index allows the database to look up rows of the table by their name as quickly as possible which makes queries that depend on filtering or sorting by the Name column run more quickly.

11.Create a synonym for the Accounts table as AccTable.
CREATE SYNONYM AccTable FOR Accounts;
This query gives a synonym named AccTable to the Accounts table. A synonym is an alternate name or alias for a database object such as a table, a view, or even another synonym. It also simplifies access to the object since one can refer to it by its synonym rather than the entire table name. In large databases with very long names, using synonyms can make SQL queries much easier to read. Synonyms are extremely helpful when access is made across other schemas or databases.

12.How do Drop the synonym AccTable.?
DROP SYNONYM AccTable;
This removes a synonym named AccTable. Deleting a synonym can also be useful when it is no longer necessary or the table itself was renamed or has been deleted. Unused synonyms also help keep your database schema clean and clutter-free and prevent confusion. First, before you drop a synonym, be very certain that current queries or scripts are not relying on it,since dropping it would result in errors in those queries when they attempt to reference the dropped synonym.

13.Create a public synonym for the Transactions table named PubTransactions.
CREATE PUBLIC SYNONYM PubTransactions FOR Transactions;
This query creates a public synonym named PubTransactions for the Transactions table. Public synonyms are visible to all users in a database. It makes it easier for users to refer to tables by not requiring them to be aware of schema to which they belong. It is especially useful in shared environments in which very many users require access to common tables. Public synonyms should however be applied with caution due to potential security risks associated with it unless properly managed as it might expose sensitive data to inappropriate users.

14.Create a synonym for the Customers table called CustData in a different schema.
CREATE SYNONYM CustData FOR OtherSchema.Customers;
This query creates a synonym named CustData for the Customers table located in the OtherSchema schema. Using synonyms to reference objects in different schemas simplifies query writing by allowing users to avoid specifying the schema name each time. It can help manage access to tables across different parts of a database or between different databases without directly altering the underlying schema. This abstraction layer is particularly useful for organizing and standardizing access to shared tables across various users and applications.

15.Check existing synonyms in the current user schema.
SELECT SYNONYM_NAME, TABLE_NAME FROM USER_SYNONYMS;
This query returns the list of synonyms available with reference to the name of an existing synonym and what original table it refers to. This can help users and system administrators find out which have been created and what objects those synonyms refer to. Understanding which synonyms are being used can be important to managing database objects well, particularly when it is common in the environment you are working in to alias tables across different schemas or for even complex table names so that they are easier to query.

16.Rebuild an index on the column named CustomerID of the table named Customers.
ALTER INDEX idx_customerid REBUILD;
This is a query rebuilding the index idx_customerid on the column CustomerID of the table Customers. Index rebuild is necessary when the index is fragmented because there are many insert, update and delete operations. In such cases, fragmentation will slow down the index, meaning that data retrieval will take more time. The rebuilt index would optimize the structure, reducing fragmentation and can greatly improve query performance if a specific query depends on that index to quickly access the customer records based on the CustomerID.

17.Create an index on the tDate column in the Transactions table to speed up queries.
CREATE INDEX idx_tdate ON Transactions (tDate);
This query creates an index named idx_tdate on the tDate column in the Transactions table. Indexing the column of tDate speeds up queries involving searching, sorting or filtering by date of transactions. Without an index the database will undertake to scan an entire table to retrieve relevant rows which is not very efficient on large datasets. The database can quickly locate records based on the date with an index and date-based queries become pretty efficient and responsive.

18.Create an index that will be used on only a single row and unique on the column AccountID of the Accounts table.
CREATE UNIQUE INDEX idx_accountid_unique ON Accounts (AccountID);
This query creates a unique index named idx_accountid_unique on the column AccountID of the Accounts table. A unique index enforces uniqueness in the indexed column no two accounts will ever have the same AccountID. It is therefore a constraint preventing duplicate entries and thereby keeps the primary key column of the table in order. Such an index is very important to uniquely identify each account and also assists queries that are always searching for a particular account based on AccountID.

19.Check the present indexes on the table of Transactions.
SELECT index_name, column_name FROM all_ind_columns WHERE table_name = 'TRANSACTIONS';
This returns a list of indexes with their corresponding columns on the Transactions table. That means users and administrators see which indexes have been created and what columns are indexed, giving visibility into the indexing strategy in effect . The existing indexes allow for optimizing the query's performance because through this information, you can see if other indexes are necessary or if some indexes can be changed or dropped to get better efficiency. This is very helpful in database tuning and optimization for queries.

20.Rebuild all indexes in the Accounts table.
ALTER INDEX ALL REBUILD;
This query rebuilds all indexes on the Accounts table. Rebuilding indexes is one of the maintenance operations that actually reorganizes the physical storage of the index itself, which reduces fragmentation and improves query performance over time. Because of changes to data an index can have fragmentation which will naturally slow down query execution. As rebuilt indexes restore the indexes to an optimal condition they enhance the efficiency of data retrieval operations. This question is particularly useful when the table is updated extremely frequently and the indexes are highly fragmented.

21.Create a unique synonym for the Customers table as UniqueCust.
CREATE SYNONYM UniqueCust FOR Customers;
This query creates a synonym called UniqueCust for the Customers table. Such synonyms can make it easier to refer to the Customers table within a query because users do not have to reference the actual name of the table. This can also be helpful in those environments, like schemas or applications where a standard reference is being developed when making access to a table that exists across one or many of these. Synonyms are extremely useful in improving the usability of the database while helping to organize the access without altering the schema or structure of the underlying tables.

22. Analyze the index created on CustomerID and get the statistics.
ANALYZE INDEX idx_customerid COMPUTE STATISTICS;
Explanation
This statement generates statistics about the use and usage efficiency of the idx_customerid index created based on the column CustomerID. These statistics are accumulated in the data dictionary of a database and the optimizer uses them while trying to decide which is the most efficient means of executing queries with this index. With fresh statistics gathered the optimizer is better able to make appropriate decisions that improve the performance of queries that involve this column with an index applied to it. It is extremely helpful in databases where there are large tables and complicated queries.

22. Create an index covering the columns AccountID and Balance.
CREATE INDEX idx_account_balance ON Accounts (AccountID, Balance);
Explanation
This creates a composite index named idx_account_balance on the columns AccountID and Balance of the Accounts table. A composite index enhances the performance of queries filtering or sorting with regards to both columns. In case the two conditions are indexed and occur together in the where clause of the query the database can easily make lookups to find out the rows that satisfy the conditions rather than have to perform full table scans. Composite indexes are especially helpful in those situations where there are multiple columns that are often accessed together in the conditions of a query.

23. Check whether a public synonym named PubCust exists.
SELECT * FROM all_synonyms WHERE synonym_name = 'PUBCUST';
Explanation
This query asks whether in the database there is already an existing public synonym called PubCust. It queries the all_synonyms data dictionary view that contains a list of all synonyms as well as private synonyms along with which table they are associated with. In case a synonym exists it's very important, since there might be naming conflicts. Next, queries that reference the synonym will successfully execute. This question helps an administrator determine if the usage of synonyms is configured correctly and therefore, used within the database especially in a shared or multi-user environment.

24. Create a bitmap index on the column AccountType in the table Accounts.
CREATE BITMAP INDEX idx_accounttype_bitmap ON Accounts (AccountType);
Explanation
It will create a bitmap index called idx_accounttype_bitmap on the column AccountType in Accounts. The bitmap index should be used on columns which have relatively low cardinality. Cardinality is by definition the number of unique values. In an account-type column, there would most likely be only 'Savings' and 'Checking'. On general practicality even if there are other types usage on multiple conditions or filters will be very efficient. Bitmap indexes employ bitmaps for representing data to take the advantage of fast logical operations. Extremely useful in warehousing data and analytical queries.

25. Create a function based index to store the upper case of the CustomerID.
CREATE INDEX idx_upper_customerid ON Customers (UPPER(CustomerID));
Explanation
This query creates a function based index named idx_upper_customerid on the upper case version of the column named CustomerID in table Customers. Function based indexes allow indexing on the result of a function or expression as such, this kind of indexing can be much faster than standard indexes for some query performance cases when the function is frequently used within search conditions. For instance, take customers queries that based on their upper case IDs look up customers. The upper case result makes the indexes on it possible thus making queries that use such searches be optimized in this manner leaving efficiency in place without any alteration in the code of application.

26. Create a private synonym for Transactions as MyTransactions.
CREATE SYNONYM MyTransactions FOR Transactions;
Explanation
This statement defines a private synonym MyTransactions referring to the table Transactions. A public synonym is accessible to all users whereas a private synonym is accessible only to the user who defined it. This reduces the access of the table Transactions for the user from having to use the full name of the table in all their queries. This can prove helpful in scenarios where users often access shared tables and want to use shorter or more intuitive names in their queries. Private synonyms assist the process of writing queries and add convenience to the user.

Learn Using Sequences in Oracle SQL.