Problem with Index

[msuresh]

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

[LKBrwn_DBA]

No problem.
Oracle generates the SYS... for all 'descending' indexes, nut the index will be created correctly.

[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???

[slimdave]

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".

[msuresh]

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

[slimdave]

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.

[julian]

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:

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>

[kiewer]

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.

[pando]

is that 8i or 9i Julian?

[DaPi]

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!).