Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35

    Unhappy Unanswered: slowing for query data

    Hi to all...
    I hv problem to query the data in application.

    eg.
    table -> cct_pp
    trxno varcha2(10)
    trxtdt date
    deptcd varchar2(5)
    ...
    ...
    ...
    ...
    ...
    ** this table cct_pp already credated index on this colunm. (trxdt)

    when i try to query the data the condition is like below

    v_found varchar2(1);
    BEGIN
    select 'x' into v_found
    from cct_pp
    where trunc(trxdt) = TRUNC(sysdate)
    and rownum = 1;
    exception when no_data_found then
    message('AA705,'Invalid Document No.');
    END;

    Question :
    Since the colunm (trxdt) already credate as index
    and store data as date with time. (DD-MON-YYYY HH:MMS), is it index that credated for this column are not fucntion already when i trunc this colum in my form.

    thank 's lot for your helping hand. i hope you all can reply me asap.
    thank you.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The reason that the index is not being used is because ( probably ) you indexed on column trxdt and you are actually querying on trunc( trxdt ), because of this function around the indexed column it totally precludes oracle from using it.

    They are two things I know you can do to overcome the problem. One is to index on the function, like below..
    Code:
    SQL@8i> create table t as select * from all_objects;
    
    Table created.
    
    SQL@8i> create index t_fbidx on t ( trunc( created ) );
    
    Index created.
    
    SQL@8i> alter session set query_rewrite_enabled = true;
    
    Session altered.
    
    SQL@8i> set autotrace traceonly explain
    SQL@8i> select /*+ index (t t_fbidx) */ * from t where trunc( created ) = trunc( sysdate );
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=277 Bytes=35456)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=277 Bytes=35456)
       2    1     INDEX (RANGE SCAN) OF 'T_FBIDX' (NON-UNIQUE) (Cost=1 Card=277)
    There, the index was used ( I told the optimizer to do so ). But this is unlikely to happen ( to hint ) if you have up to date statistics, as shown below..
    Code:
    SQL@8i> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );
    
    PL/SQL procedure successfully completed.
    
    SQL@8i> select * from t where trunc( created ) = trunc( sysdate );
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=4925 Bytes=497425)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=42 Card=4925 Bytes=497425)
       2    1     INDEX (RANGE SCAN) OF 'T_FBIDX' (NON-UNIQUE) (Cost=14 Card=4925)
    The other option you have, currently using the index on column trxdt is to use BETWEEN, since trunc( sysdate ) will default to today at 12:00 AM, you can use that as your first range, and your last range would be trunc( sysdate ) + 1 day less one second of that day, as shown below..
    Code:
    SQL@8i> drop index t_fbidx;
    
    Index dropped.
    
    SQL@8i> create index t_idx on t ( created );
    
    Index created.
    
    SQL@8i> select * from t where created between trunc( sysdate ) and trunc( sysdate )+1-1/24/60/60;
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=62 Bytes=6262)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=62 Bytes=6262)
       2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=62)

Posting Permissions

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