OraFAQ Forum: Performance Tuning » where fiter result rows save before join and grop by operation?

Home » RDBMS Server » Performance Tuning » where fiter result rows save before join and grop by operation? (oracle 10g 10.2.0.1)

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

where fiter result rows save before join and grop by operation? [message #559904]

Sat, 07 July 2012 05:24

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

Dedicated Server
Where filter middle_rows save before join and grop by operation?

It is save rows in PGA Private SQL Area or save blocks in SGA databuffer?

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559905 is a reply to message #559904]

Sat, 07 July 2012 06:00

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Depends on the type of read. In 10.x, serial reads are always indirect to the database buffer cache in the SGA, parallel reads are always direct to PGA. In the current release, serial reads are sometimes direct and parallel reads are sometimes buffered.

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559906 is a reply to message #559905]

Sat, 07 July 2012 06:17

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

serial reads are always indirect to the database buffer cache in the SGA

That is serial reads

It is reads blocks from data files to data buffer cache in the sga.
where result save when filter operation over ?

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559913 is a reply to message #559906]

Sat, 07 July 2012 09:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nowhere, the result is computed/retrieved when you asked you fetch for it unless you have some operation like group or sort that forces the result to be first retrieve then the final result is store in "X" area size and maybe in your assigned temporary tablespace.

Regards
Michel

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559937 is a reply to message #559913]

Sat, 07 July 2012 22:12

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

select age,count(*) t
from student
where first_name like'Tom%'
group by age

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559941 is a reply to message #559937]

Sun, 08 July 2012 00:36

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Rows to be returned are stored in some are size (depending on the execution plan) in PGA or in temporary tablespace if the area is not large enough (see "show parameter area").

Regards
Michel

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559945 is a reply to message #559941]

Sun, 08 July 2012 01:11

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_retained_size integer 0
sort_area_size integer 65536
workarea_size_policy string AUTO

your mean is rows save in PGA sort_area_size when it enough and not enough then save to temporary tablesapce

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559946 is a reply to message #559945]

Sun, 08 July 2012 01:13

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

When join operation rows save in hash_area_size ?

select a.*.b.*
from a
inner join b on a.id=b.id

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559947 is a reply to message #559946]

Sun, 08 July 2012 01:22

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Hello again. As Michel said, the rows aren't "saved" (which is not a good term) anywhere. If a hash join is used, one table is read into the hash area as a hash table, and the other table is scanned: as each row of the second table is scanned, the first table is probed on the hash key to make the join. So the joined rows aren't "saved". They are generated.

And by the way, you haven't said "please" or "thank you" yet. Not even once.

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559961 is a reply to message #559947]

Sun, 08 July 2012 04:36

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

to: John Watson
Thank you

select *
from
(
select id,name,age
from X
where x.name like 'tom%'
)
inner join
(
select id,name,Course,Score
from Z
where z.Score >'B'
) b on a.id=b.id

The A sub sql return rows stored to PGA HASH_AREA ?
where b sub sql return rows store ?

Please thank you !

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559962 is a reply to message #559961]

Sun, 08 July 2012 04:41

zengmuansha
Messages: 26
Registered: April 2012

Junior Member

Thank you !

Report message to a moderator

Re: where fiter result rows save before join and grop by operation? [message #559964 is a reply to message #559961]

Sun, 08 July 2012 04:53

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Do some reading, at least this chapter of the Performance Tuning guide:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i21299
If after reading that you do not understand how joins are implemented (why do think hash join is the method that would be chosen?) then ask again. But you will have to prove that you have read that manual.

Report message to a moderator

Previous Topic:	DB file sequential read (9 merged)
Next Topic:	Force Index Unique Scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:07:57 CST 2025