Hi, We are using version 12.1.0.2.0 of Oracle Exadata. Its third party application proprietary database with multiple non default settings. We have a view (with below definition ), scanning a remote table(TAB2) through DB link. This view is used in multiple other queries and we want to use/see effect of driving_site hint(and few other hints) on this remote table(TAB2) which is there inside the view. But due to its use inside a view i am not able to see a straight forward way to use the hint in the wrapper queries. Below is the view definitiona and the sample query plan how it looks like. i do see a NO_ACCESS hint in the outline section of the query.
I want to understand from experts, if there is a way to force hint for the objects used inside the view definition to influence the execution path of the other calling queries?
View Definition:-
CREATE OR REPLACE FORCE VIEW USER1.VIEW1
( BID_NBR,PGM_IND,PGA_ID,PGH_NBR,CREATE_DATE)
AS
SELECT DISTINCT BID_NBR, PGM_IND, PGA_ID,PGH_NBR, TRUNC (RA_DATE)
FROM TAB1
UNION
SELECT DISTINCT BID_NBR1, LTRIM (RTRIM (CID)), LTRIM (RTRIM (AID)), DESC, TRUNC (CREATE_DATE)
FROM TAB2;
select * from VIEW1;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1377618844
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22876 | 2881K| | 334 (1)| 00:00:01 | | |
| 1 | VIEW | VIEW1 | 22876 | 2881K| | 334 (1)| 00:00:01 | | |
| 2 | SORT UNIQUE | | 22876 | 2457K| 1632K| 334 (1)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | | |
| 4 | TABLE ACCESS STORAGE FULL| TAB1 | 22876 | 1228K| | 45 (0)| 00:00:01 | | |
| 5 | REMOTE | | | | | | | DB1 | R->S |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / VIEW1@SEL$1
2 - SET$1
4 - SEL$2 / TAB1@SEL$2
5 - SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "TAB1"@"SEL$2")
NO_ACCESS(@"SEL$1" "VIEW1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
FIRST_ROWS(1)
OPT_PARAM('optimizer_index_caching' 80)
OPT_PARAM('optimizer_index_cost_adj' 50)
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OPT_PARAM('_unnest_subquery' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT DISTINCT
"A1"."BID_NBR1",LTRIM(RTRIM("A1"."CID")),LTRIM(RTRIM("A1"."AID")),"A1"."DESCRIPTION",TRUNC("A1"."
CREATE_DATE") FROM "TAB2" "A1" (accessing 'DB1.ORACLE.COM' )