Home » RDBMS Server » Performance Tuning » View doesn't use Index
View doesn't use Index [message #255101] Mon, 30 July 2007 11:11 Go to next message
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
Re: View doesn't use Index [message #255104 is a reply to message #255101] Mon, 30 July 2007 11:22 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Need help - Tuning the SQL
Next Topic: How to find out the bad sql statemets ?
Goto Forum:
  


Current Time: Sun Dec 11 06:22:50 CST 2016

Total time taken to generate the page: 0.03926 seconds