Skip to Main Content
  • Questions
  • Full Fast Index Scans with predicates

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Paul.

Asked: June 03, 2021 - 10:28 am UTC

Last updated: June 09, 2021 - 12:46 pm UTC

Version: 11.1.0.6

Viewed 100+ times

You Asked

You have helped me previously setting up skinny indexing here: QUESTION_ID:9542855000346335758

This has been implemented, and is now in production and working really well and is super fast; thanks for all the previous help with this.

I have a follow up question. Although most of the orders tables have millions of rows, unprocessed orders are generally only a few thousand rows, and so only hitting skinny indexes by indexing virtual columns only holding data for unprocessed orders as you explained in the question above has worked brilliantly. In most cases, a FAST FULL SCAN is performed on the indexes as they are relatively small, and we select most of the data held in those indexes.
What I find odd is when I look at the explain plan in SQL Developer, if I add a predicate to the query, it's not shown in the explain plan. The explain plan remains the same i.e. just a FAST FULL SCAN. If I don't see the predicate in the explain plan, where is that being processed? I know a FAST FULL SCAN grabs all the data from the index in one go. Would I not still see the predicate in the explain plan. I can send screen shots from sql dev if that helps.
Thanks.

and we said...

Have you enabled predicate display in SQL Developer?

Tools -> Preferences -> Database -> Autotrac/Explan -> Predicates

If you're getting plans with dbms_xplan, use the +PREDICATE option to ensure this is in the output:

set serveroutput off
select * from t
where  t_id = 1;

select * 
from   dbms_xplan.display_cursor( format => 'BASIC +PREDICATE');

---------------------------------------------------    
| Id  | Operation                   | Name        |    
---------------------------------------------------    
|   0 | SELECT STATEMENT            |             |    
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    
|*  2 |   INDEX UNIQUE SCAN         | SYS_C008715 |    
---------------------------------------------------    
                                                       
Predicate Information (identified by operation id):    
---------------------------------------------------    
                                                       
   2 - access("T_ID"=1)   

Rating

  (2 ratings)

Comments

no access to v$session

Paul Brown, June 09, 2021 - 10:05 am UTC

Thanks for the answer. Not sure if this is connected, but from the schema where I am running the query, if I run:

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'BASIC +PREDICATE'));

I get:

User has no SELECT privilege on V$SESSION

Not sure if this is why sql dev is only showing a partial explain plan. If I run the query from another schema with full privs, I see this from the above query:

------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX FAST FULL SCAN| V3V_LIVEPRODUCT |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("PRODUCT"."RETAILPRICELIVE">:SYS_B_0 AND
"PRODUCT"."IDLIVE" IS NOT NULL))

So maybe it's a permissions issue on v$session?

Chris Saxon
June 09, 2021 - 12:46 pm UTC

Yes, you need to grant select/read on these:

grant select on sys.v_$session to ...;
grant select on sys.v_$sql_plan to ...;
grant select on sys.v_$sql to ...;
grant select on sys.v_$sql_plan_statistics_all to ...; 



Question Answered

Paul Brown, June 09, 2021 - 1:05 pm UTC

Thanks for the help: all sorted.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.