Re: Why the monstrous SORT?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Thu, 7 Jul 2005 01:16:09 +0100
That would be due to the group by.
See the following demo.
16 rows returned from the SQL.
46000 rows processed.
Jared
17:16:38 SQL>
17:16:38 SQL>create table tmp_objects
17:16:38 2 as
17:16:38 3 select owner, object_name
17:16:38 4 from dba_objects
17:16:38 5 /
Table created.
17:16:38 SQL>
17:16:38 SQL>exec
dbms_stats.gather_table_stats(user,tabname=>'TMP_OBJECTS',estimate_percent=>100)
PL/SQL procedure successfully completed.
17:16:38 SQL>
17:16:38 SQL>explain plan
17:16:38 2 set statement_id = 'SORT'
17:16:38 3 for
17:16:38 4 select owner, count(*) object_count
17:16:38 5 from tmp_objects
17:16:38 6 group by owner
17:16:38 7 order by owner
17:16:38 8 /
Explained.
17:16:38 SQL>
17:16:38 SQL>@showplan9i SORT
17:16:38 SQL>
17:16:38 SQL>-- showplan9i.sql
17:16:38 SQL>-- works with 7.3+
17:16:38 SQL>
17:16:38 SQL>SET PAUSE OFF
17:16:38 SQL>SET VERIFY OFF
17:16:38 SQL>set trimspool on
17:16:38 SQL>set line 200 arraysize 1
17:16:38 SQL>clear break
17:16:38 SQL>clear compute
17:16:38 SQL>
17:16:38 SQL>
17:16:38 SQL>select plan_table_output
17:16:38 2 from table(dbms_xplan.display( 'PLAN_TABLE', '&&1'))
17:16:38 3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 599302501
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 96 | 58 (9)| 00:00:01 |
| 1 | SORT GROUP BY | | 16 | 96 | 58 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TMP_OBJECTS | 46115 | 270K| 54 (2)| 00:00:01 |
----------------------------------------------------------------------------------
9 rows selected.
17:16:38 SQL>
17:16:38 SQL>
17:16:38 SQL>select owner, count(*) object_count
17:16:38 2 from tmp_objects
17:16:38 3 group by owner
17:16:38 4 order by owner
17:16:38 5 /
OWNER OBJECT_COUNT
---------- ------------
CTXSYS 338
DBSNMP 13
DMSYS 869
EXFSYS 166
XXXXXXXX 23
MDSYS 589
OLAPSYS 705
ORDPLUGINS 38
ORDSYS 1482
OUTLN 7
PUBLIC 18767
SI_INFORMT 8
N_SCHEMA
SYS 21803
SYSTEM 438
WMSYS 235
XDB 634
16 rows selected.
17:16:38 SQL>
On 7/7/05, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
>
> Sorry, I forgot to include the query - here it is in all its ugliness,
> along with the execution statistics from tkprof:
>
>
> ********************************************************************************
>
> SELECT c.entity bl_entity, c.sa_ent_ref bl_ent_ref,
> e.bitmap_location_path bl_logo, e.company_name bl_company_name,
> c.salesperson_no bl_salesperson_no, f.salesperson_no || ' - ' || f.des1
> bl_salesperson, c.customer_no bl_customer_no,
> ...
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
