This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 14, 2013 9:34 AM by 899722 Go to original post RSS
  • 15. Re: SQL Query : Order By issue with HUGE Table
    Martin Preiss Expert
    Currently Being Moderated
    896719 wrote:
    But if your table indeed has a size of 74328 MB then I suppose a full table scan will also take some time. 
    How long can we expect the wait time normally if this is the case ..
    it depends on your hardware - but I assume it would take more time than the 15 minutes of your second query.

    Here is a small example of the benefit that an index brings that covers the condition and the ordering:
    -- 11.2.0.1
    drop table t;
    
    create table t
    as
    select mod(rownum, 1000) col1
         , trunc(rownum/10000) col2
         , lpad('*', 100, '*') padding
      from dual
    connect by level <= 1000000;
    
    exec dbms_stats.gather_table_stats(user, 'T')
    
    create index t_idx1 on t(col1);
    
    explain plan for
    select *
      from t
     where col1 = 1
     order by col2 desc;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |  1000 |   105K|  1006   (1)| 00:00:06 |
    |   1 |  SORT ORDER BY               |        |  1000 |   105K|  1006   (1)| 00:00:06 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T      |  1000 |   105K|  1005   (0)| 00:00:06 |
    |*  3 |    INDEX RANGE SCAN          | T_IDX1 |  1000 |       |     5   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("COL1"=1)
    
    create index t_idx2 on t(col1, col2 desc);
    
    explain plan for
    select *
      from t
     where col1 = 1
     order by col2 desc;
    
    select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |  1000 |   105K|  1005   (0)| 00:00:06 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T      |  1000 |   105K|  1005   (0)| 00:00:06 |
    |*  2 |   INDEX RANGE SCAN          | T_IDX2 |  1000 |       |     5   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("COL1"=1)
    After the creation of index t_idx2 on t(col1, col2 desc) the optimizer decides to use the index to avoid the SORT ORDER BY. In the first case Oracle needs to read all the rows with col1 and the sort can only start when this step has finished. With the index on both columns Oracle does not have to wait for the sort and can start fetching immediately.

    But as I already said: the creation of an index on a big table is not cheap (and has an impact on following DML operations - and of course a potential impact on existing queries) and even with the index in place it is possible that the optimizer still does not consider to use it (because of sql baselines that pin a given plan, or - in the absence of such a baseline - because of the numbers (index leaf_blocks, clustering factor) in the calculation of cost).

    Regards

    Martin
  • 16. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    any help?
  • 17. Re: SQL Query : Order By issue with HUGE Table
    Justin Cave Oracle ACE
    Currently Being Moderated
    896719 wrote:
    any help?
    On what, exactly? You've been given a number of very good answers. It's not obvious to me what you are asking for additional help on. If there are particular questions that you don't believe have been answered or specific points from an answer that aren't clear to you, tell us. We're not very good at guessing where your confusion lies.

    Justin
  • 18. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    sorry about that, i didn't noticed that there was answers for my last question, it was in second page and i didn't see that. my bad
  • 19. Re: SQL Query : Order By issue with HUGE Table
    896726 Explorer
    Currently Being Moderated
    Hi,
    As Martin pointed out indexes, that's exactly what you want to do. Beside that you may wanna try to optimize the query like oracle has done when range paging is used as access in the view object in ADF application. see the example below and try running at your end.
    SELECT * FROM (SELECT /*+ FIRST_ROWS */ IQ.*, ROWNUM AS Z_R_N FROM (SELECT * FROM 
    (SELECT * FROM EES_EVT EES_EVT) QRSLT  WHERE ( ( (APLC_EVT_CD = :vc_temp_1 ) ) ) 
    ORDER BY "CRE_DTTM" DESC) IQ  
    WHERE ROWNUM < :Bind_RangePage_High) 
    WHERE Z_R_N > :Bind_RangePage_Low
    The code is exactly what range paging does and it optimize the query very good... make sure CRE_DTTM column is the one you index, that's what i am assuming.

    Thanks
  • 20. Re: SQL Query : Order By issue with HUGE Table
    899722 Newbie
    Currently Being Moderated
    Thank you so much all of you, yes mavendev that worked for me.. Thanks to Martin.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points