Re: Basic SQL Query Plan Qs

Vivek

Read this from Richard Foote:

http://richardfoote.wordpress.com/2008/02.13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

In particular the sixth para "... Oracle can navigate directly to the leaf block of interest, no matter the index order, so long as all column values are known." So options 2 and 3 can resolve by reading at most 1 leaf block. Unless one index happens to be deeper than the other, no difference in performance.

In your case, I think all your options could be resolved in the root block or btree anyway, so you probably can't see a difference in performance.

(I hate to ask, but why are you querying a table to get no rows back? sounds like a benchmark to me...)

Regards Nigel

• Original Message ---- From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>; "sfaroult_at_roughsea.com" <sfaroult_at_roughsea.com> Sent: Tuesday, February 19, 2008 12:41:40 AM Subject: Basic SQL Query Plan Qs

Folks

SQL Query:-

select ..
from DTD
where account_num='N1'
and tran_date='10-02-2005'

NOTE - DTD Table has 400,000 rows approx with ALL its Records having just 1 Date Value '12-06-2005' (i.e. a date value which is NOT in the SQL Query) DTD Table has multiple values of account_num but does NOT have the specific account_num 'N1' specified in the SQL Query.

Running on which of the following indexes will be most performing & why?

Index 1 - Unique index on (tran_date, some field2, another field3)
Index 2 - account_ num
Index 3 - (account_ num, tran_date)

Optimizer - CBO
Oracle 10.2.0.3

Cheers & Thanks

Vivek

--
http://www.freelists.org/webpage/oracle-l