limit 사용시 항상 order by를 염두하기

Altibase에서 아래의 쿼리를 사용할 때는 잘 주의해야 한다. 

(사실 이 Altibase뿐 아니라 모든 DB에도 해당되는 개념이다.)

select * from tbl_google limit 1,10000

table에 천만건의 데이터가 있었던 상황이고,

위의 select쿼리같은 것이 배치처럼 데이터를 가져오는 부분이었다.

select시, update가 일부 실행되면서 2건의 데이터를 가져오지못한 이슈가 발생했다.

당시 insert 쿼리는 하나도 실행이 되지 않았고, sequence에 상관없는 update만 일어났는데 불구하고..

아마도 Altibase는 메모리 DB이다 보니.. 메모리상에서 update쿼리로 인한 데이터 변화가 메모리상 순서를 일으키게 하여 일부 데이터의 순서가 바뀌어 모든 데이터를 가져오지 못하는 이슈가 생긴 것으로 추측한다. 

(Altibase는 limit에 대한 문서를 어디서 보나.... 별로 friendly하지 않은 느낌..)

이 문제를 해결하기 위해서는 둘 중의 해결법을 써야 한다. 

1. limit을 쓸 경우 Order by를 사용하여, 언제나 같은 데이터를 읽어올 수 있게 한다.  (update 쿼리가 들어온다 해도..)

2. limit을 쓰지 않는 다면, 모든 데이터를 모두 가져오게 한다. 이 방법은 App Client 메모리 리소스를 많이 할당함으로서 생기는 큰 문제가 있으니 패스..

limit은 데이터를 저장할 때 random하게 저장될 수 있다. 파일이 저장되는 위치가 다르기 때문이다. 따라서 order by를 써야 정상적으로 나오게 된다.

그렇다면 select * from table 의 결과 뷰는 파일이 저장되는 위치로 출력이 되거나, 보여줄 때 pk기반으로 출력되거나 하는 것으로 보일 것이다. 

일반적으로 mysql limit은 관련해서 설명이 나와 있나 보니. 내용에 대해서는 설명이 없다. 

https://dev.mysql.com/doc/refman/5.1/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table:

SET @a=1;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';

EXECUTE STMT USING @a;

The following statements will return the second to sixth row from the tbl table:

SET @skip=1; SET @numrows=5;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';

EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

Postgress는 그나마 설명이 되어 있다.  Limit을 쓸 때면 Order by 를 사용하라고 명시적으로 되어 있다. 

http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-LIMIT

LIMIT Clause

The LIMIT clause consists of two independent sub-clauses:

LIMIT { count | ALL }

OFFSET start

count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.

SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is:

OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

In this syntax, to write anything except a simple integer constant for start or count, you must write parentheses around it. If count is omitted in a FETCH clause, it defaults to 1. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. According to the standard, the OFFSET clause must come before the FETCH clause if both are present; but PostgreSQL is laxer and allows either order.

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.

The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.

오라클은 순서적인 부분때문에 limit 과 같은 역할을 하는 rownum은 아래와 같이 사용한다.

select * from (select * from table order by seq) where rownum < 4

결국..

rownum 이든지. limit 이든지 쿼리의 맨나중에 실행이 되니.. order가 반드시 필요하게 된다.

limit을 쓸 것이면 반드시 order by 는 짝이 되어야 한다. performance 이슈가 중요한 것이 아니라, 정확도가 더 중요한 것임을 잊지 말아야 겠다.