When using WITH query sub-factoring, the docs. state that this may be performed behind the scenes by either creating a temp. table or an in-line view.
When choosing the temp. table option, what happens behind the scenes about indexing, and can we influence it?
For example,
WITH ABC AS
(SELECT COLUMN_1
FROM TABLE_ABC),
--
SELECT *
FROM XYZ, ABC
WHERE XYZ.COLUMN_1 = ABC.COLUMN_1
Can ABC.COLUMN_1 be 'indexed' ?
there will be no indexing, the concept here is you are doing something "in bulk"
In your above query, Oracle would tend NEVER to use indexes - period, it would not make sense for the default optimization mode (all rows) and the fact that every row in XYZ and ABC will be processed. that would almost certainly be two full scans and a hash join.
However, if you tweaked the query to something like:
WITH ABC AS
(SELECT COLUMN_1
FROM TABLE_ABC),
--
SELECT *
FROM XYZ, ABC
WHERE XYZ.COLUMN_1 = ABC.COLUMN_1
and abc.column_1 = 1
in this case - in the above, if column_1 were indexed and selective - we would NOT generate the factored subquery, we would merge the predicate and use the index.
that is, the optimizer does the right thing, materializing when it makes sense, not doing so when it does not.