Most of documents about this topic,tell you that the Temporary TS is used when the "SORTS" can't be done fully in memory (order by, group by,union ...). Attending this,we are confuse,because we have some statements that are using the Temporary TS and don't involve "SORTS" clauses. This query, without Order by, of a table with only "7 records":
"SELECT NOMBRE,DIA_PROCESO FROM ARINCD WHERE NO_CIA = :b1 AND CENTRO = :b2 "
is using the Temporary TS, with 111 extents and 2775 blocks of 8K (consuming almost "22 MB"). Too much bytes for only 7 records! Don't you think so ?
The Explain Plan is:
SELECT STATEMENT Cost = 1
21 TABLE ACCESS BY INDEX ROWID ARINCD
31 INDEX RANGE SCAN UNIQUE PK_ARINCD
We already used the hints FULL,ROWID, ALL_ROWS,INDEX, FIRST_ROWS with the same result on the explain plan.
1. Is the Temporary TS used for other tasks than "SORTS" only ?
2. Is normal this query wastes that amount of bytes (22 MB)with a only "7 records" table? How can we resolve this situation ?