Problem with Index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Problem with Index

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159

    Problem with Index

    Hi all,
    I have a table called log_record which have a column name tran_datetime.I created an index xx on this column in descending order and when i checked the dba_ind_columns,the column name is showign as SYS_NC00083$.Can anyone reply me what could be the problem??? since my query access this table in descending order.

    Thanks
    M.Suresh

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,445
    No problem.
    Oracle generates the SYS... for all 'descending' indexes, nut the index will be created correctly.


  3. #3
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    hi,
    My query is select * from log_record order by tran_datetime desc;

    This table is having millions of record.What to do now???

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A couple of comments.

    Firstly, you do not need to create the index as DESCENDING in order for it to be used to help with the sort order DESC of the query -- either an ascending or descending index could be used for this.

    Secondly, is the tran_datetime column nullable? Without a NOT NULL constraint it could not be used to help in this query unless you included the predicate "tran_datetime IS NOT NULL".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    Yes this is a nullable column.Is there any way i get good performance on this query since the application use this a primary query.

    Thanks

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you can't put a NOT NULL constraint on there, then what might work is a function-based index .

    You could create an index like ...

    create index my_index on log_record (NVL(TRAN_DATETIME,TO_DATE('01-JAN-3000','DD-MON-YYYY')));

    Then the clause would be ...

    ORDER BY NVL(TRAN_DATETIME,TO_DATE('01-JAN-3000','DD-MON-YYYY'))

    ... and you would adjust the '01-JAN-3000' date according to whether you want the nulls at the end or the beginning.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by slimdave

    Secondly, is the tran_datetime column nullable? Without a NOT NULL constraint it could not be used to help in this query unless you included the predicate "tran_datetime IS NOT NULL".
    Wrong. How do you know if the optimizer will not use it. Look at what I did:

    Code:
    SQL> desc file_size
    
     Name                            Null?    Type
     ------------------------------- -------- ----------------------
     SID                             NOT NULL NUMBER
     PVM                             NOT NULL DATE
     DB_NAME                                  VARCHAR2(15)
     FILE_NAME                                VARCHAR2(100)
     SIZE_MB                                  VARCHAR2(20)
     USED_MB                                  VARCHAR2(20)
     FREE_MB                                  VARCHAR2(20)
     HWM_MB                                   VARCHAR2(20)
    
    -- Note DB_NAME is nullable
    
    SQL> CREATE  INDEX ndx_db_name
      2   ON file_size
      3    ( db_name );
    
    Index created.
    
    SQL> analyze table file_size compute statistics;
    
    Table analyzed.
    
    SQL> set autotrace on
    
    SQL> select * from file_size where db_name = 'SS02_V60';
    
           SID PVM             DB_NAME         FILE_NAME                                     SIZE_MB              USED_MB              FREE_MB              HWM_MB
    ---------- --------------- --------------- --------------------------------------------- -------------------- --------
             1 17-DEC-02       SS02_V60        /u03/oradata1/ss02_v60/story_data01.dbf       2048                 700                  1077                 970
    
    .
    .
    .
    
    12 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
              0
    SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=12 Bytes=816)
    
    
              1                  0
      TABLE ACCESS (BY INDEX ROWID) OF 'FILE_SIZE' (Cost=3 Card=12 Bytes=816)
    
    
              2                  1
        INDEX (RANGE SCAN) OF 'NDX_DB_NAME' (NON-UNIQUE) (Cost=1 Card=12)
    
    
    
    Statistics
    ----------------------------------------------------------
              5  recursive calls
              0  db block gets
              9  consistent gets
              0  physical reads
              0  redo size
           1397  bytes sent via SQL*Net to client
            340  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             12  rows processed
    
    -- See, Oracle did use the index!
    
    SQL> 
    SQL> 
    SQL> 
    SQL>
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Dec 2002
    Location
    Munich, Germany
    Posts
    20
    Originally posted by msuresh
    hi,
    My query is select * from log_record order by tran_datetime desc;

    This table is having millions of record.What to do now???
    there is no options to speed up this query with indexes. The only thing, what you should do - to tune sort operations in your database.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    is that 8i or 9i Julian?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by msuresh
    hi,
    My query is select * from log_record order by tran_datetime desc;

    This table is having millions of record.What to do now???
    Weeeell - what are you going to do with them all?
    - If YOU are going to read them all (talk to you again in 2015) then that's it, it will take a time, you're probably processing around a Gig.
    - If a machine is going to read them all, are you sure you need them sorted at this point? Can the sort be postponed until the output is presented for human consumption? (An unsorted list would use a FULL SCAN which would be faster.)

    It might be worth experimenting WITHOUT an index, FULL SCAN plus sorts would be used, the optimiser does not ALWAYS get it right (but it usually does better than I can!).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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



Click Here to Expand Forum to Full Width