Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Need help on Index

    Hi,

    I am trying to create an index having dates on a column in a table.

    Create index IDX_TABLE_A on PCS_TABLE_A
    (
    TO_DATE(to_char (sysdate,'yyyy/mm/dd HH24:MIS'), 'yyyy/mm/dd HH24:MIS') -
    TO_DATE(TO_CHAR(COLUMN_A,'yyyy/mm/dd HH24:MIS'), 'yyyy/mm/dd HH24:MIS') , sysdate , COLUMN_A
    );

    It is not allowing sysdate to be part of the definition.

    I need to use some pure function which is not dependent in session state.

    P Lease advice

    Cheers !
    Subhotech

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I need to use some pure function which is not dependent in session state.
    HUH?

    post CREATE TABLE PCS_TABLE_A statement

    which columns in PCS_TABLE_A need to be in the INDEX?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2010
    Posts
    64
    there are other key columns in the table which needs Indexing. I am creating the table PCS_TABLE_A by using a select statement. In the where clause i have the above mentioned date condition :-
    TO_DATE(to_char (sysdate,'yyyy/mm/dd HH24:MIS'), 'yyyy/mm/dd HH24:MIS') -
    TO_DATE(TO_CHAR(COLUMN_A,'yyyy/mm/dd HH24:MIS'), 'yyyy/mm/dd HH24:MIS') > 0

    I thought creating a function based index which improve the query performance time.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by subhotech View Post
    I thought creating a function based index which improve the query performance time.
    Maybe, instead of making causeless changes, you really should investigate what degrades the performance - see the bottom of my post.

    I wonder which kind of code generator constructed that WHERE condition as it can be simply written as
    Code:
    COLUMN_A < sysdate
    (supposing that COLUMN_A has DATE data type; otherwise the WHERE clause would be wrong).

    Then, simple index on COLUMN_A could be created. It depends on data distribution though - only large minority of rows should reside in past to make any use of it. Is it really your case?

    For performance problems, you should start studying Performance Tuning Guide. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

    Another good reading for you:
    http://www.orafaq.com/wiki/Performance_Tuning
    https://forums.oracle.com/forums/thr...174552#9360003

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SYSDATE is a DATE datatype.
    First you use TO_CHAR to transform SYSDATE to character datatype.
    Next you use TO_DATE to transform the string back into DATE datatype.
    WHY? do two transformations to end up with what you started with??????

    >I thought
    I would disagree with the assertion above.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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