Oracle Database Discussions

Hi,

There is a certain query that runs as a part of a stored procedure which when run takes about 1 and half hours to get completed and there is a hint in this query to use certain indexes on few tables.

This time was unacceptable as it was much much longer than what it should have taken so I tried to monitor it through v$session_longops using the following query

select * from v$session_longops where time_remaining > 0

It shows no rows selected but the query is still running.

When I changed the hint in the query to use the full table scan on these tables instead of the indexes it began to show up on the v$session_longops and it took like only 15 mins for the procedure execution to get completed.

My questions are two fold

1) Why did the first time with the Hint on the indexes not show up on v$session_longops with time_remaining > 0 and why did it show up only when i did a full table scan?

2) Secondly is'nt a scan on a table faster using indexes if the index has unique values on the columns we are trying to get data from than the Full Table Scans. And if so then why is it behaving differently in this case.

Thanks a lot in advance for any answers/suggestions