Drop Databases and Tables

In DolphinDB, you can delete a database or table with dropDatabase.

The following methods are provided to delete the data in a table:

  • Function dropTable deletes a table and its schema.

  • Function truncate removes rows of a table but keeps its schema.

  • Function dropPartition deletes specified partitions of a table.

  • delete statement deletes data with where clause.

Comparison of execution times using the above functions:

// create a database
$ dbName="dfs://testdb"
$ if(existsDatabase(dbName)){
$         dropDatabase(dbName)
$ }
$ n=10000
$ ticker = rand(`MSFT`GOOG`FB`ORCL`IBM`PPT`AZH`ILM`ANZ,n);
$ id = rand(`A`B`C, n)
$ x=rand(1.0, n)
$ t=table(ticker, id, x)
$ db=database(directory=dbName, partitionType=HASH, partitionScheme=[STRING, 5])

// create tables
$ pt0 = db.createPartitionedTable(t, `pt, `ticker)
$ pt0.append!(t)
$ pt1 = db.createPartitionedTable(t, `pt1, `ticker)
$ pt1.append!(t)
$ pt2 = db.createPartitionedTable(t, `pt2, `ticker)
$ pt2.append!(t)
$ pt3 = db.createPartitionedTable(t, `pt3, `ticker)
$ pt3.append!(t)

// use delete statement
$ timer delete from pt0
Time elapsed: 795.314 ms

// use function dropPartition
$ timer for(i in 0:5){ if(existsPartition(dbName + "/Key"+ string(i), `pt1))dropPartition(dbHandle=db, partitionPaths="/Key"+ string(i), tableName=`pt1)}
Time elapsed: 11.724 ms

// use function truncate
$ timer truncate(dbName, "pt2")
Time elapsed: 5.278 ms

// use function dropTable
$ timer dropTable(db, "pt3")
Time elapsed: 5.582 ms