Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Question Unanswered: order by clause performance

    I have two tables table A (with 26 columns most of them varchar2) with 1.2 million records and table 2 with 30 records. I am doing a join and then ordering the result set. I have two set of queries one takes 164 seconds and the other 1.2 seconds.

    I am not able to figure out why such a drastic difference when the only difference is in first case I do order by on a few columns and in the second case I do it on entire row.

    TableA: firstname and lastname column are functional indexed (using upper)
    Search by firstname 'JOHN%' returns back 37000+ records.

    SLOW QUERY (takes 164 seconds):
    SELECT * FROM (
    SELECT A.firstname, A.lastname, A.col4, A.col5......................A.col15
    FROM tableA A, tableB B
    WHERE A.col3 = B.col3 AND upper(A.FIRSTNAME) LIKE 'JOHN%'
    ORDER BY uppeR(A.LASTNAME)
    )
    WHERE ROWNUM <251



    FAST Query (takes 1.2s):
    SELECT Firstname, lastname, col4, col5 ..........col15
    FROM (SELECT A.*
    FROM tableA A, tableB B
    WHERE A.col3 = B.col3 AND UPPER(A.FIRSTNAME) LIKE 'JOHN%'
    ORDER BY UPPER(A.LASTNAME)
    )
    WHERE ROWNUM < 251
    Last edited by wasimalam; 03-04-11 at 18:49.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post FORMATTED EXPLAIN PLAN for both SQL

    use "code tags" as described below

    dBforums - BB Code List
    Last edited by anacedent; 03-04-11 at 21:00.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2006
    Location
    Bangalore
    Posts
    57
    Even the difference we can see using the sample tables comes with the oracle sample schema also.
    looks like the second query using index scan for the department table.
    While the first query used filter on result sets of employee table.

    Code:
    SQL> SELECT * FROM
      2  (
      3   SELECT e.employee_id, e.First_name, e.last_name, e.email
      4   FROM hr.employees e, hr.departments d
      5   WHERE e.department_id=d.department_id AND UPPER(e.first_name) LIKE 'A%'
      6   ORDER BY UPPER(e.last_name)
      7  )
      8  WHERE ROWNUM < 10;
    
    9 rows selected.
    
    Elapsed: 00:00:00.04
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 460450477
    
    -------------------------------------------------------------------------------------
    | Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |           |     5 |   265 |     4  (25)| 00:00:01 |
    |*  1 |  COUNT STOPKEY          |           |       |       |            |          |
    |   2 |   VIEW                  |           |     5 |   265 |     4  (25)| 00:00:01 |
    |*  3 |    SORT ORDER BY STOPKEY|           |     5 |   150 |     4  (25)| 00:00:01 |
    |*  4 |     TABLE ACCESS FULL   | EMPLOYEES |     5 |   150 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<10)
       3 - filter(ROWNUM<10)
       4 - filter(UPPER("E"."FIRST_NAME") LIKE 'A%' AND "E"."DEPARTMENT_ID" IS
                  NOT NULL)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              7  consistent gets
              0  physical reads
              0  redo size
            885  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              9  rows processed
    Code:
    SQL> SELECT employee_id, First_name, last_name, email  FROM
      2  (
      3   SELECT *
      4   FROM hr.employees e, hr.departments d
      5   WHERE e.department_id=d.department_id AND UPPER(e.first_name) LIKE 'A%'
      6   ORDER BY UPPER(e.last_name)
      7  )
      8  WHERE ROWNUM < 10;
    
    9 rows selected.
    
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3230686850
    
    --------------------------------------------------------------------------------------
    | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |            |     5 |   265 |     5  (20)| 00:00:01 |
    |*  1 |  COUNT STOPKEY          |            |       |       |            |          |
    |   2 |   VIEW                  |            |     5 |   265 |     5  (20)| 00:00:01 |
    |*  3 |    SORT ORDER BY STOPKEY|            |     5 |   440 |     5  (20)| 00:00:01 |
    |   4 |     NESTED LOOPS        |            |     5 |   440 |     4   (0)| 00:00:01 |
    |*  5 |      TABLE ACCESS FULL  | EMPLOYEES  |     5 |   340 |     3   (0)| 00:00:01 |
    |*  6 |      INDEX UNIQUE SCAN  | DEPT_ID_PK |     1 |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<10)
       3 - filter(ROWNUM<10)
       5 - filter(UPPER("E"."FIRST_NAME") LIKE 'A%')
       6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
    
    
    Statistics
    ----------------------------------------------------------
              8  recursive calls
              0  db block gets
             19  consistent gets
              0  physical reads
              0  redo size
            885  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              9  rows processed
    Ritesh Kumar Singh
    IBM Certified DB2 DBA for LUW
    **Knowledge Is Theft If Not Shared !!**

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    singhipst,

    since your example does not match with your original post and we don't know anything about indexes, execution plan, etc. of your OP, I just comment on the HR example:

    Code:
    select t2.col1, t2.col2, .., t2.coln
    from   t1, t2
    where t1.pk_col = t2.fk_col
    is VERY different from

    Code:
    select *
    from   t1, t2
    where t1.pk_col = t2.fk_col
    In the first case you are selecting only from one table (t2), where - by definition of a foreign key - every tuple of t2 is a valid result.
    So: No need even to look into t1 and you get along with a full table scan of t2

    In the second case, however, you are requesting ALL columns from t1 AND t2.
    to get the values for the columns of t1 a join (in this case over a nested loop) has to be performed.

    If t1 has n rows and t2 m,
    you are accessing
    m rows
    in example 1 and
    m*(2+index access overhead) rows
    or
    m*n rows (if there are no indexes on t1)
    in example 2
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •