Home » SQL & PL/SQL » SQL & PL/SQL » Impace of statistics collection (Oracle9i Release 9.2.0.6.0 )
Impace of statistics collection [message #363339] Fri, 05 December 2008 22:57 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,

I am running the below query in Oracle9i to get the unique rows based on column called GUID. Table TRY contains 15 million records and no indexes on it.

When the statistics of table is not collected, its going for nested loops and the query executes successfully. However if I gather statistics for the table TRY and run the same query, execution plan is completely changed (as expected) but I am getting the error "ORA-01652: unable to extend temp".

Can you please let know in which scenario, gathering the statistics of the table is not advisable. Or it is always recommended to gather statistics.

QUERY:::
--------
select * from try
where rowid in (select max(rowid)
from try gis_xponent_output
where guid is not null group by guid);

Execution Plan Before Analyzing the table
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW OF 'VW_NSO_1'
3 2 SORT (UNIQUE)
4 3 SORT (GROUP BY)
5 4 TABLE ACCESS (FULL) OF 'TRY'
6 1 TABLE ACCESS (BY USER ROWID) OF 'TRY'




Execution Plan After Analyzing the table
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1112462 Card=1325538 052530 Bytes=487798003331040)

1 0 HASH JOIN (Cost=1112462 Card=1325538052530 Bytes=487798003 331040)

2 1 VIEW OF 'VW_NSO_1' (Cost=249945 Card=8269035 Bytes=57883 245)

3 2 SORT (UNIQUE) (Cost=249945 Card=8269035 Bytes=33076140 0)

4 3 SORT (GROUP BY) (Cost=249945 Card=8269035 Bytes=3307
61400)

5 4 TABLE ACCESS (FULL) OF 'TRY' (Cost=105165 Card=103
44574 Bytes=413782960)

6 1 TABLE ACCESS (FULL) OF 'TRY' (Cost=105165 Card=16030142 Bytes=5786881262)


Thanks in advance,
GS
Re: Impace of statistics collection [message #363348 is a reply to message #363339] Sat, 06 December 2008 00:35 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is ALWAYS advisable.
As it is ALWAYS advisable to format your post.

Regards
Michel
Previous Topic: look for & in string
Next Topic: doubt about explain plan
Goto Forum:
  


Current Time: Sun Dec 04 14:26:05 CST 2016

Total time taken to generate the page: 0.07824 seconds