Cluster/descending Index Issues

informixdeveloper · #1 (**permalink**) 03-23-09, 11:40

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

shammat · #2 (**permalink**) 03-23-09, 11:54

There is no CREATE CLUSTERED INDEX in Oracle.

Additionally, the statement:

Code:

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.

informixdeveloper · #3 (**permalink**) 03-23-09, 12:14

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.