Single or multiple non-clustered indexes when pairing with a composite primary key I have a database where I am utilizing a TenantId throughout all tables that need to be uniquely identified to a particular tenant, and due to the ordering requirements within composite keys, I have TenantId as the first in the index list. Now comes into question an Authentication piece where the User table contains the TenantId, UserId (the IDENTITY column), and Email among other login-specific items.

The login portal is not tenant-specific, so when logging in, the user will simply input their Email, thus seeking to the row that verifies their login information. In this scenario, we are unable to immediately leverage the composite primary key of TenantId and UserId until we have found the row that applies to Email.

The composite primary key on TenantId and UserId will always be utilized in all other conditional clauses. However, in order to leverage this key in the first place, we must first seek to that row based on a query of Email. Without an index on Email, a table scan will occur instead.

My question is, what type of index combination would be seen as most suitable in this scenario? A single non-clustered index on Email alone, another composite key on UserId and Email in conjunction with the single non-clustered index on Email with INCLUDES on other relevant data, or none of the above?

The schema is similar as such:

CREATE TABLE [User] (
     [TenantId] [int] NOT NULL
    ,[UserId] [int] IDENTITY(1,1) NOT NULL
    ,[Email] [varchar](64) NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[MiddleName] [varchar](32) NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[PasswordHash] [varbinary](64) NOT NULL
    ,[PasswordSalt] [varbinary](32) NOT NULL
    ,[HashMethodId] [tinyint] NOT NULL
    ,[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT 1
    ,[IsLocked] [bit] NOT NULL CONSTRAINT [DF_User_IsLocked] DEFAULT 0

    ,CONSTRAINT [PK_User_TenantId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [UserId] ASC)
    ,INDEX [IX_User_UserId_Email] NONCLUSTERED ([UserId] ASC, [Email] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant]([TenantId])
    ,CONSTRAINT [FK_HashMethod_HashMethodId] FOREIGN KEY ([HashMethodId]) REFERENCES [HashMethod]([HashMethodId])
);
CREATE NONCLUSTERED INDEX [IX_User_Email] ON [User]([Email]) INCLUDE ([PasswordHash],[PasswordSalt],[HashMethodId],[IsActive],[IsLocked])

-- Note for research: Why can an index that has INCLUDE not be specified in CREATE TABLE?

My understanding is that [IX_User_UserId_Email] is useful in this scenario to quickly tie back to [PK_User_TenantId_UserId], thus seeking to the appropriate level of isolation more efficiently. Is this an incorrect assumption? Am I better served with using just [IX_User_Email]?

All tables will JOIN to User on TenantId and UserId.
No tables will strictly JOIN to User based on UserId.
A lookup will happen based strictly on a query of Email. TenantId and UserId will not be known until the row is fetched. Once the row is fetched, remaining queries will utilize TenantId and UserId.

Another option I have been tossing around is within the Tenant table, including a Domain column that specifies the tenant source e-mail domain (which will always be the same across a tenant). Once the user has entered their Email and tabs/selects the Password field on the login page, it will parse out the e-mail domain (@sample.com), allowing us to query the smaller Tenant table to find their TenantId, thus being able to leverage the composite key [PK_User_TenantId_UserId] and thus only having to utilize a non-clustered index on Email. This may be a needless approach, however.

This is a strange one as [UserId] [int] IDENTITY(1,1) NOT NULL is unique.

There could be a case to use [UserId] alone as the PK.  You will get less fragmentation compared to a composite clustered index TenantId, UserId.

I get you plan to use TenantId, UserId in all queries but you don't need to.  UserID will uniquely identify each user.

If you have reports by TenantId you can include that in the tables and put a non-clustered index on it.

As far as email I would just put a non-clustered unique index on it. You can include the other fields if you like but it is just picking up data from a single row so it is not really necessary.  Right now there is nothing to prevent duplicate email.

Could argue repeating TenantId in all tables is not 3NF as it can be derived from  UserId.

I personally would not repeat TenantId and have a view for each table that brings it in. I get you don't want to do it that way but it is still my answer.

select t.*, u.tenant
  from table t
  join [user] u
    on u.userid = t.userid

User is not a good name for a table as it is a keyword.


Getting File not found error doing point in time database restore in SQL Server 2008 So I'm testing out some of our DR stuff and tried to restore a point in time backup from one of our main databases to a new database on the same server.

I am admin on the box and I'm RDP'd into the box and am using SSMS to do the restore.

When I choose my database the "select the backup sets to restore" window populates with a ton of our 1hr incremental backups all the way back to about 30 days ago.

When I select a random time out of the set I get the error

  Cannot Open Backup Device {guid}. Operating System error 2, the system cannot find the file specified.

I'm restoring to a new .mdf and .ldf on a different local drive but I have access to everything.

Not sure what's going on or where to diagnose which file it can't find.

Thanks

When you open that dialog, SQL Server populates that windows with a lot of backups for the history of backups taken for that database. This history is stored in msdb and is not synchronized with the file system, so it is possible that the backup files themselves were deleted  or moved already.

Instead of restoring from that history, select to restore from disk and pick the file(s) yourself.

Restoring a Backup

You need to first restore a full backup. That restore has to happen without recovery.

Then, if you have one, restore the latest differential between the full backup and the point in time you want to restore to. this also has to happen without recovery.

Finally, you need to restore all log backups between that differential (or the full if you didn't have one) and the point in time you want to restore to. Those log backups need to be restored in chronological order, also without recovery. The final log backup you can restore with recovery to bring the database back online.

SQL Server does not force a specific extension for any backup type. Most likely you will have .bak for full backups. Differentials might be either .bak or .dif. For log backups I have seen .trn, .log and others. Some people don't use extensions at all for their backups.

But it is probably a safe guess, that your .trn file is a log backup, so you will need additional backup files to restore that database.


Oracle Database 10g JRE Error I am trying to install Oracle Database 10.2 on OEL5U6(Linux System). I followed all the instructions from HERE.
When I am executing installer this return:

  Preparing to launch Oracle Universal Installer from
  /tmp/OraInstall2013-08-06_12-57-33AM. Please wait ...The Java RunTime
  Environment was not found at
  /tmp/OraInstall2013-08-06_12-57-33AM/jre/bin/java. Hence, the Oracle
  Universal Installer cannot be run. Please visit
  http://www.javasoft.com and install JRE version 1.3.1 or higher and
  try again. : No such file or directory.

But Java is already installed on my system.

Please Do the below written instructions:

Set the class path of latest version of Java in your system  .
set the path as JAVA_HOME in Environment variable too.

Hope this would help you.


T-SQL query for date of last full backup, size and location I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file.  My problem is that it won't return any data at all for databases that have had no backups or for which there is no backup history for.  Ideally, I'd want to modify the query so that all databases are returned, regardless of whether or not there's any backup history for them.  Can anyone advise how the below query could be modified to accommodate this?

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE   bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT
        database_name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];

You need to query the list of databases first (it's in sys.databases), then join it to your query:

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE   bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT
        sd.name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM sys.databases AS sd
LEFT JOIN LastBackUp AS lb
    ON sd.name = lb.database_name
    AND Position = 1
ORDER BY [Database];


Performance of large transactions and concurrency? If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this?

Assuming it's indexed correctly, it shouldn't take long, but what rows are locked and how is the usage of that table affected?

Are rows being updated during the transaction able to be read
after the transaction starts and before it finishes?
Are rows not being updated during
the transaction able to be read after the transaction starts and before it finishes?
If another transaction starts trying to change rows that are being changed by
a previously unfinished transaction, will that transaction fail at
start or after it tries to commit (assuming conflict)?

My question is for Postgres 9.3; I assume there are variations.

Are rows being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes, in Postgres reads do not block writes and writes do not block reads. The Postgres documentation states that:

  Internally, data consistency is maintained
  by using a multiversion model (Multiversion Concurrency Control,
  MVCC). This means that while querying a database each transaction sees
  a snapshot of data (a database version) as it was some time ago,
  regardless of the current state of the underlying data. […] The main
  advantage of using the MVCC model of concurrency control rather than
  locking is that in MVCC locks acquired for querying (reading) data do not
  conflict with locks acquired for writing data, and so reading never
  blocks writing and writing never blocks reading.

Are rows not being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes.

If another transaction starts trying to change rows that are being changed by a previously unfinished transaction, will that transaction fail at start or after it tries to commit (assuming conflict)?

This depends on the Transaction Isolation Level and if you are issuing a write that depends on a read or a blind write. Using the default level Read Committed the second transaction has to wait until the first transaction is done writing. In higher transaction levels one of the transactions might get aborted with a serialization error.

You can actually try this yourself by running two psql sessions:

Session 1:

-- first set up a table
CREATE TABLE tools (id SERIAL PRIMARY KEY, description STRING);
INSERT INTO tools(description) VALUES('scredriver');
INSERT INTO tools(description) VALUES('hammer');

-- now type the following into two psql sessions
          SESSION 1             |          SESSION 2
                                |
BEGIN TRANSACTION;              |
UPDATE tools                    |
   SET description = 'anvil'    |
 WHERE id = 1;                  |
                                | BEGIN TRANSACTION;
                                |UPDATE tools
                                |   SET description = 'wrench'
                                | WHERE id = 1;
                                |-- this transaction is blocked
                                |-- until the other transaction
COMMIT TRANSACTION;             |-- commits

As you will see, session 2 will get blocked by session 1. Only if session 1 commits, session 2 will be able to continue.

The postgres documentation also contains performance suggestions on how to avoid blocking and serialization failures when using higher transaction levels.


Why does PostgreSQL not use an index when OR is used in the conditions? DB structure summarized:

the main table is cases (about 136k rows)
each case can have 0 - n referencing rows in the table case_contacts
each case contact references a main contact in the table contacts
a case contact may also reference a secondary subcontact, also in the table contacts
contacts have their names in contacts.v_fullname, which is indexed with
a trigram index

The goal is to find cases where the name of a contact or subcontact contains the string "test":

SELECT  c.id,
        c.number
  FROM  cases c
        JOIN case_contacts caco ON caco.case_id = c.id
        JOIN contacts con_main ON con_main.id = caco.contact_id
        LEFT JOIN contacts con_sub ON con_sub.id = caco.subcontact_id
 WHERE  con_main.v_fullname ILIKE '%test%'
        OR con_sub.v_fullname ILIKE '%test%'

This query (query plan) returns the correct result, but does not use the trigram index. It takes around 330ms.

Removing either of the match conditions (query plan), or having them point at the same table (query plan), removes the performance problem. Both of these use the trigram index and are executed in under 1ms, but do not solve the given task.

How can I get PostgreSQL to use my index?

I have simplified this example to the minimum necessary to demonstrate the effect. The actual query is much more complex (and partially auto-generated), so using a UNION of two queries with only one text match each would be very hard, if it's even possible.

I'm using PostgreSQL 9.5.5.
The schema is still open for modifications (to some degree).

As requested, more information about the indexes:

dbname=# \di+ *contacts*
                                         List of relations
 Schema |               Name               | Type  | Owner |     Table     |  Size
--------+----------------------------------+-------+-------+---------------+---------
 public | case_contacts_case_id_idx        | index | x     | case_contacts | 4544 kB
 public | case_contacts_contact_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_id_case_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_idx                | index | x     | case_contacts | 9608 kB
 public | case_contacts_pkey               | index | x     | case_contacts | 4544 kB
 public | case_contacts_reference_trgm_idx | index | x     | case_contacts | 4960 kB
 public | case_contacts_subcontact_id_idx  | index | x     | case_contacts | 4544 kB
 public | case_contacts_type_idx           | index | x     | case_contacts | 6208 kB
 public | case_contacts_unique_types_idx   | index | x     | case_contacts | 5464 kB
 public | contacts_parent_id_id_idx        | index | x     | contacts      | 456 kB
 public | contacts_parent_id_idx           | index | x     | contacts      | 360 kB
 public | contacts_pkey                    | index | x     | contacts      | 360 kB
 public | contacts_v_fullname_trgm_idx     | index | x     | contacts      | 1560 kB
(13 rows)

This is how the index on contacts.v_fullname is created:

CREATE INDEX contacts_v_fullname_trgm_idx ON contacts USING GIN (v_fullname gin_trgm_ops);

I cannot really answer your question, because I really don't know why, but I've found a way to make PostgreSQL do more or less what I guess you want. I've tested your situation with a simplified simulation scenario, and using PostgreSQL 9.6.1 (latest as of today). I get the same results.

Good news is: If you can change the way you make your query, you have a couple of options which use the trigram index.

The first one consists on moving the condition on the subcontacts. In this case, the trigram index is used for one of the situations (but not the other):

SELECT
    c.id, c.number
FROM
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
            contacts
        WHERE
            v_fullname ilike '%test%'
    ) AS con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* if the left join gave an answer, it's got '%test%' */ ;

A very few trials with simulated data (where aprox. 0.1%, 2.5%, 5% or 25% of the v_fullname contain '%test%') show that the difference in execution times is minuscule. [My disc is SSD, a real HD might behave very differently.] This should actually be checked with a real system with real data... but it seems that using the trigram index or not, doesn't make a big difference.

PostgreSQL is not exceptionally good at estimating how many rows will appear searching "like '%test%'", but it seems not to matter on which plan decides to use.

There is another option, which (with my little experimentation) works a little bit faster in most cases, and a lot faster when the percentage of '%test%' is low. This option means using a CTE to "prefilter" the contacts (and it uses the trigram index once, because it doesn't need to use it twice):

WITH filtered_contacts AS
(
SELECT
    *
FROM
    contacts
WHERE
    v_fullname ilike '%test%'
)
SELECT
    c.id, c.number
FROM
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN filtered_contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN filtered_contacts con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* we need this test again, or we'll miss rows */ ;


How to calculate running balance for many accounts? please help me fix query below to calculate running balance correctly.

What I need to do is:

Calculate running balance (debit- credit) by account_no
Reset the balance when account_no changes
Reset the balance if transaction_date is in a new financial period AND account_type > 5

Query

SELECT
    acc_account_transactions.id,
    account_no,
    account_type,
    transaction_id,
    DATE_FORMAT(acc_transactions.transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(acc_finacial_periods.id, - 1),
    (@current_period_id:=IF(IFNULL(acc_finacial_periods.id, - 1) <> @current_period_id,
        IFNULL(acc_finacial_periods.id, - 1),
        @current_period_id)) current_period_id,
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(acc_finacial_periods.id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no,
    (@running_balance:=@running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) AS running_balance
FROM
    (SELECT
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r,
    acc_account_transactions
        INNER JOIN
    acc_transactions ON acc_transactions.id = acc_account_transactions.transaction_id
        INNER JOIN
    acc_accounts ON acc_accounts.id = acc_account_transactions.account_no
        LEFT JOIN
    acc_finacial_periods ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no , acc_account_transactions.id

What's wrong with it now?

current_account_no variable has a wrong value, -it should be same as account_no column- hence, the running_balance result is also incorrect

Fiddle: here

Note:
Database server info is below

Please don't close my question, I really need help :/

EDIT

current wrong result as in the fiddle (last query):

what I expect, something like this:

There is something niot right, but you can take it from here.

I replaced your FROM clause, because you need a sorted list to do that coalculation and mariadb  takes the definition, that tables are unsorted literary and eliminates all sorting in subqueries. so you have to circumvent it

Check the numbers, i think you must work on that.

SELECT  id,account_no,account_type,transaction_id,
    DATE_FORMAT(transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(afp_id, - 1),
    (@current_period_id:=IF(IFNULL(afp_id, - 1) <> @current_period_id,
        IFNULL(afp_id, - 1),
        @current_period_id)) current_period_id,
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(afp_id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal
        ,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no
        ,

    (@running_balance:=@running_balance
    + (debit_balance - credit_balance)) AS running_balance
FROM

    (SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
    ,(SELECT
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r

db<>fiddle here

To yxpand the idea

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615

Builds the basis for the calculation.

It is a new sub query that sorts the data given so that you can get the wanted result.

the LIMIT 18446744073709551615  is there,because when mariadb processes the query above, it would remove the order by that is needed.

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id

are the columns that are needed for the calculation, if you need more column you first have to include them here. be aware mysql/mariadb doesn't like the same column name, so i have to afp.id as afp_id give the secnd id fiekld an alias.


query behaviour of composite gin index CREATE   TABLE products(
    products_id serial,
    location_id integer, --(not unique)
    idle_products jsonb --has 1000 elements ('[13,45,8976.....]'::jsonb)
);

CREATE EXTENSION btree_gin;
CREATE INDEX idx ON  products USING GIN(location_id, idle_products);

Take just for example;

products table have  millions of rows and 1000 elements in every row in idle_products column.

SELECT * FROM products
WHERE   location_id IN (SELECT * FROM unnest(array[1245,456,6342,2,23453]::int[])) AND
        idle_products @> to_jsonb(222) ;

On that query what i want to learn is:

Is idle_productsbeing searched through only these 5 rows by composite gin index and if so,  does that mean much more performance gain(because there are only 5x1000 elements in 5 rows in idle_products column,
i mean it would only search through 5 rows using gin composite index) or is idle_products being searched through all rows in products table (despite filtering against location_id) and then reduce(limit) rows to 5
(array[1245,456,6342,2,23453]::int[] has 5 five location_id) by filtering location_id  values?

If you want to see how the query will be run, you should put "EXPLAIN" before the "SELECT" and it will give you the execution plan.

There are three ways it could use the gin index:

It could look up just the location_id that you specify, then fetch those rows and filter out ones not containing 222.
It could look up just the idle_products you specify, then fetch those rows and filter out ones not containing one of your location_id list.
It could look up all the index entries for 222, and all the index entries for your location_id list, and take the intersection of those to go fetch from the table.

It will choose which of those it thinks will work better, based on what it knows about your data distribution.

Also, I don't find composite gin indexes to be useful.  Having separate indexes is more flexible, and PostgreSQL can still combine them (using BitmapAnd) when that is useful to do.  I'd replace the composite index with a ordinary btree index on location_id and a gin index on the array column.


Change postgresql data directory location Currenly my postgresql service running with

/usr/local/psql/bin/postmaster -d /usr/local/pgsql/data

Due to insufficient disk space (data directory having 10GB of data), I planned to do the following steps:

Step1:

service postgresql stop

Step2:

mkdir -p /newdisk/newdata
chown postgres:postgres /newdisk/newdata

Step 3:

Change PGDATA path in /etc/rc.d/init.d/postgresql file

From

PGDATA=/usr/local/pgsql/data

To

PGDATA=/newdisk/newdata

Step 4:

initdb -D /newdisk/newdata
service postgresql start

If I do above steps, expecting 10GB data will be moved to /newdisk/newdata path and henceforth, new transactions will be stored in /newdisk/newdata path.

This approach is fine to proceed? or do I need to consider anything else?

(Using Postgresql 9.1)

in Your way, also EXPORT PGDATA=/newdisk/newdata

but also You can move all files to the new disk, and mount this disk to old PGDATA path (if all disk for PGSQL only)

Edit your /etc/fstab:
/dev/disk_name    /usr/local/psql/data    auto   defaults    1   2


SELECT DISTINCT in extra space data from a single column ( no duplicates ) Hello i am having problems with this column data
cities
Charlotte
Cincinnati
Cleves
Columbus
Fairfield
Fresno
Los  Angeles -- HERE IS THE PROBLEM
Los Angeles
Madison
Manhatttan  Beach
New York
Orange
Palo Alto
Phoenix
Sacramento
San Francisco
Takoma  Park -- maybe this city must have problems also
Tarrytown
Valencia
Washington

How i can filter that value, what is the easy way with trim? i did my research but i find long sql statements that i dont understand if that could help is a simple error.
This is the query:
-- 3. Write a query that will list all the cities that have customers with a heading of Cities. Only
-- list each city once (no duplicates) and sort in descending alphabetical order.

select distinct customer_city as cities
FROM customers
ORDER BY customer_city ASC

-- left Los  Angeles

As Erik said in the comments, you should fix bad data rather than trying to query around it, but if you absolutely cannot fix the data, the below query will get the distinct list of cities by replacing double spaces with a single space:
SELECT DISTINCT REPLACE(customer_city, '  ', ' ') as cities
FROM customers
ORDER BY customer_city ASC

This is a really basic example, however, if the input data is not being validated, this may not be the only type of whitespace you're encountering causing duplication.
Prior to SQL 2017, you need to daisy chain multiple REPLACE statements to replace multiple characters. For example, this code replaces double spaces and tab characters with a single space:
SELECT DISTINCT REPLACE(REPLACE(customer_city, '  ', ' '), CHAR(9), ' ') as cities
FROM customers
ORDER BY customer_city ASC

In 2017, you can use the TRANSLATE function to swap all of the characters you're searching for with a single character, then replace that character with nothing to ensure you're finding all exact duplicates:
SELECT DISTINCT REPLACE(TRANSLATE(customer_city, CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32), '####'), '#', '') as cities
FROM customers
ORDER BY customer_city ASC

This means you don't have to repeat REPLACE for every character you want to strip, you just add the character code (+ CHAR(?)) to the TRANSLATE function and add another replacement character (#). As you can see, the TRANSLATE example replaces 4 characters for basically the same amount of code as the two-character replacement in prior versions.


Where can I find the algorithm used in Oracle Range Partitioning I am new to database and would like to know where can I find the resource to study and understand about different algorithms used in table partitioning

For example, what algorithm is used between range and hash partition to find the partition that a data to be inserted.

Manuals:

Basic description can be found in the Database Concepts
Some information about administering partitioned tables can be found in theDatabase Administrator’s Guide
Detailed description as you requested can be found in the
Database VLDB and Partitioning Guide

Books:

Expert Oracle Database Architecture,
by  Kyte, Thomas, Kuhn, Darl
contains about 70 pages about partitioning. I did not know it but I assume it contains a very detailed presentation of the topic.

Whitepapers:

Partitioning in Oracle Database 11g

Partitioning with Oracle Database 11g Release 2

Oracle uses a data dictionary to store the structure of the database objects. These data dictionary contains a set of tables that are located in the SYS schema and have a name that ends with '$'. The table SYS.TABPART$ contains a row for each partition. So if before Oracle stores a row in a partitioned table it hast to read SYS.TABPART$ table of the data dictionary to get information about the partitioned table. Oracle is implemented in C, so access to partitiones is implemented in C-code, too. This C-code is proprietary to Oracle and not made publicly available. The data structures that are used in these programs aren't publicly available too and I think Oracle has no interest to make it known to the public. But it is not necessary to know such details if you want to work with Oracle or want to get a deep understanding of how Oracle works.
The partition relevant dictionary objects are created by the $ORACLE_HOME/rdbms/admin/dpart.bsq' script. There are a lot of other bsq-scripts in this directory that create other dictionary objects. These scripts are executed when the database is created. They are referenced by the well known USER_ / ALL_ / DBA_ views that are a more user friendly access to the dictionary data.
You cannot access the Oracle C-code but if your are interested in how a real database is implemented you can study the source code of an open  source database. As far as I know MariaDB (a MySQL successor) is an open source database that has implemented partitioning. Also there are a lot of books about  database systems  and data structures.


Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0 I'm trying to put the log_bin_trust_function_creators=1 parameter into the my.cnf configuration file to set the variable correctly on startup on a machine with Ubuntu 20.04 LTS. But every time I save the file with this line written in the config file,  when I try to access the db I can't log in and iIhave this error output message:

mysql: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 23.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

When I use SET GLOBAL log_bin_trust_function_creators=1; the parameter and the db work fine, but I would prefer to not having to reboot the machine when I set this parameter.
The version that we're using is mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Can someone help me with this problem?

Put it under mysqld, there it should be found and set
[mysqld]
log-bin-trust-function-creators = 1


Address Parts - Hierarchy vs Flat I'm using Google to geocode addresses, which breaks down an address into geographic units like country, state, city, etc. Depending on the address being geocoded, some geographic units may or may not be returned (e.g. maybe city is not returned, but suburb will be).

I'm tempted to store the data of each broken down address in a single row of a table, like:

USA, California, San Francisco
USA, California, San Jose
USA, California, Los Angeles

Obviously, this doesn't seem normalized as data is being repeated. I still could potentially query this effectively, though (e.g. list all states for USA).

Another option would be to save the data in parent-child relationships, a tree. That would be more normalized, but potentially harder to maintain and query (I can use a library to make this a lot easier, though, so it's not much of a concern for me).

My biggest concern using a parent-child model is that the branches may not all be the same length (e.g. city is not returned, but instead suburb is after the state). If I were to query for all the nodes below the state level, I could get a mix of cities and suburbs -- which might not be ideal. I could avoid this problem if I saved the data in a single row, with each geographic unit in their appropriate columns.

What's the proper way to model address parts?

Depending on what country you are in the rules for addresses (especially postal addresses) can get pretty dicey. For example, you're pretty safe to assume that a zip/postal code has one official city name, but both the U.S. and Canada allow for alternative city names for a postal code. I know this for a fact because I used to develop postal address validation software for North America. The non-official names are often recognized by the postal authorities and you typically have to permit their use.

Outside of U.S. / Canada the rules can get even more loose.  There are not that many countries with postal authorities that standardize addresses in a fairly hierarchical structure (U.K., Germany, Australia) so in a lot of countries you can't even count on that.

The difficulty with normalizing address data is that, as you've already seen, it is somewhat hierarchical, but inconsistent across different geographies.  Japan has prefectures, Canada has provinces and territories, the U.S. has states, districts, territories and commonwealths.  Apart from naming conventions, in different places you may skip (or add) entire levels of geography.

You're thinking of the problem from the wrong angle.

You're instinct to be wary of redundant data is a good one.  In transactional systems you should always try to start with data that's in third normal form (3NF) at least.  This helps you to avoid insert, update and delete anomalies.  In other words, the problem with redundant data is that it can become inconsistent when you start changing it.

In your case, however, all of your geographic data is coming from a Google API. If you change an address, I presume you're going to go back to the Google API and refresh the whole thing as a unit.  You wouldn't go in and selectively change one element in an address because it had been renamed, for example.

Normalization doesn't especially help you because you aren't manipulating data elements within the address, you're always working with the address as a whole.

In your case, I would recommend leaving address elements classified in columns according to what the Google API says they are.  Don't try to build trees because they will be hard to work with and the usual reasons for wanting to represent your data in a tree don't apply to your situation.


After Insert Trigger WIth MS SQL Server I think I have all my bases covered but wanted to ask someone who knows better than I.  This is my syntax.
UPDATE SI
SET autoInsert = 0
FROM inserted
WHERE SI.autoInsert = SI.autoInsert
OR SI.autoInsert IS NULL;

and I want to set autoInsert = 0 where autoInsert = 1 or autoInsert IS NULL

I'm going to assume you have some sort of identity or unique key column on your SI table. (This will be a little more difficult if you haven't.)
I'm also going to assume your autoInsert column is a BIT data type. This means the only values we can possibly have are 1, 0 or NULL. And you want the value updated to 0.
And finally, I'm going to assume you only care about updates to the autoInsert column.
If that's all true, try this:
CREATE OR ALTER TRIGGER dbo.trg_ZeroAutoInsert
    ON dbo.SI
AFTER UPDATE
AS
UPDATE          dbo.SI
SET             autoInsert = 0
FROM            dbo.SI
    INNER JOIN  inserted AS i
        ON      SI.id = i.id ;

If, however, your autoInsert column is an INT (or similar) data type, you can add the where clause to only cover values of 1 or NULL:
CREATE OR ALTER TRIGGER dbo.trg_ZeroAutoInsert
    ON dbo.SI
AFTER UPDATE
AS
UPDATE          dbo.SI
SET             autoInsert = 0
FROM            dbo.SI
    INNER JOIN  inserted AS i
        ON      SI.id = i.id
WHERE           i.autoInsert = 1
    OR          i.autoInsert IS NULL ;


UPDATE query with JOIN taking forever I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would save a ton of time if it works.

I have two tables:

items: ~40,000 Rows
inventory: ~1,000,000 Rows

I have a query that matches up the inventory with the items the way the old system did, and updates the itemid column in the inventory table, to the id of the matched row in the items table (to work with the new system).

The problem is that the queries are taking forever, and I have very similar queries that I have to run in order to prioritize similar matches so waiting this amount of time isn't very great as the data in the old database is not consistent at all, so I have to continuously tweak, rerun, tweak, rerun, etc. It was taking a little over 40 minutes per query when there was only 250,000 rows in the inventory table.

In a nutshell, I need to make this query faster:

UPDATE inventory r
JOIN items t ON (FIND_IN_SET(r.orderNumber,
                REPLACE(
                    REPLACE(
                        REPLACE(t.orderNumber,'/',','),
                    ' ', ''),
                '[]',',')
            )
            AND r.selected_number != '99999'
            AND r.guideid = t.guideid
            AND (r.itemid IS NULL OR r.itemid = '')
                 )
SET r.itemid = t.id

My indices are (comma delimited obviously being composite, please excuse the names they were auto-generated by SQLYog):

items table:

PRIMARY => `id`
guideid => `guideid`, `orderNumber`
guideid_2 => `guideid`
orderNumber => `orderNumber`

inventory table:

PRIMARY => `id`
guideid => `guideid`, `orderNumber`
temp_usNum => `temp_usNum`
orderNumber => `orderNumber`
selected_number => `selected_number`
guideid_2 => `guideid`
itemid => `itemid`
itemid_2 => `itemid`, `guideid`, `orderNumber`, `selected_number`

Here is the result of the explain query:

I have access to all server settings, as it is on my local machine.

With your current structure, your query may be doing >1,000,000,000 row scans. This is probably due to the FIND_IN_SET - meaning the column cannot be used in an index, resulting in the optimizer choosing other indexes with very bad selectivity (you have to read 500000 rows instead of 1 million, that is very bad selectivity). The fact that an index is being used does not mean good performance, if it is the wrong index. To understand why, you must know that function(column) = value cannot be indexed directly in MySQL.

I would start by changing the column items.ordernumber and putting that information on a separate table, with only one item per column, instead of the denormalized arrangement you have currently.

With that, and the proper indexes, you will be able to do the query in the items -> inventory direction and use ordernumber to filter adequately your rows.

As a secondary thing, you have many redundant indexes, like (guideid, orderNumber) and (guideid). You are in need of a good cleanup in terms of design.

To measure the number of row operations that you are doing for a particular query, do the following:

FLUSH STATUS;
-- Execute query
SHOW STATUS like 'Hand%';


How can i make SQL exec statements run in order SQL server 2012, I have multiple execs calling the same package with different parameters.  Normally i'll highlight one at a time and run them individually but I'd like to be able to hit go and run them all in order.  One exec pulls results that were created from another so it has to be in order.  Is there a way to pull this off?  i'm not sure if it's the default nature but I haven't been able to verify it.

Short answer is yes, that is the default behavior.

SQL is a data access language that will run statments in batches, you'd have to use other features (Service Broker for example) to run statements asynchronously.

One of the main concepts of the language is the idea of Transactions. There are several articles out there which explain this concept in detail with straightforward examples. For Example.

I'd highly recommend practicing this because understanding how to use Transactions becomes especially important when one statement relies on the successful execution of the previous statement.


Do we have any File Table / File Stream like support in any other database than MS SQL Server? I am looking for an alternative to Microsoft SQL Server's File Tables / File Stream feature. The reason is because I really wanted to use SQL Server File Tables but I am on Linux and using docker.
Unfortunately, Microsoft does not provide any support for Linux when it comes to File Tables. See this Issue which is still open. MySQL or PostgreSQL is preferable though.
SQL Server File Tables uses Operating System's File System to store files and synchronize these file values in SQL Tables. It is much faster than saving a BLOB. Also, it is backed up if I run Database Backup. So, its using File System for speed but also keeping it synchronized with Database. I can also access the files via File System and / or via transactions.

The alternative implementation in the past was to store the files on a shared location and store their location's reference in the database in a table.
Here are the current options for modern DBMS:

Oracle PL/SQL - Loading documents and other file data into the Oracle Database: Full Text or Binary Data with BFiles or External Tables might be closest to what your use case is.

MySQL: BLOB Storage: Seems like there's not much alternative in MySQL (aside from storing the reference to the file's location).

PostgreSQL: Storing Binary Data & Are there performance issues storing files in PostgreSQL?: Can use the bytea data type or the Large Object feature which stores the data in dedicated table.

MongoDB: BSON Types & GridFS: BSON data types can store data up to 16 MB in size, after that GridFS is the solution for larger storage (especially for binary data).

SQLite: Can we store .doc .pdf and .jpg files in SQLite Database (Android)?
 & Datatypes in SQLite Version 3: BLOBs are the go-to in SQLite.

MariaDB: LOAD_FILE: There doesn't seem to be a lot of support in MariaDB at all, but in general they also use BLOB storage have a LOAD_FILE function.

AWS: SimpleDB: While this isn't achievable via Docker, just for reference for anyone adamant about storing files in the database, their SimpleDB (among some of their other services) offer ways to somewhat store files in the database and use meta-data to tag what kind of file they are (typically leveraging S3 buckets).

Here's also an oldie but goodie on the pros and cons of storing large files in the database.


How to convert a unix time to PostgreSQL' Timstamp without timezome? I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30)

I have some data in a table which is created like this:

CREATE TABLE "CLOUDDATA"
(
  "CD_Tm_Obs" timestamp without time zone,
  "CD_Avg_Cloud" double precision
)

I want to query the data and get the values for a specific time. My Input will be an Unix Timestamp (i.e. Seconds from 1-Jan 1970)

The only way of converting the unix time to Timestamp I have found is this:
select to_timestamp(TRUNC(CAST(1395036000 AS bigint))). But this create a timestamp with timezone. My data is in timestamp without time zone, so I don't get any results.

How to convert a unix time to PostgreSQL's Timstamp without timezome?

Here are a couple of approaches:

I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST().

SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';

SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';

For reference, more information can be found at the following links:

First
query
Second
query


ORDER BY gives ERROR: function array_position(text[], character varying) does not exist I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with:

I though adding an ORDER BY array_position() would do it:

SELECT color, count(*)
FROM research
GROUP BY color
ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

But I'm seeing a type error:

ERROR: function array_position(text[], character varying) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 98

What do I need to cast color as so I can use the array_position function to order it?

Sample data

You have a table like,

CREATE TABLE research(colors)
  AS VALUES ('Blue'), ('Orange'), ('Yellow');

ENUM type

You have an enumerated list of colors. So the easy thing here would be to use an ENUM type

CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue');

Then

ALTER TABLE research
  ALTER COLUMN colors      -- myColorsColumn
  SET DATA TYPE colors
  USING (colors::colors);  -- myColorsColumn::NewType

Now it's faster, more efficient, and cleaner. MORE WIN. MORE JOY. Etc. ENUM are stored as 4-byte internally.

ORDER BY colors

Is all you'll need.

Array Sort

But, you've got a God-given right to treat this like any other database that doesn't support ENUM types,

ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

I think this is perfectly fine, but I believe there is a limitation here in the implementation,

ARRAY['foo', 'bar', 'baz']

Is essentially

ARRAY['foo', 'bar', 'baz']::text

This means you need to either (speed doesn't matter as both options perform the same)

Make the color column of the native text. This can be done in the call, or you can actually modify the table and should. In PostgreSQL, nothing should be varchar without a limit (since that's just a parallel type for text), and very few things should be varchar with a limit (since there is no advantage)

color::text            -- PostgreSQL sexy sexy cast
CAST(color AS TEXT))   -- ANSI SQL standardized vanilla and boring

Or, you can construct the array itself as type varchar[]

array_position(ARRAY['Red','Orange','Yellow','Green','Blue']::varchar[], color);

Further notes

In PostgreSQL, we wouldn't use varchar for colors. Even if you insist on not using an ENUM here (though I would), that should be text
The array_position is a shorthand, I expect it to be substantially slower than a similar operation though

CASE
  WHEN color='Red' THEN 1::smallint
  WHEN color='Orange' THEN 2::smallint
  WHEN color='Yellow' THEN 3::smallint
  ... etc
END;


Create a view with distributed Lobs I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:

CREATE TABLE DB1.TEST_TABLE ID NUMBER, FOO CLOB;
INSERT INTO DB1.TEST_TABLE (ID, FOO) VALUES (1, "RANDOM TEXT");

Now I created da databaselink to access DB1 from instance 2

SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

The select works perfect. But if I want to create a view with this select I get an error:

CREATE VIEW DB2.V_TEST_TABLE AS SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

I don't undertand why selecting a distributing lob works fine but creating a view of this select not.

You can use a view if all the objects returned are scalars not lobs.  In this case if you returned the first 4000 characters of the LOB that should work as the documentation indicates that using a function that creates a temporary lob on the source is allowed as long as the result that is returned is not a LOB.
The documentation for 12 is here. It does not say that creating a view will work so in the Oracle way that means only what they describe will work:

create table t as select * from ...
insert into t select * from ...
use a function that returns a scalar such as DBMS_LOB.SUBSTR(your_lob, 32767, 1);

Another workaround is to create your view with trimmed lobs on the source database and then create a view of the view on the target database.
Note the little gotcha where selecting LOBS into objects in the destination automatically converts it to the character set of the destination. The workaround is to cast to RAW and select then cast back.


Oracle subquery column aliasing syntax? I have something like:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x, y
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) AS subquery (a, b)

Which of course doesn't work in Oracle (that syntax - apart from the INTO and the optional AS) would be fine in SQL Server http://sqlfiddle.com/#!6/b7dcf/1

Is there an alternative to this (which requires me to put the aliasing inside) where the column aliasing is outside:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x AS a, y AS b
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) subquery;

http://sqlfiddle.com/#!6/c12e1/1 - which works on both SQL Server and Oracle, of course

My objection to the interior aliasing is that it is not as good for maintenance, obviously, if new sections are added above that section, the aliasing gets whacked.

Consider using the WITH syntax that has the subquery above the outer SELECT and has the better maintainability you were looking for:

WITH subquery (a, b) AS
(
    SELECT x, y FROM whatever
    UNION
    SELECT z, w FROM something
)
SELECT a, b FROM subquery;

... runs on Oracle 11.2 according to: http://sqlfiddle.com/#!4/d0b42/2/0


How to add table to database inventory in sqlplus oracle 12c Problems

How to create database Inventory database and inside it table Items in sqlplus
oracle 12c .
Details

Im beginner on Oracle 12c release
 i setup oracle and i create database Inventory

Now how to add table items to database Inventory

i can create table but how to create it inside database Inventorty
this actually what i need

Create table Items
(
itemno number(4) not null,
itemname varchar2(40) not null
);

You either login as user Inventory before running the DDL, or you create the table by specifying the Schema like

create table Inventory.Items
( ... );


What joins are available in Informix 9.2? I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT JOIN, OUTER JOIN or just OUTER), but without any success. INNER JOIN and LEFT JOIN work fine.

Does anyone know the correct syntax for outer/right joins in older versions of Informix, or are these joins just not possible?

Ok, at least an outer join works just with different syntax: just using OUTER with a comma after the first table and a WHERE instead of an ON.

Edited to add an example from http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls679.htm

SELECT c.customer_num, c.lname, o.order_num FROM customer c,
   OUTER orders o WHERE c.customer_num = o.customer_num


How to run Trigger only on certain updates I have the following two tables:

CREATE TABLE IF NOT EXISTS `Product` (
    `ProductID` VARCHAR(16),
    `ProductName` TINYTEXT NOT NULL,
    `CategoryID` VARCHAR(3) REFERENCES Category (CategoryID)
    ON DELETE CASCADE ON UPDATE CASCADE,
    `ProductDescription` TEXT,
    `MRP` DOUBLE NOT NULL,
    `SellingPrice` DOUBLE NOT NULL,
    `ProductBrand` VARCHAR(30) NOT NULL,
    `Stock` INT(11) NOT NULL,
    PRIMARY KEY (`ProductID`)
);

CREATE TABLE IF NOT EXISTS `PriceDetails` (
    `ProductID` VARCHAR(16) REFERENCES Product (ProductID),
    `Time_Stamp` DATETIME NOT NULL,
    `Price` DOUBLE NOT NULL,
    PRIMARY KEY (`ProductID` , `Time_Stamp`)
);

And a trigger:

DELIMITER $$

    CREATE TRIGGER price_update
        AFTER UPDATE ON Product
        FOR EACH ROW
    BEGIN
    INSERT INTO PriceDetails
    values(
        NEW.ProductID,
        now(),
        NEW.SellingPrice);

    END$$
DELIMITER ;

This trigger runs every time the Product table is updated.

Now, I want to run this trigger only when the SellingPrice is updated in the Product table.

The trigger always "runs" -- but just because it runs doesn't mean it has to perform any action.  And a trigger that doesn't perform any action doesn't take very long, if you're concerned about efficiency.

In your case, all you need is logic to compare NEW.SellingPrice to OLD.SellingPrice and only perform the INSERT action if the two values are not the same.

Note that a common error at this step is to assume that != is the correct operator to use when comparing old and new values, but this will bite you if the column is nullable, because null values are neither equal to nor not equal to any other value, including null itself (since null is not a value, even though I myself carelessly used the phrase "null values" earlier in this same sentence).  Even if the column isn't nullable now, it is probably still a good practice to use the null-safe comparison ("spaceship") operator <=> and negate the result with NOT, because <=> expresses the actual relationship we're trying to evaluate:

DELIMITER $$

CREATE TRIGGER price_update
    AFTER UPDATE ON Product
    FOR EACH ROW
BEGIN
  IF NOT (NEW.SellingPrice <=> OLD.SellingPrice) THEN
    -- this update has changed the value of SellingPrice
    -- for the row that invoked this run of the trigger
    INSERT INTO PriceDetails
    values(
      NEW.ProductID,
      now(),
      NEW.SellingPrice);
  END IF;

END $$
DELIMITER ;


Data replication in Cassandra We have a 5 node cluster in Cassandra in which we have created a keyspace with RF=3.

After creating a column-family(table) when we insert the data into the same, it is getting replicated in all the 5 nodes even though RF=3.

Shouldn't the data be replicated to 3 nodes only and thus be accessible in 3 nodes and not all 5 nodes?

Also, we have used vnodes.

Perhaps I'm misunderstanding your question, but when you write data to the cluster, the primary key is hashed using the Murmur3 partitioner (usually) and then the data is written to the node that is responsible for that hash range, which could be any one of your 5 nodes.

In addition, your data is also being written at the same time to 2 other nodes for a total of 3 copies of the data spread around the cluster. When you do nodetool status some-column-family you should see that each node is responsible for (3 copies/5 nodes) 60% of the data.

It doesn't matter which node you query for a read, because all of the nodes are equal and any node can act as a coordinator to gather the results to pass back to the client.

Hopefully that answers your question or points you in the right direction.


cant access both phone numbers in my nested table type Im using Oracle Developer and I have a customers table where each customer has 1 home-phone number beginning with 0131 and either 1 or 2 mobile numbers. I am trying to display the customers name and phone number which contains 0770 and has more than 1 mobile phone number

I have a nested table in my database which contains the 2 sets of phone numbers.

CREATE TYPE PHONENUMBER_TYPE AS OBJECT(
    int_code VARCHAR2(4),
    area     VARCHAR2(5),
    local    VARCHAR2(8));

CREATE TYPE PHONENUMBER_TABLE_TYPE AS TABLE OF PHONENUMBER_TYPE;

Here is my customers table and type

CREATE type customer_Type AS object(
    cust_ID NUMBER(8),
    address address_type,
    name name_type,
    homephone PHONENUMBER_TYPE,
    cust_mobiles PHONENUMBER_TABLE_TYPE,
    ninum VARCHAR2(8));

CREATE TABLE customer OF customer_Type(
     cust_ID PRIMARY KEY)
NESTED TABLE cust_mobiles STORE AS customer_mobiles;

And I'm entering data into the table using the following insert statement

  INSERT INTO customer VALUES(
      '1002',
      address_type('Adam', 'Edinburgh', 'EH1 6EA'),
      name_type('Mr', 'Jack', 'Smith'),
      phonenumber_type(NULL, '0131', '4960403'),
      phonenumber_table_type(PHONENUMBER_TYPE('44', '07889', '900003'),
      PHONENUMBER_TYPE('44', '07705', '623443')),
      'NI810');

And obviously I am entering both phone numbers into phonenumber_table_type but I cant seem to access both when I'm using the where clause in my query.

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

Which produces the following output:

+-------+-------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |
+-------+-------+----------+-------+--------+

But the expected out put should be

+-------+-------+----------+-------+--------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |    44    | 07889 | 900003 |
+-------+-------+----------+-------+--------+----------+-------+--------+

So how would I produce the expected result?

I though just adding in the another e.* again but that would produce the same number twice.

SELECT t.name.firstname   AS fname,
       e.*, e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

I suspect its got something to do with the where clause. Because when I run the following I get all the numbers in the phonenumber_table_type.

SELECT C.name.firstname, U.* FROM customer c, TABLE(c.CUST_MOBILES) u

Please take a look at http://www.orafaq.com/wiki/NESTED_TABLE.

When you include the nested table it is like a join, so if you want a single row per customer, you will need to do some sort of unpivot (note that there could be more than two phone numbers in the nested table — Oracle needs to know how many columns to return. This will return all the rows:

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e;

Useful hints for unpivoting can be found here: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1


SQL Server commands to clear caches before running a performance comparison When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second.

In a Google Search, I could find these commands:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

In fact, my queries are taking a more realistic time to complete after several executions than before. However, I'm not sure this is the recommended technique.

What's the best practice?

Personally, for a common query the 2nd and subsequent executions matter more.

Are you testing disk IO or query performance?

Assuming your query runs often and is critical, then you want to measure that under real life conditions. And you don't want to clear prod server caches each time...

If you want, you can:

DBCC DROPCLEANBUFFERS clears clean (unmodified) pages from the buffer pool
 Precede that with a CHECKPOINT to flush any dirty pages to disk first
DBCC FLUSHPROCINDB clears execution plans for that database

Also see (on DBA.SE)

Solving high page load time using SQL Profiler


How to make ST_DWithin to use meters for distance in Postgis? I have already asked a similar question here:

Find the nearest geo points across two tables with Postgis (or without)?

but now I am stuck around the correct geo-reference to calculate the distance in ST_DWithin. What I have did with my tables is creating a column geom in this way:

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 4326);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Using this setup will take the function ST_DWithin to use as distance parameter "degrees" instead of "meters".
How can I set the geom to work with meters? Is this the right setup?

ALTER TABLE my_table ADD COLUMN geom geometry(Point, 2163);
update my_table set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 2163);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Cast the type to geography,

SELECT ST_AsText(geom)
FROM table
WHERE ST_DWithin( geom::geography, ... )

Consider setting the type on the table to geography too.


How to improve performance of SELECT query to same tables in Linked Server I have a SELECT query to a linked server. It looks similar to this

SELECT     @Var1 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition1>),
           @Var2 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition2>),
           @Var3 = (SELECT  COL1
                    FROM    [LinkedServer].[dbo].[TableA]
                    WHERE   <Condition3>),
           ...
FROM       [LinkedServer].[dbo].[TableB]
WHERE      <Condition4>

So there are multiples SELECT statements to the same table in this linked server. Is there a way to improve the performance? Is copying TableA to a temp table in local database server a bad idea? Size of TableA is large.

[UPDATE1]: Updated my query

SELECT      VAR_1,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_1
            AND      COL_2 = [TableA].COL_2
            AND      COL_A = [TableA].COL_A) AS VAR_2,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_3
            AND      COL_2 = [TableA].COL_4
            AND      COL_A = [TableA].COL_A) AS VAR_3,
            (SELECT  SOL
            FROM     [LinkedServer].[dbo].[TableSol]
            WHERE    COL_1 = [TableA].COL_5
            AND      COL_2 = [TableA].COL_6
            AND      COL_A = [TableA].COL_A) AS VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11

It took me 15 secs to run the above query. I changed to use crosstab like below:

SELECT      VAR_1
            SOL.VAR_2,
            SOL.VAR_3,
            SOL.VAR_4,
FROM        [LinkedServer].[dbo].[TableA]
INNER JOIN  [LinkedServer].[dbo].[TableB] ON [TableA].COL_10 = [TableB].COL_11
CROSS APPLY (SELECT
                CASE
                    WHEN COL_1 = [TableA].COL_1 AND COL_2 = [TableA].COL_2 THEN SOL
                    ELSE ''
                END AS VAR_2,
                CASE
                    WHEN COL_1 = [TableA].COL_3 AND COL_2 = [TableA].COL_4 THEN SOL
                    ELSE ''
                END AS VAR_3,
                CASE
                    WHEN COL_1 = [TableA].COL_5 AND COL_2 = [TableA].COL_6 THEN SOL
                    ELSE ''
                END AS VAR_4
             FROM    [LinkedServer].[dbo].[TableSol]
             WHERE    COL_A = [TableA].COL_A
            ) AS SOL (VAR_2, VAR_3, VAR_4)

But it took me much longer (> 10 mins and I just stop because it did not finish).
Did I miss something?

The query can be rewritten with a crosstab, in order to avoid hitting TableA multiple times:

DECLARE @TableA TABLE (col1 int, col2 int);
DECLARE @TableB TABLE (col3 int, col4 int);

INSERT INTO @TableA
VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

INSERT INTO @TableB
VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

DECLARE @Var1 int, @Var2 int, @Var3 int;

SELECT     @Var1 = CA.var1,
           @Var2 = CA.var2,
           @Var3 = CA.var3
FROM @TableB AS TB
CROSS APPLY (
    SELECT
        SUM(CASE WHEN col2 = 1 THEN col1 END) AS var1,
              -- ^^ This is <Condition1>
        SUM(CASE WHEN col2 = 2 THEN col1 END) AS var2,
              -- ^^ This is <Condition2>
        SUM(CASE WHEN col2 = 3 THEN col1 END) AS var3
              -- ^^ This is <Condition3>
    FROM @TableA AS TA
) AS CA(var1, var2, var3)
WHERE TB.col3 > 2 -- <Condition4>

SELECT @Var1, @Var2, @var3

This should make it faster. If it doesn't, please post the execution plan, so that we can investigate it further.


Postgres startup fail error I was running some code when my laptop shut down. Now everytime I connect to the server, I get an error message as follows:

ambangra@access1:~$ LOG:  database system shutdown was interrupted; last known up at 2014-04-03 04:27:57 SGT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/1864240
LOG:  record with zero length at 0/18668CC
LOG:  redo done at 0/18668A4
LOG:  last completed transaction was at log time 2014-04-03 04:19:34.929757+08
FATAL:  xlog flush request 0/18B02DC is not satisfied --- flushed only to 0/18668CC
CONTEXT:  writing block 0 of relation base/16384/12547_vm
LOG:  startup process (PID 14919) exited with exit code 1
LOG:  aborting startup due to startup process failure

Does it mean the code is still running? I am unable to connect at all. Any help would be greatly appreciated. Thanks!

Hi managed to clear it up using:

pg_dump data -f

I am able to connect to the server now.


Query for master in Postgres replication I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication.

From a slave, I can:

SELECT pg_is_in_recovery()

And this will give me a 't' result if I'm on a slave and an 'f' result on the master, which is step one.

Next, I'd like to run a query that gives me some information about the master that it's replicating.  Preferably an IP address or hostname.

For the record, I can query the master with:

SELECT * from pg_stat_replication

And this will give me information about slaves.  I am hoping that there is a reciprocal method for querying a slave.

Is this possible?  If so, how?

There is no way through SQL to get master information from a slave, as Craig said.

One solution I came up once was to use pg_read_file to get the contents of recovery.conf file, as:

SELECT pg_read_file('recovery.conf');

With that information, we can easily parse it using a regular expression to get all config/values. I used the following to get it done:

SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
) AS recovery_confs
ORDER BY rm[1], rn DESC;

With that, it is just a matter of choosing your config:

WITH recconfs AS (
    SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
    SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
    FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
    ) AS recovery_confs
    ORDER BY rm[1], rn DESC
)
SELECT setting FROM recconfs WHERE name = 'primary_conninfo';

The regex was adapted from PG's source file guc-file.l, but I did not used all the possible variants, which means that it will only work for strings (unquoted or with single quotes), which is Ok for primary_conninfo.

There are, in my opnion, two problems with this approach:

I don't guarantee this regex is really 100% perfect, anyone can see a mistake?
Only a superuser can use pg_read_file, but that is not really a problem, as it is expected or you can wrap it on a function (I did that, because I wanted a non-superuser to check it, in my case was a REPLICATION user).


Random write freezes I have a MySQL 5.5.23 DB that performs about 3k qps, 5% of which are writes. Lately, I've been having a big problem with random write freezes. Everything is going great, and then all of a sudden any write that comes in stops in the "update" or "Updating" state. They stay in this state for about a minute or two and then finally finish. The problem is that with such high amount of connections, when they freeze randomly like this, the connection stays open and then I inevitably get too many connection errors.

It's happening on all tables during both INSERT and UPDATE queries.

Has anyone seen this before? Is there anything that can be done about it?

I'm using Amazon RDS with the biggest instance they offer. I'd be more than happy to provide any information you guys need, just let me know what that is.

UPDATE: The main table I'm inserting into is:

CREATE TABLE `mytable` (
 `hash` varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `name` varchar(256) DEFAULT NULL,
 `ip` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `deletehash` char(15) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `datetime` datetime NOT NULL,
 `api_key` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
 `account_id` int(10) unsigned DEFAULT NULL,
 `type` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `width` int(10) unsigned NOT NULL DEFAULT '0',
 `height` int(10) unsigned NOT NULL DEFAULT '0',
 `size` int(10) unsigned NOT NULL DEFAULT '0',
 `animated` tinyint(1) NOT NULL DEFAULT '0',
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `lastviewed` datetime NOT NULL,
 PRIMARY KEY (`hash`),
 UNIQUE KEY `deletehash` (`deletehash`),
 KEY `datetime` (`datetime`),
 KEY `account_id` (`account_id`),
 KEY `ip` (`ip`),
 KEY `api_key` (`api_key`),
 KEY `views` (`views`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Sample insert:

insert into mytable (hash, name, ip, deletehashe, datetime, api_key, account_id, type, width, height, size, animated, views, lastviewed) values('abcde', 'file name', '127.0.0.1', 'abcdefghijklmn', '2012-05-31 00:00:00', NULL, NULL, 'mime/type', 0, 0, 0, 0, 0, '0000-00-00 00:00:00');

Sample update:

update mytable set views = views+1, lastviewed = NOW() where hash = 'abcde';

I think the issue is due to the choice of the clustered index. From MySQL docs, Clustered and Secondary Indexes:

  Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

Also check the answer by @marc_s in this SO question: How to choose the clustered index in SQL Server?, where he mentions:

  According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:

  Unique
  Narrow
  Static

  And if you can also guarantee:

  Ever-increasing pattern

  then you're pretty close to having your ideal clustering key!

Now, your clustered index is the (Primary Key):

hash varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,

which (lets go through the check-list) is:

Unique  (yes, OK)
Narrow  (yes, OK)
Static  (perhaps, you know that)

but is probably not:

Ever-increasing pattern (No, it probably isn't)

So, what happens when you use a non-ever-increasing clustered index?

I can't answer better than Kimberly L. Trip: Ever-increasing clustering key - the Clustered Index Debate..........again!

  If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity).

Note that despite the mention of SQL-Server, the same concept applies to InnoDB clustered indexes as well. I suppose that the clustered index has 2 issues:

When you are inserting a new row (the "random" hash guarantees that) it gets inserted in a random location of the index. This means that it sometimes will find no space there available to be inserted (note that InnoDB always leaves some space free in the index but when that free-available space is filled) there has to be some rearrangement of the index - and that takes time.
What the rearrangement is also causing over time is fragmentation of the index. Which will eventually make other queries and statements slower.


How to know which columns value is different between two rows I am trying to compare two addresses from the same ID to see whether they match. For example:

Id  Adress Code     Address
1   1               123 Main
1   2               123 Main
2   1               456 Wall
2   2               456 Wall
3   1               789 Right
3   2               100 Left

I'm just trying to figure out which column have changed value between two records having same ID. Like in above data ID ( 1, 2 ) have same address but different address code and ID 3 have different Address

Update
Expected result should be like this

Id   ColumnChanged
1    AddressCode
2    AddressCode
3    Address

This is the solution I propose, If you have more columns to test differences you just have to union another select in the same manner.
As I previously commented, in the sample date you provide for id =3 both [Address] and [Address Code] have different values, for these situations two rows are given one showing that column [Address] is different and another showing that column [Address Code] is different.

select id, 'Address Code' As [Column Changed] from
(
select distinct id, [Address Code] from Addresses
) As Differents
group by id
Having count(*) > 1
UNION
select id, 'Address'  As [Column Changed] from
(
select distinct id, [Address] from Addresses
) As Differents
group by id
Having count(*) > 1

Output

id  Column Changed
1   Address Code
2   Address Code
3   Address Code
3   Address


How to get EstimatedRows values from SQL Server execution plan nodes When I execute the following code in an Azure SQL Database (v12) or SQL Server 2016 instance, I get values in the EstimatedRowsRead column, but not in the EstimatedRows column, and I'm not seeing why. Any ideas?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT')
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimatedRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;

The reason is a small typo but I agree that Estimatedrows would seem like the way to go.

In the execution plan's XML it is called Estimaterows instead of the expected EstimatedRows.

An example:

EstimateRows="12"

This should work:

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT')
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimateRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;

Result example

NodeId  EstimatedRowsRead   EstimatedRows
3         530                1
4         1,93078            1
9         12                 12
8         12                 12


MySQL - 450 columns per table good or wrong practice I have table having 450+ columns. Can you please confirm is this fine or should I need to split this table?

If yes, what value will be recommended for the number of columns per table in MySQL?

The technical limit on the number of columns depends on the engine.  InnoDB allows 1017 in MySQL5.7
Those 1017 columns can cover a maximum of 65535 bytes.
Records are stored on "pages".  InnoDB allows the page size to be configured to 4, 8, 16, 32, 64Kb.  Your record must fit on a page so you can't stick a 5K record on a 4K page.

The problems with having wide records is that when the DB engine retrieves records it does so in pages. You can get few wide records on a page so retrieval performance decreases. DBs pull the results through memory so subsequent retrievals will see if the data remains in memory before falling back to storage.  Having many records on a page means that the first physical retrieval of a page is more likely to load into memory records which can be logically (and much faster) read from memory.

From a design perspective it depends on what your use case is.  In an OLTP system then I would feel uncomfortable with 450+ columns.  A database is not a dumb store.  It can be used to enforce rules on the structure of information and the relationships between different data entities. This is an incredibly powerful weapon to have in your arsenal.

In a data warehouse supporting certain analytical systems 450+ sounds like a lot however I have seen some wide denormalised tables used to feed OLAP cube technologies.

If I saw a 450+ column table I should also ask questions about security.  When I grant access to that table do I want everyone with access to have access to all 450+ columns?  In addition to storage efficiency/performance normalisation can also factor in a security design.

Consider performance.  Of those 450+ columns which ones get retrieved the majority of the time?  Do you really want to have the expense of retrieving 450+ columns if only 32 are used on a regular basis?

The answer I have given assumes that InnoDB (the default) is used.


Postgres special character rendering I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table.  This field contains a lot of garbage characters (higher than Ascii 128) which Postgres has a difficult time rendering and results in ‘empty looking’ records.

I say empty looking as I am able to query those missing records out of the foreign table using the regexp_replace function to substitute spaces for those problematic characters.

This leaves me with two questions:
1)  The Postgres database encoding is UTF-8, so shouldn’t that be able to render any characters properly?

2)  Since I can query it, the Postgres db is obviously processing this data at some level.  Why does it display that entire field as blank until the special characters are replaced?

The ultimate answer turned out to be different encoding in the two databases.  Thanks Erwin for steering me in that direction.

My apologies for not asking the question correctly.  I am definitely out of my depth in this area.


non cumulative differential backup for SQL Server SQL Server natively do not support non-cumulative differential backup type.
I wonder why independent backup software providers like from RedGate, Idera or Quest do not support it either?
It looks easy to implement: just store LSN from last non-cumulative backup and put in the backup files only pages changed from this LSN and could be useful for log shipping databases in simply recovery mode.
Is there any caveat I am missing?

Transaction log backups are incremental, that is, they only store incremental changes since the previous transaction log backup.
And they do it more or less as you told, using last well know backuped LSN (a checkpoint).
This is why you have to restore the full backup and then all the TLOG backups in the proper order.
But this requires full recovery model. Differential backups work in all recovery models, namely Simple, Bulk-Logged, and Full.
If you want incremental backups (i.e. non-cumulative differential backup), take transaction log backups regularly.


Using MySQL/PHP PDO, is there a 'simulate' or dry run option? Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually committing any INSERT, DELETE or UPDATE statements?)

'Inserted' data (people IDs, business IDs, phone number Ids, phone type IDs, etc.) should be stored somewhere, so that they can be retrieved if need be. Data shouldn't be written to the main database during testing/development, only when the script is complete and ready for production use.

The reason for asking is this: In developing a data-copying/transferring script, it's inconvenient to delete the test data out of the tables and reset AUTO_INCREMENT before running the changed import script.

Alternately, should I ...

Create my tables in memory at the start of the script and delete them at the end?
Replicate the database (create a slave) for use in development and replace the slave with each modification to the script?

(There will be about 1000-2000 people imported, with up to 15 phone numbers for each person, 1 business for each person and up to 10 roles for each person.)

The best way to do SQL syntax checking without actually writing data is using the engine BLACKHOLE, which is the MySQL equivalent to UNIX /dev/null. A its name suggests, it is an engine that accepts all (but invalid syntax) but stores no data. Great for testing, and incredible inserting performance :-)

Aside from that, people test setups by creating slaves with live data on a separate instance.

Be careful with very dynamic data on InnoDB, and large uncommitted transactions, as that could create a lot of unused gaps on production depending on the configuration (innodb_file_per_table = 0, undo area, etc.) and also performance problems (rollbacks are very costly in InnoDB).


How to obtain row counts for all tables in Netezza? Is there a system table or view in Netezza that shows the number of records for each table?

How about:

SELECT TABLENAME,
       OBJTYPE,
       OWNER,
       CREATEDATE,
       USED_BYTES,
       USED_BYTES/1073741824 as USED_GB,
       RELTUPLES as "ROWS"
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;


Large NOT IN Statement vs Creating/Populating Temp Table I have a large daily data feed which can contain up to around 5,000 ID's. I need to delete all pre-existing records in the DB that are not referenced in the new feed. Should I create a temporary table for these 5,000 ID's, and run a LEFT JOIN IF NULL DELETE statement? Or, is it all the same if I just pass the ID's over and run a DELETE IF NOT IN statement. My assumption is that MySQL would do the work of creating the temp table for me when using IN statements.

With a large set you may want to use an NOT EXISTS clause rather than a NOT IN clause.  It will help if you index the id on your feed table.  Using an index will allow the optimizer to use the index rather than reading the table.  Try a query like:

DELETE
FROM    table
WHERE NOT EXISTS (
    SELECT  feed.id
    FROM    feed
    WHERE   feed.id = table.id )


How to script all the service broker elements for each database it is on, in the current server? I have service broker running on different servers.

Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.

For the next time I need to rebuild a server that has service broker running in any of its databases

I would like to script all the service broker architecture for that specific database

-- Message Types
SELECT *
FROM sys.service_message_types;

Basically using the scripts below, how can I be able to script all the service broker elements for a particular database?

-- Message Types
SELECT *
FROM sys.service_message_types;

-- Contracts
SELECT *
FROM sys.service_contracts;

-- Queues
SELECT *
FROM sys.service_queues;

-- Services
SELECT *
FROM sys.services;

-- routes
SELECT *
FROM sys.routes;

-- Endpoints
SELECT *
FROM sys.endpoints;

After seeing a recent comment from David Browne:

  SSDT / SqlPackage supports Service Broker objects.

I tried it out. I created some service broker objects based on the demo here:

Configuring Service Broker for Asynchronous Processing

Then I download the DAC framework from here:

Download Microsoft® SQL Server® Data-Tier Application Framework (18.0)

Then I ran this command (without the carriage returns):

sqlpackage
  /Action:extract
  /SourceServerName:.\instancename
  /SourceDatabaseName:AsyncProcessingDemo
  /TargetFile:C:\scripts\sb.dacpac

Then I went to c:\scripts, right-clicked the dacpac file, and chose Unpack...:

This asked me for an output folder:

I clicked Unpack, went to that folder, and there were four files, including model.xml:

And sure enough, that .sql file contained a script for all the broker objects, assembled in almost the right order. It generated a script with the contract created before the message types it depends on. So you will end up with:

CREATE CONTRACT ... ([MessageType1] SENT BY INITIATOR, ...
GO
CREATE MESSAGE TYPE [MessageType1]...

You'll need to flip those around so the message types are created first.

I'm sure SqlPackage.exe and/or the DAC wizard has ways to restrict this to just the service broker objects, but this should be a good start.


Log File Full -- Database is in AlwaysOn Database log file is 99% used.and sys.databases says LOG_BACKUP.Will manual log backup affect the AlwaysOn ? and like log shipping will AlwaysOn have log backup jobs ? Shrinking fails because of pending log backup ? Please advice should I create a maintenance plan for regular log backups ?

Will manual log backup affect the AlwaysOn ?
No Manual Log backups wont affect,this is same like you are doing through scheduled jobs..In Summary

Log backups can happen on any node and the log still gets truncated.  The transaction log is basically replicated amongst all replicas.

like log shipping, will AlwaysOn have log backup jobs

AlwaysOn is similar to Mirroring in areas where log will be transferred over wire..

From Bob Dorr article

Always On is a bit different than database mirroring (DBM) with respect to sending the log blocks to the secondary replica(s).   DBM flushes the log block to disk and once completed locally, sends the block to the secondary.
Always On changed this to flush the block(s) in parallel.  In fact, a secondary could have hardened log block(s) before the primary I/O completes.    This design increases performance and narrows the NOT IN SYNC window(s).

Shrinking fails because of pending log backup
if you are saying can i shrink log to reduce space.Don't do that,shrinking is a bad practice.Log space will be reused .Taking into Account factors that can prevent log space reuse,you also need to account for fact that replicas have hardened the log blocks to Disk
References:
https://blogs.msdn.microsoft.com/sqlgardner/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica/


T-SQL is not working on DBeaver on Linux I am trying to run a basic T-SQL script

    DECLARE @temptable TABLE(
      abilityTypeId BIGINT NOT NULL,
      companyId BIGINT NOT NULL
    );

    INSERT INTO @temptable(abilityTypeId, companyId)
    VALUES (1, 2);

    SELECT * FROM @temptable;

but I get the following error:

  SQL Error [1087] [S0002]: Must declare the table variable "@temptable".
  Must declare the table variable "@temptable".

MERGE INTO is also not working.
Is it possible to get this working on Linux or do I have to use Visual Studio for this?

DBeaver parses queries one by one using a statement delimiter (“;” by default) and executes them consecutively.

Try removing the empty lines and those “;”.

Check more details here.


How to use aggregate functions on each array in a table, in Postgres? Imagine I have a table like:
games (
    scores INTEGER[]
)

And it had values
{1,2}
{3,4}
{}

I'd like to do arbitrary aggregations on that array.
For example, how would I do SELECT SUM(scores) AS total FROM games? Or an average? Or a max?
I'd like to get out:
3
7
0

I tried SELECT SUM(UNNEST(scores)) AS total FROM games but that gave ERROR:  aggregate function calls cannot contain set-returning function calls.

You need a scalar subquery:
select (select sum(i)
        from unnest(scores) as x(i)) as total
from games;


Steps to determine why NT AUTHORITY\SYSTEM has permission on one database, but not another Have a service running under NT AUTHORITY\SYSTEM (Local System) and accessing local SQL Server database using Windows auth.

On one database they have permission to read/write - on other database (on local database server & same instance) appears to be read only permission.

How would you go about troubleshooting this and determining what is the cause of the different permissions?

Unless NT Auth\System has server level access that covers all of the databases (for example sysadmin (not recommended)) then why would you think it should have access to all of the databases?

Permissions for the most part are handled on a database by database level.  You can look at it through the GUI (security dropdown under the DB in the object explorer among other places) or there are lots of queries out there that can help you.

I've got two

sp_dbpermissions
sp_srvpermissions

that will help you list out the permissions for a given user/db/etc.  Once you have determined what permissions the ID SHOULD have for a given database it's just a matter of adding the user to the db (CREATE USER) and granting them the permission using the GRANT command.


Query speed improvements Referencing:

CREATE TABLE words (
  word_id int(11) NOT NULL,
  word varchar(25) NOT NULL,
  PRIMARY KEY (word)
)

CREATE TABLE synonyms (
  source_index int(10) unsigned NOT NULL,
  destination_index int(10) unsigned NOT NULL
)

Querying:

SELECT w.word, z.word
 FROM words w
 INNER JOIN synonyms y
  ON w.word_id=y.source_index
  INNER JOIN words z
  ON z.word_id=y.destination_index
 WHERE w.word='kind'

The problem is a query on a table with less than 120,000 entries takes 400+ seconds. I was hoping this would be more efficient than having a second table with a similar word list for the synonyms, but so far it is proving otherwise. I have no qualms with keeping synonyms a separate word table as it isn't quite a duplicate of words. I am not finding anything applicable on tuning such queries for lower time online. Is there a way to  tune this for reasonable speed (<100 msec) or am I better off without the 'split self reference'?

Right now you have only added one index on words for word. Therefore your query will right now result in at least one full table scan for both words and synonyms, probably even more - that might depend on your DBMS and it's query optimizer's abilities.

Try adding an index on words for word_id and on synonyms for at least source_index. This way your query will use the index and not do a full table scan.

You could probably improve on that by using covering indeces, e.g. (word_id, word) on words and (source_index, destination_index) on synonyms.


Restore MySQL dump to a container fails with ERROR 1114 "The table is full" I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command:
mysql -h example.com -u user -p matomo < dump.sql

But it fails with an error:

ERROR 1114 (HY000) at line 7238: The table 'piwik_log_link_visit_action' is full

Many other small tables are copied successfully, but while coping this table it fails with above error. The size of this table is more than 2GB.
Based on different suggestions available on Stack Overflow, I tried each one but nothing worked.
I tried adding 'autoextend' to the my.cnf file:
innodb_data_file_path=ibdata1:10M:autoextend

I also tried to increase the tmp_table_size and heap_table_size by adding following parameter to the my.cnf file:
tmp_table_size=2G
max_heap_table_size=2G

Also, I made sure that the server (from where I am running the dump restore command) has enough space (more than 20GB of storage available). But nothing worked.
I tried debugging this more and found that, the docker container where MySQL is running has overlay filesystem of size 5GB which starts getting filled and as soon as it fills 100%, I get above error.
Volume mounted on the container is of more than 30GB size. I am not sure from where this overlay file system is coming in docker. Overlay is something coming from docker I guess, but not sure where I can increase its size.

I can't even go inside the overlay directory and keep deleting or freeing the space. Can anyone please help me here.
my.cnf file
[mysqladmin]
user=user1

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mariadb
port=3306
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
max_allowed_packet=256MB
bind_address=0.0.0.0
log_error=/opt/bitnami/mariadb/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mariadb/plugin
innodb_data_file_path=ibdata1:10M:autoextend:max:10G
max_heap_table_size=2G
tmp_table_size=2G

[client]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mariadb/plugin

[manager]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
!include /opt/bitnami/mariadb/conf/bitnami/my_custom.cnf

"Table is full" implies a disk space problem.  But there are other issues:
How much RAM in your container?
tmp_table_size=2G
max_heap_table_size=2G

not more than 1% of RAM available.  Anyway, those are used during complex SELECTs; they are not used during INSERTs.

dump of size around 18GB

Depending on the datatypes, etc, etc, the amount of disk needed could be a lot less or a lot more than 18GB.  I would be skeptical about 30GB being enough.  The dump is text; the data may be something else.  For example, a 4-byte INT could take 2 bytes ("0,") in the dump or 12 bytes ("-1234567900,") in the dump.  After loading into the database it is likely to take 8-12 bytes, depending on overhead.  That might be a 6x expansion (2->12) or a shrinkage (12->8).  (That's just one example.)
If you have integers arbitrarily in BIGINT, that 4 bytes becomes 8.  Shrinking the datatypes (where appropriate) will shrink the disk space needed.  Please provide SHOW CREATE TABLE; there may be some other obvious suggestions.
And what is "overlay"?  It really looks like it hit a limit of 5GB.