Geek asks and answers

What is the fastest way to get the maximum value of a column in a table?

INFORMIX-SQL 4.10.DC1 (SE Engine), on DOS 6.22, on Microsoft Virtual PC 2007, on Windows 7!

EDIT: Looking for Jonathan Leffler wisdom on this one!

I have a child table called a transaction and a parent table called a client.

Client.pk_id SERIAL = transaction.fk_id INTEGER joins these tables.

The transaction table has a clustered index at fk_id, so each client transaction is physically grouped together with the data corresponding to the index. The reason I chose the cluster index for fk_id is because most requests are executed by client name, and this quickly returns all the transactions belonging to the requested client.

The transaction table also has a column named transaction_number SERIAL with a unique index.

When a new transaction is added for any client, a new row with the highest transaction number is added to the EOF. Note. Informix does not support physical clustering in the data file after index creation.

Therefore, when I want to print out the client’s receipt of the last transaction, I make the following request:

SELECT * 
  FROM customer c, 
       transaction t
 WHERE c.pk_id = t.fk_id
   AND t.trx_num = (SELECT MAX(trx_num)
                      FROM transaction)

EDIT: The following INDICES exist:

UNIQUE INDEX ON customer(pk_id) {SERIAL}
UNIQUE CLUSTER INDEX ON customer(last_name,sur_name,first_name,middle_name) {CHAR(30's)}
UNIQUE INDEX ON customer(ident_type,ident_num,ident_state,ident_country) {CHARS(n)}

UNIQUE CLUSTER INDEX ON transaction(fk_id) {INT}
UNIQUE INDEX ON transaction(trx_num) {SERIAL}

EDIT: EXPLAIN results for the above query:

QUERY:
------
SELECT *
  FROM customer c,
       transaction t,
 WHERE c.pk_id   = t.fk_id           
   AND t.trx_num = (SELECT MAX(trx_num) 
                      FROM transaction)

Estimated Cost: 14
Estimated # of Rows Returned: 2

1) f.transaction: INDEX PATH

    (1) Index Keys: trx_num 
        Lower Index Filter: f.transaction.trx_num = <subquery> 

2) f.customer: INDEX PATH

    (1) Index Keys: pk_id 
        Lower Index Filter: f.customer.pk_id = f.transaction.fk_id 

    Subquery:
    ---------
    Estimated Cost: 3
    Estimated # of Rows Returned: 1

    1) f.trx_num: INDEX PATH

        (1) Index Keys: trx_num

It seems that the query optimizer performs a full table scan on the transaction to find the highest transaction number, the new line of which is always placed in the EOF, but since the transactions are grouped by fk_id, the remaining transaction numbers are scattered throughout the table.

Is there a better way to get a faster response time for a request?

Did you CREATE UNIQUE INDEX trxnumidx ON transaction(transaction_number) DESCENDINGfind MAX transaction_number quickly?

EDIT: , , , , :

SELECT * 
      FROM customer c, 
           transaction t
     WHERE c.pk_id = t.fk_id
       AND t.trx_num = $trxnum  {ace input variable, type INT}

, , , ( = 2), , MAX ( = 14). , , , , , ​​ !

EDIT: DBINFO('sqlca.sqlerrd1') ?

+1
2

fk_id, , ( UNIQUE):

CREATE {UNIQUE} INDEX ix_lookup1 ON Transaction(FK_ID, Transaction_Number);

, , fk_id.

, oers:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 12345678         -- crucial to limit the data to one return row
   AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = c.pk_id)

, , , , , . , , -:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567
   AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = 1234567)

:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567
   AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = 1234567)

SQL .

, , , (), - , () MAX , :

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567
   AND t.transaction_number = 23456789;

, c.pk_id = 1234567 , , , . , , , .

, , , ; , , .

+1

JOIN ( JOIN ) WHERE:

SELECT * 
FROM customer
  JOIN transaction
    ON customer.pk_id = transaction.fk_id
WHERE transaction.transaction_number = ( SELECT MAX(transaction_number)
                                         FROM transaction)

:

SELECT * 
FROM customer
  JOIN transaction
    ON customer.pk_id = transaction.fk_id
  JOIN ( SELECT MAX(transaction_number) AS max_tn
         FROM transaction
       ) AS tr
    ON transaction.transaction_number = tr.max_tn

JOINs :

SELECT * 
  FROM customer
     , transaction
 WHERE customer.pk_id = transaction.fk_id
   AND transaction.transaction_number IN 
       ( SELECT MAX(transaction_number)
         FROM transaction
       )

, IN (SELECT ...) , = (SELECT ...)

( ) :

SELECT * 
  FROM transaction, customer 
 WHERE transaction.transaction_number
       = ( SELECT MAX(transaction_number)
           FROM transaction )
  AND customer.pk_id = transaction.fk_id

, , , :

SELECT * 
FROM 
      ( SELECT MAX(transaction_number) AS max_tn
        FROM transaction
      )
  AS tr
   , transaction
   , customer
WHERE transaction.transaction_number = tr.max_tn
  AND customer.pk_id = transaction.fk_id
0

More articles:


All Articles