CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-execution-plan
Lakshmanan Chidambaram
Lakshmanan Chidambaram

Reputation: 133

SQL Explain Plan Intrepretation - Oracle DB

Scenario:

I'm getting the same explain plan (Tab Access Full and same numbers for Cost, Bytes and Cardinality) for two different queries on the same table(not indexed / no PK / no FK).

Query1:

select * from tab1 where col1 = 'A'

Query2: 

select * 
from tab1 
where col1 = 'A' 
and months_between(decode(col2,NULL,col3,col4),col5)<13;

Question:

Won't the added conditions in Query2 needs some more time to process?
or
Is it like, even Query1 takes the time as that of Query2, which is the maximum time needed for almost any such query on the table(Tab Access Full)?

Please note that the table (data vol. is always < 0.5 mil) is not showing any performance issue so far and this is just to intrepret the explain plan provided by oracle COB, so that it would be beneficial for future enhancement.

Upvotes: 0

Views: 270

Answers (1)

Lord Peter
Lord Peter

Reputation: 3501

You say that you have no indexes on your table. The optimizer therefore has no choice but to full scan. In addition, your are selecting all columns (select *). The cost for query2 (mainly the IO of full scan), will be very very similar perhaps a minuscule bit extra CPU for the additional conditions. The cardinality is the number of rows the optimizer estimates will be retrieved by each step of the query plan. Similarly the Byte count is the number of bytes expected. If your query1 and query2 are returning the same number of rows, i.e. the extra predicate does not affect the result, then the Cardinality and Byte count will be the same for query1 as for query2. If you think they should be different, then you should check that your table stats are up to date.

Upvotes: 3

Related Questions