[askdba] Re: Which index is getting used

  • From: Thiagoo <thiagoodba@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Mon, 6 Dec 2004 08:10:11 -0800 (PST)

Hi,
 
Pls see if this helps you.
 
[]Do Explain Plan of the below SQL stmnt , it should give you which  index is 
getting used. It depends on the order in which you use the indexed columns in 
the where clause. Which all columns have indexes in this table? Since you use 
LIKE, it may use FULL scan,if it was not indexed. If any index is not getting 
used(if you find in explain plan), you can use hints to force the use of Index.
 
[] Not sure abt this, if this table has very high volume of records, desirable 
columns may yield better result.
 
Thanks & Best Wishes,
Thiagu
drumil narayan <drumilnarayan@xxxxxxxxx> wrote:
Dear all,

How should i find out which index is getting used on
following query in a cursor

SELECT * from gin_con 
where bill_num like
nvl(:BILL_TRAN_HDR.bill_num,bill_num)
AND VND_CD like nvl(:BILL_TRAN_HDR.VND_CD,VND_CD)
and DOC_NUM like nvl(:BILL_TRAN_HDR.DOC_NUM,DOC_NUM)
and DOC_TYPE = nvl(:BILL_TRAN_HDR.DOC_TYPE,DOC_TYPE)
AND DOC_YR = nvl(:BILL_TRAN_HDR.DOC_YR,DOC_YR)
and bill_dt = nvl(:BILL_TRAN_HDR.bill_dt,bill_dt)
and DOC_dt = nvl(:BILL_TRAN_HDR.GIN_dt,DOC_dt);

and secondly i want to ask performance wise
what is the difference in

select * from table_name ;
and
select desired_column_list from table_name;

TIA..drumil


________________________________________________________________________
Yahoo! India Matrimony: Find your life partner online
Go to: http://yahoo.shaadi.com/india-matrimony


                
---------------------------------
Do you Yahoo!?
 Yahoo! Mail - You care about security. So do we.


Other related posts: