0

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.

Lakshmanan Chidambaram
  • 133
  • 11

1 Answers1

3

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.

Lord Peter
  • 3,433
  • 2
  • 33
  • 33
  • Your reply makes sense. Based on your reply, I have two questions - 1. You said 'minuscule bit extra CPU' - even if its minuscule, how could it be measured? If my table is going to have more rows, and both the query has same explain plan with same C-B-C, it is misleading the user on the regard that the retieved rows on both the query would be of same performance, right? I suppose that would not be the case here. Please explain. 2. Also please explain what do you meant by cardinality in the above answer, as I had it as 'no. of 'distinct' rows'. – Lakshmanan Chidambaram Jan 01 '13 at 11:26
  • Please ignore the second question in the above comment. The following stack overflow [link](http://stackoverflow.com/questions/13838173/how-to-consider-explain-plan-as-good-oracle-10g?rq=1) has helped me with the terminologies for an in-depth understanding of explain plan feature of oracle. – Lakshmanan Chidambaram Jan 01 '13 at 11:41
  • 1
    Approx CPU used can be seen in the query plan (as % of total cost of each query step) Here it is likely to be extremely small or rounded off to nothing when compared to the cost of IO. If you were to include some elaborate calculations in your select list you may see the CPU % increase. "Cardinality" is the est size of the result set from each step of query = row count = num rows, same thing. Remember that the query plan is an estimate, so numbers will be approx correct depending on stats. If you want exact values you need to trace the query and use e.g. TKProf to see exactly what happened. – Lord Peter Jan 01 '13 at 12:03
  • Thanks, Lord. That helps. Wish you a happy 2013 :-) – Lakshmanan Chidambaram Jan 01 '13 at 12:14