How to determine which view is the right view to use in Oracle?

Database views are a handy tool to help obfuscate complex joins from developers and analysts. However, knowing which view to use can be tricky, especially when faced with multiple views with similar-sounding names. After all, you don’t want to join various tables only to discover that all the information you needed was in just a single table. But how do you know which view has the data you are after and will allow you to get it with the minimum number of joins?

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands any references to a view within a query, turning it into a subquery in the original statement that displays the full query text behind that view. This trick lets you see where and how the needed data will be retrieved.

Let’s look at an elementary example of this procedure in action, using a SELECT * statement on a view called SALES_V.

SET serveroutput ON 
DECLARE 
    l_clob CLOB; 
BEGIN 
    dbms_utility.Expand_sql_text(input_sql_text => 'SELECT * FROM sales_v', output_sql_text => l_clob); 
    dbms_output.Put_line(l_clob); 
END; 
/

The result of this procedure call is the following output.

SELECT "A1"."order_id" "ORDER_ID", 
       "A1"."time_id"  "TIME_ID", 
       "A1"."cust_id"  "CUST_ID", 
       "A1"."prod_id"  "PROD_ID" 
FROM   (SELECT "A3"."order_id" "ORDER_ID", 
               "A3"."time_id"  "TIME_ID", 
               "A3"."cust_id"  "CUST_ID", 
               "A3"."prod_id"  "PROD_ID" 
        FROM   "SH"."sales" "A3", 
               "SH"."products" "A2" 
        WHERE  "A3"."prod_id" = "A2"."prod_id") "A1"

The subquery with the alias A1 above is the view definition for SALES_V.

It’s a simple two-table join between SALES (alias A3) and PRODUCTS (alias A2). Although the view only returns columns from the SALES table (A3), it does come with the overhead of a join. The execution plan for our simple SELECT * query below shows that.

PLAN_TABLE_OUTPUT                                                                          
__________________________________________________________________________________________ 
Plan hash VALUE: 2857462611                                                                
 
---------------------------------------------------------------------------------------    
| Id  | Operation                  | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |    
---------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT           |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|*  1 |  HASH JOIN                 |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|   2 |   TABLE ACCESS STORAGE FULL| PRODUCTS |  4999 | 29994 |     2   (0)| 00:00:01 |    
|   3 |   TABLE ACCESS STORAGE FULL| SALES    | 10000 |   263K|     5   (0)| 00:00:01 |    
---------------------------------------------------------------------------------------    
 
Predicate Information (IDENTIFIED BY operation id):                                        
---------------------------------------------------                                        
 
   1 - access("S"."PROD_ID"="P"."PROD_ID")

In this case, it would be best to find an alternative view that only accesses the SALES table or access the SALES table directly. Below is the plan for a direct select statement from the SALES table, and as you can see, the cost of this plan is lower.

EXPLAIN PLAN FOR
SELECT s.order_id, s.date_id, s.cust_id, s.product_id 
FROM sales s;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT                                                                      
______________________________________________________________________________________ 
Plan hash VALUE: 781590677                                                             
 
-----------------------------------------------------------------------------------    
| Id  | Operation                 | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |    
-----------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT          |       | 10000 |   263K|     5   (0)| 00:00:01 |    
|   1 |  TABLE ACCESS STORAGE FULL| SALES | 10000 |   263K|     5   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. This blog post is part of that series. Other blogs in this series include How to add a SLEEP COMMAND to your Oracle PL/SQL code and How diff optimizer statistics.

How do I add a SLEEP to a PL/SQL Stored Procedure?

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP().

Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.

    DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_LOCK.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
    END;
   /
 
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
 
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

However, the sleep function was part of the DBMS_LOCK package, which is not granted to PUBLIC, by default, due to the other more powerful functions inside that package. That means you had to beg the DBA or the security team to give you access to this package just to put your session to sleep for a few minutes.

DBMS_SESSION.SLEEP()

Things got a lot easier starting in Oracle Database 18c, as the sleep function is now available in the DBMS_SESSION package, which is granted to PUBLIC by default. That means you can call the function without any additional privileges. Plus, the function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code!

DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_SESSION.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
   END;
   /
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

How to create a parameterized view in Oracle

Database views have been used for decades to help simplify both ad-hoc queries and reporting for developers, analysts, and end-users. But the problem with defining views is they tend to be either too specific (not easily reused) or not specific enough (too generic to be performant).

Imagine we need a view to help developers quickly find the details about orders waiting to ship, which have a total value of $100 or more. How would you create such a view?

Would you create a particular view that returns only the columns needed for this scenario with restrictive where clause predicates that limit the orders to only those that have not shipped and have a value greater than $100?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT i.order_id, i.product_id, i.price, i.description 
FROM   orders o, order_items i
WHERE  o.order_status > 6   -- orders waiting to ship
AND    o.order_total >= 100
AND    o.order_id = i.order_id;

Or do you create a more generic view that could be used for other reports and queries, even though it won’t be as performant as the dedicated view above?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT o.order_status, o.order_total,
       o.cust_id, o.order_date, o.rep,       
       o.order_mode, o.promotion_id, 
       i.* 
FROM   orders o, order_items i
WHERE  o.order_id = i.order_id;

Regardless of your approach, you will compromise either reusability or performance.

Starting in Oracle Database 19c (19.7), you no longer have to compromise, as Oracle introduced parameterized views or SQL Table Macros.

SQL table macros are expressions found in a FROM clause that acts as a polymorphic or parameterized view. Let’s look at how this can help us with the view we need to create to find the details about orders waiting to ship, which have a total value of $100 or more.

Creating a SQL Macro is similar to creating a PL/SQL function with an additional SQL_MACRO clause. The SQL MACRO clause can take an argument SCALAR (available from 21c onwards) or TABLE (19c onwards), but if you leave it blank, it defaults to a TABLE macro.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 ...
 RETURN stmt;
END orders_waiting_to_ship;
/

A SQL Macro always returns the view you define as text (VARCHAR, CHAR, or CLOB). The database will resolve that view definition and makes it part of the SQL statement that calls the SQL Macro. I’m capturing the view definition in a CLOB using the variable stmt above.

Let’s add the text for the view and the parameters we want to pass.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship(order_value INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
  SELECT i.*
  FROM   orders o, order_items i
  WHERE  o.order_status > 6
  AND    o.order_total >= order_value
  AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_waiting_to_ship;
/
 
FUNCTION ORDERS_WAITING_TO_SHIP compiled

In the code example above, I’m using the more selectivity view definition, but instead of specifying the order_total to be $100 or higher, I’m going to parameterize the value of the order. That way, we can reuse this SQL Table Macro regardless of what order_value is needed. You will notice I have also added a parameter to the function called order_value, which the user will pass in when they call the function.

You will also notice that my function ORDERS_WAITING_TO_SHIP compiled without errors. This doesn’t mean my view definition or syntax is correct. You will only see an error at runtime. Remember, a SQL TABLE Macro produces your view definition as text that the optimizer will insert into the SQL statement that calls it.

It’s straightforward to call a SQL Table Macro. The Table Macro goes in the FROM clause of the SQL statement. In the example below, I’m calling our orders_waiting_to_Ship SQL Macro and passing it the value 100.

 SELECT *
FROM orders_waiting_to_ship(100) 
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION    
___________ _____________ ________ ______________ 
      10110            10       23 lipstick       
      10110             7       17 Lip gloss      
      10110             8       30 Mascarra
      10110             1       35 Foundation
      20209             9       15 Blusher        
      20209            10       23 lipstick  
      20209             1       35 Foundation
      20209             2       32 Coverup
      30307             7       17 Lip gloss      
      30307             8       30 Mascarra       
      30307             1       35 Foundation       
      30307             2       32 Coverup

We could take it a step further and generalize our SQL Table Macro a little more by parameterizing the order_status and the order_value; that way, our developers can use it to check the status of shipped orders and orders waiting to ship.

 CREATE OR REPLACE FUNCTION orders_chk(order_value INTEGER, stat INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
 SELECT i.*
 FROM   orders o, order_items i
 WHERE  o.order_status = orders_chk.stat
 AND    o.order_total >= orders_chk.order_value
 AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_chk;
/
 
FUNCTION ORDERS_CHK compiled
 
SELECT *
FROM orders_chk(10,6)
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION     ......
___________ _____________ ________ ______________ __________ 
      10110            10       23 lipstick        ......
      10110             7       17 Lip gloss       ......
      10110             8       30 Mascarra        ......
      10110             1       35 Foundation      ......
      10111             9       15 Blusher         ......         
      10112             1       35 Foundation      ......
      10113             1       17 Lip gloss       ......

There are several restrictions on SQL TABLE Macros you should be aware of:

    • When used directly in a SQL statement, the SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINE.
    • A SQL macro always runs with invoker rights.
    • SQL macros in views are always executed with the privileges of the view owner.
    • SQL macros can’t be used in virtual column expression, function-based indexes, editioning views, or materialized views.
    • SQL macros can’t be used in type methods.

How to watch Database World 2023 sessions

Over the last two months, Oracle has taken our Database World Conference on the road with events in San Francisco, Singapore, Toyko, London, and São Paulo. The Oracle Database product management team delivered these one-day technical events and covered a variety of topics, including giving folks a sneak preview of Oracle Database 23c.

If you couldn’t make it in person or get to all the sessions you wanted, don’t worry, as Oracle has made the technical sessions available online.

Don’t miss this chance to check out what you can expect in 23c to help make apps simple to build and run, including the keynote by Juan Loaiza.

Oracle has also made Oracle Database 23c FREE – developer Release available to try out all the new features and capabilities described in these sessions. You can download 23c FREE straight from the Internet with no oracle.com user account or license click-through requirements as a Container Image, VirtualBox VM, and Linux RPM installation file.

How to use DBMS_STATS DIFF_TABLE_STATS functions

In 11g, Oracle introduced the DBMS_STAT.DIFF_TABLE_STATS functions to help you compare two sets of statistics for a table along with all its dependent objects (indexes, columns, partitions).

There are three versions of this function depending on where the statistics being compared are located:

  • DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (compares statistics for a table from two timestamps in the past)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (compares pending statistics and statistics as of a timestamp or statistics from the data dictionary)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (compares statistics from a user statistics table and the data dictionary, from two different user statistics tables, or a single user statistics table using two different STATSIDs)

The functions return a report that has three sections:

  1. Basic table statistics
    The report compares the basic table statistics (number of rows, blocks, etc.).
  2. Column statistics
    The second section of the report examines column statistics, including histograms.
  3. Index Statistics
    The final section of the report covers differences in index statistics.

Statistics will only be displayed in the report if the difference in the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the functions (PCTTHRESHOLD); the default value is 10%. The statistics corresponding to the first source, typically the current table stats in the data dictionary, will be used to compute the differential percentage.

The functions also return the MAXDIFFPCT (a number) along with the report. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics.

Let’s look at an example.
Continue reading “How to use DBMS_STATS DIFF_TABLE_STATS functions”

Five things you might not know about Oracle Database

Today I had an opportunity to catch up with old friends and colleagues when my local Oracle User Group –  Northern California Oracle User Group (NoCOUG), held its first in-person event in three years.

In my session, we looked at five things you might not know about the Oracle Database or might have forgotten. For each topic, I explained the functionality and demonstrated the benefits using real-world examples. The topics covered apply to anyone running Oracle Database 11g and up, including Standard Edition, with only a few minor exceptions.

Many folks asked for the slides after the session, so I thought it would be helpful to post them here.

 

 

How to watch Oracle CloudWorld 2022 Technical Sessions

Just over a month ago, we said goodbye to Las Vegas and Oracle CloudWorld 2022.

If you couldn’t make it in person or couldn’t get to all the sessions you wanted, don’t worry, as Oracle has made over 50 critical database technical sessions available on YouTube.

Here are some of the sessions I think you might find worth a watch:

Andy Mendelsohn’s Solution Keynote on Oracle Database 23c. In his keynote, Andy shared details on Oracle Database 23c, the next long-term support release for the database. 23c was code-named App Simple because the primary goal of this release was to assist developers in building next-generation mission-critical, high-performance database apps.

Juan Loaiza’s Solution Keynote on JSON Relational Duality. Juan’s session focuses on one of the marque features of Oracle Database 23c, JSON Duality Views, which promises to provide use-case simplicity of JSON with multi-use case power of relational. With the help of Tirthankar Lahiri and Beda Hammerschmidt, Juan explains how this new approach solves the age-old problem of object-relational mismatch.

Jeff Smith’s technical session Tips and Tricks for the Oracle Database Developer[LRN1523], where Jeff explores Which features of Oracle SQL Developer, Oracle SQL Developer Web, Oracle SQLcl, and Oracle REST Data Services will save database developers the most time?

Vlad Haprian’s session explains Operational Property Graphs [LRN3789] and how they are part of the standard SQL in Oracle Database 23c. He includes some great, simple-to-follow examples too.

If you are looking for more information on our in-database microservices support, you will want to check out Deepak Goel’s session on Distributed Transactions for Kubernetes Microservices [LRN3484].

As always, there were several great sessions by Conor McDonald, including one where he shared his Five-Step Guide to SQL Tuning [LRN3790].

If performance-related sessions are what you are after, then you might want to check out my session on Harnessing the Power of Oracle Database Optimizer Hints[LRN1479] and Nigel Bayliss’s session on Automatic Indexing.

If you are interested in knowing, How to Write Great SQL That’s Easy to Maintain, check out Chris Saxon and Alex Nuijten’s session.

Finally, if you want more details on what to expect in 23c and the features that should make your life a lot easier, you should check out Gerald Venzl’s session on Oracle Database application development: what’s new, what’s next?

And don’t forget to save the date for CloudWorld 2023, which is happening September 18–21 in Las Vegas.

How to do a Fuzzy Text Search on JSON #JoelKallmanDay

More and more apps I deal with store data as JSON documents in the Oracle Database. It is exceptionally convenient for the developers but doesn’t always make it easy to know exactly what data we have stored.

The good news is that Oracle offers multiple ways to help you understand precisely what data you have stored in those JSON documents. For example, you can use the built-in JSON Data Guide, which will trawl through all your documents and return a list of all the attributes you have stored.

But suppose you are interested in searching through your documents and only returning those that contain a particular word, value, or a variation thereof. In that case, you will want to take advantage of Oracle’s fuzzy text search or approximate string matching.

Imagine we have a table that stores movie reviews as JSON documents. I’m doing this demo in 19c, so I’m using a VARCHAR2 column, but from 21c onwards, you can use a JSON column.

CREATE TABLE movie_reviews( 
                     title varchar2(200),
                     cust_id NUMBER(26), 
                     cust_reviews varchar2(32000)
                     CONSTRAINT cr_is_json CHECK (cust_reviews IS JSON));

If you really want a JSON column in 19c, you can take advantage of this trick I learned from @Connor_mc_d.

 CREATE TABLE movie_rewiews( title varchar2(200), 
                             cust_id NUMBER(26), 
                             cust_reviews BLOB,
                             CHECK (cust_reviews IS json format oson));

Each review document contains details on the movie id, the star rating, and the review.

{“movie_id”: 5641,
“star_rating”: 1,
"Feedback":" Loved the tv show, but hated the movie. I am so disappointed."
}

We have been asked to find all the reviews that contain the word disappoint or variations of it. To facilitate a fuzzy text search, we need to create a text-based index on the feedback column inside the review documents.

CREATE SEARCH INDEX review_search_ind ON movie_reviews(cust_reviews) FOR JSON;

Once the index is created, we can run a fuzzy text search using the following query:

SELECT m.title, m.cust_reviews.feedback AS customer_review
FROM   movie_reviews m
WHERE  JSON_TEXTCONTAINS(m.cust_reviews, '$.feedback', 'fuzzy((disappoint))');

This results in the following entries being returned.

TITLE                    COMMENTS
-------------------- ----------------------------------------------------------------
Can You Ever Forgive Me? This movie was so disappointing
Top Gun                  Tom Cruise never disappoints. Definitely worth a watch.
Vice                     Perry’s performance in this movie is just so disappointing
Baywatch                 Loved the tv show but hated the movie. I’m so disappointed.
La La Land               Rent this movie you won’t be disappointed!
Batman                   Complete Disappointment

Alternatively, you can use the abbreviated syntax, which will return the same results as above:

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '?disapoint');

You can also use the stem search operator $. That will match verb forms sharing the same stem, so $disappoint will match “disappointing,” “disappointed,” and “disappoints,” but not “disappointment.”

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '$disapoint');

This blog was made possible by the lovely Roger Ford, the product manager for Oracle Text and JSON, who has taught me everything I know about text searches in the Oracle Database.

Looking forward to Oracle CloudWorld

There are only 2 days to go until Oracle CloudWorld in Las Vegas, the largest gathering of Oracle customers, partners, developers, and technology enthusiasts of the year! That’s right, folks; we are finally back in person.

Of course, the database team will be there. You will have multiple opportunities to meet with us in one of the informative executive keynotes, technical sessions, customer sessions, or hands-on labs.

But what can you expect from this year’s event?

To help whet your appetite, here are some of the things I’m looking forward to at this year’s CloudWorld.

Reconnecting with the community

If you have ever been to an Oracle OpenWorld conference, you will know there are always plenty of chances to network throughout the day and into the evening. But this year, after two years of pandemic living, I reckon it’s more important than ever to spend time face to face with friends and colleagues to catch up and to learn from one another. That’s why I’m looking forward to networking on the expo floor, meeting customers in the Executive Briefing Center (formerly CVC), hanging out in our Expert Lounges, and kicking back with friends and customers at the legendary Oracle Party featuring the Steve Millar Band.

Getting Inspired

There is nothing quite like attending an Oracle Keynote by Larry Ellison to reignite my love of Oracle technologies. Larry never fails to deliver insights on our end-to-end data strategy and the latest on databases, apps, analytics, and our cloud services.
There will be three other Keynotes along with Larry’s this year featuring Oracle customers and leaders offering additional insights and inspiration on tech and apps.

I’m particularly keen to see Andy Mendelsohn and Juan Loaiza’s Solution Keynotes. Andy will share details on Oracle Database Directions on Tuesday, October 18th, at 12:15 pm. While Juan will be introducing new capabilities that are Revolutionizing Objects, Documents, and Relational Development on Wednesday, October 19th at 11:15 am.

Boosting my knowledge

This year there are even more ways to boost your knowledge and build your skills at CloudWorld via technical deep dives, panel discussions, hands-on labs, and more.

With hundreds of in-depth learning sessions across nine different tracks, no matter your role or where you plan to run your Oracle systems (in the public cloud, on-premises, hybrid cloud, etc.), we will have all the product updates, technology deep-dives, and best practices sessions you need. Choose a track based on your focus areas to get information, advice, and best practices tailored to your specific needs and goals.

I plan to spend most of my time in the database and developer tracks. You’ll hear about new product enhancements and what’s next for the Oracle Database and be able to attend deep-dive sessions on Machine Learning, Graph, Spatial, JSON, REST, APEX, MAA, Exadata, and App Dev. These sessions will be jam-packed with technical details, tips, and tricks you can take home and apply immediately, not marketing hype. You can check out the entire searchable catalog online.

I’m particularly looking forward to sessions on my favorite topic, the Oracle Optimizer.  There will be sessions by Nigel Bayliss, the Optimizer product manager, covering the Oracle Database 19c Optimizer (Thursday at 9 am) and the new Automatic Indexing capabilities (Tuesday at 4:40 pm). There will even be a session on Optimizer Hints given by little ole me (Wednesday at 3:45 pm)!

As well as in-depth technical sessions delivered by Oracle experts and Aces, you will also get an opportunity to try out all the technological innovations you hear about in our Hands-on-Labs.

Putting my knowledge to the test

One of the other things I’m looking forward to participating in at Cloud world is the OCI certifications.  You can earn a recognized OCI certification credential with on-site preparation classes and exams. I’ll definitely try my hand at the updated Oracle Autonomous Database 2022 Certified Professional exam.

Visiting Las Vegas again

Las Vegas is the ultimate home to world-class entertainment, cuisine, and nightlife. If you are a foodie like me, a trip to Las Vegas would not be complete without checking out some of the incredible restaurants they have on offer. Whether you want a great steak, sushi, or a vegetarian feast, Las Vegas has you covered. After all, you will need some fortification after spending all day learning and trying your hand at the latest Oracle technology.

Regardless of what you look forward to at Oracle CloudWorld, I hope to see you there!

If you can’t make it in person, be sure to sign up for a digital pass, so you don’t miss any of the action. I have a code you can use to get a free Cloud World Digital Pass! Simple use Comp Code: OCWDIGITAL.

Register for Oracle Cloud World today!

SQL Tuning: How to fix a SQL statement that isn’t getting partition pruning

Partitioning is one of the most powerful tools at your disposal when managing large volumes of data and improving the performance of queries that would otherwise scan and filter lots and lots of data.

However, it can be tricky to determine why it isn’t helping when you thought it should.

Typically partitioning improves query performance by ensuring only the partition(s) needed to answer the business user’s query will be scanned rather than the entire table.

But how can you tell if you got partition pruning in an execution plan? Or worse yet, how do you determine why you didn’t get partitioning pruning when you were expecting it.

In the video below, I share the steps I use to determine if partition pruning has occurred and what to look at and correct if you don’t automatically get the partition pruning you were expecting.

This blog post is part of a series on SQL Tuning. I shared some simple steps to help you tune a SQL Statement using the wrong Join Type in part one. While part two deals with how to tackle a problem where the optimizer picks the wrong index, and part three shares tips on how to fix statements where the Optimizer chooses a nested loop join instead of a hash join.

Happy Tuning!

 

%d bloggers like this: