Basic relational database structures and SQL tuning techniques

Understanding the structures within a relational database management system (RDBMS) is critical to optimizing performance and effectively managing data. Here is a breakdown of the concepts with examples.

RDBMS structures

1. Partition

Partitioning in an RDBMS is a technique of dividing a large database table into smaller, more manageable parts, called partitions, without changing the application’s SQL queries.

Example

Let’s consider a table sales_records which contains sales data over several years. Division of this table by years (YEAR column) means that the data for each year is stored in a separate partition. This can significantly speed up queries that filter the partition key, e.g. SELECT * FROM sales_records WHERE YEAR = 2021because the database only searches the relevant partition.

2. Subpartition

Subpartitioning is dividing a partition into smaller parts called subpartitions. This is essentially another level of partitioning and can be used to further organize data within each partition based on a different column.

Example

Use sales_records table, you can partition the data by year and then sub-partition the data in each year by quarter. This way, data for each quarter of each year is stored in its subpartition, potentially improving the performance of queries for searches within a given quarter of a given year.

3. Local index

A local index is an index that exists on a partitioned table, where each partition has its own independent index. The scope of a local index is limited to its partition, meaning that each index contains only keys from that partition.

Example

If it is sales_records the table is divided by years, local index to customer_id column will create separate indexes for each year’s partition. Query filtering on both customer_id and year can be very efficient, because the database can quickly locate a partition by year and then use a local index to find records within that partition.

4. Global index

A global index is an index on a partitioned table that is not partition specific. It includes keys from all table partitions, providing a way to quickly search all partitions.

Example

Global index on customer_id column in sales_records table would allow quick lookups for records of a particular customer across all years without having to access each partition’s local index.

5. Create deterministic functions for the same input and known output

A deterministic function in SQL returns the same result every time it is called with the same input. This consistency can be exploited for optimization purposes, such as function-based indexes.

Example function

CREATE OR REPLACE FUNCTION get_discount_category(price NUMBER) RETURN VARCHAR2 DETERMINISTIC IS

BEGIN

    IF price < 100 THEN

        RETURN 'Low';

    ELSIF price BETWEEN 100 AND 500 THEN

        RETURN 'Medium';

    ELSE

        RETURN 'High';

    END IF;

END;

This function returns a discount category based on price. Because it is deterministic, the database can optimize calls to this function within a query.

6. Create a bulk load for large data sets

Bulk loading is the process of efficiently importing large amounts of data into a database. This is essential for initializing databases with existing data or periodically integrating large data sets.

Example

In Oracle, you can use SQL*Loader to bulk load data. Here is a simple command to load data from a CSV file into sales_records table.

Bash:

sqlldr userid=username/password@database control=load_sales_records.ctl direct=true

Control file (load_sales_records.ctl) defines how the data in the CSV file is mapped to the columns in sales_records table. The direct=true option specifies that SQL*Loader should use direct path loading, which is faster and uses fewer database resources than conventional path loading.

SQL tuning techniques

SQL tuning methodologies are essential for optimizing query performance in relational database management systems. Here is an explanation of the methods with examples to illustrate each one:

1. Explain plan analysis

An explanatory blueprint shows how a database executes a query, including its paths and data access methods. Analyzing the explanatory plan helps identify potential performance issues, such as full table views or inefficient joins.

Example

EXPLAIN PLAN FOR

SELECT * FROM employees WHERE department_id = 10;

Analysis of the results can reveal whether a query uses an index or a full table scan, guiding optimization efforts.

2. Collect statistics

Statistics collection includes gathering data about table size, column distribution, and other characteristics that the query optimizer uses to determine the most efficient query execution plan.

• Full statistics: Collect statistics for the entire table
• Incremental statistics: Collect statistics for parts of the table that have changed since the last collection

Example

-- Gather full statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS('MY_SCHEMA', 'MY_TABLE');

-- Gather incremental statistics

EXEC DBMS_STATS.SET_TABLE_PREFS('MY_SCHEMA', 'MY_TABLE', 'INCREMENTAL', 'TRUE');

EXEC DBMS_STATS.GATHER_TABLE_STATS('MY_SCHEMA', 'MY_TABLE');

3. Structure your queries for efficient joins

Structuring your SQL queries to take advantage of the most efficient join methods based on your data characteristics and access patterns is critical to query optimization. This strategy involves understanding the nature of your data, the relationships between different data sets, and how your application accesses that data. You can significantly improve query performance by matching your query design to these factors. Here’s a deeper look at what that entails:

Understanding your data and access patterns

• Amount of data: The size of the data sets you are joining affects which join method will be the most efficient. For example, hash joins may be preferred for joining two large data sets, while nested loops may be more efficient for smaller data sets or when there is an indexed access path.
• Data distribution and slope: Knowing how your data is distributed and whether there are asymmetries (eg, some values ​​are far more common than others) can influence the association strategy. Skewed data may require some optimizations to avoid performance bottlenecks.
• Indexes: The presence of indexes on the join columns can make nested loop joins more efficient, especially if one of the tables involved in the join is significantly smaller than the other.
• Choosing the right type of connection: Use inner joins, outer joins, cross joins, etc., based on the logical requirements of your query and the characteristics of your data. Each type of connection has its own performance implications.
• The order of the tables in the connection: In certain databases and scenarios, the order of table joins can affect performance, especially for nested loop joins where the outer table should ideally have fewer rows than the inner table.
• Filter early: Apply filters as early as possible in your query to reduce the size of the datasets that need to be joined. This can include subqueries, CTE (Common Table Expressions) or WHERE clause optimizations to narrow the data before it is joined.
• Use indexes efficiently: Design your queries to take advantage of indexes on join columns, where possible. This may include structuring your WHERE clauses or JOIN conditions to make efficient use of indexed columns.

Practical examples

• For merging large data sets: If you are joining two large data sets and know that the join will involve scanning large parts of both tables, structuring your query to use a hash join can be useful. Ensure that no table has a filter that could significantly reduce its size before the join, as this could make nested loop joins more efficient if one of the tables becomes much smaller after filtering.
• For indexed access: If you are joining a small table to a large table and the large table has an index on the join column, structuring your query to encourage nested loop joins can be useful. The optimizer will probably choose this join method, but it can ensure careful query structuring and hinting.
• Join sorting and filtering: Consider how join order and setting filter conditions can affect performance in complex queries involving multiple joins. Setting the most restrictive filters at the beginning of the query can reduce the amount of data that is joined in later steps.

By matching your query structure to the inherent characteristics of your data and the specific access patterns of your application, you can direct the SQL optimizer to choose the most efficient execution paths. This often involves a deep understanding of both the theoretical aspects of how different join methods work and practical knowledge gained from observing the performance of your queries on your particular data sets. Continuous monitoring and tuning are key to maintaining optimal performance based on changing data volumes and usage patterns.

• Example: If you are joining a large table with a small table and there is an index on the join column of the large table, structuring the query to ensure that the optimizer chooses a nested loop join may be more efficient.

4. Use Common Table Expressions (CTEs)

CTEs make your queries more readable and can improve performance by breaking complex queries into simpler parts.

Example

WITH RegionalSales AS (

    SELECT region, SUM(sales) AS total_sales

    FROM sales

    GROUP BY region

)

SELECT *

FROM RegionalSales

WHERE total_sales > 1000000;

5. Use global temporary tables and indexes

Global temporary tables store intermediate results for the duration of a session or transaction, which can be indexed for faster access.

Example

CREATE GLOBAL TEMPORARY TABLE temp_sales AS

SELECT * FROM sales WHERE year = 2021;

 

CREATE INDEX idx_temp_sales ON temp_sales(sales_id);

6. Multiple indexes with different column orders

Creating multiple indexes on the same set of columns, but in different orders, can optimize for different query patterns.

Example

CREATE INDEX idx_col1_col2 ON my_table(col1, col2);

CREATE INDEX idx_col2_col1 ON my_table(col2, col1);

7. Use hints

Hints are instructions embedded in SQL statements that direct the optimizer to choose a specific execution plan.

Example

SELECT /*+ INDEX(my_table my_index) */ *

FROM my_table

WHERE col1 = 'value';

8. Merging using numerical values

Numeric joins are generally faster than string joins because numeric comparisons are faster than string comparisons.

Example

Instead of joining on string columns, if possible, join on numeric columns like IDs that represent the same data.

9. Full table scan versus partition pruning

Use a full table scan when you need to access a significant portion of the table or when there is no matching index.

Use partition pruning when you query partitioned tables and your query can be limited to specific partitions.

Example

-- Likely results in partition pruning

SELECT * FROM sales_partitioned WHERE sale_date BETWEEN '2021-01-01' AND '2021-01-31';

10. SQL Tuning Advisor

SQL Tuning Advisor analyzes SQL statements and makes recommendations for improving performance, such as creating indexes, restructuring queries, or collecting statistics.

Example

In Oracle you can use DBMS_SQLTUNE package to run the SQL Tuning Advisor:

DECLARE

  l_tune_task_id VARCHAR2(100);

BEGIN

  l_tune_task_id := DBMS_SQLTUNE.create_tuning_task(sql_id => 'your_sql_id_here');

   DBMS_SQLTUNE.execute_tuning_task(task_name => l_tune_task_id);

   DBMS_OUTPUT.put_line(DBMS_SQLTUNE.report_tuning_task(l_tune_task_id));

END;

Conclusion

Each of these structures and techniques optimizes data storage, retrieval, and manipulation in an RDBMS, enabling efficient handling of large data sets and complex queries.

Each of these tuning methodologies targets specific aspects of SQL performance, from how queries are structured to how the database optimizer interprets and executes them. By applying these techniques, you can significantly improve the efficiency and speed of your database.