Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009

    Unanswered: Cluster/descending Index Issues

    I have a customer master table with 420K+ rows.
    About 30K customers have been active, the rest have been inactive for years.
    My user insists on keeping the inactive customers on the master table.
    So I added a status column char(1). If the row gets updated, LET status = "A".
    Once a month we run, UPDATE customers SET status = "I" WHERE date_last_updated < (today - 365).
    Table has: UNIQUE INDEX ON (customer_name); INDEX ON (status,customer_name)

    When a customer arrives, a wildcard query is performed on the customer_name char(30) column: *LastName*SurName*First* (EXAMPLE: *RIVERA*RODR*P*) and status = "A".

    So I CREATE CLUSTERED INDEX ON customers(date_last_updated desc) to physically
    maintain the most recently active customer rows ahead of the inactive rows to improve query times and return less rows, but this actually made the queries
    run slower!... Any ideas on how to best optimize this, other than moving inactives to another table?


    Another perplexing result is that: I wanted to improve query time on
    SELECT MAX(transaction_number) FROM transactions,

    so i: CREATE UNIQUE INDEX ON transactions(transaction_number desc)

    to quickly retrieve the MAX transaction_number, but it actually retrieves faster when the index is in ascending rather than descending order!!!

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    There is no CREATE CLUSTERED INDEX in Oracle.

    Additionally, the statement:
    UPDATE customers SET status = "I" WHERE date_last_updated < (today - 365).
    is not valid Oracle syntax.

    As "I" is using double quotes, this means (to Oracle) I is a column.
    Character literals need to be quoted using single quotes. With double quotes Oracle (and any DBMS compliant with the ANSI standard) will assume you are referencing a column (and I assume you don't have a column called I)

    Secondly today is not a valid Oracle keyword.

    Are you sure you are using Oracle?
    Looks more like SQL Server to me.

  3. #3
    Join Date
    Mar 2009
    Sorry for the confusion, i didnt specify that im using Informix. All the syntax I used is valid in Informix. Given that, Im looking for a design solution to improve performance.

  4. #4
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >i didnt specify that im using Informix.
    So exactly why are you posting to an ORACLE forum & not Informix forum?
    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.

  5. #5
    Join Date
    Sep 2002
    Provided Answers: 1
    Moved to correct forum.

Posting Permissions

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