OraFAQ Forum: Performance Tuning » how do oracle filter data?

Home » RDBMS Server » Performance Tuning » how do oracle filter data? (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how do oracle filter data? [message #551953]

Sun, 22 April 2012 21:18

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

t_user(id,name,provcode,areacode,salary)
the table have no index,key,partiton.
it have 3,000,000 rows

the sql

select provcode,sum(salary)
from t_user
where provcode in (1,2,3,4,5)
group by provcode

the sql plant
----------------------------------------------------------
Plan hash value: 2578068701

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    26 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |        |     1 |    26 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_USER |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - filter("PROVCODE"=1 OR "PROVCODE"=2 OR "PROVCODE"=3 OR
"PROVCODE"=4 OR "PROVCODE"=5)

Note
-----
- dynamic sampling used for this statement

I want it that filter step
1 read all block into buffer
2 filter no include rows block
3 fetch rows from include rows block into new buffer block
4 fetch columns from new buffer block into new buffer block
5 sort include a few columns block
6 goup by and sum rows
7 send result to USER

I want the oracle filter setp is True?

Why temp table is faster than direct sum()?

the temp table :
1 create t_user_temp(provcode,salary)
2 insert into t_user_temp
select provcode,salary
from t_user
where provcode in (1,2,3,4,5)
3 select provcode sum(salary)
from t_user_temp

Report message to a moderator

Re: how do oracle filter data? [message #551954 is a reply to message #551953]

Sun, 22 April 2012 21:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>it have 3,000,000 rows
Then why does EXPLAIN PLAN show only 1 row?
If/when CBO does not have accurate statistics, it can produce incorrect/slow execution plan.

Report message to a moderator

Re: how do oracle filter data? [message #551955 is a reply to message #551954]

Sun, 22 April 2012 21:54

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

Thank you
The plan is empt table to test and now generation 120,000 rows

the new plan it is :

----------------------------------------------------------
Plan hash value: 2578068701

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     5 |    40 |   163   (3)| 00:00:04 |
|   1 |  HASH GROUP BY     |        |     5 |    40 |   163   (3)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T_USER | 19335 |   151K|   162   (2)| 00:00:04 |
-----------------------------------------------------------------------------

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

2 - filter("PROVCODE"=1 OR "PROVCODE"=2 OR "PROVCODE"=3 OR
"PROVCODE"=4 OR "PROVCODE"=5)

Report message to a moderator

Re: how do oracle filter data? [message #551956 is a reply to message #551955]

Sun, 22 April 2012 22:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The plan is empt table to test and now generation 120,000 rows
>Rows=19335 from most recent PLAN;
which is still a 6 fold discrepancy.

>Why temp table is faster than direct sum()?
we have seen no proof above is true or reproducible.

Do the results change when the order of the tests are reversed?
What is the impact of having rows already in SGA for second & subsequent invocations?

Report message to a moderator

Re: how do oracle filter data? [message #551957 is a reply to message #551956]

Sun, 22 April 2012 22:20

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

i have see other tool to plan
the plan "TABLE ACCESS FULL" do filter data!
>Rows=19335 from most recent PLAN;
>which is still a 6 fold discrepancy.

SQL> select count(*) from t_user;

COUNT(*)
----------
120002

SQL> select count(*) from t_user where provcode in(1,2,3,4,5);

COUNT(*)
----------
19448

Report message to a moderator

Re: how do oracle filter data? [message #551958 is a reply to message #551957]

Sun, 22 April 2012 22:24

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

I have forget FLUSH SHARED_POOL at run procedure
Slow than direct sum()!

Report message to a moderator

Re: how do oracle filter data? [message #551959 is a reply to message #551958]

Sun, 22 April 2012 22:24

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

I want the oracle filter setp is True?

1 read all block into buffer
2 filter no include rows block
3 fetch rows from include rows block into new buffer block
4 fetch columns from new buffer block into new buffer block
5 sort include a few columns block
6 goup by and sum rows
7 send result to USER

Report message to a moderator

Re: how do oracle filter data? [message #551960 is a reply to message #551959]

Sun, 22 April 2012 22:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

OK, it is TRUE.
So what actually changes for you now?

Report message to a moderator

Previous Topic:	Oracle Apps/Software installed in a dB Server
Next Topic:	How to Tune Long running queries?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 14:17:18 CST 2025