-
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
-
No problem.
Oracle generates the SYS... for all 'descending' indexes, nut the index will be created correctly.
-
hi,
My query is select * from log_record order by tran_datetime desc;
This table is having millions of record.What to do now???
-
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".
-
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
-
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.
-
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,12c
email: ocp_9i@yahoo.com
-
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.
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|