Home » SQL & PL/SQL » SQL & PL/SQL » Tuning /index (Oracle 10G)
Tuning /index [message #438909] Wed, 13 January 2010 00:24 Go to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Hi Friends,
This might be a very basic question.... but I don't have any knowledge on tuning/index so please help....

this query return me a rows in with in a seconds Smile
SELECT * from sy_pop_master
 WHERE receipt_mode not in ('R','MR','MC')
   AND  Category in (1,2,4)
   AND pop_amt >= 500000



but after adding AND POP_DATE = (sysdate) it takes more than 7 to 8 mints Shocked .... can you tell me why so..
SELECT * from sy_pop_master
 WHERE receipt_mode not in ('R','MR','MC')
   AND  Category in (1,2,4)
   AND pop_amt >= 500000
   [b]AND POP_DATE = (sysdate)[/b] 







Re: Tuning /index [message #438910 is a reply to message #438909] Wed, 13 January 2010 00:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We do not know what "POP_DATE" is & obviously it matters

You need to provide a few details so we know what you know.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Wed, 13 January 2010 00:28]

Report message to a moderator

Re: Tuning /index [message #438914 is a reply to message #438910] Wed, 13 January 2010 00:41 Go to previous messageGo to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

Thanks for quick res ponce BlackSwan.
POP_DATE is DATE column (not null).
table got 2124686 rows.
Re: Tuning /index [message #438915 is a reply to message #438914] Wed, 13 January 2010 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does POP_DATE have an index on it?

post EXPLAIN PLAN for query
Re: Tuning /index [message #438929 is a reply to message #438909] Wed, 13 January 2010 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Tuning /index [message #438930 is a reply to message #438915] Wed, 13 January 2010 01:37 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
In addition , dont we need Trunc() here to get the rows for the day? or otherwise wont fetch the matching rows at that instance of time for the day?

hr@orafaq> select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
13/01/2010 13:10:18


It will filter the rows based on the date on time on that point of time only.

[Updated on: Wed, 13 January 2010 02:18]

Report message to a moderator

Re: Tuning /index [message #438934 is a reply to message #438930] Wed, 13 January 2010 02:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does it return EVERY row within seconds? Or just the first page of results?

Oracle feeds back rows as they are found, so maybe the first query just APPEARS fast because it finds a handful of matching rows early in its search. By adding the POP_DATE clause, those early-found rows may be filtered out, lengthening the search for the first handful of matching rows.

Compare the total time to fetch EVERY row for the two queries.

Ross Leishman
Re: Tuning /index [message #438939 is a reply to message #438934] Wed, 13 January 2010 02:56 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
rleishman wrote on Wed, 13 January 2010 02:22
Does it return EVERY row within seconds? Or just the first page of results?


Index minimise the SEARCH , if you use pipeline function then oracle will return rows as it finds the match else it will return the compete result-set when it done search operation.
Re: Tuning /index [message #438947 is a reply to message #438939] Wed, 13 January 2010 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
if you use pipeline function then oracle will return rows as it finds the match else it will return the compete result-set when it done search operation.


Reality is somewhat more complex than this makes out.
for example, any query, even one from a pipelined function, with an Order By clause will only start to return rows once all the rows have been fetched, as the ordering cannot be done before that (in general).

Re: Tuning /index [message #438976 is a reply to message #438939] Wed, 13 January 2010 05:55 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rahulvb wrote on Wed, 13 January 2010 19:56
Index minimise the SEARCH , if you use pipeline function then oracle will return rows as it finds the match else it will return the compete result-set when it done search operation.


Sorry @rahulvb, I don't know how to sugar-coat this, but that is just plain wrong.

Prove it to yourself. Go to SQL*Plus, find a table with millions of rows, then SELECT * from it. It will start returning rows immediately - not after it has scanned the entire table and found every matching row.

Ross Leishman
Re: Tuning /index [message #438992 is a reply to message #438976] Wed, 13 January 2010 08:27 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
@rleishman , may be I have put it wrong way : -

Just Select * from starts to send rows rapidly as it is just plain statement , how about complex queries.. in my experience if we write the complete queries it takes long time to complete the COMPLETE matching ( searching the opetaion ) and then sending it out to SQL PLUS.

I will be happy if proven wrong Cool and your .02 cents in advance.




Re: Tuning /index [message #439002 is a reply to message #438992] Wed, 13 January 2010 10:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There are execution plans which require all the data from at least one table to be read in before any data can be returned, such as Hash Joins, and Sort Merge Joins.

If the execution plan of the query uses plans like these, then there will be a greater delay between the execution of the query and the first data coming back.

Re: Tuning /index [message #439541 is a reply to message #438909] Mon, 18 January 2010 04:04 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
ashishmate wrote on Wed, 13 January 2010 07:24
Hi Friends,
This might be a very basic question.... but I don't have any knowledge on tuning/index so please help....

Please use EXPLAIN PLAN at least. Without it you might not get a clear answer.
Start sqlplus, enter "set autotrace on" or "set autotrace traceonly", enter both queries and copy/paste plan/stats (tables/list that appear after the execution) for both queries.
In the future please refer to posting guidelines.
Previous Topic: view create replace performance issue
Next Topic: Why IN operator in this way is not working?
Goto Forum:
  


Current Time: Sat Feb 08 20:50:43 CST 2025