Why explain plan uses INDEX UNIQUE SCAN and TABLE ACCESS FULL?

Question

I query a view that is built from 3 tables. The main tables are joined by a column that in table A it is indexed & in table B it is PK. The view has additional column that is used in the condition but it is not indexed. seems a simple query from the table is using the primary key but also accesses the table with table access full. I need an explanation as to why this happens & how to make it better. Tabc in the example code below is very small & therefore expected to be accessed full.

create or replace view v_per_pda
as select cola, colb, case when colc = 51 then colc else 0 end decd
from taba, tabb, tabc
where taba.id = tabb.id
and tabb.cold = 1
and taba.id2 = tabc.id2

in the explain plan below you can see : taba is accesses by index range scan & tabb is accesses by the primary key the table access full below relates to table b. why does oracle uses this?

just to be clear, tabc in the explain plan is accesed by index range scan as well. the bottom one. — Yael Bar Corley, Sep 10 '19 at 10:54
Please post the execution plan as text including table names and predicates. See[here](https://stackoverflow.com/a/34975420/4808122) for details. — Marmite Bomber, Sep 10 '19 at 11:24
I did what you asked but it gives me a different execution plan. I would like to re-check this. — Yael Bar Corley, Sep 10 '19 at 13:04

score 0 · Answer 1 · answered Sep 11 '19 at 06:10

0

I consulted a DBA & what the pl sql developer shows is adaptive explain plan after executing the explain plan using plan_table - you do not see the table access full. moreover, it is more accurate.

so thank you & this question is a closed issue

answered Sep 11 '19 at 06:10

Yael Bar Corley

11
1

Why explain plan uses INDEX UNIQUE SCAN and TABLE ACCESS FULL?

1 Answers1