According to Oracle's documentation, however, the optimizer ignores global hints in this format that refer to multiple query blocks. So if you wanted to use the "leading" hint with more than one view like this /*+ leading(view1.table1 view2.table2) */, Oracle will not utilize your input, and you will be left banging your head against the wall.
Luckily, there is another format for referencing these query blocks, but it takes a little digging to get it right. In the following example, I will create a table with a view that references it along with another view on the ever famous "dual" table. These two views will be joined in a query and we will see what options there are for manipulating the execution plan without changing the views.
Here are the table and view statements to set up the example.
CREATE TABLE orders AS
SELECT
LEVEL order_id,
SYSDATE + DBMS_RANDOM.VALUE(-1000, 1000) order_date,
DBMS_RANDOM.STRING('A', 20) comments
FROM dual
CONNECT BY LEVEL <= 100000;
CREATE INDEX order_id_ix ON orders ( order_id );
CREATE INDEX order_dt_id_ix ON orders ( order_date, order_id );
CREATE VIEW vw_orders AS
SELECT * FROM orders;
CREATE VIEW vw_dual AS
SELECT * FROM dual;
Here is a basic query which joins the two views
SELECT /*+ gather_plan_statistics */
order_id,
order_date,
comments
FROM vw_orders,
vw_dual
WHERE TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY')
AND order_id = 1;
After running the query, we can take a look at the execution plan chosen by the optimizer with the following script.
set pages 999 set lines 200 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR ( null, null, 'allstats' ));
The plan shows that DUAL is the driving table and a nested loop is utilized to access the ORDERS table via an index.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dh3p87qwt5njy, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ order_id, order_date,
comments FROM vw_orders, vw_dual WHERE TRUNC(order_date) =
TO_DATE('12-JAN-13','DD-MON-YY') AND order_id = 1
Plan hash value: 1760841149
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS | | 1 | 9 | 1 |00:00:00.01 | 4 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 9 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | ORDER_ID_IX | 1 | 9 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TRUNC(INTERNAL_FUNCTION("ORDER_DATE"))=TO_DATE('12-JAN-13','DD-MON-YY'))
4 - access("ORDER_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.
Now, if we want to force the optimizer to use a full table scan on the ORDERS table, we can throw in a global hint that references the view and the table. As you can see from the execution plan, the optimizer follows the directive and a full table scan is performed.
SELECT /*+ gather_plan_statistics full(vw_orders.orders) */
order_id,
order_date,
comments
FROM vw_orders,
vw_dual
WHERE TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY')
AND order_id = 1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9xby7nxuz82tc, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics full(vw_orders.orders) */
order_id, order_date, comments FROM vw_orders, vw_dual WHERE
TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY') AND order_id = 1
Plan hash value: 2691672058
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 545 |
| 1 | NESTED LOOPS | | 1 | 9 | 1 |00:00:00.01 | 545 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 3 | TABLE ACCESS FULL| ORDERS | 1 | 9 | 1 |00:00:00.01 | 545 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("ORDER_ID"=1 AND TRUNC(INTERNAL_FUNCTION("ORDER_DATE"))=TO_DATE(
'12-JAN-13','DD-MON-YY')))
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
If we try the same approach with the "leading" hint which references more than one query block, Oracle is not so cooperative. After running this example, we see that the "leading" hint is ignored and DUAL is still the driving table.
SELECT /*+ gather_plan_statistics full(vw_orders) leading(vw_orders.orders vw_dual.dual) */
order_id,
order_date,
comments
FROM vw_orders,
vw_dual
WHERE TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY')
AND order_id = 1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cbp5hxyg5mjkx, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics full(vw_orders)
leading(vw_orders.orders vw_dual.dual) */ order_id, order_date,
comments FROM vw_orders, vw_dual WHERE TRUNC(order_date) =
TO_DATE('12-JAN-13','DD-MON-YY') AND order_id = 1
Plan hash value: 2691672058
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 545 |
| 1 | NESTED LOOPS | | 1 | 9 | 1 |00:00:00.01 | 545 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 3 | TABLE ACCESS FULL| ORDERS | 1 | 9 | 1 |00:00:00.01 | 545 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("ORDER_ID"=1 AND TRUNC(INTERNAL_FUNCTION("ORDER_DATE"))=TO_DATE(
'12-JAN-13','DD-MON-YY')))
Note
-----
- dynamic sampling used for this statement (level=2)
33 rows selected.
In order to give the optimizer the information it needs in this situation, we need to change our "DBMS_XPLAN" script so it will include the "alias" and the "outline" information. In the "DBMS_XPLAN" script below, I have also eliminated the "predicate" and "note" data.
SELECT /*+ gather_plan_statistics */
order_id,
order_date,
comments
FROM vw_orders,
vw_dual
WHERE TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY')
AND order_id = 1;
set pages 999
set lines 200
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
(
null,
null,
'allstats +alias +outline -note -predicate'
));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 12b497umk9ytc, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ order_id, order_date,
comments FROM vw_orders, vw_dual WHERE TRUNC(order_date) =
TO_DATE('12-JAN-13','DD-MON-YY') AND order_id = 1
Plan hash value: 1760841149
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS | | 1 | 9 | 1 |00:00:00.01 | 4 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 9 | 1 |00:00:00.01 | 4 |
| 4 | INDEX RANGE SCAN | ORDER_ID_IX | 1 | 9 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5428C7F1
2 - SEL$5428C7F1 / DUAL@SEL$3
3 - SEL$5428C7F1 / ORDERS@SEL$2
4 - SEL$5428C7F1 / ORDERS@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5428C7F1")
MERGE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
INDEX_RS_ASC(@"SEL$5428C7F1" "ORDERS"@"SEL$2" ("ORDERS"."ORDER_ID"))
LEADING(@"SEL$5428C7F1" "DUAL"@"SEL$3" "ORDERS"@"SEL$2")
USE_NL(@"SEL$5428C7F1" "ORDERS"@"SEL$2")
END_OUTLINE_DATA
*/
53 rows selected.
In the output above, the operations are numbered in the execution plan. These numbers correspond to the numbers identifying the query block and object alias. These two query blocks and aliases are found in the outline section with a leading hint. This leading hint can be modified by listing the ORDERS table first as shown below and using the ORDERS alias in the full table scan hint as well. The optimizer now accepts the hints and executes the query as directed.
SELECT /*+ gather_plan_statistics full(@"SEL$5428C7F1" "ORDERS"@"SEL$2") leading(@"SEL$5428C7F1" "ORDERS"@"SEL$2" "DUAL"@"SEL$3") */
order_id,
order_date,
comments
FROM vw_orders,
vw_dual
WHERE TRUNC(order_date) = TO_DATE('12-JAN-13','DD-MON-YY')
AND order_id = 1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bpy75y38s6phy, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics full(@"SEL$5428C7F1"
"ORDERS"@"SEL$2") leading(@"SEL$5428C7F1" "ORDERS"@"SEL$2"
"DUAL"@"SEL$3") */ order_id, order_date,
comments FROM vw_orders, vw_dual WHERE TRUNC(order_date) =
TO_DATE('12-JAN-13','DD-MON-YY') AND order_id = 1
Plan hash value: 795833099
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 544 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 10 | 0 |00:00:00.01 | 544 |
| 2 | TABLE ACCESS FULL | ORDERS | 1 | 10 | 0 |00:00:00.01 | 544 |
| 3 | BUFFER SORT | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 4 | FAST DUAL | | 0 | 1 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5428C7F1
2 - SEL$5428C7F1 / ORDERS@SEL$2
4 - SEL$5428C7F1 / DUAL@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5428C7F1")
MERGE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5428C7F1" "ORDERS"@"SEL$2")
LEADING(@"SEL$5428C7F1" "ORDERS"@"SEL$2" "DUAL"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$5428C7F1" "DUAL"@"SEL$3")
END_OUTLINE_DATA
*/
48 rows selected.
