Q
Problem solve Get help with specific problems with your technologies, process and projects.

Temp space usage increases when query includes view

I run a query which joins four to five tables (it includes views). When the query is run without a particular view the query runs fine, but when we include that view the temp space usage by the query increases rapidly.

I run a query which joins four to five tables (it includes views). When the query is run without a particular view the query runs fine, but when we include that view the temp space usage by the query increases rapidly. The script for the view is something like this:
SELECT v_id,
v_name,
v_flag,
v_date,
created_by,
FROM base_table
WHERE (v_id, v_name,rowid) IN (SELECT v_id, v_name,max(rowid)
FROM base_table WHERE a_flag = 'Y' GROUP BY v_id , v_name ) 
The base table has around 8,000 records and this view selects about 57 records. What might be the issue with the view that is increasing temp space usage of the query?
The GROUP BY in the view definition requires sorting. Adding that to a join of four or five other tables may sufficiently increase the amount of data to be sorted so that it can no longer be sorted in memory, thus requiring the use of temporary segments.
This was last published in April 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close