Search code examples
sqlitefull-text-searchcreate-table

Whats difference between create table vs create virtual table in SQLite?


Does create Virtual table statement makes table solely on the RAM? If thats true how can you deal with big data with less ram devices?

Demonstrate the mechanism of statement - create virtual table, in sqllite.

If you want example than heres an easy statement:

   //Create a FTS3 Virtual Table
    private static final String DATABASE_CREATE =
        "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
        KEY_CUSTOMER + "," +
        KEY_NAME + "," +
        KEY_ADDRESS1 + "," +
        KEY_ADDRESS2 + "," +
        KEY_CITY + "," +
        KEY_STATE + "," +
        KEY_ZIP + "," +
        KEY_SEARCH + "," +
        " UNIQUE (" + KEY_CUSTOMER + "));";

Solution

  • A virtual table is not implemented with the usual mechanisms; instead, all operations on it are redirected to some virtual table module.

    What that actually means depends on how the virtual table module chooses to implement them.

    There are virtual table modules that access data that is stored outside the database. But in the case of FTS, creating the virtual table also creates several shadow tables in the same database, which store the actual table data and the contents of the full-text index. This implies that you do not have to manage any external data, and that all operations are automatically protected by the usual transaction management.