| max and min function taking lot of time [message #630960] |
Tue, 06 January 2015 23:44  |
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 #631014 is a reply to message #630964] |
Wed, 07 January 2015 22:45  |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
