MySQL, "columns partitioning" when multiple columns are individiually important MySQL 5.5 introduces "columns partitioning".
http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html

I'm trying to better understand how it works when two columns are important individually.

Let's say for a table which holds the messaging between two system users. We would potentially have "sender_id" and "receiver_id" columns, and we might want to query against these columns individually.

If we have separate indexes on both of these columns, we can query them individually when necessary. Results are fast.

But what if our table is 100M rows large, and we consider partitioning. My understanding is multiple column partitioning focuses on the first column in the columns definition, then the second one. Here is a sample table structure:

CREATE TABLE messages (
         message_id INT,
         sender_id INT,
         receiver_id INT
     )
     PARTITION BY RANGE COLUMNS(sender_id,receiver_id) (
         PARTITION p0 VALUES LESS THAN (10,10),
         PARTITION p1 VALUES LESS THAN (20,20),
         PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)

If we query for "WHERE receiver_id=5", partition pruning will not kick in, right? It will need to search all partitions. But if we were to search for "WHERE sender_id=5", then we would immediately know the result is in p0.

So for a table where two columns could potentially be individually important, partitioning might not be the best solution since now we lost the benefit of a full-table index for the secondary column(receiver_id, for this case) in the columns parameter. Is that right?

You are right that mysql will check only one partition for a specific sender_id, but checks all the partitions for a specific receiver_id, as shown here:

mysql> explain partitions select * from messages where sender_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from messages where receiver_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0,p1,p3   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

However, there are still benefits to that partitioning, depending on your hardware.  When looking in all partitions for a receiver_id, mysql is really performing 3 select statements, one for each partition.  It may be able to parallelize these select statements.  Additionally, if you index receiver_id, it will be accessing 3, smaller, indicies.

In the end, you just have to do performance testing and see if it is paying off for your use case.  Seeing as 100MB fits in RAM pretty easily these days, I wouldn't consider partitioning such a small table unless you have specific reasons to do so.


Restore of Replicated Database fails with “Cannot drop the table  because it is being used for replication.” published database restoration failed once you setup transactional Replication using system Store procedures. Few work around to this problem is restore with KEEP_replication option and than drop the publication and subscription.

Is there anything we can do while setting up the Transactional replication using System SP that can avoid this issue ?

Use below query to avoid replication related SP in Transactional Replication.

SELECT name 'ROUTINE_NAME','T' 'ROUTINE_TYPE'
            FROM sys.tables
            WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 1 AND name not like 'MSpeer%'


Is there a possible reason for having index on (a) and on (a,b) at the same time Suppose I have a table with several column, among them - columns a and b.

Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial?

AFAIU for simple equality check using first column of multi-column index is equally good as using the single-column index.

You are right. The index on (a) is not necessary if there is another one on (a,b).

The only situation where this might (!) be helpful is, if a lot of index only scans are done on the indexes (something like select count(*) from foo where a = 42) and b is e.g. a large text column.

The index on (a) will obviously be a lot smaller, so an index only scan on that index will be faster (whether or not that "faster" is actually measurable depends on the data in the column b)

But I would consider this is an edge case with a very specific workload.


Performance hit of CONVERT from nvarchar to money (SQL Server) I have a dollar amount value, that for historical reasons has always just been stored in an nvarchar field. Now that we are storing many more rows than we have in the past, and are using this field to total up amounts using an application that will automatically refresh frequently, I'm concerned about performance.

But I'm also concerned about the cost of making a change like this late in the development cycle.

Soon this database will be used by a customer that will generate millions of rows. All of these rows will not participate in this calculation. The rows are divided into groups of around 10,000, each parent of the group will be processed every 10 minutes or so, which is when these totals will be calculated.

Is it worth it now to change the column type to money, including the stored procedures, UDT's, data layer, etc?

It seems like it could have a performance impact, but unfortunately, I can't generate enough volume with the resources I have to do performance tests thta would be realistic. So I'm hoping someone has experience with a string to number conversion and can give me an idea if doing this conversion on 10,000 rows at a time will be a problem.

Performance cost of cast is minor. Database cost is usually in IO and a cast seldom even shows on the radar.

But that is not the problem. The real problem is going to be data purity. If you allow arbitrary nvarchar then rest assured, non-numeric values will show up in the database. Despite every precaution you take in the app, experience tells us that down the road somewhere somehow will insert 'abagft' as the value. And then the casts will start throwing runtime exceptions and you (or whoever will be running the show then) will curse and curse and curse. And you'll try to 'fix' it with a boolean shortcircuit WHERE, just like everybody else, and it won't work. This road is paved with bodies, trust me.

There are also problems related to data type precedence, type inference in dev tools (fields ends up as C# string) and, most importantly, index seek sargability problems.

Use a numeric type for numeric values. DECIMAL is good for moneys. And the time for change is always before you go live with millions of values. Right now is the cost of code change and risk of regression. In 6 months will be the same cost of code change and risk of regression plus downtime and size-of-data update operation.


EAV structure explained in Layman's terms I've read several articles online that explain how EAV structures work, but there are different interpretations of them.  Some articles mention that it's a representation of a data model metadata (or schema).  Other articles describe an EAV structure as being an ontology.  I picture an ontology as either a conceptual model (showing relationships among "things" in the world), or class instances to the actual data in a database.

Can someone explain what each table in this model represents in Layman's Terms and how one would use this EAV data model?

I didn't see this as a representation of a data model, because [Object] is not directly linked to [Attribute].  You need an actual value to relate those two.  So there's nothing to enforce that you could use a [Attribute] A with [Object] O if A is a field/column of table O in your data model.  Now I did see the RI tables.  I assumed that meant "Referential Integrity".  But if we're talking about ontologies, then we could call it relationship identity.  I'm merely referring to the relationship or predicate when talking about an ontology triple--meaning Subject/Predicate/Object.  Note, that the joins are not connected with attribute, which would represent more of a relational data model FK/PK relationship with table columns, so I expected it to be connected to Attribute, not Object.  It's connected with [Object].

Please shed some light on this if you're able.  Maybe I'm thinking too much into this.  If you have a different EAV model that's useful, let me know.

Reference: http://eav.codeplex.com

Here is what I think is intended in this model:

Category is a kind of thing.  In a data model it would be an entity type, in a database it would be a table.
Attribute is a facet of a thing.  In a data model it would be a predicate type, in a database it would be a column.
Object is an instance of a thing.  In a data model it would be an entity, in a database it would be a row (in a particular table).
Value is an instance of a predicate or a column value in a database table.
Domain Lookup is a mechanism that allows you to constrain attribute values.  This implementation works by constraining the value to one in a set of legal values.  In a database this could be likened to a check constraint or a lookup table.  Obviously there are other types of constraints that could be imposed (but aren't in this metadata model) such as range constraints.
RIConfig is a relational constraint between two kinds of things.  This is a relationship type in a data model or a foreign key constraint in a database.  At this level it is describing the relational rule, not the instances of relationships.
RI is an instance of a relationship between to specific entities.  In a physical database, this would be a record in a foreign key index (perhaps?)

You are quite right to observe that the modeling of relationships is flawed insofar as it doesn't account for the implementation of these relationships.  RIConfig should join to Attribute not to Category - or rather, there should be an intersection table between RIConfig and Attribute to allow for compound primary/foreign key relationships.

As I noted above, domain constraints are also not handled with realistic flexibility.

Given the limitations of this model, it would not be adequate as a metadata repository for many real-world databases.


PostgreSQL table partitioning problem I partitioned my table  day by day. The child tables are look like points_20150830. Every day, I run a cron job to create table and I insert records without using any trigger function. I insert records by calculating day of timestamp.

However, when I run a select query, query plan shows that every child table visited without checking constraints. Also I enabled constraints via "constraint_exclusion = on" in postgresql.conf

I think that the problem may be related to timestamp with timezone but I could not find any solution.

Thanks for your help, here are details

Master table :

CREATE TABLE points (
    point_key bytea,
    users_id integer,
    point_date timestamp with time zone
);
CREATE INDEX points_point_date ON points USING btree (point_date);
CREATE INDEX points_users_id ON points USING btree (users_id);

Child table :

CREATE TABLE points_20150701 (
    CONSTRAINT points_20150701_point_date_check CHECK (((point_date >= '2015-07-01 00:00:00'::timestamp without time zone) AND (point_date < '2015-07-02 00:00:00'::timestamp without time zone)))
)
INHERITS (points);
CREATE INDEX points_20150701_point_date ON points_20150701 USING btree (point_date);
CREATE INDEX points_20150701_users_id ON points_20150701 USING btree (users_id);

Query that I perform :

EXPLAIN (ANALYZE, BUFFERS)
            SELECT
                COUNT(point_key) as points
              FROM points
              WHERE users_id = 100 AND point_date >= '2015-08-25 00:00:00'::timestamp AND point_date <= '2015-08-31 23:59:59'::timestamp

Result of the query :

http://explain.depesz.com/s/VDI0

I'm going to add my answer, which was correct in the comments, as the answer here.

Be specific about your types!

In your master table, you have point_date as a timestamp with time zone.

In your CHECK constraint, you have used timestamp without time zone.

Then, in your query, you just use point_date >= '2015-08-25 00:00:00'::timestamp, thus using a timestamp type in your WHERE predicate.

To fix your problems, make sure that all of these entries are referring to a consistent type.  In your comments above, you indicated that you changed all entries to refer to the type timestamp with time zone, and that it fixed your issues.


MySQL - Do not allow two rows to have the same specific columns id | source | target | desc
--------------------------------------------
0  | hello  | hola   | this is a description   //Valid insert
1  | hello  | no     | this is a description   //Valid insert
2  | what   | what   | desc                    //Valid insert
3  | hello  | hola   | testing                 //INVALID insert

I want the row with ID 3 to error when inserted because it's source matches another row's source AND its target matches that same other row's target.

I know that I could do this in PHP, but I would prefer it done within the MySQL RDBMS.

CREATE TABLE `t1` (
  `id` int(11) ,
  `source` varchar(30) ,
  `target` varchar(30) ,
  `desc` varchar(100) ,
  PRIMARY KEY `pk_id` (`id`),
  UNIQUE KEY `uc_source_target` (`source`,`target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

or on existing table

 ALTER TABLE t1 ADD CONSTRAINT uc_source_target UNIQUE (`source`,`target`);

INSERT INTO t1
    (`id`, `source`, `target`, `desc`)
VALUES
    (0, 'hello', 'hola', 'this is a description'),
    (1, 'hello', 'no', 'this is a description'),
    (2, 'what', 'what', 'desc');

 mysql>  INSERT INTO t1
    ->            (`id`,`source`,`target`,`desc`)
    ->     VALUES
    ->             (3,'hello','hola','testing');
ERROR 1062 (23000): Duplicate entry 'hello-hola' for key 'uc_source_target'


Why are my SQL Server backup ending as a file that is twice the size of my database? I have a SQL Server 2008 r2 database that is currently 56 GBs. When I run the following code I get a bak file that is 93 GBs. What could be causing this?

    BACKUP DATABASE [CommerceCenter]
TO DISK = 'N:\MSSQL\DATA\CC\CC_backup_tues.bak'
WITH COMPRESSION

If I use SSMS to run the backup to the same location with compression the bak file is only 8 GBs.

Most likely, it's multiple backups inside the same file. To see it:

RESTORE HEADERONLY
FROM DISK = N'N:\MSSQL\DATA\CC\CC_backup_tues.bak' ;

If you see multiple backups in there, you have a couple of different options:

Specify a different file name per backup (I'm a huge fan of this - use date-based file names rather than days), or
Use the WITH INIT command to overwrite the last backup - this can be a bad idea if you ever need to go back more than a week


How do you truncate a `date` (not `timestamptz`) to month? Postgres has date_trunc which operates on timestamp or interval, and:

  Values of type date and time are cast automatically to timestamp or interval, respectively.

In other words we can use date_trunc for date values with a cast:

select date_trunc('month',current_date)::date;
┌────────────┐
│ date_trunc │
├────────────┤
│ 2014-12-01 │
└────────────┘

But is this timezone safe—if the current date is in Daylight Saving Time and the start of the month is not or vice versa will the correct date result?

Yes it's safe.

It's not exactly clear in the documentation but date_trunc can return either timestamp or timestamptz depending on what you pass it. If you pass a date, it will cast the input to timestamp and returns timestamp so there can be no tz issue when casting back to date.

If you pass a timestamptz it does the truncation the way you'd expect and returns a timestamptz:

select current_timestamp-'40d'::interval;
┌───────────────────────────────┐
│           ?column?            │
├───────────────────────────────┤
│ 2014-10-27 19:32:13.869909+00 │
└───────────────────────────────┘

select date_trunc('month',current_timestamp-'40d'::interval);
┌────────────────────────┐
│       date_trunc       │
├────────────────────────┤
│ 2014-10-01 00:00:00+01 │
└────────────────────────┘


Oracle Materialized View I need your help with a issue on Oracle Materialized View.

I created the Materialized View using the following command:

CREATE MATERIALIZED VIEW SNAP_TEST
TABLESPACE tbs
NOCACHE
LOGGING
NOPARALLEL
REFRESH COMPLETE
START WITH TO_DATE('10-nov-2011 07:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1
AS
SELECT item
from item@abc;

If I manually refresh it everything works properly. But it doesn't work automatically: I expected an auto-refresh everyday at 7:00, but it never happened.
Why? Do I have to activate something?

You need to set JOB_QUEUE_PROCESSES to a positive number in order to enable database jobs to run. JOB_QUEUE_PROCESSES controls the number of jobs that are allowed to run simultaneously so if you set the parameter to 1, only 1 job could run at a time. If you're going to be creating multiple materialized views that you want to refresh at approximately the same time or if you are potentially going to be creating additional jobs, you may want to set this parameter to a value greater than 0.


Why Sql Server management studio use English regional settings instead my local Windows settings? Scenario:
I've a Sql Server instance on English Windows Server.
But I've my own ITALIAN client with Sql Server Management Studio and SSMS regional settings set to Italian.
So when I extract data, i need separator as follow :
, (comma) for decimal
. (point) for thousands
Example: 1.065,00
Instead , Sql Server Management Studio return me number like:
1065.0000
(point for decimal separator)
If I copy & paste into Excel all data are mess..
How can I do without writing specific query with "Format" or similar ?
Thanks
Example : point , but i need comma!

SSMS is inconsistent. For some data types, it uses comma (I'm Swedish) as decimal separator. For other types, it uses dot.
For money and float SSMS presents comma, but for decimal I get dot. None of them gives me a thousand separator.
SELECT CAST(3000.1 AS money), CAST(3000.1 AS float), CAST(3000.1 AS decimal(9,2))
I guess that one could consider the inconsistency a bug.
SSMS was never designed to be an end-user tool, so we pretty much have to accept what we are given. If we don't like how SSMS present the data returned from SQL Server we can open a feature request for this to Microsoft: https://feedback.azure.com/forums/908035-sql-server
I wouldn't have too much hope for this to be addressed.


Enable Agent Database Mail - TSQL I have several new servers that need Database Mail configured on them.  I found the TechNet article describing how to add the profile and account via procedure calls, but is there a way to enable database mail and select the profile for the SQL Agent in the same manner?

The following T-SQL will enable Database Mail in SQL Server Agent:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',
    N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
    N'REG_SZ', N'<profilename>'
GO

For future reference, you can easily determine this by expanding the "SQL Server Agent" node in SQL Server Management Studio's Object Explorer pane.  Then right-click on "SQL Server Agent", click "Properties", select "Alert System", enter the appropriate details, and click on "Script":


How to get the row number when using alias in orderby I have a query. I use an alias in order by when using row_number and I got

  [42703] ERROR: column "total_comments" does not exist error

How can I fix this?

 select
      cr_seller_history_id,
      c.created_at,
      company_name,
      business_name,
      brand,
      kep_mail,
      address,
      phone,
      mail,
      slug,
      name,
      point,
      contact_positive,
      contact_negative,
      product_number,
      (product_positive + product_negative) as total_comments,
      ROW_NUMBER() OVER(ORDER BY total_comments) as rank
    from cr_companies a
      INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id
      INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id
      WHERE DATE(c.created_at) = DATE 'yesterday'
    ORDER BY total_comments DESC NULLS LAST

The error is (from the column used inside the OVER) in SELECT. You can't use a column alias defined in the select list for another column expression in the same select list:

SELECT
      --
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY total_comments) AS rank
                               -- ^^ this is throwing the error

You just have to duplicate the expression inside the OVER (ORDER BY ...):

SELECT
      --
      (product_positive + product_negative) AS total_comments,
      ROW_NUMBER() OVER (ORDER BY product_positive + product_negative) AS rank
---
ORDER BY
      total_comments DESC NULLS LAST ;       -- you can keep it here

Irrelevant to the issue but this condition will stop the optimizer from using any indexes on (created_at):

WHERE DATE(c.created_at) = DATE 'yesterday'

I'd write it like this, so indexes can be used more effectively:

WHERE c.created_at >= DATE 'yesterday'
  AND c.created_at  < DATE 'today'


SQL Server 2008 DB Performance on single disk I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly.

Somehow I can not add multiple independent harddisks to increase IO performance.

If a add large table in separate file group, will it help to improve performance on single disk?

Or any one has tips to improve performance?

Thank you

First off, gather a performance baseline in order to know what the situation is now. Keep monitoring the performance to see if there is a trend for degeneration. If the performance seems to become an issue, there are a few things to do.

Identify the root cause for lack of performance. Is the server expiring memory pressure, IO waits or high CPU usage? The word on the street is to look at Brent's performance tuning pages for a quick start.

Don't overlook good DB design. Check the DB against missing indexes, outdated statistics and poorly written queries. Any of these issues can eat up all the iron you got and then some.

Edit:

Using multiple file groups can - in certain situations - increase performance. Even Microsoft's documentation claims performance advantages. Alas, in your scenario they do not, as you do not have extra disks.

The idea of file groups is to divide database contents to multiple files. File groups make it possible to locate hot tables to high performance storage. This, requires advanced storage configuration your system is lacking. Partitioning tables can take advantage of storage this way too.

Administration tasks like backup/restore can be more manageable with filegroups. Usually a VLDB (very large database) is needed to get such an advantage. Another a classic example is handling read-only data and separating it to its own filegroup. As the data doesn't change, it can be backed up more seldom than the rest of the database.

If you deploy multiple file groups to single a disk, there is no performance bonus. The reason is that you still got a single disk, a single spindle, single set of read/write heads. In order to access data, the disk has to move heads to proper a location. They can't be at two locations at the same time, so extra file group doesn't increase performance.


Obtaining the average of three columns per row in SQL The AVG() function in SQL works particular column data. But here, we want to calculate the average of three such columns for each row. In math, we would do

AVG=(col1 + col2 + col3)/3

Similarly: is there any query to calculate AVG(col1, col2, col3...)?

If the columns aren't nullable then simply using

(col1 + col2 + col3)/3

will work fine (though on some RDBMSs you might need to have a non integer numerator or divisor to avoid integer division).

For nullable columns you might want to use something like

SELECT CASE
         WHEN COALESCE(col1, col2, col3) IS NOT NULL THEN
         ( COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) ) /
            (CASE WHEN col1 IS NULL THEN 0 ELSE 1 END +
             CASE WHEN col2 IS NULL THEN 0 ELSE 1 END +
             CASE WHEN col3 IS NULL THEN 0 ELSE 1 END)
       END

On SQL Server you could also use

SELECT *,
       (SELECT AVG(Col)
        FROM   (VALUES(Col1),
                      (Col2),
                      (Col3)) V(Col)) AS col_average
FROM   YourTable


Is there a way to define macros/alias in psql? It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries.

So, is there a way to define an aliases (autocompletable ones would be very nice) in psql similar to the way it is in bash?

E.g. FULLEXPLAIN SELECT * FROM pg_stat; results into EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM pg_stat;

\set fullexplain EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

:fullexplain SELECT * FROM pg_stat;.

Probably not the most elegant thing I admit.

Also you could put this in .psqlrc.


SQLCMD -L Returns Bizarre Response Instead of Server List I have a server with three installed SQL Server 2014 instances with Service Pack 1 (build number is 12.0.4213.0) installed. When I run SQLCMD -L on the server I am getting the bizarre response below.

  C:\Users\Tester>sqlcmd -L

  Servers:
      ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;
  *APP:AppName=?;*WSID:WorkStation ID=?;

The SQL Server is running normally and the SQL Browser service is started. Querying the SQL Browser service with another tool on the same server yields the expected results. I can run regular SQL queries with SQLCMD and get the expected results.

I was able to verify that the output above is the same on a workstation where I caused SQLCMD -L to receive no responses at all by enabling the firewall; however, the firewall is not an issue on this server. I've checked the settings and, as stated previously, my other tool gets SQL Browser Service responses to a broadcast just fine.

Also, running WHERE reports that the path to SQLCMD is C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE, which is correct per File Locations for Default and Named Instances of SQL Server.

Does anyone have any idea why SQLCMD is doing this and how to fix it?

This can happen with a computer, probably a laptop, that is not joined to the domain.


Moving data from an old table into a newly created table with different field names / number of fields I've been handed over management of a database, and I have had very little exposure to MySQL (or databases in general) so far.

I would like to do two things, both of which are (I expect) fairly simple, however it is the combination of the two which is causing me difficulty.

At present, I have one very large table containing all data. It can easily be divided into three smaller tables, as one of the fields effectively has only three distinct values, by which I wish to separate it. I assume writing a query to find entries of each value and copy only the correct one across to a new table would be very simple.

However, my problem is that the fields in this table are very, very poorly named and misspelled. So, when I create my new tables I would like to rename many of the field names. I will also potentially need to add fields.

Is there a way to achieve this so that I may create new tables with correctly named fields and then copy the data across without any intermediary steps?

The two obvious (though very clumsy) ways to do this that I can see would be to -

Copy the current table, rename all the fields to match the newly designed tables, and then copy the data across since the field names match. (This is not ideal as the current table is very large, and the issue of additional fields in the new tables may still cause issues?)
Create the new tables with the same poor field names as the original. Copy the data across. Change the new table's field names.

Both these potential methods seem very clumsy. Is there a way to do this that simply lets me create a new table with correct field names and then transfer the old data easily at any time?

(Please forgive me if this is an obvious or awkward question, I'm very, very new to this. Thank you.)

It depends of your volumetry but you can use a simple INSERT SELECT statement.

You old table:

mysql> desc old_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| fname | varchar(255) | YES  |     | NULL    |       |
| lname | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Imagine you want to replace fname by firstname, lname by lstname and add a birthdate field.

Create your new structure:

CREATE TABLE new_table (
    id int(11),
    firstname varchar(255),
    lastname varchar(255),
    birthdate DATETIME DEFAULT NULL
);

Now, transfert the old data in the new table, note that the new fields (here Birthdate) will be empty:

INSERT INTO new_table (id, firstname, lastname) SELECT id, fname, lname FROM old_table;

It's just a generic example, if you want for specific one, please add your tables structures in your question.

Max.

Edit for question "At what point would it be a good idea to do something else? ":
The INSERT SELECT statement locks (write lock) your table to enforce data consitency during operation. I can't answered to the question "at what point" it depends of your server, configs... But imagine your have a table with 1 Million rows, the operation could take some seconds, we'll say 4 seconds for example, so for 4 seconds, your users will not write in your table.


Select with inconsistent linkage I have a table with three columns (ID, ReviewToId, ReviewFromId)

I would like to get record with the ReviewToId and ReviewFromId is not Consistent

For example, with pic attached:

I would like to return result with ID 4,6 and 8 Cos

4 say it review from 2, but 2 review to 3
5 review to 6, but 6 say it review from nothing
7 review is 8, but 8 say it review from nothing

How to write the SQL query for this data result?

Using left outer join?

Next time please build an example like below.

create table dbo.Reviews
(
   Id int not null
  ,ReviewFromId int null
  ,ReviewToId int null
)
GO

insert into dbo.Reviews
(Id,ReviewFromId,ReviewToId)
values
 (1,null,2)
,(2,1,3)
,(3,2,null)
,(4,2,5)
,(5,4,6)
,(6,null,7)
,(7,6,8)
,(8,null,null)
GO

From start I can tell your problem is when two rows are reviewed from the same row (3 and 4)
so that row (2) can persist only one ReviewToId

Also you do needs two results, one for each column.

/* Wrong reviews from */
select r4.Id, r4.ReviewFromId, r2.Id, r2.ReviewToId
from dbo.Reviews r2
join dbo.Reviews r4 on r4.ReviewFromId = r2.Id
where r2.ReviewToId != r4.Id

/* Wrong reviews to */
select r5.Id,r5.ReviewToId,r6.Id,r6.ReviewFromId
from dbo.Reviews r5
join dbo.Reviews r6 on COALESCE(r5.ReviewToId,0) = r6.Id
where COALESCE(r6.ReviewFromId,0) != r5.Id

Results are:

Id          ReviewFromId Id          ReviewToId
----------- ------------ ----------- -----------
4           2            2           3

Id          ReviewToId  Id          ReviewFromId
----------- ----------- ----------- ------------
5           6           6           NULL
7           8           8           NULL


I want to change to Dynamic SQL Query instead of CASE statement I have two tables as below and description is given in below

Employee table

id EmployeeID   departmentid department1   department2 department3
1    300            100           IT          Finance
2    400            101           IT          Sales
3    500            102           Technology  Finance
4    600            101           IT                       HWD

Department table

id  EmployeeID departmentkeys Departmnetvalues
100     300      1                      IT
101     400      1                      IT
102     500      2                      Finance
103     600      1                      IT

In department table, i have departmentkeys column that contains the values 1,1,2,1 and maps to column department1,department2,department3 in the Employee table .

1 means department1
2 means department2
3 means department3

I want to fetch record based on matching records from employee and department
table.

select * from Employee e
inner join department d on e.departmentid=d.id
and Case
  When d.departmentkeys=1 then e.department1
  When d.departmentkeys=2 then e.department2
  When d.departmentkeys=3 then e.department3
End=d.departmentvalues

I do not want to to use CASE statement because its causing a performance issue. Instead of CASE statement i want to use SQL Dynamic query .

As mentioned by Dan Guzman in the comments, you can do this with UNION ALL instead of dynamic SQL:

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=1 AND e.department1 = d.departmentvalues

UNION ALL

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=2 AND e.department2 = d.departmentvalues

UNION ALL

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=3 AND e.department3 = d.departmentvalues;

Akina mentioned another option, which is to use multiple left joins to the same table.

This has the possibility of producing different results if there are any employees that don't have a department, and UNION ALL is probably a better option, performance-wise here.  But this is the general approach for the multiple join option:

select * from Employee e
left join department d1
    on e.departmentid=d1.id and d1.departmentkeys=1 and e.department1=d1.departmentvalues
left join department d2
    on e.departmentid=d2.id and d2.departmentkeys=2 and e.department2=d2.departmentvalues
left join department d3
    on e.departmentid=d3.id and d3.departmentkeys=3 and e.department3=d3.departmentvalues


Log shipping to fix unintended deletion? I am reading the book 'Pro SQL Server 2019 Administration' from Carter:

In a scenario in which a user accidentaly deletes data; he mentions that we can recover the data (from the DR server) and then "repopulate the production server".
Then he adds "This means that you do not need to restore a backup to recover the data."
What he means by 'repopulate'? and how is this better/different than a backup/restore?

Because the database at the DR site is in read_only mode, you can export the data from the DR table where the deletes occurred on the primary and 'repopulate' the primary server for just that one table using the data exported from the DR site.  This would be especially useful if the database is extremely large, because you would not have to restore the entire database to get just that one table recovered.
This, of course, assumes the log records containing the deletes have not been applied to the DR database.


How to list all table columns in sorted order? I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?

Generally, use the information_schema:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'blah'
ORDER BY column_name ASC;


Can't grant privileges to "root" when it has no password in MariaDB I am setting up a small development local server (is a VM running CentOS 7) and because of that I didn't add any password to MySQL after run the command: mysql_secure_installation. Now I need to connect from the host through MySQL Administrator but I can't because the user root isn't allowed to connect from the host IP address so I am gonna to change that by giving root all privileges and the host.

This is what I have done so far:

$ mysql -uroot -p
Enter password:  [here is just ENTER because root hasn't a password]
Server version: 10.1.18-MariaDB MariaDB Server

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

I have found that error so I do some research on Google and found a bunch of links so I pick some of them like this one and tried:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

But still not working, is a restriction and I am not aware? is a configuration on the my.cnf file? is a problem in the SQL query? Any help?

"If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist." -- https://mariadb.com/kb/en/mariadb/grant/

If that is the case, then the solution is probably:

CREATE USER 'root'@'192.168.3.%';
GRANT ... (as you tried)

"Can't find any matching row..." means that there was no row with both user='root' and host='192.168.3.%'.  You were coming in with user='root' and host='localhost' (or something like that).


Allow public use of functions without giving them database control I came up with a set of functions that I would like to be accessed from a variety of places.  SQL seemed like the best place for this due to accessibility, but I receive this error when trying to use them with a user with only the db_datawriter and db_datareader permissions: "The EXECUTE permission was denied on the object"... etc etc

I want to pass a date and return other dates.  Apparently I can't just

SELECT [dbo].[fn_Acct_Day_One] ('1/3/2015')

Any way I can do this without granting db_owner to the user?

You need to grant rights to use the function.

GRANT EXECUTE ON Function to UserOrRole


how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time how do I change the query below so it only reads and displays 132 characters each line

with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
         substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
  from foo
  union all
  select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
         substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;

Thanks

This should work:

with w(id, line#, line, rest) as (
  select id, 1, SUBSTR(regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 1, 132),
         substr(to_char(bar), LEAST(132, regexp_instr(bar, '$', 1, 1, 1, 'm'))+1)
  from foo
  union all
  select id, line#+1, SUBSTR(regexp_substr(rest, '^.*?$', 1, 1, 'm'), 1, 132),
         substr(rest, LEAST(132, regexp_instr(rest, '$', 1, 1, 1, 'm')+1))
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, ROW_NUMBER() OVER (ORDER BY line#) AS line#, line
from w
where TRIM(line) <> ''
order by id, line#;

What it does is:

line: truncate (SUBSTR(..., 1, 132)) the first full line to the first 132 characters (which does nothing for lines shorter than 132)
rest: grab the rest of the CLOB of either the first full line, or whatever comes after 132 characters, whichever starts first (substr(rest, LEAST(132, ...)))

Response to the sequence question in comments:

To create a sequence over a partition, your best bet is self-join with a grouped-and-sorted:

SELECT t.MO_ID, g.DOC_NO, ...
FROM <your table> t
JOIN (
  SELECT DISTINCT ROWNUM AS DOC_NO, MO_ID
  FROM <your table>
  WHERE <same as in outer query>
  ORDER BY MO_ID
  ) g ON (g.MO_ID = t.MO_ID)
WHERE ...

or with a CTE + ROW_NUMBER() window function:

WITH
  t AS (
    SELECT *
    FROM <your table>
    WHERE ...
    ),
  g AS (
    SELECT MO_ID, ROW_NUMBER() OVER (ORDER BY MO_ID) AS DOC_NO
    FROM (SELECT DISTINCT MO_ID FROM t) x
    )
SELECT t.MO_ID, g.DOC_NO, ...
FROM t
JOIN g ON (g.MO_ID = t.MO_ID)


String templating technique I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to deliver them to the User FaceBook style.

The problem is that I am going to have a whole class of notifications eg. "Spongebob you have not taken a class in 2 weeks, stop slacking", and "You are xyz% away from reaching your goal weight, don't stop now!" etc etc.

I was thinking of a structure like this:

**Notification**
--Id
--Message

**UserNotification**
--Id
--UserId
--NotificationId
--ProcessedMessage
--Seen?

My question is, so I will have placeholders in the Notification message eg. "{{name}} you have reached your goal weight! Congratulations", but these placeholders will be different depending on the message.

I was thinking of "compiling" (via string replace) the messages with the MySQL event when I have all the relevant data on hand e.g. I will create an event for getting users who have not done any exercise in 3 weeks and so on, and then saving the value in UserNotification. This lets me update the message with time, whilst keeping history intact, but I lose the ability to fix spelling errors etc. in one place only and having it automatically propagating.

If I don't compile the message in the MySQL event, then I will have to do a custom columns solution in UserNotification so I can generate the message later.

Is there a better way to doing this?

Custom columns {{shudder}}.

There's not One True Way® that comes to mind, but what about one or more rows in the new UserNotificationMetadata table, which has a MetaKey VARCHAR(x) not null (with a small but practical "x")  and primary key of (NotificationId,MetaKey) and a MetaValue TINYTEXT not null (or a comparably suitable data type)?

This is one of the relatively few cases where a sort of EAV-style structure is arguably not a case of urDoinItWrong™ since the data, here, isn't part of your core schema and there's (again, arguably) not a need to be able to impose constraints on the data... a number or date stored here is just a string of characters that's going into a message.

For each (yes, that is also a little bit procedural-sounding) row matching the UserNotification, take CONCAT('{{',MetaKey,'}}') and use REPLACE() to substitute it with MetaValue in the message.

I would also suggest that 'seen' should be a timestamp, rather than a boolean.


What are the conditions for a functional dependency to exist? Let be a relation R of scheme: R(A,B,C,D,E) and r an instance of the relation. What functional dependencies verify r?

Observing the functional dependencies that r verify are  doesn't show that they exists on R and are verified by all possible instances of R.

The definition of a functional dependency is:

I said that there is:

A→E, C→A, B→E, C→D, C→E, D→E, C → A, C→B

AB→D, AB→E, AC→B, AD→D, AD→E, BC→A, BC→D, BC→E, BD→ A, BD→C, BD→E, DE→A, DE→ C

ABC→D, ABC→ E, ABD→E, ABE→D

But I don't know if they do exist, I don't know what to do to verify if they actually exists

Community Wiki answer generated from comments on the question by Renzo and ypercubeᵀᴹ

The definition of functional dependencies requires that they must hold for every possible instance.

From an instance, you cannot find the functional dependencies that hold in a certain relation schema.

You also missed A → D.


DB2 - What is the difference between online backup and AUTO_DB_BACKUP? We would like to perform online backup.
I see that there is one parameter AUTO_DB_BACKUP.
I read this article on IBM link for AUTO_DB_BACKUP  http://www.ibm.com/developerworks/data/library/techarticle/dm-0801ganesan/

but still I cannot see the purpose of this AUTO_DB_BACKUP when online backup is ON.

Thank you

AUTO_DB_BACKUP means DB2 will take a backup based on when it feels it should do so. The things that influence the taking of a backup are:

Never completed a full backup
Time elapsed since the last full backup was a specified number of hours
Transaction log space consumed (with archival logging enabled), since the last full backup is a specified size.

In order to have AUTO_DB_BACKUP working, you also need to have AUTO_MAINT turned ON as well. I would also wager a guess that in order to enable the scheduling part, you may need to have the DBM CFG HEALTH_MON turned to ON as well.

Most DB2 DBAs I talk to turn off HEALTH_MON and AUTO_MAINT because of the performance issues encountered. DB2 may choose to exercise maintenance and backups when it is least convenient for you. So I would recommend against using them, unless you are using BLU under DB2 10.5. Then it sounds like AUTO_MAINT may be more important again.

Anyway, an online backup is just a backup while users are still engaging the database and/or the database is activated. You can take online or offline backups regardless of whether or not you have AUTO_MAINT and AUTO_DB_BACKUP enabled. You probably want online backups enabled if you wish to use AUTO_DB_BACKUP in case DB2 decides to take a backup in the middle of the day. Otherwise it could either fail to backup, or kick users off the system. I'm not sure which as I've never tried. Like I said, I steer clear of HEALTH_MON and AUTO_MAINT.

This way I can either take scripted backups when I want them scheduled via a job scheduler. Or I can take an on-demand online or offline depending on the situation.


MySQL: Does the order of the ON condition of a LEFT JOIN matter? I have two tables, employees and unionMembers. The people in unionMembers are a small subset of the people in employees. I want to do a SELECT query using a LEFT JOIN like so:

SELECT    wage, unionDues
FROM      employees
LEFT JOIN unionMembers ON employees.ID = unionMembers.ID
WHERE     employees.ID = 'ID001';

Does it matter if I do ON employees.ID = unionMembers.ID or reverse the order and do ON unionMembers.ID = employees.ID?

Let's say I have 100 employees and 5 of them are union members. When doing the LEFT JOIN, ideally it would have to do 5 comparisons instead of a 100. Would the order of the equality test make any difference?

SQL is a declarative language; that is, it does not specify how something should be done, only what the result should be.

The computing languages I have used all respect the properties of equality, so employees.ID = unionMembers.ID is equivalent to unionMembers.ID = employees.ID

In this case, rather than an outer join, which can generate a large data-set, you would probably want to use a sub-query or the EXISTS operator, depending on your data structure. Something like:

SELECT   ID,
          wage,
          unionDues
FROM      employees
WHERE     ID in (
    SELECT    ID
    FROM      unionMembers
    WHERE     ID = 'ID001'
    );

There is an in-depth discussion here: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html

HTH


System table missing in SQL Server 2012 I restored a SQL Server 2000 database to 2005 then to 2012. I am not able to see system tables that existed in SQL Server 2000 now.

Why?

You should be using the catalog views instead of the old system tables (e.g. sys.objects instead of sys.sysobjects, sys.columns instead of sys.syscolumns). The system tables still exist, but they are there for backward compatibility only. Some of them have been moved to the hidden system database, mssqlsystemresource, and/or are only visible when connecting with the dedicated administrator connection. Of course you will need certain permissions to view the new catalog views.

Please read the notes on the old docs, e.g. for sys.syscolumns:

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Here is a link to the mapping document they mention, but don't link. This should show you how to start making the transition from, say, syscolumns to sys.columns.

Stop using the old system tables. Learn the new metadata; it's here to stay.


mysqldump vs mysqlbackup I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes with the MySQL Enterprise Backup is very fast. Any other alternatives or ideas to speed up the dump/load time?

Dump based backups (logical backups) and recommended for small-medium databases, however, you could speed up logical backup by using enhanced logical backup tools like mysqlpump or mydumper.

Raw backups are recommended for large databases and there are two major tools to explore: mysqlbackup (Enterprise backup) and Percona xtrabackup (free - only Linux based environments).


Clustered Index and Primary Key on table with unique identifier and date I have table with Orders with unique incrementing ID and (order creation) Date. This table is quite large and wide (5mn rows, 50 columns, 10 columns of which are FK IDs). Example:

CREATE TABLE Orders As (
    ID int UNIQUE AUTOINCREMENT (1,1),
    DATE datetime,  --This is SQL Server 2005, so no [date] data type
    OrderStatus char (2),
    ClientID int
    ...
)

I get this table as copy of yesterday's production table. Table (as whole DB) is used for reporting purposes and therefore read-only.

Part 1

I have 2 very common use cases:

80% (or more) of queries have DATE column in WHERE clause, as users want data for specific business date.

Here I want to create clustered index on DATE.

40%-60% of queries use OrderID to JOIN Orders table to other tables with information about Order details (Product, Supplpier, Payments, Reservations, etc).

Here I want to create clustered index on OrderID.

Part 2

Can I have one CI for both cases?
Index (ID, DATE)  will not work with WHERE clause.
Index (DATE,ID) will not work for joins on ID only or ID in WHERE

Catch. We know that DATE is incremental as well as ID. ID with higher value cannot appear yesterday under any circumstances as it is AUTOINCREMENT.

Question. Is there a way to tell SQL Server that CI (DATE, ID) will have ID sequentially ordered for all dates?

My only solution at the moment is crating non clustered covering index (ID, DATE), but it's suboptimal.

I've searched for some time, but could not find anything. If there is a solutions for later versions of SQL Server I would be interested in it too.

Update

I know clustered index basics. Please note, database is put in read-only state for users.

Logically you can just ignore Date column in (Date, ID) index without any harm at all. Possibly this is a very specific use case that is not yet covered by SQL Server functionality.

The main characteristic of the clustered index is that the data is all in that order.  That allows SQL to "read ahead".  So for example if you created your CI on the Date column and ran queries pulling summary info for a week or a month then SQL could pull the data more quickly.

If on the other hand you are having to do seeks (a single Id for example) then the CI is no different than a covering NCI.  Note the covering part of that.

Here is something you could try.  Place your CI on the Date, OrderId combination.  Do this because you are more likely to pull range data on a date than an Id.  Also because you said that 80% of your queries use the date.  Some portion of these will presumably also use the OrderId.

Then see what columns the queries that just use OrderId use and add those columns to an OrderId NCI using the INCLUDE clause.  And just to save time if the answer is "they are using all of the columns on these queries" then a) you need to look at those queries and see if they really need all that data and b) yes you could INCLUDE all of the columns in the table.

I realize that would be a HUGE INCLUDE.  In fact it would double the size of your table and you'll want to carefully test that you aren't going to have a serious negative impact on write operations.  However for reads it should work just fine.


Can COALESCE be used with PERFORM/EXECUTE in PostgreSQL? I have the following usage of COALESCE, which works as expected:
[...]coalesce
(
    status_avail_direct.status,
    (
        SELECT  status
        FROM    status_avail_direct

        WHERE   (status_avail_direct.meter_id    = oms_rec.meter) AND
                (status_avail_direct.clt_rec_at <= clt_rec.captured_at)

        ORDER BY    status_avail_direct.clt_rec_at DESC
        LIMIT       1
    ),
    (SELECT -1)
)

To make it better readable, I would like to extract and document the embedded SELECT into some outer statement/query or alike. The important thing is that I need values of the currently processed rows for that SELECT to work, e.g. oms_rec.meter and clt_rec.captured_at. From my understanding, because of that a CTE doesn't work, but a prepared statement should exactly fit my needs. So I changed the SQL to the following:
PREPARE status_calc_by_time(timestamp with time zone, int) AS
    SELECT  status
    FROM    status_avail_direct

    WHERE   (status_avail_direct.meter_id    = $2) AND
            (status_avail_direct.clt_rec_at <= $1)

    ORDER BY    status_avail_direct.clt_rec_at DESC
    LIMIT       1
;

[...]coalesce
(
    status_avail_direct.status,
    (EXECUTE status_calc_by_time(clt_rec.captured_at, oms_rec.meter)),
    (SELECT -1)
)

But that doesn't work, a syntax error is recognized instead:

[Code: 0, SQL State: 42601]  FEHLER: Syntaxfehler bei »status_calc_by_time« Position: 263  [Script position: 1510 - 1550]

From the docs I had the feeling that wherever I can use a query directly, I might be able to use a prepared statement as well:

statement
Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

https://www.postgresql.org/docs/9.2/sql-prepare.html

Outputs
The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.

https://www.postgresql.org/docs/9.2/sql-execute.html
Is there something wrong with my syntax I don't see or does executing a prepared statement like that simply doesn't work at all in this context? In the latter case, where can I use EXECUTE most likely/only?

EXECUTE is a PL/pgSQL statement, not an SQL statement, so you cannot use it as subquery inside an SQL statement.
You could write a function status_calc_by_time, then your code would work if you simply remove the EXECUTE.
If you write the function in LANGUAGE sql and make it VOLATILE, it might get inlined by the database, so you have no performance loss at all.


Single table of all chat conversations or separate tables? I'm planning to make a chat application. The problem is that there can be billions of records, since there will be lots of chat messages.
I have come up with the idea to make separate tables for each conversation, and that would make millions of tables. Or should I add them to a single table? Which makes tens of billions of records in a single table?
I'm also thinking about distributing each conversation between different servers.
Which of these would be an appropriate solution? Please feel free to guide if I'm doing this wrong! I can consider different database methodologies as well (like NoSQL).

If this is an actual service that you would like to create and not just an academic exercise, then it would be best to start with some realistic expectations and grow from there. Many years ago I implemented something like this at the day job where employees could communicate with one another in a Microsoft Teams-like fashion (though before Teams was a thing). The system is still being used today and sees anywhere between 5 and 25 messages per minute between the hours of 7:30am and 2:45am local time. It is used by about 18,500 employees scattered across the globe.
Current statistics (as of 00:00:00 UTC today):

Item
Number
Note

Posts
35,641,804
As per HR and Legal requirements, no records are deleted, but they are instead "soft deleted".

Files
2,613,017
Same as above

Channels
74,331

Accounts
24,459
6,000+ idle/expired accounts

Each one of these items has its own table, and there are additional tables that enable additional functionality, such as ChannelMember, which specifies which Accounts are members of a Channel and what level of permission they have (Read-Only / Read-Write).
Posts are saved in Markdown format and have no realistic size limit. People are free to use the space as they see fit and, so long as nobody reports an Account or Channel as being inappropriate, no communications are monitored or shared with management.
Search is handled by splitting posts apart on a word-by-word basis and storing unique words in a PostSearch table, which is then used for somewhat faster lookups. Because the words are split apart, search results can be "weighed" for their relevance before being returned to the requester.
This runs on a MySQL database with a PHP-based API handling all communication between the front-end applications and the database.
Now the fun stuff:

Action
Average Response Time

Loading Channels a Person Belongs To
0.18 seconds

Loading a Timeline view of recent Posts
0.21 seconds

Loading the most recent 250 Posts in a Channel
0.13 seconds

Publishing a new Post
0.51 seconds

Searching for Posts with 3 terms
1.33 seconds

Searching for Posts with 5+ terms
1.61 seconds

These are API response times, meaning requests are first authenticated, processed, then returned in a JSON format.
The database server is running MySQL 8.0.23 and Ubuntu 20.04 LTS on a db.m6g.xlarge instance on AWS. There are three web servers, all running Apache and Ubuntu 20.04 LTS on t4g.medium instances.
Unless you have tens of millions for marketing a hot new service that will supplant the existing big players, you may not need to over-think the structure just yet. Build something specific and see whether it catches on or not. If it does, then you can look at getting it to scale.


what are the prerequisite for installing oracle 11gr2 I have windows 2008 R2 sever and I want install oracle 11gr2 on this server. So any one tell me what are the prerequisite tools for install oracle 11gr2 like as a jdk and ect...

Check this out:
[https://docs.oracle.com/cd/E11882_01/install.112/e47798/reqs.htm#NTDBI002][1]
[http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm][2]
Probably you will get answers for such questions quickly if you google them.


How does MariaDB handle joins on ENUMs? I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)?

For the follow-up see also, How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Two tables can't have the same ENUMs. MySQL assumes all ENUM keys are globally unique..

CREATE TABLE foo ( a ENUM ('A', 'B', 'C'), x int );
CREATE TABLE bar ( a ENUM ('A', 'C', 'D'), y int );
INSERT INTO foo ( a, x ) VALUES ('A',1), ('B',2), ('C',3);
INSERT INTO bar ( a, y ) VALUES ('A',4), ('C',5), ('D',6);

SELECT * FROM foo JOIN bar USING(a);
+------+------+------+
| a    | x    | y    |
+------+------+------+
| A    |    1 |    4 |
| C    |    3 |    5 |
+------+------+------+

So in this context, the value of the ENUM is the string value of the key. PostgreSQL will return the same result, but because it has actual ENUM types the comparisons in the join are interger and it doesn't have to resolve the ENUM to a string,

CREATE TYPE myType AS ENUM ('A', 'B', 'C', 'D');
CREATE TABLE foo ( a myType, x int );
CREATE TABLE bar ( a myType, y int );
INSERT INTO foo ( a, x ) VALUES ('A',1), ('B',2), ('C',3);
INSERT INTO bar ( a, y ) VALUES ('A',4), ('C',5), ('D',6);

SELECT * FROM foo JOIN bar USING (a);
 a | x | y
---+---+---
 A | 1 | 4
 C | 3 | 5

If foo and bar were different ENUM types as MySQL requires in it's enum "type", you'd have the same problem but PostgreSQL wouldn't allow the join at all, because they're different types. PostgreSQL also won't allow one enum type to reference a different discrete enum type type without manually providing a method to go from one enum to another.


Ensure correct username when using pg_restore I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this:

sudo -u postgres psql -c "create user app_user with password 'pass';"
sudo -u postgres psql -c "create database app_production owner app_user;"

Now, when I try to restore the the dump I use the following command:

pg_restore --verbose --schema=public --no-acl --no-owner --jobs=8 --exit-on-error --username=app_user --dbname=app_production /tmp/app_production.dump

Now in psql with \l to see ownerships I get the following:

                                         List of databases
          Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------------+-----------+----------+-------------+-------------+-----------------------
 app_production   | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres         | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0        | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |           |          |             |             | postgres=CTc/postgres
 template1        | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |           |          |             |             | postgres=CTc/postgres

As you can see, the ownership of app_production database has now turned from app_user to postgres. I would have expected the owner of the app_production database to still be app_user, so what am I doing wrong?

BTW, The --schema=public was added, because I was getting a weird error:

  "Could not execute query: ERROR:  must be owner of extension plpgsql"

Another thing is, that the owner of the dump is the user that the database was having on heroku, which would be something like 'jebf473b73bv73v749b7'

If you want to create the plpgsql extension I believe you have to be Superuser.  My recommendation is to make app_user temporarily a superuser and then revert this.  Superusers also are treated as owners of everything.

ALTER USER app_user WITH SUPERUSER;

run the restore, then when you are done

ALTER USER app_user WITH NOSUPERUSER;

This should avoid any other permissions issues but still leave you with the permissions you want.


Upgrading 5.6 to 8.0 without losing any data I am using windows server. As per need of project I need to upgrade MySQL from 5.6 to 8.0 but I already have the existing databases and tables. How should I upgrade without losing any data in windows?

backup your data to sql dump file and upgrade by incrementing major version. 5.6 to 6 to 7 to 8.


How to find the table name from the pg_subscription_rel.srrelid (postgres logical replication) I have postgresql logical replication setup between two servers and it seems to be working for the most part, except for one row when running SELECT * FROM pg_stat_subscription; never changes from srsubstate = d.
Is it possible to find the table that this row is referring to using the srrelid?
Or any other way to debug why this is the case?

my_db=# SELECT * FROM pg_subscription_rel;
 srsubid | srrelid | srsubstate |   srsublsn
---------+---------+------------+--------------
  209358 |  208195 | r          | D83/BED60B88
<< clipped data >>
  209358 |  208164 | r          | D83/BEC66E90
  209358 |  207290 | d          |
  209358 |  208436 | r          | D83/BED60AA8
<< clipped data >>
  209358 |  208328 | r          | D83/BED57BF8
(74 rows)

my_db=# SELECT * FROM pg_stat_subscription;
 subid  |  subname  |  pid  | relid | received_lsn |      last_msg_send_time      |     last_msg_receipt_time     | latest_end_lsn |       latest_end_time
--------+-----------+-------+-------+--------------+------------------------------+-------------------------------+----------------+------------------------------
 209358 | s_upgrade | 26880 |       | DA9/C31E5560 | 2020-11-16 16:53:35.66203+11 | 2020-11-16 16:53:35.669512+11 | DA9/C31E5560   | 2020-11-16 16:53:35.66203+11

You can join it to pg_class
SELECT s.subname AS subscription_name,
       c.relnamespace::regnamespace::text as table_schema,
       c.relname as table_name,
       rel.srsublsn,
       case rel.srsubstate
         when 'i' then 'initialized'
         when 'd' then 'copying'
         when 's' then 'synchronized'
         when 'r' then 'ready'
       end as state
FROM pg_catalog.pg_subscription s
  JOIN pg_catalog.pg_subscription_rel rel ON rel.srsubid = s.oid
  JOIN pg_catalog.pg_class c on c.oid = rel.srrelid


Query fails sometimes on casting error I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds
The query is generated by hive metadata service, and I can't modify it.
This is a simplified version of the query:
select
    "TBLS"."TBL_ID",
    "FILTER0"."PART_ID",
    "TBLS"."TBL_NAME",
    "FILTER0"."PART_KEY_VAL"
from
    "PARTITIONS"
inner join "TBLS" on
    "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
    and "TBLS"."TBL_NAME" = 'test_table_int'
inner join "PARTITION_KEY_VALS" "FILTER0" on
    "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
where
    cast("FILTER0"."PART_KEY_VAL" as decimal(21, 0)) = 1

When I spin up a new database, and populate the relevant tables, this is how the whole data looks like (querying without any filters):

and running the query above will return a single row (the one with PART_KEY_VAL = 1)
the problem starts after I run some automated tests that write to those tables. I couldn't find any pattern, I just run a few complicate tests that write to those tables
Now if I populate those tables again, the data looks similar:

but running the query above will result in:

SQL Error [22P02]: ERROR: invalid input syntax for type numeric: "c"

for some reason, the value "c" is being cast to decimal and it fails, even though the same query on the same data was working earlier
what could be the reason for this behavior?

for reference, here is where the query is generated, but I simplified it a bit above: https://github.com/apache/hive/blob/rel/release-3.1.2/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1289-L1339

the problem is that for some reason postgres stops using the index on the column "TBL_NAME".

when the index is used, the table is filtered first, and the remaining values are all ints.
but when the index is not used, the cast will happen on all the rows, and will fail on the strings.

as a workaround, I set enable_seqscan = false, and that forces postgres to use the index, but that is not a long term solution.

I opened a bug on hive with a more detailed explanation of the problem:
https://issues.apache.org/jira/browse/HIVE-23576


Can't run Alter Table command due to locks I'm unable to drop, delete or alter a database in my Local SQL Server instance.

I have run sp_Who2 and there don't appear to be any processes using the database apart from a process which gets spawned by my issuing the Alter Database command (I do this against the Master db). This process has a Wait type of LCK_M_X and a value of -2 for the Blocked By attribute.

I have issued KILL commands against them and I've restarted all the related system services and my machine a few times but to no avail. Is there anything else I can try?

Stop (do your alter) and start DTC and all will be fine.


MySQL improve query syntax I have this simple MySQL query:

SELECT * FROM table1
INNER JOIN table2 ON table2.col = table1.id
WHERE table1.id = '123'

id is the primary key in table1.

table2 has composite primary key of two columns: col and col_two.

Now I ran EXPLAIN on the above query to see how it performs and got the following rows:

id select_type table   type  possible_keys  key     key_len  ref    rows Extra
1  SIMPLE      table1  const PRIMARY        PRIMARY 8        const  1
1  SIMPLE      table2  ref   PRIMARY        PRIMARY 8        const  1    Using index

From my understanding, ref would be used if the join matches multiple rows but I'm using WHERE with a constant value. So, shouldn't the join have a const type like the table1 query?

I'm worried that this would cause performance issues on a large scale. Is there a way to improve the above query or it's already good enough?

The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id.

For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you absolutely need to return all columns.

If you have any examples of more complex iterations of the query, then there might be more than can be said.


Difference between Seek Predicate and Predicate I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise.

I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek Predicate and also a Predicate

What's the difference between Seek Predicate and Predicate?

Note: I can see that there are plenty of problems with this node (e.g. the Estimated vs Actual rows, the residual IO), but the question does not relate to any of that.

Let's throw one million rows into a temp table along with a few columns:

CREATE TABLE #174860 (
PK INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (PK)
);

INSERT INTO #174860 WITH (TABLOCK)
SELECT RN
, RN % 1000
, RN % 10000
FROM
(
    SELECT TOP 1000000 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN
    FROM   master..spt_values v1,
           master..spt_values v2
) t;

CREATE INDEX IX_174860_IX ON #174860 (COL1) INCLUDE (COL2);

Here I have a clustered index (by default) on the PK column. There's a nonclustered index on COL1 that has a key column of COL1 and includes COL2.

Consider the following query:

SELECT *
FROM #174860
WHERE PK >= 15000 AND PK < 15005
AND COL2 = 5000;

Here I'm not using BETWEEN because Aaron Bertrand is hanging around this question.

How should SQL Server optimizer that query? Well, I know that the filter on PK will reduce the result set to five rows. SQL server can use the clustered index to jump to those five rows instead of reading through all million rows in the table. However, the clustered index only has the PK column as a key column. Once the row is read into memory we need to apply the filter on COL2. Here, PK is a seek predicate and COL2 is a predicate.

SQL server finds five rows using the seek predicate and further reduces those five rows to one row with the normal predicate.

If I define the clustered index differently:

CREATE TABLE #174860 (
PK INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (COL2, PK)
);

And run the same query I get different results:

In this case, SQL Server can seek using both columns in the WHERE clause. Exactly one row is read from the table using the key columns.

For one more example consider this query:

SELECT *
FROM #174860
WHERE COL1 = 500
AND COL2 = 3545;

The IX_174860_IX index is a covering index because it contains all of the columns needed for the query. However, only COL1 is a key column. SQL Server can seek with that column to find the 1000 rows with a matching COL1 value. It can further filter down those rows on the COL2 column to reduce the final result set to 0 rows.


Access SQL Server from a different subnet I'm creating a program which accesses MS SQL Server 2008, the problem is that I can run the program smoothly as an administrator account, even on different subnet computers and using terminal service (mstsc).  But the program can not successfully access SQL Server if using another account, even though I've already included the account into SQL permissions, Terminal Service configuration, add the Administrators user group.

On SQL Server Network Configuration - Protocol - TCP/IP is Enabled.

What am I forgetting to do?

If this is installed on a windows 2008 server then you will need to check that the firewall is either disabled (not recommened) or that there is a firewall rule for the SQL port (1433 for default instance, 1434 and whatever is configured for a named instance).


Best way for processing over 150 million rows MySql I wrote a function on nodejs to SELECT 150 million records, process the data and UPDATE those same 150 million records using
UPDATE 'table' SET value1='somevalue' WHERE idRo2 = 1;

Single Update for each row concatenate send one single query string allowing multipleStatements querys on the db connection.
I've being encountering multiple errors as

Error: Connection lost: The server closed the connection.
JavaScript heap out of memory.
RangeError: Invalid string length.
Killed process.

I think i might not be using the right technologies or programing technique
*Edit:
The data process i need to do is to take the 'wholeApiResponse' column value, parse that string and then insert the parsed values into new added columns (askPrice, lastPrice, lowPrice, highPrice, volumePrice, platformTimestamp), this end up modifying the existing row by adding new values from an existing string.

Big Updates (and Deletes) are problematic.
Plan A:  Avoid the big update.  If value1 should always be 'somevalue' when idRo2 = 1, then don't store it in the table; store it elsewhere and use a JOIN.  Then, instead of checking 150M rows, you are changing exactly 1 row.
Plan B:  Do the Update in chunks of 1K rows at a time.  This avoids timeouts, and a number of other potential problems.  Details:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .  Even 10K can be problematic, hence my recommendation of 1K.  Anyway, going beyond 1K is getting into "diminishing returns".
Plan C:  Tells us what value1 and idRo2 are really called.  That might lead to some specific suggestions.
Minor issues with datatypes.  Shrinking the table size will help performance some.

INT SIGNED has a limit of 2 billion.  150M is getting kinda close to it.  Keep your eye on any AUTO_INCREMENTs.
Similarly, INT for idCurrencyPair may be wasting space.  (INT takes 4 bytes; there are smaller datatypes)
DOUBLE can lead to rounding errors.  And it take 8 bytes.  Consider DECIMAL(...).
platformTimestamp DOUBLE -- Huh?
I'll bet that LONGTEXT is rarely used?  We should discuss better ways to store it.


Not authorized on admin to execute command mongoDB: Atlas M0 Free Tier cluster My MongoDB shell has version v3.6.5 (the same with older versions) & MongoDB server has version: 3.6.8
I have MongoDB M0 Free Tier cluster and I want to use from shell different commands.
So, I created admin user with password:

Set up IP Whitelist:

And then connect via MongoDB shell using admin user:

Up to this point, everything is fine, but when I started to use commands I get always the issue:

I checked the answer from here and tried to create admin user directly from mongodb shell using command, but also the same error:

Why it happens? If I has already created admin user? Can someone explain me, thanks a lot in advance.

Since you are using a hosted database service (MongoDB Atlas), many administrative commands are limited as they are either provided via the management interface or likely to introduce performance/stability issues.

The Atlas free tier has additional limitations since it is backed by a shared MongoDB instance. See: Unsupported Commands in M0/M2/M5 Clusters.

  "not authorized on admin to execute command {setFeatureCompatibilityVersion: \"3.4\" }"

The setFeatureCompatibilityVersion command is an administrative command used during upgrades. You cannot set this on Atlas Free Tier (M0) or shared (M2/M5) clusters.

  db.createUser(...)

User permissions and roles for MongoDB Atlas must be managed via the Atlas user interface or API. See: Add MongoDB Users.


How to recover previous values in Mysql Table? I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause:

update url_queue set is_disabled=1

while wanted to run

update url_queue set is_disabled=1 where is_disabled=3;

I havent told my boss yet. And if somehow I can undo this before the meeting I would be able to save my job.

Please help!

unless you have a delayed replica, the only way to recover your old values is to restore the table from a backup.  sorry.


mysql/aurora not immediately seeing results of committed transactions In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The problem we're running into is when two threads are both trying to insert 'abc' into the table at almost the same time.

As expected, if thread1 & thread2 start at the same time, thread2 gets stuck waiting on the "select...for update" statement at the start. Once thread1 commits its transaction, the lock is released and thread2 continues executing.

The problem is that thread2's next line selecting from tbl_foo returns no result causing it to execute the wrong block of code after, despite it just having been inserted. In fact, the later insert by thread2 will fail due to a unique constraint violation. Is this a known problem with mysql and/or aurora read caching? Is there some configuration setting that will change it?

<start transaction>

SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;

SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

if (found row in tbl_foo)
    do stuff
else
    do different stuff including inserting (123, 'abc') into tbl_foo

<commit transaction>

SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;
SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

You are missing FOR UPDATE on the second one?

Can you combine both SELECTs with a JOIN?  (This would help avoid a deadlock.)


PostgreSQL: Change log file location How can I change the location of the log file in PostgreSQL? Can it be done without stopping the cluster?

re 1) See the chapter "Where to log" in the manual.

http://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

You want to change the log_directory property.

re 2) No, you need to restart Postgres when you change this.


Decorating an existing database table with information that no one else cares about We have a table that many processes read from. Some of them want to keep track of the fact they have processed this data, whereas others simply rely on fetching the data from the table based on user actions.

I think ideally, the consumers would interface with this data indirectly: probably via a web service, and then they could just keep track of what resources (URIs) they have discovered and processed, all internally to themselves.

The other options seem to be either adding a column to the existing table, or adding a new table - either way, one for each consumer. I think adding columns is easier and probably performs the best, but adding a new table keeps the clutter away from the original table.

Aside from those points, are there any others that I should be aware of?

Edit:

To elaborate..

I have a table foo that is populated by an external process, and read:

By a web-service that simply exposes data depending on criteria (show me the latest... show me the one for this particular date, etc)
By a post-processor, that looks for foo records that it has not seen before, processes them (which writes data somewhere else, unrelated to the web service) and then wants to mark them as processed.

In order to mark them as processed, we can either have a column on the foo table (processed = 1), or we can have a foo_processed table (that just has the ids of the foo that have been processed, and use an outer join to find unprocessed foo records)

At this time there is only one post-processor, but I can't guarantee that.

Your instincts about indirect access are correct. Adding columns for clients to the table flies in the face of this and is a generally bad idea. From a mechanical point of view, you are taking on lots of DDL changes to a key table -- how much do you want to touch it? Will all the tracking map to something you can keep in a single row? Will someone stuffing data into this decide to dump a big nasty serialized XML object in there destroying performance? What happens when two of the apps want a field called "report_status"?

The correct course of action is to push these folks to separate tables or other data stores for their tracking. Then they can track what they need to track without interfering with the core data or with anyone else.


Moving FILESTREAM to another database Our development organization has created an application employing FILESTREAM, which through the pilot has been incorporated into a schema within our Data Warehouse Staging database.  Going to production, the development and BI teams have determined that they want it separated out into a separate database, and they'd like to separate it in the current pilot environment (DEV).

How can I best move (or at least copy) the existing FILESTREAM data from the current database into a new one?

To clarify: I'm not looking to move the database, but to pull the FILESTREAM (and the entire schema) out of one database and put it into another.

The easiest method would be to move it by restoring the database, and then just clean out the filestream table in the original.

If you really need to move it, then it would be treated like any other situation where you want to move one table to another database. The additional steps involved would be repeating how you setup filestream on the original database.


SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B and C. In the case that Site A data center goes down, looses connectivity, etc. the users of Site A users will fail over to Site B or C (depending which is up). Also, if a user from Site A travels to Site C they should be able to access their data as it was on Site A.

My questions is what SQL replication technology (SQL Replication or 3rd party) can support this scenario? We are using SQL 2008 R2 Enterprise at each site, each site runs on top of VMWare with a Netapp filer. Would something like distributed caching help in this scenario as well?

We have looked at and tested Peer-to-Peer replication but have encountered issues with conflicts during our testing. I imagine there are other global data centers that have encountered and solved this issue.

What you are describing would be either peer to peer replication, or merge replication.  Peer to peer is masterless, while merge replication has a master, however you can use mirroring on the publisher and mirror the publisher to another data center so that if the publisher goes offline the workload can fail over to another site.


oracle to PostgreSQL database conversion In oracle:

select owner, constraint_name, table_name, column_name, position
from dba_cons_columns;

Is there any table like dba_cons_columns in PostgreSQL database that we get the same information?

PostgreSQL has the SQL_standard information_schema which is actually a bunch of views backed by the pg_catalog schema.

information_schema has a fixed structure as mandated by the standard, pg_catalog is liable to change between major PostgreSQL versions.

For the information you want it's probably the table information_schema.constraint_table_usage.


Correlated Subqueries: resolving ambiguous names Suppose I have 2 tables which share some column names. In this case the primary key is both is called id.

CREATE TABLE artists(
    id int primary key,
    name text,
--  …,

);
CREATE TABLE paintings(
    id int primary key,
    artistid references artists(id),
    title text,
--  …,
);

Note: I know there are arguments against calling your primary key a generic name like id, but let’s suppose, for argument’s sake that it’s out of my control. In any case, it could have been any other column for the purpose of this question.

Suppose I now have a carelessly written SELECT statement which seeks to extract data from the referenced table using a correlated subquery:

SELECT
    id, title,
    (SELECT name FROM artists WHERE artistid=id) as artist
FROM paintings;

Clearly the inner WHERE clause could have been better written as WHERE paintings.artistid=artists.id. However I have got away with it and it works. It even works if I write the WHERE clause as id=artistid.

I know it’s not the best way to go about it, but I’m more surprised that it has understood my intention, which is not what we have come to expect from SQL.

The question is: How does SQL interpret ambiguous columns in a Correlated Subquery?

It resolves the column names in scope order. Inside the sub query columns will be resolved against those from the artists table if there is such ambiguity.

This can sometimes be quite useful though it can also be quite dangerous.

I usually use table aliases and two part names to avoid any unexpected issues and make it clearer to the reader which table each column is from.

SELECT p.id,
       p.title,
       (SELECT a.name
        FROM   artists a
        WHERE  p.artistid = a.id) AS artist
FROM   paintings p;


join same table twice and get the results of rows i have table like wp_term_relationships

object_id  term_taxonomy_id    term_order
     194            2           0
     194            7           0
     194            8           0
     194            38          0
     194            44          0
     194            59          0
     194            100         0
     194            101         0

My question is: I need a query like the term_taxonomy_id of 44 and 8.

44 is country id and 8 is course id. I need query when am selecting country id and course id, than display an particular country having this paricular course.

7 is parent course id. 38 is country parent id.

I have written a query like this:

SELECT object_id
FROM   wp_term_relationships
WHERE  term_taxonomy_id IN ( 44, 8 )

its displaying the country having all the coursee and course having all the countries. But I need only particular country having the particular course in its cities.

How about a "self join":

SELECT a.object_id
    FROM wp_term_relationships AS a
    JOIN wp_term_relationships AS b  ON a.object_id = b.object_id
    WHERE a.term_taxonomy_id = 44
      AND b.term_taxonomy_id =  8;


Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql.

I need to do the following:

Query the information schema to get the parameter names to an sp
SELECT  PARAMETER_NAME, DATA_TYPE from information_schema.parameters where   specific_name=@SPName
Create a dynamic query using this names like select @param1+","+@param2 in a function.Which accepts @SPName as the parameter
call this function in my sps to get the dynamic sql
Execute this sql in my procedure and get the param 1 and param 2 value (param 1 +param2)
Update this value in my spcallTracking Table SpCallDetails field

I have tried this method ,but getting error like
Must declare the scalar variable (param 1)
and the reason i Guess is Dynamic sql runs in a different session and therefore variables defined outside the dynamic query will not be available to the dynamic query

https://stackoverflow.com/questions/14960777/must-declare-the-scalar-variable-error-sql

1. Table-

CREATE TABLE [dbo].[Single](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Age] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
    [ID] ASC,
    [Name] ASC,
    [Age] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

2. Function-

CREATE FUNCTION [dbo].[udf_getSPName]
(
    @SPName nvarchar(100)
)
RETURNS  nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)='SELECT '
DECLARE @tmpParameterDetails table (ParamName varchar(200),DataType varchar(200))
INSERT @tmpParameterDetails
    SELECT  PARAMETER_NAME, DATA_TYPE from information_schema.parameters
    where specific_name=@SPName
    SET @ReturnString=@ReturnString+
                (SELECT STUFF(
                    (SELECT ','+ParamName
                     FROM
                            @tmpParameterDetails

                    FOR XML PATH (''))
                 ,1, 1, ''))

     Return @ReturnString

  RETURN @ReturnString
END

3. Procedure-

CREATE PROCEDURE [dbo].[usp_Single_insert]
    @Name nvarchar(50),
    @Age  int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   DECLARE @ReturnString as nvarchar(max)

    INSERT INTO [Single]
           ([ID]
           ,[Name]
           ,[Age])
     VALUES
           (NEWID(),
            @Name,
            @Age)
            DECLARE  @CreatedBy as nvarchar(100)='test'
            DECLARE  @CreatedOn as datetime=getdate()
            DECLARE @ColV nvarchar(max)

           DECLARE @ParmDefinition nvarchar(500);

    SET @ReturnString=[dbo].[udf_getSPName]('usp_Single_insert')
    EXECUTE sp_executesql @ReturnString

END

Unless I am mistaken, the request is to ultimately dynamically construct a string to be executed that would be able to pull in local variable values that exist in the context that is executing the Dynamic SQL. For example:

The function constructs and returns something along the lines of:

SET @ReturnString = N'@Param1 INT = ''+@Param1+'', @Param2 VARCHAR(15) = ''+Param2+''';

The proc that calls the function EXECs that Dynamic SQL in the hopes of replacing the escaped-quoted parameters as follows:

SET @SQL = N'INSERT INTO dbo.spcallTracking (ProcName, SpCallDetails) VALUES ('''
              + OBJECT_NAME(@@PROCID)
              + N''', '''
              + @ReturnString
              + N''');';

EXEC sp_executesql @SQL;

and the desire is that @ReturnString actually renders as:

@Param1 INT = 2, @Param2 VARCHAR(15) = 'bob'

Unfortunately no, you cannot access variables that exist in the parent context. That would require an eval() (JavaScript / C# 1 / C# 2 / PHP)-like system stored procedure to execute the dynamic SQL in the current context and no such functionality exists, at least not in T-SQL.


ORACLE_HOME / ORACLE_SID variable - why do we need to export them ? I am not sure if this is a bash / shell question or oracle question.
I am on linux and oracle 11g.

I have always export my ORACLE_HOME and ORACLE_SID variable and e.g. using invoking sqlplus is no issue.

I thought have thought that sqlplus is able to be invoked because it is in my PATH variable with $ORACLE_HOME/bin added to $PATH.

=============================================

Today, not sure why, i decided to unset ORACLE_HOME and ORACLE_SID and just declare them as local variable to my current shell.  My PATH is still the same. (contain the full path to oracle_home/bin)

ORACLE_HOME=/path/to/oracle_home
ORACLE_SID=orcl

and I invoke sqlplus from the shell.

==============================================

The sqlplus program is able to be located, but it is not able to run saying that i need to set my ORACLE_HOME

[oracle@SJOAM ~]$ sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[oracle@SJOAM ~]$

===============================================

My question is

1) why is sqlplus unable to know the ORACLE_HOME and ORACLE_SID since it is run from the current shell ?

is it because it is invoked as a child process and thus cannot access the parent process (bash) local variable ?

Regards,
Noob

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer.

In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets about performing the task given to it by its parent.

When that child process completes, it returns a code to the parent (success or failure) - AFAICR, - 0 is success, failure is anything else.

So, the new bash process spawned by your shell (aka "the child") takes its variables from your original (i.e. the spawning or parent) bash shell. If you don't tell the new SQLPlus shell where to find the SQLPlus executable, then it will throw an error.

Unusually for Linux/Unix and Oracle, the error message is actually informative. This is not always the case! :-). If you want a good laugh about Unix, read this. In fairness to Linux/Unix, they've come a long way since that document was written, but it's still worth a read.

So, unless you "export" - i.e. feed the values of your environment to all of the child processes - your system will misbehave.

Take a look at Andrew Tanenbaum's book - or any good text on modern Unixen (or *nix). There's plenty of info out there - just Google Unix and processes - you'll get a truckload of stuff.

@StringerBell makes a good point. In one job I worked in, we had 4 test instances on the one machine. We'd log in and then go to our desired instance and run oraenv.blue, oraenv.red... There are pros and cons - it was easy enough to forget which instance one was on - it's maybe a good idea to change the prompt as well - to give DBAs a fighting chance :-)


High performance count with where clause and dynamic data I am looking for a high-performance approach to solve this problem.
I have a table on a SQL-Server 2019 with 316 columns and several million rows.
For this query, there are only a few of those columns that are relevant.  Something like a date-field and an amount-field (maybe the id-field as well).
Now I would like to know how many entries exist in a specific date range (from now, to X days in the past) that has an amount that is lower than Y.
My current approach (split the request into two requests):

Create a nonclustered index over the date-field & id-field.

Do a first select with a where-clause to get only entries in the relevant date range. (Select only id) Will this avoid a full-table scan?

Do a second select (count) with a where-clause over the ids (and maybe the amount-field).  The id-field is also the primary key and clustered index.

At step three I am not really sure if I can put the amount-field into the where-clause without any performance loss.  If that is not possible, maybe I should select only the ids and the amount-field and do the count in my application (in memory)?  It would be great if it is possible to avoid this.
Also, I am not sure if it will be necessary to split the request at step three if I will have 1,000,000 Ids which will hit the condition from step two.
What do you think about my approach?  Should I go for it, or is there a better one?

Create a composite index and use only one SELECT statement. That index would either be on (date, amount) or (amount, date). Which one you choose depends on your query and the selectivity for each column.
Imagine an old-school (physical) phone book. You find something by last name, and keep searching for the first name you want (Doe, John).
Sure, columnstore indexex can be useful, but that might also be overkill.  There are specific performance concerns when you modify data frequently and there are no SEEKs in a columnstore index.


Can I somehow ignore uncomitted transactions which are causing the database to go into recovery? I have a database which I have inherited which is just a collection of static data in seperate tables with no relations between tables. It is essentially just a number of tables which are the results of queries run against other databases. No-one else has access to the database. It turns out this hasn't been backed up.

A query was running over the weekend and something happened, perhaps a power cut. Now when I start SQL Server the database in question goes into recovery. Normally I would just leave this and let the recovery finish but in this case having left it a few hours I looked back and the estimated time to completion is 4.5 days!!!

Now, I'm not bothered about anything which happened as a result of the query which I ran over the weekend, I don't actually need the results of that anyhow now. All the query was doing was creating a new table, reading data from existing tables, no writing to existing tables. I can recreate almost everything in the database by running the original queries which will just take a few hours. My biggest problem is that I need access to the database, I can't wait 4.5 days!

Is there a way I can delete the transaction log, accepting that it won't recover whatever it was interruped over the weekend, so that SQL will stop trying to recover the database so I can carry on using the data within it?

I wouldn't delete it, just in case you run into problems. If space isn't an issue create a copy of the original files with the instance offline, rename the original (so they aren't found on restart) then try:

CREATE DATABASE MyDatabase
ON (FILENAME = [mdf path]) FOR ATTACH_REBUILD_LOG