Re: ordered_hint

One full table scann is often not allone. Using ORDERED clause with some success but can have its draw backs when the execution plan is not cast in stone. It can happen that suddenly is doing much worse after a while. It's likely that the index is cached which makes it fast when running the same query again.

SELECT /*+ ORDERED INDEX(t1 index_name) USE_NL(t1 v1) */ ...

Since with ORDERED it will use a nested loop on t1 and v1. It's not very scalable and it's a little bit of a gamble what is happening inside the view over time.

t1 will always have a full table scan without any hints. Maybe is worth trying to change t1 to an IOT table, so index is already part of t1. Received on Mon Sep 26 2005 - 17:41:18 CDT