Home » SQL & PL/SQL » SQL & PL/SQL » TABLE ACCESS FULL
TABLE ACCESS FULL [message #223186] Wed, 07 March 2007 22:34 Go to next message
rahulvasanth
Messages: 34
Registered: January 2006
Member
I have a query in oracle 10g

SELECT UNIQUE wi.item_size c_item_name
FROM t_web_inventory wi
WHERE decode(style_code,NULL,'0',wi.style_code) = nvl(:c_style_code,'0')
AND decode(color_code,NULL,'0',wi.color_Code) = nvl(:c_color_code,'0')
AND decode(c_width,NULL,'0',wi.width_code) = nvl(:c_width,'0')
AND wi.quantity_at_hand > 0



When I ran the explain plan for the above query I get the following result.


SELECT STATEMENT, GOAL = ALL_ROWS Cost=4 Cardinality=1 Bytes=21
SORT UNIQUE Cost=4 Cardinality=1 Bytes=21
TABLE ACCESS FULL Object owner=SOAS Object name=T_WEB_INVENTORY Cost=3 Cardinality=1 Bytes=21


The query is doing a TABLE ACCESS FULL scan even though I have indexes on T_WEB_INVENTORY table. I want to know why the query is not doing a INDEX scan. Is it because of DECODE function ? But I have used DECODE function in some of the other queries and they are doing proper INDEX scans.

Thanking in advance.
Re: TABLE ACCESS FULL [message #223209 is a reply to message #223186] Thu, 08 March 2007 00:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, it is because of the DECODE. Take a look at this link for some further info and tips.

Ross Leishman
Re: TABLE ACCESS FULL [message #223676 is a reply to message #223186] Sat, 10 March 2007 04:17 Go to previous message
parlovi
Messages: 6
Registered: February 2007
Location: BANGALORE
Junior Member

Thanks Rlishman for giving such an excellent link.
Tutorial is really good.

Thanks a Ton,
P
Previous Topic: VIEW
Next Topic: Creating Procedures
Goto Forum:
  


Current Time: Fri Dec 02 16:41:05 CST 2016

Total time taken to generate the page: 0.20879 seconds