query help needed - nested loop (semi)
The following query is taking too long (1+ hour) to run, jumped from 2 mins; does anyone see any issues with it ?
It is using the best indexes possible, the only thing I can think of is the nested loop, instead of Hash Join ? What does (semi) mean ?
select * from chx_bbo_max
where TIMESTAMP between
TO_DATE('20030623' || ' 000000', 'YYYYMMDD HH24MISS') and
TO_DATE('20030623' || ' 235959', 'YYYYMMDD HH24MISS')
AND ISSUE_SYMBOL IN(
SELECT DISTINCT ISSUE_SYMBOL
FROM floor_stock_table_listed
WHERE PROCESS_DATE between
TO_DATE('20030623', 'YYYYMMDD') and
TO_DATE('20030623', 'YYYYMMDD')
AND TIME_OF_DAY_INDICATOR = 'BOD'
AND TIME_DELAY > 5);
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=134)
1 0
NESTED LOOPS (SEMI) (Cost=3 Card=1 Bytes=134)
2 1
TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHX_BBO_MAX' (Cost=1 Card=1 Bytes=118)
3 2
INDEX (RANGE SCAN) OF 'CHX_BBO_MAX_IDX1' (NON-UNIQUE)
4 1
TABLE ACCESS (BY INDEX ROWID) OF 'FLOOR_STOCK_TABLE_LISTED' (Cost=2 Card=2324 Bytes=37184)
5 4
INDEX (RANGE SCAN) OF 'FLOOR_STOCK_TABLE_LISTED_PK' (UNIQUE) (Cost=1 Card=2)