OraFAQ Forum: SQL & PL/SQL » which column to have index

Home » SQL & PL/SQL » SQL & PL/SQL » which column to have index (Linux, Oracle 11.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

which column to have index [message #624447]

Mon, 22 September 2014 19:57

rahul1982
Messages: 53
Registered: November 2011
Location: Pune

Member

Hi,

I am joining below two tables, ARV_tbl is having only 100 records whereas NAME_tbl is having around 10000000 records. Below SQL is not getting execute.

Pls. suggest which column should have index created.

SELECT DISTINCT ARV_DATA.entity_id, 
                --NAME.CORP_NUM, 
                NAME.be_nm, 
                ARV_DATA.cus_id, 
                ARV_DATA.cst_no, 
                ARV_DATA.domestic_juris, 
                ARV_DATA.annual_rate, 
                ARV_DATA.afel_name 
FROM   name_tbl NAME, 
       arv_tbl ARV_DATA 
WHERE  ARV_DATA.domestic_juris = name.incorporation_state_cd 
       AND NAME.be_nm_cur_rec_flg = 'Y' 
       AND NAME.src_bus_nm_type_cd = '3' 
       AND Upper(NAME.be_nm) = corpname

Thankx in advance.

*BlackSwan formatted the SQL Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/

[Updated on: Mon, 22 September 2014 20:03] by Moderator

Report message to a moderator

Re: which column to have index [message #624448 is a reply to message #624447]

Mon, 22 September 2014 20:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

INDEX the columns below

WHERE  ARV_DATA.domestic_juris = name.incorporation_state_cd 
       AND NAME.be_nm_cur_rec_flg = 'Y' 
       AND NAME.src_bus_nm_type_cd = '3' 
       AND Upper(NAME.be_nm) = corpname

BTW use of function like UPPER precludes use of INDEX on NAME.be_nm

Report message to a moderator

Re: which column to have index [message #624452 is a reply to message #624448]

Tue, 23 September 2014 00:10

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

... but you might create a function-based index, such as

create index if_nt_benm on name_tbl (upper(be_nm))

Report message to a moderator

Re: which column to have index [message #624467 is a reply to message #624447]

Tue, 23 September 2014 02:03

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

I would advise you to buy this,
http://www.amazon.com/Oracle-Performance-Tuning-Optimization-Cardinalities/dp/1501022695
the answer to your question drops straight out of chapter 3.

Report message to a moderator

Re: which column to have index [message #624521 is a reply to message #624467]

Tue, 23 September 2014 06:31

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Nice wee plug for Mr Meade there John!

Report message to a moderator

Re: which column to have index [message #624522 is a reply to message #624521]

Tue, 23 September 2014 06:39

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Abso-bloody-lutely. It's good.

Report message to a moderator

Re: which column to have index [message #624538 is a reply to message #624522]

Tue, 23 September 2014 07:32

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I've read a couple of chapters, IMHO it's fantastic. Always been a bit of a fan of Kevin anyway.

Report message to a moderator

Re: which column to have index [message #624568 is a reply to message #624538]

Tue, 23 September 2014 08:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Good to see the decoded real names in page 29 Wink

Report message to a moderator

Previous Topic:	Query to find duplicate rows from table
Next Topic:	Alternate of UNION ALL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:22:27 CDT 2024