New answers tagged rdbms
1
I'm not sure why distinct is needed in the count. If the table is defined as in your post:
CREATE TABLE EMPLOYEE
( EMPLOYEE_ID VARCHAR
, TITLE VARCHAR(30)
, DEPARTMENT VARCHAR(30)
, ACTIVE BOOLEAN
, LOCATION VARCHAR
, EFFECTIVE_DATE DATE
);
then an employee can exist several times each data:
INSERT INTO EMPLOYEE
(EMPLOYEE_ID,TITLE,DEPARTMENT,ACTIVE,...
1
Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.
You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.
0
You can try with monthly partition table design using date column. It will keep each month's data in appropriate data files and easy to manage. Index B-Tree structure would faster in this method with big volume of data.
Index searching page count will be less and faster as per B-Tree structure
Easy to make read-only, remove specific files from database
Can ...
1
This is going to vary between databases, but there are things that we can say are generally (but certainly not always) true of common databases such as SQL Server, Postgres, etc.
Drive IO generally works in blocks, usually these days of 4Kbyte or 0.5K, and to write any of a block the drive will write the whole block. Database engines organise their ...
Top 50 recent answers are included
