Index after building a table of multiple joins in Oracle

Question

I use WITH clause where I build a table from 4 tables based on columns using bitmap indexes. let's say column col1 from table1 and col2 from table2 have bitmnap index. it is pretty fast and I get a result as one table_A with 10 columns with col1 and col2 among them.

later in the process a will use either join or exists like

select * from table_XY 
where exists (
     select 1 from table_A 
     where col1= table_XY.colx and col2=table_XY.coly
)

1.tableXY has no index , will Oracle scan the whole table_A to find the first match or is Oracle using index? 2. if Oracle can use index because table_A is just a construct of multiple tables in WITH clause, the only improvements would be to have and index in tableXY? what in case if tableXY is also a construct in another WITH clause?

Answer 1

Generate an explain plan and then you'll know the answer. You'll need to use your entire SQL, not just the piece you shared above... the WITH clause you mentioned will need to be part of it:

EXPLAIN PLAN FOR
WITH table_A AS (......)
select * from table_XY 
where exists (
     select 1 from table_A 
     where col1= table_XY.colx and col2=table_XY.coly
)
/
SELECT * FROM TABLE(dbms_xplan.display())
/

Then read the output. There are plenty of guides out there on how to read explain plans. It will tell you exactly what Oracle intends to do with your query.

If Oracle decides to materialize your WITH clause (store its results in a SQL-duration temp table), keep in mind that that temp table is not, and cannot be, indexed in any way. But it probably doesn't need to be - Oracle will likely rewrite your query in such a way that it can accomplish it with a hash join against the temp table. If Oracle decides not to materialize the view and instead merges it, it could hit the base table(s) and then it might use indexes, depending on its cardinality estimates from table_XY and its stats on those indexes. We can't predict that in this forum, only the explain plan will tell you.

Don't worry about trying to get it to use indexes as your first concern. Your first concern is simply, does it perform acceptably? If it does, move on. If not, generate the explain plan and figure out what mistake it is making, and apply the appropriate remedial measure to correct it. I cannot be more specific with only generalities - there are so many possibilities, tuning can only be properly done in-house with access to your data, full SQL, table structure, explain plans, and ASH (v$active_session_history) data.