« Static fields in generic classes | Main | Creating DataView »
August 16, 2005
Oracle's cardinality hint
Whenever you are trying to use in memory array in your SQL statement, Oracle's query optimiser seems to get confused in doing its job. You have to help it by giving the cardinality hint, otherwise a full table scan is guaranteed to happen.
For example you have a type
numtable is table of number
And you populate your internal array like this:
select client_id bulk collect into client_ids
from client where client_type = "XXX";
When you're trying to use the array in this query:
select a.client_name, a.address
from client_detail a,
table(cast(client_ids as numtable )) t
where a.client_id = t.column_value
This looks simple, but regardless how you define the index, Oracle will do a full table scan. Don't really know why. To fix this you need to give it a cardinality hint, doesn't have to be precise but at least some rough estimate how many records you think the array will contain. For example:
select /*+ cardinality (t 8) */ a.client_name, a.address
from client_detail a,
table(cast(client_ids as numtable )) t
where a.client_id = t.column_value
That will make the query peforms heaps better.
Posted by vhadiant at August 16, 2005 08:52 PM
Trackback Pings
TrackBack URL for this entry:
http://www.hadianto.net/mov32/mt-tb.cgi/97
