Home » SQL & PL/SQL » SQL & PL/SQL » Tunning Question
Tunning Question [message #223537] Fri, 09 March 2007 06:46 Go to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
Hi all,

Can any one tell about the beloe mentioned query..!!

I am having one table with 10 columns.. In that seqno is having primary key index and flag_indicator is having non_unique index
while trying to use

select * from test where flag_indicator = 1 .. The table is going in full scan (FULL TABLE SCAN) even though non_unique index is present in that column...


Re: Tunning Question [message #223540 is a reply to message #223537] Fri, 09 March 2007 06:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
- How many rows are there in the table?
- how many of these have a value 1 in that column?
- did you analyze your table recently?
Re: Tunning Question [message #223835 is a reply to message #223540] Sun, 11 March 2007 23:57 Go to previous messageGo to next message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
hi frank,

- How many rows are there in the table? 13870237- How many of these have a value 1 in that column? 9027766- did you analyze your table recently? yes.. on 03/11/2007
Re: Tunning Question [message #223866 is a reply to message #223835] Mon, 12 March 2007 02:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, 9 mln out of 14 mln rows have the searched value.
Why would you want to use the index then? Using the index means 1 IO overhead per row (at least), resulting in more IO then a full table scan.
Re: Tunning Question [message #224209 is a reply to message #223835] Tue, 13 March 2007 07:07 Go to previous message
bsureysh
Messages: 17
Registered: January 2007
Location: india
Junior Member
Hi Frank,

Thanks for your suggestion.
Previous Topic: How to invoke multiple sessions of sql*plus thru pl/sql program.
Next Topic: raise_APPILICATION_ERROR
Goto Forum:
  


Current Time: Sat Dec 03 10:00:21 CST 2016

Total time taken to generate the page: 0.17216 seconds