| View doesn't use Index [message #255101] |
Mon, 30 July 2007 11:11  |
vettorel
Messages: 1
Registered: July 2007
|
Junior Member |
|
|
Hi Experts,
i've got a little problem:
i use a view with "start with / connect by prior":
Create or Replace View test
AS
SELECT LEVEL lev, LPAD (' ', LEVEL * 4 - 4) || si.item_class OBJECT,
si.*,
DECODE (si.item_class || si.item_sub_class,
'addressobject', 1,
'addressstreet', 2,
'addresscontact', 3,
'acbd', 1,
'acarp', 2,
99
) ord,
si.ROWID rid
FROM vus_exc_send_item si
START WITH (nvl(si.send_item_id_p,'###')= '###')
CONNECT BY PRIOR (si.send_id || si.send_item_id) =
(si.send_id || si.send_item_id_p
)
ORDER SIBLINGS BY ord, si.item_class, si.item_sub_class;
Problem:
My Query:
Select *
from test
where send_id = '12345'
doesn't use the index on send_id!
the optimizer uses the index for Start With -> (nvl(si.send_item_id_p,'###'))
the where clause index (send_id, non-unique, 95% different values) is not used even, if i give a hint (/*+ index....)
statistics are new, tried almost everything.
thanks and greets
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
