Database Systems with SQL - Full Course - Database Systems - Tablespaces

A tablespace is where a table gets stored. A tablespace is usually a file that maps to one or more tables. It provides a layer of abstraction between physical and logical data, and allocates storage for all DBMS managed segments. A tablespace can be referred to by name when creating database segments. Most databases actually create a tablespace for each table, and storing it to a separate file. You can create tablespaces manually and assign one or more tables to each tablespace. It’s a good idea to assign frequently accessed tables to tablespaces stored on fast storage media. Use the CREATE TABLESPACE command to name a tablespace and assign it to a file. Then use CREATE TABLE to assign a table to your tablespace. Your indexes are also stored in the same tablespace as the indexed table. CREATE TABLESPACE TablespaceName [ ADD DATAFILE 'FileName' ]; CREATE TABLE TableName ( ColumnName ColumnDefintion, ... ) [ TABLESPACE TablespaceName ]; Databases typically perform better with a single table per tablespace because concurrent updates of multiple tables is faster if each table is stored in a separate file. As these files get updated, blocks become fragmented across many tracks. Another advantage is that tables can be backed up independently of other tables. When you delete or drop a table, the tablespace is also deleted, and if you had a tablespace with several tables, then all those tables need to be deleted for the files to also be deleted. Also, when updating tables, storing one table per file minimizes fragmentation and optimizes table scans. Fragmentation is when blocks become scattered, and it slows down queries because more tracks need to be read. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!