why is this query asking for more space in temp tablespace?

[parijat67]

hi all,

i am getting an error while executing the following sql statement. The error is "ORA-01652: unable to extend temp segment by 256 in tablespace TEMP". This query does not use any order by clause however uses many inline views. The execution plan shows proper utilization of indexes(index skip scans) and the overall cost is 118 units. After getting this error I have increased the temp tablespace size to 2GB from its initial size of 100MB but even then this single query is using the whole of 2gb space and still needs more space. Each of the inline views return 184 records and has 5-6 fields in it.

pls mention what is going wrong and how to solve this problem. I cant make the query any shorter for sure.


SELECT
RS0.objecttypeid,
RS0.objectid,
RS0.propertyid,
RS0.sequence,
RS0.mailingaddress_TYPEID ,
RS0.mailingaddress_ID ,
RS1.salutation ,
RS2.zipcode ,
RS3.email ,
RS4.addressline4 ,
RS5.firstname ,
RS6.title ,
RS7.addressline3 ,
RS8.active ,
RS9.statesidref_typeid ,
RS9.statesidref_id ,
RS10.addressline1 ,
RS11.middleinitial ,
RS12.telephone ,
RS13.companyname ,
RS14.entitytyperef_typeid ,
RS14.entitytyperef_id ,
RS15.city ,
RS16.addressline2 ,
RS17.lastname ,
RS18.fax ,
RS19.countryidref_typeid ,
RS19.countryidref_id ,
RS20.addressline5
FROM
( select objecttypeid,
objectid,
propertyid,
sequence,
valueobjecttypeid mailingaddress_typeid,
VALUEOBJECTID mailingaddress_id from paul.TB_PROPERTIES_OBJECTS
WHERE
objecttypeid = 10113 and
propertyid = 869 and
valueobjecttypeid = 45028 ) RS0 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY salutation from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 852 ) RS1 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY zipcode from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 29 ) RS2 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY email from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 863 ) RS3 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY addressline4 from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 857 ) RS4 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY firstname from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 849 ) RS5 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY title from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 853 ) RS6 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY addressline3 from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 856 ) RS7 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY active from paul.TB_PROPERTIES_LOGICAL where objecttypeid = 45028 and
propertyid = 864 ) RS8 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEOBJECTTYPEID statesidref_typeid, VALUEOBJECTID statesidref_id from paul.TB_PROPERTIES_OBJECTS where objecttypeid = 45028 and
propertyid = 860 ) RS9 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY addressline1 from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 854 ) RS10 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY middleinitial from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 850 ) RS11 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY telephone from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 861 ) RS12 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY companyname from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 35 ) RS13 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEOBJECTTYPEID entitytyperef_typeid, VALUEOBJECTID entitytyperef_id from paul.TB_PROPERTIES_OBJECTS where objecttypeid = 45028 and
propertyid = 848 ) RS14 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY city from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 859 ) RS15 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY addressline2 from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 855 ) RS16 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY lastname from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 851 ) RS17 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY fax from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 862 ) RS18 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEOBJECTTYPEID countryidref_typeid, VALUEOBJECTID countryidref_id from paul.TB_PROPERTIES_OBJECTS where objecttypeid = 45028 and
propertyid = 844 ) RS19 , ( select OBJECTTYPEID, OBJECTID, PROPERTYID, SEQUENCE, VALUEPROPERTY addressline5 from paul.TB_PROPERTIES_TEXT where objecttypeid = 45028 and
propertyid = 858 ) RS20 where RS0.mailingaddress_id = RS1.objectid and
RS1.objectid = RS2.objectid and
RS2.objectid = RS3.objectid and
RS3.objectid = RS4.objectid and
RS4.objectid = RS5.objectid and
RS5.objectid = RS6.objectid and
RS6.objectid = RS7.objectid and
RS7.objectid = RS8.objectid and
RS8.objectid = RS9.objectid and
RS9.objectid = RS10.objectid and
RS10.objectid = RS11.objectid and
RS11.objectid = RS12.objectid and
RS12.objectid = RS13.objectid and
RS13.objectid = RS14.objectid and
RS14.objectid = RS15.objectid and
RS15.objectid = RS16.objectid and
RS16.objectid = RS17.objectid and
RS17.objectid = RS18.objectid and
RS18.objectid = RS19.objectid and
RS19.objectid = RS20.objectid



thanks

Parijat Paul

[stmontgo]

post the execution plan, 50 bucks says it shows a cartesian

[parijat67]

hi,

the query plan is given below:

thanks

paul


Query Plan
----------------------------------------------------------------------------------------------------
1.118 SELECT STATEMENT id = Cost = 118
2.1 HASH JOIN
3.1 HASH JOIN
4.1 HASH JOIN
5.1 HASH JOIN
6.1 HASH JOIN
7.1 HASH JOIN
8.1 HASH JOIN
9.1 HASH JOIN
10.1 HASH JOIN
11.1 HASH JOIN

Query Plan
----------------------------------------------------------------------------------------------------
12.1 HASH JOIN
13.1 HASH JOIN
14.1 HASH JOIN
15.1 HASH JOIN
16.1 HASH JOIN
17.1 HASH JOIN
18.1 HASH JOIN
19.1 HASH JOIN
20.1 HASH JOIN
21.1 HASH JOIN
22.1 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_OBJECTS

Query Plan
----------------------------------------------------------------------------------------------------
23.1 INDEX SKIP SCAN PROP_OBJ_OBJTYP_PROPID NON-UNIQUE
22.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
23.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
21.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
22.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
20.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
21.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
19.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
20.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
18.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
19.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE

Query Plan
----------------------------------------------------------------------------------------------------
17.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
18.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
16.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
17.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
15.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_LOGICAL
16.1 INDEX SKIP SCAN PROP_LOGICAL_OBJTYPE_PROPID NON-UNIQUE
14.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_OBJECTS
15.1 INDEX SKIP SCAN PROP_OBJ_OBJTYP_PROPID NON-UNIQUE
13.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
14.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
12.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT

Query Plan
----------------------------------------------------------------------------------------------------
13.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
11.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
12.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
10.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
11.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
9.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_OBJECTS
10.1 INDEX SKIP SCAN PROP_OBJ_OBJTYP_PROPID NON-UNIQUE
8.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
9.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
8.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE

Query Plan
----------------------------------------------------------------------------------------------------
6.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
7.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
5.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
6.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE
4.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_OBJECTS
5.1 INDEX SKIP SCAN PROP_OBJ_OBJTYP_PROPID NON-UNIQUE
3.2 TABLE ACCESS BY INDEX ROWID TB_PROPERTIES_TEXT
4.1 INDEX SKIP SCAN PROP_TXT_OJTYPEID_PROPID_IDX NON-UNIQUE

63 rows selected.

[abhaysk]

Originally posted by parijat67
hi all,

pls mention what is going wrong and how to solve this problem. I cant make the query any shorter for sure.


thanks

Parijat Paul

U can make the query very simple....


BTW, i really dont understand this part of the query

( select objecttypeid,
objectid,
propertyid,
sequence,
valueobjecttypeid mailingaddress_typeid,
VALUEOBJECTID mailingaddress_id from paul.TB_PROPERTIES_OBJECTS, paul.TB_PROPERTIES_TEXT, TB_PROPERTIES_LOGICAL
WHERE 
objecttypeid = 10113 and
propertyid = 869 and
valueobjecttypeid = 45028 ) RS0

The columns objecttypeid & propertyid does exist in all three tables ( as from all of other sub quries )...
Unless columns are distinct to the tables, You have to have alias.column name...

I am not sure how did it work for you...

Abhay.

[stmontgo]

how ar e you getting the explain plan? try
set autotrace traceonly explain, run the select and then post the plan

steve

[Umesh_Shar]

Try giving "unlimited quota" grant on the tablespace to the user whom you are running the query as. This might help in some cases.

Regards,
Umesh S Sharoff