Home » RDBMS Server » Performance Tuning » where fiter result rows save before join and grop by operation? (oracle 10g 10.2.0.1)
where fiter result rows save before join and grop by operation? [message #559904] Sat, 07 July 2012 05:24 Go to next message
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?
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 Go to previous messageGo to next message
John Watson
Messages: 4611
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.
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 Go to previous messageGo to next message
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 ?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
select age,count(*) t
from student
where first_name like'Tom%'
group by age
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
John Watson
Messages: 4611
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.
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 Go to previous messageGo to next message
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 !
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 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
Thank you !
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 Go to previous message
John Watson
Messages: 4611
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.
Previous Topic: DB file sequential read (9 merged)
Next Topic: Force Index Unique Scan
Goto Forum:
  


Current Time: Thu Oct 02 07:02:15 CDT 2014

Total time taken to generate the page: 0.11170 seconds