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
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
SELECT LEVEL lev, LPAD (' ', LEVEL * 4 - 4) || si.item_class OBJECT,
DECODE (si.item_class || si.item_sub_class,
'addressobject', 1,
'addressstreet', 2,
'addresscontact', 3,
'acbd', 1,
'acarp', 2,
) 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;

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: 63921
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).

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

Current Time: Tue Oct 25 11:03:41 CDT 2016

Total time taken to generate the page: 0.16516 seconds