Enjoy spexp tool http://valentinnikotin.com/spexp/
First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third, the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.
This has several consequences.
1. One function may have different dependencies for different input arguments. Let’s look at the following example:
--drop table (more...)
It seems that CBO can work with the line comment inside sql hints.
Let’s see. I create a table with 3 indexes:
--drop table ttt purge; create table ttt ( a number not null, b number not null, c number not null ); insert into ttt select 1,1,1 from dual connect by level <= 10000; commit; create index iiia on ttt(a); create index iiib on ttt(b); create index iiic on ttt(c);
And then I’m able to switch access paths by commenting:
explain plan for select /*+ --full(ttt) --index(ttt(c)) index(ttt(a)) --index(ttt(b)) */ count(*) from ttt; select * from table(dbms_xplan.display); (more...)