[sqlite] PRAGMA journal_mode and PRAGMA version compatibility
Hi, 1) Using v3.8.6 I noticed that journal files were created and removed while executing a transaction event though I had set the "PRAGMA journal_mode = MEMORY;". Right after creating a new sqlite file using sqlite3_open16() I execute a couple of PRAGMA statements. Right after executing all pragma statements, executing "PRAGMA journal_mode;" returns result code 0 ( == DELETE). As a test I've set "PRAGMA journal_mode = PERSIST;" and then the returned result code still is 0 ( == DELETE) but I see that, after closing the sqlite3 file, the journal file still exists. The return value for other pragma statements do return the correct value (e.g. page_size, max_page_size, ...). Questions: - Is the "PRAGMA journal_mode;" statement returning an incorrect value? - Are there situations when a journal file needs to be created even when journal mode is set to MEMORY? 2) Then I tried upgrading to the newest version 3.9.2. Opening an sqlite file created with v3.8.6 now fails to execute a couple of PRAGMA statements, for instance: - "PRAGMA max_page_count = 1073741823;" - "PRAGMA auto_vacuum = NONE;" - "PRAGMA journal_mode = MEMORY;" The result code returned by function sqlite3_prepare16_v2() that prepares each of these statements is 26 (== SQLITE_NOTADB): file is encrypted or is not a database. Other pragma statements like "page_size, encoding, foreign_keys, ..." return result code 0 (== SLITE_OK). Preparing any pragma statement for a sqlite file created with v3.9.2 returns result code 0 (== SLITE_OK). Question: - Did the compatibility break for some of the PRAGMA statements between v3.8.6 and v3.9.2? Any help or information would be greatly appreciated. Thanks in advance, Jan Asselman
[sqlite] full table scan ignores PK sort order?
Thanks for answering both of my questions. I guess this is similar to the 'Skip-Scan Optimization' mentioned in the documentation. That is what I assumed and explains the difference in query performance. Scanning the table once is faster than scanning the table for each and every value of column 'a' (and the table is too large for any sort of cache to be useful)... I can try to see if the ANALYZE statement makes a difference. But it would only be useful if I can copy the 'impact' of this statement to other database files with equal table definitions. Because in my application, I am constantly creating new database file, filling them, and eventually deleting them. Can I copy the "statistics tables" from one database file to another? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: dinsdag 3 maart 2015 13:08 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] full table scan ignores PK sort order? The subquery is the index access (partial table scan), which is performed once for each and every value in your IN list for the column a (in effect, the IN list is transformed into an ephemeral table and joined to your test table). Since you did not declare an index for your primary key constraint, SQLite has to invent one. If you insert a representative data set and run ANALYZE then the query plan may well change to something that suits the shape of your data better. -Urspr?ngliche Nachricht----- Von: Jan Asselman [mailto:jan.asselman at iba-benelux.com] Gesendet: Dienstag, 03. M?rz 2015 11:27 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? Thank you! I think I get it: - primary key is nothing but a UNIQUE constraint (in my case comparable to a separate index == table with columns a, b and rowID) - the full table scan returns rows in rowID order, which is the order in which the rows were added to the table There is just one more thing I would like to understand: Most of my queries are in the form "SELECT * FROM test WHERE a == ? AND b < ?;" and use the primary key index so that the rows are returned in the expected order without using the ORDER BY statement. It's only a special case where the query "SELECT * FROM test WHERE b < ? AND c > ?;" is used. That's why I decided not to add a separate index to column b. What I was trying, in order to prevent a full table scan, is force the use of the primary key index for this query and have sqlite allow all possible values for column a: "SELECT * FROM test WHERE a IN (<<256 different values>>) AND b < ? AND c > ?;" The query plan confirms the use of the primary key index: 0, 0, 0, SEARCH TABLE data USING INDEX sqlite_autoindex_test_1 (a=? AND b
[sqlite] full table scan ignores PK sort order?
Thanks for the detailed explanation. I understand that I should not make assumptions about SQL engine internals for typical database usage. I now also understand that by not using the ORDER BY clause, SQLite might one day decide to execute query plans that disrupt the order that I induce from the index that is traversed. But, basically, I use SQLite as a file format that allows me to access (ranges of) persistent BLOBs in O(log N) disk time. I require the B-tree functionality, and very little of the SQL functionality. The reason why I am reluctant to use the ORDER BY clause is that I absolutely want to exclude the possibility that all rows must be read into memory before they can be sorted and iterated. But I will add the ORDER BY clause, as it doesn't make a performance impact for me in the current version, and hope the query planner in future versions behaves the same :) -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: dinsdag 3 maart 2015 12:20 To: General Discussion of SQLite Database Subject: Re: [sqlite] full table scan ignores PK sort order? On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps wrote: > An SQL engine doesn't guarantee any row "order" unless you explicitely force > an ORDER BY clause. Think of row order as random, where rowid order is just a > possibility among zillions others. Of course neither SQLite nor other engines > willingly use random() to foil your expectations but you should never rely on > such an implementation detail. > > Also rowids are technically independant of insertion order: you may feed any > valid random literal rowids at insert time. Just to formalise this ... SQL defines a table as a set of rows. There is no order to a set: it's just a jumble of things like Scrabble tiles in a bag. You can't tell what order rows were added in. The most frequent error SQL users make is to assume that table rows are inherently ordered in primary key order. They're not. The primary key is just another unique index. To help users avoid incorrect assumptions about an inherent 'order of rows' SQLite provides PRAGMA reverse_unordered_selects = ON which can be useful for testing code which was hacked up in a hurry. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] full table scan ignores PK sort order?
Thank you! I think I get it: - primary key is nothing but a UNIQUE constraint (in my case comparable to a separate index == table with columns a, b and rowID) - the full table scan returns rows in rowID order, which is the order in which the rows were added to the table There is just one more thing I would like to understand: Most of my queries are in the form "SELECT * FROM test WHERE a == ? AND b < ?;" and use the primary key index so that the rows are returned in the expected order without using the ORDER BY statement. It's only a special case where the query "SELECT * FROM test WHERE b < ? AND c > ?;" is used. That's why I decided not to add a separate index to column b. What I was trying, in order to prevent a full table scan, is force the use of the primary key index for this query and have sqlite allow all possible values for column a: "SELECT * FROM test WHERE a IN (<<256 different values>>) AND b < ? AND c > ?;" The query plan confirms the use of the primary key index: 0, 0, 0, SEARCH TABLE data USING INDEX sqlite_autoindex_test_1 (a=? AND bmailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik Sent: maandag 2 maart 2015 22:52 To: sqlite-users at sqlite.org Subject: Re: [sqlite] full table scan ignores PK sort order? On 3/2/2015 4:48 AM, Jan Asselman wrote: > But when I step over the rows they are not returned in primary key sort > order. Why is this? Because you didn't add an ORDER BY clause. If you need a particular sort order, specify it with ORDER BY. > If I look at the images at the query planning document > (https://www.sqlite.org/queryplanner.html) I get the idea that the primary > key B-tree should be used to traverse the table when a full table scan is > executed. Not the primary key, but the ROWID column. It may optionally be aliased by a column declared as INTEGER PRIMARY KEY (must be spelled exactly this way); your table doesn't have such an alias. This changes for tables created with WITHOUT ROWID clause; but this, too, doesn't apply in your case. > And since the 'DESC' keyword is used on column b in the primary key, I would > expect that, as the rowId increases, the values retuned for column b would > decrease. But this is not the case. How can this be the case? You can update the value of b in an existing row - do you expect all the rows to be physically moved and renumbered when this happens? > Does a full table scan then ignore the PK B-tree? What you think of as "PK B-tree" doesn't exist. > If I explicitly order using an 'ORDER BY' statement then - looking at the > query plan - sqlite seems to perform a full table scan and store the result > in a temporary table which is then sorted. I'd like to avoid the memory > consumption produced by this query plan... If you "ORDER BY a, b desc" SQLite should be using index scan without an explicit sort step. Does this not happen? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] full table scan ignores PK sort order?
Hi, I created the following table in sqlite 3.8.6 "CREATE TABLE IF NOT EXISTS test ( a INTEGER, b INTEGER, c INTEGER, d BLOB, PRIMARY KEY (a, b DESC) );" When I execute the following query "SELECT * FROM test WHERE b < ? AND c > ?;" A full table scan is executed because column a is not part of the query. But when I step over the rows they are not returned in primary key sort order. Why is this? If I look at the images at the query planning document (https://www.sqlite.org/queryplanner.html) I get the idea that the primary key B-tree should be used to traverse the table when a full table scan is executed. And since the 'DESC' keyword is used on column b in the primary key, I would expect that, as the rowId increases, the values retuned for column b would decrease. But this is not the case. Does a full table scan then ignore the PK B-tree? Does it perhaps scan all table pages in the order in which they appear in the file system? I guess this would make sense to increase traversal speed, since it needs to check all pages anyway? If I explicitly order using an 'ORDER BY' statement then - looking at the query plan - sqlite seems to perform a full table scan and store the result in a temporary table which is then sorted. I'd like to avoid the memory consumption produced by this query plan... Any help or information would be appreciated! Thanks in advance! Jan Asselman iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr. Ulrich Lettau CEO, Marta Anhaus, Harald Opel; Registered Office: Fuerth/Germany; Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE 132760166, WEEE-Reg.No. DE11469996 This message is intended only for the named recipient and may contain confidential or privileged information. Taking notice of this message by third parties is not permitted. If you have received it in error, please advise the sender by return e-mail and delete this message and any attachments. Any unauthorized use or dissemination of this information is strictly prohibited.
[sqlite] max blob size without overflow page
Hello sqlite list, Given the following table with large page size (32 KB): CREATE TABLE table ( column_0 INTEGER PRIMARY KEY, column_1 INTEGER, column_2 REAL, ... column_n INTEGER, column_blob BLOB ); With 'column_1' to 'column_n' being BLOB nor TEXT columns. What would be the maximal allowed size of the blob in order to make a single record fit onto a single sqlite page? I want to avoid overflow pages as I can make the BLOBs any size I want (they are part of a continuous data stream). I took a look at the sqlite file format documentation and I have deducted the following formula: MAX_BLOB_SIZE = SQLITE_PAGE_SIZE [32K] - DATABASE_HEADER_BYTE_OFFSET_20 [0] - 35 - RECORD_SIZE [VARINT] - KEY VALUE [VARINT] - RECORD_HEADER_SIZE [VARINT] - N * (HEADER_TYPE [SINGLE_BYTE_VARINT] + RECORD_DATA [VARINT]) - BLOB_HEADER_TYPE [VARINT] with [VARINT] = 9 [SINGLE_BYTE_VARINT] = 1 Any comments or improvements would be appreciated. Thanks in advance, Jan Asselman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select intersecting intervals
Hi, Given the following table with large row count 'row_count': CREATE TABLE table ( i_name TEXT, i_from INTEGER, i_toINTEGER, i_data BLOB ) I am wondering what would be the fastest way to get all rows with a given name 'myname' that intersect with a given interval [a, b]? CREATE INDEX idx_from ON table (i_name, i_from); CREATE INDEX idx_to ON table (i_name, i_to); SELECT data FROM table WHERE name = 'myname' AND i_from < b AND i_to > a -> index idx_from will be used -> in worst case (a is larger than all i_to) all 'myname' rows will be traversed before concluding result set is empty SELECT data FROM table WHERE name = 'myname' AND i_to > a AND i_from < b -> index idx_to will be used -> in worst case (b is smaller than all i_from) all 'myname' rows will be traversed before concluding result set is empty I know this is exactly what a one dimensional R-tree index is used for, but my project requires 64 bit integer minimum- and maximum-value pairs... All suggestions or corrections are appreciated. Thanks in advance, Jan Asselman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
