I'm trying to learn how to force Oracle 18c to use a function-based index — by using a hint:
--I've omitted the custom function and function-based index because it cluttered the question too much.
--https://gis.stackexchange.com/a/431019/62572
with cte as (
select /* INDEX (active_transportation atn_endpoint_list_idx) */
infrastr.endpoint_list(shape) as list
from
infrastr.active_transportation
where
infrastr.endpoint_list(shape) is not null
)
select
to_char(rownum) || '-STARTPOINT' as unique_id,
cast(regexp_substr(list, '[^,] ', 1, 1) as number) as x,
cast(regexp_substr(list, '[^,] ', 1, 2) as number) as y
from
cte
union all
select
to_char(rownum) || '-ENDPOINT' as unique_id,
cast(regexp_substr(list, '[^,] ', 1, 3) as number) as x,
cast(regexp_substr(list, '[^,] ', 1, 4) as number) as y
from
cte
When I hit the explain plan button in SQL Developer, I get this:
And I can see that there is information about hints. Good.
Similarly, I want to output the explain plan as plain text, using:
explain plan for
...(the query)
select * from table(dbms_xplan.display());
But when I do that, I don't see any details about the hint:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29068 | 110M| 77 (2)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D669A_67C8FF97 | | | | |
|* 3 | INDEX FULL SCAN | ATN_ENDPOINT_LIST_IDX | 14534 | 965K| 161 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | COUNT | | | | | |
| 6 | VIEW | | 14534 | 27M| 38 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669A_67C8FF97 | 14534 | 965K| 38 (0)| 00:00:01 |
| 8 | COUNT | | | | | |
| 9 | VIEW | | 14534 | 27M| 38 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669A_67C8FF97 | 14534 | 965K| 38 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("INFRASTR"."ENDPOINT_LIST"("SHAPE") IS NOT NULL)
Question:
How can I output the explain plan as plain text — and include details about hint usage?
I tried using a line from this page: Oracle Scratchpad - Fussy FBIs:
select * from table(dbms_xplan.display_cursor(format=>'cost allstats last hint_report remote outline'));
But I got an error:
PLAN_TABLE_OUTPUT
-----------------
Error: format 'cost allstats last hint_report remote outline' not valid for DBMS_XPLAN.DISPLAY_CURSOR()
CodePudding user response：
You might be after the outline, ie
SQL> explain plan for select * from emp;
Explained.
SQL> select * from table(dbms_xplan.display(format=>' outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 407 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 11 | 407 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Outline Data
-------------
/*
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EMP"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
40 rows selected.