Home » RDBMS Server » Performance Tuning » max and min function taking lot of time (oracle 10g)
max and min function taking lot of time [message #630960] Tue, 06 January 2015 23:44 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
we are querying min and max function on table which is taking lot of time.


table structure is



set1
(at_id varchar2(20),
set_date date,
st varchar2(20))


on daily bases java application is inserting entries into setl table.


now we are generating reports on this table which is taking lot of time

query is

select min(set_date),max(set_date) from setl
where
at_id='sa01'
and st='CLOSE'



Re: max and min function taking lot of time [message #630962 is a reply to message #630960] Tue, 06 January 2015 23:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Re: max and min function taking lot of time [message #630963 is a reply to message #630962] Wed, 07 January 2015 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are at_id and st columns both indexed?
Re: max and min function taking lot of time [message #630964 is a reply to message #630960] Wed, 07 January 2015 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: max and min function taking lot of time [message #631014 is a reply to message #630964] Wed, 07 January 2015 22:45 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Assuming you have lots of rows that match your query criteria, you might consider two queries instead of one in order to take advantage of the MIN/MAX of index optimization. FIRST ROW (seen in some of the plan below) means use of the index stops after the first rows is reached. This means you do not traverse any portion of the index and then group the results to get your min or max which is what you are doing in the other plans that do not have this. Since the index shown below is sorted by the column values, the first row for each set of keys has the MIN and the last row for each set of keys has the MAX. Knowing this, Oracle is able to either start at the front of that group of rows and take the first row it comes to which gives the min, or start at the end of the group of rows for the key and take the first row which gives the max. Thus you touch only one index entry with each query instead of all index entries that match your key. Can't get much faster than that for a min or max. This works because you have all equalities in the query and the index has all the right columns with the min/max column at the end. But you can only search for one at a time and hence the need for two queries instead of one. But I show how to get around that at the bottom of this post too.

create index set1_i1 on set1 (id,st,set_date)
/

select min(set_date)
 from setl
 where at_id='sa01'
 and st='CLOSE'
/

select max(set_date)
 from setl
 where at_id='sa01'
 and st='CLOSE'
/


Consider the differences in these explain plans.

drop table kevtemp1
/

create table kevtemp1
as
select *
from dba_objects
/

create index kevtemp1_i1 on kevtemp1 (owner,object_name,created)
/

explain plan for select min(created),max(created)
from kevtemp1
where owner = 'SYS'
and object_name = 'DBA_TABLES'
/

@showplan11gshort

explain plan for select min(created)
from kevtemp1
where owner = 'SYS'
and object_name = 'DBA_TABLES'
/

@showplan11gshort

explain plan for select max(created)
from kevtemp1
where owner = 'SYS'
and object_name = 'DBA_TABLES'
/

@showplan11gshort


And the results

23:29:41 SQL> explain plan for select min(created),max(created)
23:29:41   2  from kevtemp1
23:29:41   3  where owner = 'SYS'
23:29:41   4  and object_name = 'DBA_TABLES'
23:29:41   5  /

Explained.

Elapsed: 00:00:00.01
23:29:41 SQL>
23:29:41 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2640567105

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    92 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    92 |            |          |
|*  2 |   INDEX RANGE SCAN| KEVTEMP1_I1 |     1 |    92 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBA_TABLES')

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

Elapsed: 00:00:00.07
23:29:41 SQL>
23:29:41 SQL> explain plan for select min(created)
23:29:41   2  from kevtemp1
23:29:41   3  where owner = 'SYS'
23:29:41   4  and object_name = 'DBA_TABLES'
23:29:41   5  /

Explained.

Elapsed: 00:00:00.01
23:29:41 SQL>
23:29:41 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1604076070

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    92 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |    92 |            |          |
|   2 |   FIRST ROW                  |             |     1 |    92 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| KEVTEMP1_I1 |     1 |    92 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBA_TABLES')

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

Elapsed: 00:00:00.07
23:29:41 SQL>
23:29:41 SQL> explain plan for select max(created)
23:29:41   2  from kevtemp1
23:29:41   3  where owner = 'SYS'
23:29:41   4  and object_name = 'DBA_TABLES'
23:29:41   5  /

Explained.

Elapsed: 00:00:00.00
23:29:41 SQL>
23:29:41 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1604076070

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    92 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |    92 |            |          |
|   2 |   FIRST ROW                  |             |     1 |    92 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| KEVTEMP1_I1 |     1 |    92 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBA_TABLES')

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

Elapsed: 00:00:00.07
23:29:41 SQL>


You can even put them together as in this.

explain plan for select * from
(
  select min(created)
  from kevtemp1
  where owner = 'SYS'
  and object_name = 'DBA_TABLES'
) min_date
,(
   select max(created)
   from kevtemp1
   where owner = 'SYS'
   and object_name = 'DBA_TABLES'
) max_date
/

@showplan11gshort


which yields

23:38:32 SQL> explain plan for select * from
23:38:35   2  (
23:38:35   3    select min(created)
23:38:35   4    from kevtemp1
23:38:35   5    where owner = 'SYS'
23:38:35   6    and object_name = 'DBA_TABLES'
23:38:35   7  ) min_date
23:38:35   8  ,(
23:38:35   9     select max(created)
23:38:35  10     from kevtemp1
23:38:35  11     where owner = 'SYS'
23:38:35  12     and object_name = 'DBA_TABLES'
23:38:35  13  ) max_date
23:38:35  14  /

Explained.

Elapsed: 00:00:00.01
23:38:35 SQL>
23:38:35 SQL> @showplan11gshort

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 160510377

----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |    18 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |             |     1 |    18 |     6   (0)| 00:00:01 |
|   2 |   VIEW                         |             |     1 |     9 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |             |     1 |    92 |            |          |
|   4 |     FIRST ROW                  |             |     1 |    92 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| KEVTEMP1_I1 |     1 |    92 |     3   (0)| 00:00:01 |
|   6 |   VIEW                         |             |     1 |     9 |     3   (0)| 00:00:01 |
|   7 |    SORT AGGREGATE              |             |     1 |    92 |            |          |
|   8 |     FIRST ROW                  |             |     1 |    92 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN (MIN/MAX)| KEVTEMP1_I1 |     1 |    92 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBA_TABLES')
   9 - access("OWNER"='SYS' AND "OBJECT_NAME"='DBA_TABLES')

Note
-----
   - dynamic sampling used for this statement (level=2)

26 rows selected.

Elapsed: 00:00:00.09


Good luck. Kevin

[Updated on: Wed, 07 January 2015 22:47]

Report message to a moderator

Previous Topic: How To Check Performace Tuning
Next Topic: redo buffer allocation retries not near 0, how do I set _log_parallelism_max
Goto Forum:
  


Current Time: Thu Mar 28 07:30:25 CDT 2024