Generally speaking, when are join indexes being used by the optimizer?
i.e. SELECT COUNT(*) query with date filter..
SPARSE JOIN INDEX 3 columns and a date filter on the base table
Will the optimizer utilize the sparse join index?
Generally speaking, the query should specify the columns defined in the index? else it wouldn't use that index?
Secondly, even your query used all the columns defined in the index sometimes the optimizer may not choose that index if it sees that it is slower?
Need your thoughts on this
Q3: Yes, if the query can be processed using only the JI the optimizer will probably use it (if it's more efficient than accessing the base table).
Q1: Based on Q3, should be Yes.
Q2: If additional columns are accessed the optimizer might still use the JI (if a unique column or the ROWID is included in the JI enabling a join back to the base table)
Only an Explain can reveal the actual usage :-)
There's only a single row in that table and an "all-AMPs RETRIEVE step":
FTS -> One IO per AMP to access that datablock
Now assume using the JI, which needs a join back to the base row:
One IO per AMP to access the JI datablock plus another IO to read the base row datablock.
You should always test with some larger datasets and collected stats:
DELETE FROM test2;
INSERT INTO test2
SELECT day_of_calendar, DATE '2012-01-01' + RANDOM(1,100), 'pat'
COLLECT STATS ON test2_join COLUMN col1;
COLLECT STATS ON test2 COLUMN col1;
COLLECT STATS ON test2 COLUMN col2;
Btw, instead of "CAST('2012-03-01' AS DATE FORMAT 'YYYY-MM-DD')" better use "DATE '2012-03-01'"