Re: how to tune up this query? (Long post with example)

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 30 Jan 2003 15:43:27 -0000
Message-ID: <3e394823$0$239$ed9e5944@reading.news.pipex.net>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3e3943f6$0$230$ed9e5944_at_reading.news.pipex.net...
> > SELECT IDENT, A_NAME, priority FROM table_name
> > WHERE stage = 'STAGE_1' AND state = 1
> > AND ROWNUM <= 10
> > ORDER BY priority ASC;

<SNIP>
> This one does a FTS, filters for the first ten rows and then orders these
by
> priority. So what you are getting is in effect the first ten rows in the
> heap ordered by priority.
>
> > SELECT IDENT, A_NAME, priority FROM
> > (SELECT IDENT, A_NAME, priority FROM table_name
> > WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC)
> > WHERE ROWNUM <= 10;

<snip>
> this one does its FTS, sorts all the data by priority, then you select the
> first 10 rows from this ordered set.

I meant to say that you can see this from the stats

<snip table creation and population>

SQL>
SQL> set autotrace on explain statistics;
SQL>
SQL> SELECT IDENT, A_NAME, priority FROM tab1

  2 WHERE stage = 'STAGE_1' AND state = 1   3 AND ROWNUM <= 10
  4 ORDER BY priority ASC;

     IDENT A_NAME                           PRIORITY
---------- ------------------------------ ----------
         1 NAME_1                                  1
        11 NAME_11                                11
        21 NAME_21                                21
        31 NAME_31                                31
        41 NAME_41                                41
        51 NAME_51                                51
        61 NAME_61                                61
        71 NAME_71                                71
        81 NAME_81                                81
        91 NAME_91                                91

10 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=10 Bytes=130
          000)

   1    0   SORT (ORDER BY) (Cost=79 Card=10 Bytes=130000)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'TAB1' (Cost=49 Card=5000 Bytes
          =130000)

Statistics

---

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL> SELECT IDENT, A_NAME, priority FROM   2 (SELECT IDENT, A_NAME, priority FROM tab1   3 WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC)   4 WHERE ROWNUM <= 10;

     IDENT A_NAME                           PRIORITY
---------- ------------------------------ ----------
         1 NAME_1                                  1
      1001 NAME_1001                               1
      2001 NAME_2001                               1
      5001 NAME_5001                               1
      4001 NAME_4001                               1
      3001 NAME_3001                               1
      9001 NAME_9001                               1
      8001 NAME_8001                               1
      7001 NAME_7001                               1
      6001 NAME_6001                               1

10 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=10 Bytes=215
          000)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=79 Card=5000 Bytes=215000)
   3    2       SORT (ORDER BY STOPKEY) (Cost=79 Card=5000 Bytes=13000
          0)

   4    3         TABLE ACCESS (FULL) OF 'TAB1' (Cost=49 Card=5000 Byt
          es=130000)

Statistics

---

          0  recursive calls
          0  db block gets
        497  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL> SELECT blocks FROM user_tables WHERE table_name='TAB1';

    BLOCKS

---

       493

If you look at the consistent gets you will see that the first query manages to only visit 4 blocks or thereabouts to see what the lowest priority records are whereas as the second one really does visit the whole table.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Thu Jan 30 2003 - 09:43:27 CST