oracle using index even though there is no filter criteeria specified

Posted by Kaushik on Stack Overflow See other posts from Stack Overflow or by Kaushik
Published on 2012-10-19T22:58:37Z Indexed on 2012/10/19 23:00 UTC
Read the original article Hit count: 200

Filed under:

oracle11g

|

explain-plan

In this query:

 SELECT WTTEMPLATE.TEMPLATEuID,
                    MAX (WTTRX.VALUEDATE) AS template_last_use_date
               FROM wttemplate, wttrx
              WHERE WTTEMPLATE.TEMPLATEID = WTTRX.TEMPLATEID(+)
                    AND WTTEMPLATE.CUSTID = WTTRX.CUSTID
           GROUP BY WTTEMPLATE.TEMPLATEuID

The explain plan shows:index fast full scan using indexes on WTTEMPLATE.TEMPLATEID and (WTTRX.TEMPLATEID,WTTRX.CUSTID). My question is this: I have not specified any filter criteria , so how can it use indexes? It should do full scan...right?

© Stack Overflow or respective owner

Related posts about oracle11g

???? Oracle11g ????????? No.2 - v$database.CURRENT_SCN

as seen on Oracle Blogs - Search for 'Oracle Blogs'
«????Oracle 11g ???????»???????????,?11.2.0.3.0?????: select current_scn from v$database union all select current_scn from v$database; ??????????SCN,??????11.2.0.1.0???????????SCN?????? ??,????11.2.0.1.0????,11.2.0.3.0????X$KCCDI(V$DATABASE?????,??CURRENT_SCN??)??,?????????SCN? … >>> More
Cognos 8.3 and Oracle11g

as seen on Server Fault - Search for 'Server Fault'
When i place ojdbc5.jar in the c8\webapps\p2pd\WEB-INF\lib directory... i receive the error below. And yes the content store is on an oracle 11g RAC database. I am running Oracle11g Client. Cognos version is 8.3 SP4. [Content Manager database connection] 1. [ ERROR ] The database connection failed… >>> More
Cognos 8.3 and Oracle11g

as seen on Server Fault - Search for 'Server Fault'
When i place ojdbc5.jar in the c8\webapps\p2pd\WEB-INF\lib directory... i receive the error below. And yes the content store is on an oracle 11g RAC database. I am running Oracle11g Client. Cognos version is 8.3 SP4. [Content Manager database connection] 1. [ ERROR ] The database connection failed… >>> More
how to display databases in oracle11g using sqlplus

as seen on Stack Overflow - Search for 'Stack Overflow'
in mysql its show databases; but how to show the available databases in oracle. can someone help >>> More
INS-40719 error when Install Oracle RAC?

as seen on Server Fault - Search for 'Server Fault'
I'm tying to (learn how to) install Oracle RAC 11g on CentOS 6 all went OK so far but I get INS-40719 Error message regarding SCAN Name I do not have DNS server and I'm not going to try to use it on this setup I add this line to /etc/hosts 192.168.244.100 rac-cluster then used "rac-cluster" as… >>> More

Related posts about explain-plan

Explaining Explain Plan Notes for Auto DOP

as seen on Oracle Blogs - Search for 'Oracle Blogs'
I've recently gotten some questions around "why do I not see a parallel plan" while Auto DOP is on (I think)...? It is probably worthwhile to quickly go over some of the ways to find out what Auto DOP was thinking. In general, there is no need to go tracing sessions and look under the hood. The thing… >>> More
SQL explain plan: what is Materialize?

as seen on Stack Overflow - Search for 'Stack Overflow'
I asked PostgreSQL to explain my query. Part of the explanation was: table_name --> Materialize What does materialize do? I'm joining two tables, not views or anything like that. >>> More
Oracle EXECUTE IMMEDIATE changes explain plan of query.

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the execution time to increase 5x. The main difference… >>> More
EXPLAIN PLAN FOR in ORACLE

as seen on Stack Overflow - Search for 'Stack Overflow'
I am making a test. I have all tests in rows, so my rows looks like this; ID | TEST ---------------------------------- 1 | 'select sysdate from dual' 2 | 'select sysdatesss from dual' Now I read it row by row and I need to test it with EXPLAIN PLAN FOR so the for the first row it would… >>> More
Question about Cost in Oracle Explain Plan

as seen on Stack Overflow - Search for 'Stack Overflow'
When Oracle is estimating the 'Cost' for certain queries, does it actually look at the amount of data (rows) in a table? For example: If I'm doing a full table scan of employees for name='Bob', does it estimate the cost by counting the amount of existing rows, or is it always a set cost? >>> More