Illustration explaining SQL indexes, synonyms, and sequences for effective database optimization and management.

6.How do you manage indexes in a database?
Index management in the database involves creating, maintaining and optimizing indexes so that there is efficient query performance in exchange for storage and update costs. Proper management includes creating indexes on frequently queried columns, monitoring their usage and removing unnecessary indexes.
Key Steps in Index Management:
1. Creating Indexes:
Use the CREATE INDEX or CREATE UNIQUE INDEX statements to create indexes on often accessed columns. as given below. CREATE INDEX idx_employee_name ON employees(name);
2. Dropping Indexes:
Delete unused or redundant indexes in order to free storage and reduce overhead on modification of data:
DROP INDEX idx_employee_name;
3. Monitoring Indexes:
As a part of database utilities or queries monitor index usage and decide whether indexes help or hurt performance.
4. Rebuild Indexes:
For broken or stale indexes, apply rebuild commands such as ALTER INDEX. REBUILD to ensure efficiency.
5.Avoid Over-Indexing:
Too many indexes can inflate storage usage and degrade performance for INSERT, UPDATE and DELETE. Create indexes only when needed. Optimal query performance is achieved by actively managing indexes with minimum resource costs.

7.How do you improve query performance with indexes?
Indexes are one of the essential facilities to optimize query performance in SQL-based databases. They operate as a map and help the database locate some rows immediately rather than the full table scan. An index on one or more columns, which appear most frequently in WHERE clauses, join conditions, or sorting operations (ORDER BY) makes execution of those queries highly efficient.
Example
Let us assume that a table has been created and named as Employees and column EmployeeID receives queries very frequently. The process of creating an index on EmployeeID will enable searching of any row very fast from the table without scanning it. This can be performed as follows:
CREATE INDEX idx_employee_id ON Employees(EmployeeID);
Here
The CREATE INDEX statement creates an index named idx_employee_id on column EmployeeID.
• When you execute a query like this:
SELECT * FROM Employees WHERE EmployeeID = 101;
the database retrieves the appropriate row through its index.
Indexes are particularly useful for big tables but have trade-offs. While they speed up the read operation, write operations such as INSERT, UPDATE, and DELETE can get slow since the index must be updated too. So, selecting the appropriate columns to index based on patterns will lead to optimal performance with no unnecessary overhead.

8.How do you use EXPLAIN to analyze query performance with indexes?
The EXPLAIN statement is an excellent tool in learning how the database is executing your queries. You would know such things as whether or not the database used an index to access your information or if it has scanned an entire table to access it. This kind of information is very helpful in query optimization.
For example, assume that you have an Employees table and often search for some employee using their EmployeeID.
Then you can run this query with EXPLAIN:

EXPLAIN SELECT * FROM Employees WHERE EmployeeID = 101;
The results will tell you whether the database is using an index to find the row or scanning the whole table. If the database isn't using an index, you can create one like this:

CREATE INDEX idx_employee_id ON Employees(EmployeeID);
After adding the index, re-running the EXPLAIN command will show that the database now uses the index to perform the query thereby speeding it up. Use EXPLAIN to see how your queries are working and what to change to make them run more efficiently.

9.How do you create a sequence to generate unique numbers?
A sequence in SQL is used to create unique numbers, often for primary keys or other fields that require an unique identifier. Sequences are independent database objects that automatically increment based on the rules set. They are useful for creating unique values without manual intervention.
Creating a Sequence
Suppose you need to create unique EmployeeID numbers that start at 1001 and increment by 1.

CREATE SEQUENCE EmployeeID_Seq
START WITH 1001
INCREMENT BY 1
NOCACHE;
•CREATE SEQUENCE: It defines a new sequence called EmployeeID_Seq.
•START WITH 1001: It states the initial value for the sequence.
•INCREMENT BY 1: It states to increase the sequence value by 1 each time you want a new number in the sequence.
•NOCACHE: It prevents the preallocation of sequence values. Instead, it produces a value each time it is needed (useful in avoiding gaps in sequences).
Generating the Next Value
The following is how you can create the next value in the sequence:

SELECT EmployeeID_Seq.NEXTVAL FROM DUAL;
This returns the next unique number in the sequence. For example, the first call would return 1001 the second 1002, and so on.
Application in Table Inserts
You can use the sequence directly in INSERT statements:
INSERT INTO Employees (EmployeeID, Name, Age)
VALUES (EmployeeID_Seq.NEXTVAL, 'John Doe', 30);

Why Use Sequences?
Sequences ensure uniqueness are very efficient, and eliminate the possibility of duplication when inserting rows into tables. They are of special use in multiuser environments where concurrency can result in duplicate values without adequate precaution.

10.How do you use NEXTVAL and CURRVAL to get sequence values?
Using NEXTVAL and CURRVAL in SQL with Example
NEXTVAL and CURRVAL are pseudocolumns in SQL used to work with sequences that generate unique numeric values.
NEXTVAL retrieves the next value in the sequence and increments it, while CURRVAL gives the last value generated by NEXTVAL in the current session. However, CURRVAL can only be used after calling NEXTVAL in the same session.
For example, create a sequence named order_seq as follows:

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
Each time you reference order_seq.NEXTVAL it returns a next value such as 1 and increments the sequence. Use this in an INSERT statement like the following:

INSERT INTO orders (order_id, customer_name) VALUES (order_seq.NEXTVAL, 'John Doe');
Later in the same session you will be able to obtain the last value that was generated by referring to CURRVAL:

SELECT order_seq.CURRVAL FROM dual; -- Output: 1
Emphasize during interviews that NEXTVAL guarantees uniqueness even in concurrent access across sessions, and since sequence values are non-transactional they can't be rolled back once they are generated. Thus, sequences are the best option to use for creating unique IDs.

11.How do you manage sequences in SQL (e.g., ALTER, DROP)?
Managing sequences in SQL is easy and allows you to alter or drop them when you need to.
To alter an existing sequence, you can use the ALTER SEQUENCE command.
For instance, you can change the amount of step through which the sequence increments, a new limit or enable it to restart when reaching the maximum value. If initially you have defined a sequence to increment by 1 but then want to set it to increment by 10 you could issue
ALTER SEQUENCE my_sequence INCREMENT BY 10;
without disrupting its value.
A sequence can be dropped if no longer needed, simply with the DROP SEQUENCE command.
You may drop a sequence such as the one created when a project is finished no longer in use by a sequence and instead you may issue the command as shown:
DROP SEQUENCE my_sequence;
Dropping the sequence is done when it has no active database objects which rely on the sequence as well as dropping sequences keep the database organized and up-to-date for change.

12.What is the purpose of a synonym in SQL?
A synonym in SQL is merely an alias or some other name of database object which can be tables, view, sequence as well as stored procedure. So basically it serves to easier access, especially when that objects can be located in any number of schemas or any number of databases. Having created synonym, one can get at object by a shorter convenient name while making query a cleaner easier to maintain:
Also synonyms introduce a kind of abstraction-they will now hide the name of the actual object or even its location, which is very useful in cases where the names of objects in complex systems change and a person has to refer to objects but these objects are under another schema, so he could not refer every time to that full schema name. For example, one does not have to write 'HR.employees' every where. You can make that a synonym in that particular area so it would then even appear in the actual query itself.
Synonyms can also help if you want to refer to objects across different databases or even in distributed systems. With use of synonyms, you ensure that even if the underlying object changes-for example, its name or location-the synonym remains the same thus, the effect on your applications will be minimal.

13.How do you create a synonym for a table or view?
To create a synonym for a table or view in SQL you use the CREATE SYNONYM statement. This allows you to refer to a table or view by a different name, simplifying queries and maintaining flexibility.
The basic syntax is:
CREATE SYNONYM synonym_name FOR schema_name.object_name;
For example, if you have a table 'HR.employees', you can create a synonym 'emp' like this:
CREATE SYNONYM emp FOR HR.employees;
Now you write 'HR.employees' in your query. Use 'emp':
SELECT * FROM emp;
It is also more concise and manageable with queries.

14.How do you drop a synonym?
To drop a synonym in SQL, one uses the DROP SYNONYM statement. This deletes the synonym from the database but does not affect the object underneath it-the table or view that was being referred to by the synonym.
The basic syntax is:
DROP SYNONYM synonym_name; For example if you had created a synonym emp for the 'HR.employees' table you can delete it using the following command:
DROP SYNONYM emp;
After this, synonym emp will get deleted and any query you use which references it, then the error will result unless you create a synonym again or refer to the object directly.

15.How do you use synonyms to reference database objects?
SQL Synonyms will give an alias or shorten a name for the objects in the database such as the tables, views, sequence and even the stored procedure. These turn helpful in writing fewer complex queries since you could utilize even more abbreviated or meaningful names of these objects if they come within the other schemas or other databases. This is how one can make use of them in order to address the database objects.
1.CREATE SYNONYM Use the CREATE SYNONYM statement to create a synonym for a database object.
CREATE SYNONYM synonym_name FOR schema_name.object_name;
To create a synonym for the HR.employees table
CREATE SYNONYM emp FOR HR.employees;
2.Use the Synonym After creating the synonym you can refer to the object using the synonym name in your queries.
SELECT * FROM emp;
Instead of writing the HR.employees, you just use emp, and it cuts down on the length of the query and is easier to maintain.
3.Cross-Schema Access: Synonyms allows you to reference objects in other schemas without having to know the name of each schema at that time. For instance, if HR.employees is located in another schema, then creating a synonym such as emp allows referring to it as if it were located in your schema:.
4.Simplify Complex Names: Synonyms can be used to make long or complex object names easier to read in a query. In short, synonyms abstract the underlying structure of your database objects and provide a much more flexible, user-friendly way to refer to them in SQL queries.

16.How do you handle schema changes involving synonyms?
Whenever schema changes are applied for instance renaming, moving objects to some other schema or changing type of the object, synonyms shall be updated or recreated in order to function properly. Dropping and then recreating.
for example: DROP SYNONYM emp;
CREATE SYNONYM emp FOR HR.new_employees;
If an object is moved to another schema, you must modify the synonym to point to the new schema.
DROP SYNONYM emp;
CREATE SYNONYM emp FOR Sales.employees;
If the object type is changed, for example from a table to a view re-create the synonym as the new object type
DROP SYNONYM emp;
CREATE SYNONYM emp FOR HR.employee_view;
These updates mean that synonyms will keep on working following schema changes.

17.How do you create an index on an expression or function?
When schema changes are made including rename, object movement to a new schema or type change for objects, synonyms must be updated or rerun so functionality is ensured. If an object is renamed the synonym should be dropped and recreated with the new object name.
For example:
DROP SYNONYM emp;
CREATE SYNONYM emp FOR HR.new_employees;
If the object is moved to a different schema then you have to update the synonym accordingly to the new schema.
DROP SYNONYM emp;
CREATE SYNONYM emp FOR Sales.employees;
If the type of the object changes from table to view, you have to recreate the synonym with new type of the object.
DROP SYNONYM emp;
CREATE SYNONYM emp FOR HR.employee_view;
Synonyms will work after schema changes.

18.How do you look at the database information for indexes?
A user wants to see how indexes are accessed as well as troubleshoot why they don't work. Most database systems provide system views or commands that can be used to print out information about the indexes, including their name, associated table and column name as well as the number of times they were referenced.
For example, you can query the USER_INDEXES and USER_IND_COLUMNS views in Oracle to obtain information about indexes. USER_INDEXES shows all indexes in your schem, while USER_IND_COLUMNS shows the columns indexed.
Here is a query that fetches index information for a table named employees:
SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

To see the indexed columns:
SELECT index_name, column_name
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'';

You can see the index information by using SHOW INDEX in MySQL.
SHOW INDEX FROM employees;
It gives the details about index name, included columns and whether the index is unique or not.
For PostgreSQL, you query the pg_indexes system catalog.
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE tablename = 'employees';
These commands help you to know the structure and use of indexes. They help you identify places where optimization of queries or elimination of unnecessary indexes could be important for efficient performance of a database.

19.How do you manage and optimize sequences in high-transaction environments?

Managing and optimizing sequences in high-transaction environments is critical to maintaining database performance and ensuring that operations operate uninterruptedly. Since sequences generate unique values primarily for primary keys an inefficient sequence can negatively affect overall transaction throughput.
One is to increase the cache size for the sequence.
A bigger cache reduces disk I/O because it preallocates more values in memory, which minimizes contention.
For example:
ALTER SEQUENCE order_seq CACHE 1000;
The other is to adjust the increment value.
If the sequential numbering is not an issue then a bigger increment value can reduce the conflicts of concurrent transactions:
ALTER SEQUENCE order_seq INCREMENT BY 10;
Monitoring sequence usage regularly ensures the sequences satisfy application requirements. If there is an allowable gap between sequence values, sequences may be reserialized to provide optimal storage. Furthermore, using sequences only when available and batch requests can greatly reduce overhead. Finally, wraparound values can be managed through proper setting of a maximum to ensure continuity instead of reset.
Through implementation of these strategies, you may optimize the behavior of a sequence for smooth performance in the high-transaction environment.

20.How do you use CREATE INDEX with options like ASC or DESC?
You use the CREATE INDEX statement with options like ASC (ascending) or DESC (descending). This lets you specify the order in which columns in an index are sorted. The feature is especially useful when you have queries that sort and filter frequently in a specific order, helping improve performance.
Syntax:
CREATE INDEX index_name ON table_name (column1 ASC, column2 DESC);
In this scenario, column1 is indexed in ascending order and column2 is indexed in descending order. The sort options are used to improve query performance for specific ORDER BY clauses
. Use Case
Suppose that you have a table named sales with columns: sale_date and amount. A large number of queries yield sales ordered in descending by date and then in ascending by amount.

You could create an index as follows:
CREATE INDEX idx_sales_date_amount
ON sales (sale_date DESC, amount ASC);
If the database has an index to access it faster, this query like the one illustrated here is executed:
SELECT sale_date, amount
FROM sales
ORDER BY sale_date DESC, amount ASC;
You combine ASC and DESC in an index to fine-tune database structure toward patterns of query execution to enhance performance of data access where data retrieval is sorted.

21.How do you optimize index usage in large databases?
Optimization of index usage in large databases is a very important requirement for maintaining high query performance and efficient storage. Proper management of indexes minimizes the time taken to respond to queries and ensures balanced resources for the system.
This can be achieved in the following ways:
1. Index columns that will frequently appear in the SQL query. Identify columns that are present in WHERE, JOIN, ORDER BY or GROUP BY clause and make indexes on columns.
Avoid indexing rarely accessed columns to avoid storage.
CREATE INDEX idx_customer_name ON customers (name );
2. Composite Indexes: One composite index can improve query performance if the query also filters by more than column.
Ensure that the columns of the index are in the order of filters in queries.
CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);
3. Monitor and Drop Unused Indexes: Monitor index usage by using database tools or system views to drop unused indexes that waste storage.
DROP INDEX idx_unused_index;
4. Use Covering Indexes: Build indexes such that they cover all the columns queried so that the table is not accessed.
CREATE INDEX idx_full ON orders (order_date, customer_id, order_total);
5. Partition Indexes: Partitioning of indexes in a big table runs better as the data is indexed only to those partitions
Big databases may remain in their optimum levels if constantly checked and optimized and also kept abreast of the needs of the query.

22.How will you handle index fragmentation and performance degradations?
Index fragmentation as well as performance degradations must be handled for maintaining a database's efficiency. Because, over a period when data gets inserted, updated or deleted the indexes get fragmented causing slower performance of queries and space usage increases. How to deal with it?
1. Index Fragmentation
Using database-dependent tools or queries: fragmentation level.
SQL Server. sys.dm_db_index_physical_stats Returns fragmentation information;
SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'DETAILED');
2. Rebuild Index
For those highly fragmented indexes where the fragmentation level > 30%. Do a complete rebuild and reorganizes index pages and compacts. Generally, these are performed in large databases
ALTER INDEX idx_name ON table_name REBUILD;
3. Rebuild Indexes: When fragmentation is moderate that may be 5 to 30% rebuild of the index rebuilds the pages at the leaf level of an index hence consuming more resources.
ALTER INDEX idx_name ON table_name REBUILD ;
4. Use Fill Factor: It uses an appropriate fill factor of an index at the time of creating or rebuilding indexes such that it leaves out space for future inserts that will not increase fragmentation:
CREATE INDEX idx_name ON table_name (column_name) WITH (FILLFACTOR = 80);
5. Index Maintenance Scheduling: Schedule regular maintenance tasks on indexes during off-peak hours in order not to impact performance.
Monitor and correct fragmentation in indexes so that they work correctly, thereby supporting faster queries and efficient storage usage.

23.How do you apply index hints to regulate the execution of queries?
How to use Index Hints in Oracle for Controlling Execution Plans
An index hint in Oracle is used to force the optimizer to use a specific index in the execution of a query. It can be very handy when the optimizer has chosen an execution plan that is less efficient than another or when you are certain that a particular index will provide better performance.
To apply an index hint in Oracle, you'll use the /*+ INDEX(table_name index_name) */ syntax, which you put in your SELECT statement. An INDEX hint tells the optimizer to apply a specific index for the table in your query.
Syntax:
SELECT /*+ INDEX(table_name index_name) */ column_names
FROM table_name
WHERE condition;
Example:
Let's assume you have a table employees with an index idx_employee_name on the name column. If Oracle's optimizer selects a suboptimal index or doesn't use an index, you can explicitly tell it to use idx_employee_name as follows:
SELECT /*+ INDEX(employees idx_employee_name) */ *
FROM employees
WHERE name = 'John';
This will make use of the idx_employee_name index on the employees table. Thus, it will reap the advantage of having an optimized query performance. A word of caution, though: the index hints restrict the adaptability of the optimizer. In a situation like that of the structural change this is highly disastrous.
Other Database Notes:
For MySQL, the index hints can be applied with USE INDEX, FORCE INDEX or IGNORE INDEX clauses.
•SQL Server Index hints as parameter to WITH (INDEX) clause
•PostgreSQL PostgreSQL does not support index hints and instead of it relies on statistics and query optimizer to decide on index usage.

24.How do you apply sequences in the INSERT statement?
Applying Sequences in the INSERT Statement in Oracle.
In Oracle, sequences are primarily used to produce unique numbers, usually for primary key columns. A sequence is an independent object that generates a series of numbers. This number can be applied directly into INSERT statements to fill columns automatically.
To use a sequence in an INSERT statement, you call the NEXTVAL function of the sequence to produce the next available value.
That value can then be used to insert into the appropriate column.
Syntax:
INSERT INTO table_name (column1, column2,.)
VALUES (sequence_name.NEXTVAL, value2,.);
Suppose you have a sequence called emp_seq for generating employee IDs and a table employees with the columns employee_id, name and salary. To add a new record to the employees table by using the sequence for the employee_id column, the SQL statement will be as follows:
INSERT INTO employees (employee_id, name, salary)
VALUES (emp_seq.NEXTVAL, 'John Doe', 50000);
In this case, emp_seq.NEXTVAL creates the next value from the emp_seq sequence and puts it in the employee_id column. Other Databases Notes:
MySQL: MySQL does not have sequences as in Oracle. Here, it is using AUTO_INCREMENT to auto-generate unique values for columns of the primary key.
SQL Server: SQL Server has IDENTITY columns to auto-generate unique values as in MySQL with the help of AUTO_INCREMENT
PostgreSQL: PostgreSQL has sequence and the NEXTVAL function same as Oracle. You can utilize sequence_name.NEXTVAL in the INSERT statement.

25.How does the database manage automatic index creation?
Managing Automatic Index Creation by the Database in Oracle.
The Oracle database auto-creates some indexes to support integrity constraints, like the primary key and unique constraints. These indexes created automatically enhance the performance of operations such as INSERT, UPDATE or DELETE since the database must have uniqueness or enforce foreign relationships.
Types of Automatically Created Indexes:
1.Primary Key Index: When a primary key constraint is created, Oracle automatically creates a unique index on the primary key column(s).
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR2(100)
);
Here, Oracle will automatically create a unique index on the employee_id column to enforce the primary key constraint.
2.Unique Index: When an attribute is specified as unique on a column or set of columns, Oracle also creates a unique index that prevents duplicate values.
For Example:
CREATE TABLE employees (
employee_id INT,
email VARCHAR2(100) UNIQUE
);
In this case, Oracle will create a unique index on the email column to enforce uniqueness for email addresses.
3.Foreign Key Index: While foreign key constraints never creates an index in some circumstances, in most of the scenarios it is preferred that user will create indexes manually in columns which are assigned to a foreign key for performance enhancement. It never does so unless the foreign key constraint with the option ON DELETE CASCADE has been defined or else mentioned to the Oracle specifically
Control Over Automatic Indexing
Although the database automatically creates indexes for integrity constraints, you should still be in control of how those indexes impact performance. Regularly review index usage and eliminate or disable unused indexes. Tools like the DBMS_STATS package can help gather statistics so that the optimizer makes better decisions.
Other Databases' Notes
•MySQL: MySQL creates indexes for primary keys and unique constraints automatically. For foreign keys, MySQL only creates an index when the index is explicitly specified or when the index is needed for optimization.
•SQL Server: SQL Server automatically creates a unique index for the primary key and for unique constraints. Indexes for foreign keys must sometimes be manually created.
• PostgreSQL: PostgreSQL creates indexes automatically for primary and unique constraints. Foreign keys, however, do not automatically create indexes, and they should be manually added for performance reasons.