Re: Why the monstrous SORT?

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 7 Jul 2005 01:16:09 +0100
Message-ID: <bf463805070617161d2b1e59@mail.gmail.com>

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

| 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_at_oneneck.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 06 2005 - 19:18:12 CDT