Home » SQL & PL/SQL » SQL & PL/SQL » sql developer vs toad, indexing
sql developer vs toad, indexing [message #437501] Mon, 04 January 2010 15:26 Go to next message
namitanamburi
Messages: 32
Registered: March 2009
Member
Hi all,



There is a table EMP and has few fields like date, amount etc on which Iam querying as below

select * from EMP where trunc(date) = to_date('01/01/2009','mm/dd/yyyy')


When I do the explain plan in toad , it shows a full table scan.

There is an function based index on date field with column expression as trunc("date").


When I run same query in Toad, it doesn't do a full table scan instead a index based query .

Does sql developer expect a query like this?

select * from EMP where trunc("date") = to_date('01/01/2009','mm/dd/yyyy')


what is the right way of querying when the index is as above?



Any clue what the issue is?



Thanks
Nammu




Re: sql developer vs toad, indexing [message #437502 is a reply to message #437501] Mon, 04 January 2010 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Any clue what the issue is?
No, because you did not follow Posting Guidelines.

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


How can we reproduce EXACTLY what you report?
Re: sql developer vs toad, indexing [message #437505 is a reply to message #437501] Mon, 04 January 2010 16:23 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Check if the two tools have the optimizer_mode set differently.
You should be able to see what it is at the top of each explain plan and from memory TOAD has a drop down list in the explain plan window that lets you change it.

EDIT: fixed tags

[Updated on: Mon, 04 January 2010 16:24]

Report message to a moderator

Re: sql developer vs toad, indexing [message #437571 is a reply to message #437501] Tue, 05 January 2010 01:34 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
date and "date" and 2 different things, date is DATE in upper case for Oracle whereas "date" is date in lower case.

Regards
Michel
Previous Topic: need help please
Next Topic: Mutating Trigger
Goto Forum:
  


Current Time: Mon Dec 05 21:18:02 CST 2016

Total time taken to generate the page: 0.22816 seconds