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.