Tag Archives: query optimizing

[sourcecode language=”sql”]
drop table xt_test purge;
create table xt_test(a not null,b not null,c)
as
select
length(object_name)
,nvl(object_id,0)
,o.OBJECT_NAME
from dba_objects o
;
create index ix_test_a on xt_test(a);
begin
dbms_stats.gather_table_stats(
”
,’XT_TEST’
,estimate_percent=>100
,cascade=>true
,method_opt => ‘for all indexed columns size auto’
);
end;
/

select i.index_name
,i.distinct_keys,i.num_rows
,i.blevel,i.leaf_blocks
,i.avg_leaf_blocks_per_key,i.avg_data_blocks_per_key
from user_indexes i
where i.table_name=’XT_TEST’;
[/sourcecode]

[sourcecode language=”sql”]
DB11G/XTENDER> select distinct a from xt_test;

30 rows selected.

Elapsed: 00:00:00.05

Execution Plan
———————————————————-
Plan hash value: 4206828362

———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 30 | 90 | 42 (10)| 00:00:01 |
| 1 | HASH UNIQUE | | 30 | 90 | 42 (10)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IX_TEST_A | 69230 | 202K| 38 (0)| 00:00:01 |
———————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
751 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
[/sourcecode]

[sourcecode language="sql"]
with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a>t.a)
              from t_unique t
              where a is not null
)
select cursor(
              select rid from(
                 select/*+ index_desc(tt ix_xt_test_ab) */
                    tt.a
                   ,tt.rowid rid
                   ,row_number()over(partition by a order by b desc) rn
                 from xt_test tt
                 order by tt.b desc
              )
              where a=v.a and rn<=5
       )
from t_unique v
[/sourcecode]

[sourcecode language="sql"]
DB11G/XTENDER> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a>t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select/*+ use_nl(rids tt) */ *
 10  from t_unique v
 11      ,table(
 12            cast(
 13                 multiset(
 14                          select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid
 15                          from xt_test tt
 16                          where tt.a=v.a
 17                            and rownum<=5
 18                          order by tt.b desc
 19                         )
 20                 as sys.odcivarchar2list
 21                )
 22            ) rids
 23      ,xt_test tt
 24  where tt.rowid=rids.column_value
 25  order by tt.a,tt.b desc;

150 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4085270117

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |    11M|   506M|       |   149K  (1)| 00:29:54 |
|   1 |  SORT ORDER BY                               |               |    11M|   506M|   649M|   149K  (1)| 00:29:54 |
|   2 |   NESTED LOOPS                               |               |    11M|   506M|       | 16402   (1)| 00:03:17 |
|   3 |    NESTED LOOPS                              |               | 16336 |   239K|       |    60   (0)| 00:00:01 |
|   4 |     VIEW                                     |               |     2 |    26 |       |     4   (0)| 00:00:01 |
|   5 |      UNION ALL (RECURSIVE WITH) BREADTH FIRST|               |       |       |       |         |             |
|   6 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   7 |        INDEX FULL SCAN (MIN/MAX)             | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|   8 |       SORT AGGREGATE                         |               |     1 |     3 |       |         |             |
|   9 |        FIRST ROW                             |               |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN (MIN/MAX)           | IX_TEST_A     |     1 |     3 |       |     2   (0)| 00:00:01 |
|* 11 |       RECURSIVE WITH PUMP                    |               |       |       |       |         |             |
|  12 |     COLLECTION ITERATOR SUBQUERY FETCH       |               |  8168 | 16336 |       |    28   (0)| 00:00:01 |
|* 13 |      COUNT STOPKEY                           |               |       |       |       |         |             |
|* 14 |       INDEX RANGE SCAN DESCENDING            | IX_XT_TEST_AB |  2308 | 64624 |       |     8   (0)| 00:00:01 |
|* 15 |    TABLE ACCESS BY USER ROWID                | XT_TEST       |   692 | 22144 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - access("T1"."A">:B1)
  11 - filter("A" IS NOT NULL)
  13 - filter(ROWNUM<=5)
  14 - access("TT"."A"=:B1)
  15 - access(CHARTOROWID(VALUE(KOKBF$)))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        166  consistent gets
          0  physical reads
          0  redo size
       7523  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
        150  rows processed
[/sourcecode]

[sourcecode language="sql"]
alter session set events '22829 trace name context forever';
with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a>t.a)
              from t_unique t
              where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
    ,lateral(
              select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
              from xt_test tt
              where tt.a=v.a
                and rownum<=5
              order by tt.a, b desc
     ) r
order by r.a,r.b desc
[/sourcecode]

[sourcecode language="sql"]
with t_unique( owner ) as (
              select min(owner)
              from ttt
              union all
              select (select min(t1.owner) from ttt t1 where t1.owner>t.owner)
              from t_unique t
              where owner is not null
)
select r.*
from t_unique v
    ,xmltable('/ROWSET/ROW'
              passing(
                dbms_xmlgen.getxmltype(
                  q'[select *
                     from (
                       select/*+ index_asc(tt ix_ttt) */ owner, to_char(created,'yyyy-mm-dd hh24:mi:ss') created
                       from ttt tt
                       where tt.owner=']'||v.owner||q'['
                       order by tt.created asc
                     )
                     where rownum<=5
                  ]'
                )
              )
              columns
                owner   varchar2(30) path 'OWNER'
               ,created varchar2(30) path 'CREATED'
               ,x xmltype path '.'
             ) r
where
  v.owner is not null
order by r.owner,r.created asc;

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                        |      1 |        |    148 |00:00:00.28 |     365 |       |       |          |
|   1 |  SORT ORDER BY                              |                        |      1 |  16336 |    148 |00:00:00.28 |     365 | 20480 | 20480 |18432  (0)|
|   2 |   NESTED LOOPS                              |                        |      1 |  16336 |    148 |00:00:00.10 |     365 |       |       |          |
|*  3 |    VIEW                                     |                        |      1 |      2 |     30 |00:00:00.01 |      66 |       |       |          |
|   4 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|                        |      1 |        |     31 |00:00:00.01 |      66 |       |       |          |
|   5 |      SORT AGGREGATE                         |                        |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       INDEX FULL SCAN (MIN/MAX)             | IX_TTT                 |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   7 |      SORT AGGREGATE                         |                        |     30 |      1 |     30 |00:00:00.01 |      63 |       |       |          |
|   8 |       FIRST ROW                             |                        |     30 |      1 |     29 |00:00:00.01 |      63 |       |       |          |
|*  9 |        INDEX RANGE SCAN (MIN/MAX)           | IX_TTT                 |     30 |      1 |     29 |00:00:00.01 |      63 |       |       |          |
|  10 |      RECURSIVE WITH PUMP                    |                        |     31 |        |     30 |00:00:00.01 |       0 |       |       |          |
|  11 |    COLLECTION ITERATOR PICKLER FETCH        | XMLSEQUENCEFROMXMLTYPE |     30 |   8168 |    148 |00:00:00.10 |     299 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("V"."OWNER" IS NOT NULL)
   9 - access("T1"."OWNER">:B1)
[/sourcecode]