Tuning /index [message #438909] |
Wed, 13 January 2010 00:24  |
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
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 .... 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 #438930 is a reply to message #438915] |
Wed, 13 January 2010 01:37   |
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   |
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 #438947 is a reply to message #438939] |
Wed, 13 January 2010 03:09   |
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   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
rahulvb wrote on Wed, 13 January 2010 19:56Index 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 #439002 is a reply to message #438992] |
Wed, 13 January 2010 10:04   |
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  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
ashishmate wrote on Wed, 13 January 2010 07:24Hi 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.
|
|
|