Repopulating Oracle tables using data from Excel/CSV file I'm developing a Java EE application that utilizes Hibernate/JPA.  Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle tables, overwriting any existing data.  I'm using a shell script that kicks off 5 sql*loader control files, where each control file specifies filler fields to skip because the excel file is not normalized.

OPTIONS(skip=2)
load data
REPLACE
--#########################################
into table TABLE_NAME
 fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
  (
ACCOUNT_ID ,
REST_STOP_ID ,
DESCRIPTION FILLER ,
GROUP FILLER ,
FUNCTION  ,
JOB_ID "substr(:JOB_ID, 0, INSTR(:JOB_ID,' - ',1))",
<35 other fields>
etc...

First time import of this data into empty tables using SQL*Loader and a csv file from this spreadsheet works fine.  But then any subsequent run sql*loader fails because either control file option:

when using REPLACE:

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword)
**ORA-02292:** integrity constraint (SCHEMA.FK5BA979794B0A176A) violated - child record found

when using TRUNCATE:

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword)
**ORA-02266:** unique/primary keys in table referenced by enabled foreign keys

I've also tried reordering which the tables are populated but no luck.

I've also tried disabling constraints

ALTER TABLE TABLE_NAME DISABLE CONSTRAINT SYS_C0090398

ORA-02297: cannot disable constraint (SCHEMA.SYS_C0090398) - dependencies exist
(0 rows affected)

My work around so far is to drop all the tables restart my application and have Hibernate recreate my 5 tables and then run my sql*loader script. Right now I'm in development so not a big deal.  But when we go to production, I can't keep restarting and creating tables.  Actually when I go to production, I'll create the tables outside of hibernate but right now I have the persistence option hibernate.hbm2ddl.auto set to update.

I'm looking for an elegant (semi)automatic solution to periodically repopulate these 5 tables with data provided to me in the csv/Excel file.

For reusable data loads, I prefer defining static external tables that read from CSV files. You'll need an Oracle DIRECTORY object to read the file.

This example creates a five-column external table called FOO_TAB that reads the FOO_TAB.CSV file from the FOO_DIR directory.

CREATE TABLE foo_tab
(
   a1   VARCHAR2(4000),
   a2   VARCHAR2(4000),
   a3   VARCHAR2(4000),
   a4   VARCHAR2(4000),
   a5   VARCHAR2(4000),
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY foo_dir
   ACCESS PARAMETERS
   (
      RECORDS DELIMITED BY newline
      BADFILE 'foo_tab.bad'
      DISCARDFILE 'foo_tab.dis'
      LOGFILE 'foo_tab.log'
      SKIP 1 /* first row just has column headers */
      FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
      LTRIM
      MISSING FIELD VALUES ARE NULL
      (
         a1   CHAR(4000),
         a2   CHAR(4000),
         a3   CHAR(4000),
         a4   CHAR(4000),
         a5   CHAR(4000),
      )
   )
   LOCATION (foo_dir:'foo_tab.csv')
)
REJECT LIMIT UNLIMITED;


Is a “‘Junction’ Table” the same as a Weak Entity? Is a “‘Junction’-Table” basically just the table that sits between two entities that have a many-to-many (M-M) relationship?

A junction table is a weak entity, but a weak entity often is not a junction table.  A junction table is not the same as a weak entity.  A junction table is a type of weak entity.

Tables used to resolve repeating groups are also weak entities.  These are only junction tables if the repeating group contains a repeated foreign key relationship.


Insert Missing Date row for each group With & W/O using CTE This is my calendar table:

DECLARE @StartDate   DATETIME
DECLARE @EndDate     DATETIME

SET @StartDate       =      '9/24/2018';
SET @EndDate         =      '9/28/2018';

WITH theDates AS
     (SELECT @StartDate AS theDate
      UNION ALL
      SELECT DATEADD(DAY, 1, theDate)
        FROM theDates
       WHERE DATEADD(DAY, 1, theDate) <=  @EndDate
     )
INSERT INTO dbo.tblDateRangeNBNO
SELECT theDate FROM theDates
OPTION (MAXRECURSION 0);

I need to join this to the another table to get each Group's (Values to be carried from same group) missing rows for that date.

Another table:

MeetRoom | StartDT                       | Status | MinsUsed |
 tpa         2018-09-25 08:15:00.000         IN         50
 tpa         2018-09-26 08:35:00.000         FL         90
 ngf         2018-09-24 08:00:00.000         TN         600
 ngf         2018-09-27 17:56:31.563         MN         210
 pdb         2018-09-24 02:11:00.000         TL         200
 pdb         2018-09-28 14:54:46.473         TD         150

Expected result set:

'FC' will be the Status & 1440 will be MinsUsed for each missing MeetRoom dates

MeetRoom | StartDT                       | Status | MinsUsed |
 tpa         2018-09-24 00:00:00.000         FC         1440
 tpa         2018-09-27 00:00:00.000         FC         1440
 tpa         2018-09-28 00:00:00.000         FC         1440
 ngf         2018-09-25 00:00:00.000         FC         1440
 ngf         2018-09-26 00:00:00.000         FC         1440
 ngf         2018-09-28 00:00:00.000         FC         1440
 pdb         2018-09-25 00:00:00.000         FC         1440
 pdb         2018-09-26 00:00:00.000         FC         1440
 pdb         2018-09-27 00:00:00.000         FC         1440

I've the following query, but skipping few records (not sure whats the missing piece)

SELECT p.DateRange, COALESCE(bi.[Status], 'FC') [Status], p.MeetRoom
                    FROM
                    (
                      SELECT MeetRoom, DateRange
                        FROM
                      (
                        SELECT MeetRoom, MIN(DateRange) Min_Date, MAX(DATEADD(DAY, 1, DateRange)) Max_Date
                          FROM tblBI b RIGHT JOIN tblDateRangeNBNO d  ON CAST(d.DateRange AS DATE) = CAST(b.StartDT AS DATE)
                         GROUP BY MeetRoom
                      ) q CROSS JOIN tblDateRangeNBNO dr
                       WHERE dr.DateRange BETWEEN q.min_date AND DATEADD(DAY, 1, q.max_date)
                    ) p LEFT JOIN tblBI bi
                        ON p.MeetRoom= bi.MeetRoom AND CAST(p.DateRange AS DATE) = CAST(bi.StartDT AS DATE)
                    WHERE bi.[Status] IS NULL

To get each day in the range:

DECLARE @StartDate date = '20180924', -- stay away from m/d/y!
        @EndDate   date = '20180928';

;WITH n(n) AS
(
  SELECT 0 UNION ALL SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS
(
  SELECT DATEADD(DAY, n, @StartDate) FROM n
)
SELECT d FROM d
OPTION (MAXRECURSION 0);

This returns:

d
----------
2018-09-24
2018-09-25
2018-09-26
2018-09-27
2018-09-28

Now to produce a row for every possible meeting room on every date, you cross join against the distinct set of meeting rooms present within the range, and then left outer join that set against your data (I'm not sure why you are inserting your date range into a permanent table, this isn't necessary and it kills concurrency).

DECLARE @StartDate date = '20180924',
        @EndDate   date = '20180928';

;WITH n(n) AS
(
  SELECT 0 UNION ALL SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS
(
  SELECT DATEADD(DAY, n, @StartDate) FROM n
),
roomdata AS
(
  SELECT MeetRoom, StartDT, status, minsused
    FROM dbo.tblBI
    WHERE StartDT >= @StartDate
    AND StartDT < DATEADD(DAY, 1, @EndDate)
),
rooms AS
(
  SELECT DISTINCT MeetRoom FROM roomdata
)
SELECT r.MeetRoom, d.d, status = COALESCE(rd.status, 'FC'),
  minsused = COALESCE(rd.minsused, 1440)
FROM d
CROSS JOIN rooms AS r
LEFT OUTER JOIN roomdata AS rd
ON r.MeetRoom = rd.MeetRoom
AND rd.StartDT >= d.d
AND rd.StartDT < DATEADD(DAY, 1, d.d)
ORDER BY r.MeetRoom, d.d
OPTION (MAXRECURSION 0);

Currently this returns the rows that exist and a row for each combination that doesn't exist. If what you want is just the set of rows that don't exist, then just add a WHERE clause before the ORDER BY:

...
AND rd.StartDT < DATEADD(DAY, 1, d.d)
WHERE rd.status IS NULL
ORDER BY r.MeetRoom, d.d;


How can i make dump of big mysql table? I have server with db(36Gb data), only mysql access. When i'm trying to use mysqldump utility, i get connection timeout(or something like this).

I doesn't have ssh access for copy /var/mysql/data.
It's a production server and i want copy data without any loads on server CPU/IOdisk.

Now i have only one solution. Read batch of data (1000-10000 lines) with script, insert in local database, wait xxx ms.

May be exists ready solutions?

You might try mydumper which is multi-threaded whereas mysqldump is single-threaded.
Percona have also improved the less locking feature in the most recent version.
The excellent Mysql Performance Blog has this useful article.


how to use mysql to delete only the first ten table records? I'm currently using php and mysql together.  What I want to try is if I have ten "stable" records for the database and in the same .php file I have some text boxes so I can type in then what I typed will be saved into database but each time I goes to the page only the ten records will be deleted and recreated and the ones I entered will NOT be deleted.  Is it possible?  I know sounds weird but I'm reading the book and there's such task.  Well the task didn't mention clearly if all should be deleted or just the ten "stable" records.

Am I not making much of a sense here?

There are like quite lots scripts for what I have already done so I didn't paste the codes here and wonder if I can get a concept first.

I can understand you saying "stable". There is no clean mechanism in MySQL to implicitly govern the order in which MySQL can delete rows using LIMIT. It is actually not transaction-safe for MySQL Replication.
The only way to guarantee an orderly deletion of rows is to delete by a PRIMARY KEY or UNIQUE KEY that does not rely on an auto_increment column.
EXAMPLE OF STABLE DELETE
Suppose you want to delete the first 10 employees in a company table (layoffs do hurt, this is only an example)
CREATE TABLE emp
(
    id INT NOT NULL AUTO_INCREMENT,
    empid INT NOT NULL,
    ...
    PRIMARY KEY (id),
    UNIQUE KEY (empid)
);

You can safely delete the 10 employees as follows:
CREATE TABLE empids_to_delete
SELECT empid FROM emp ORDER BY empid LIMIT 10;
ALTER TABLE empids_to_delete ADD PRIMARY KEY (empid);
DELETE B.* FROM empids_to_delete A INNER JOIN emp B USING (empid);
DROP TABLE empids_to_delete;

This would be great as long as you create the empid outside this table. This would not present a Replication Slave with a problem even if the auto_increment sequence  on a Master is completely different from that of the Slave.


Which grants are needed to access a table? I want to know which grants at the very least are required to select/insert/update/delete from a table.

Say I have a table t in schema s in database d and a role r.

I start with granting the required permissions mentioned above:

grant select, insert, update, delete on table s.t to r;

What else do I need? Are USAGE on schema s and CONNECT on the database absolutely required, but also enough?

grant connect on database d to r;
grant usage   on schema   s to r;

There is no simple list of necessary and sufficient grants, because there are multiple paths that can be used.  CONNECT on the database and USAGE on a schema are often done through "PUBLIC" rather than to individual roles, for example.

Newly created databases will have CONNECT privilege for PUBLIC by default, so unless you have gone out of your way to remove it no explicit grant is needed in that case.  When you create a database, it comes with a schema named "public" which also has USAGE granted to PUBLIC, but schemas you create manually do not come with that privilege.


Group By hour over large dataset Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits 100% and the selection takes too long. I need to possibly save those averaged values so that SQL does not have to select all those records on each request. What can be done?

  SELECT DISTINCT
         CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour,
         MIN([timestamp]) as TimeStamp,
         AVG(MyField) As AvgField
    FROM MyData
   WHERE TimeStamp > '4/10/2011'
GROUP BY CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR)
ORDER BY TimeStamp

The part of the query is maxing out the CPU for long periods is the the functions in the GROUP BY clause and the fact that the grouping is always going to require an unindexed sort in this instance. While an index on the timestamp field will help the initial filter this operation has to be performed on every row that the filter matches. Speeding up this are using a more efficient route to do the same job as suggested by Alex will help, but you still have a huge inefficiency there because what-ever function combination you use the query planner is not going to be able to come up with something that will be helped by any index so it will have to run through every row first running the functions to compute the grouping values, only then can it order the data and compute the aggregates over the resulting groupings.

So the solution is to somehow make the process group by something it can use an index for, or otherwise remove the need to consider all the matching rows at once.

You could maintain an extra column for each row containing the time rounded to the hour, and index this column for use in such queries. This is denormalising your data so might feel "dirty" but it would work and would be cleaner than caching all the aggregates for future use (and updating that cache as the base data is altered). The extra column should be maintained by trigger or be a persisted computed column, rather than maintained by logic elsewhere, as this will guarantee all current and future places that might insert data or update the timestamp columns or existing rows result in consistent data in the new column. You can still get the MIN(timestamp) out. What the query will result in this way is still a walk down all the rows (this can not be avoided, obviously) but it can do it index order, outputting a row for each grouping as it gets to the next value in the index rather than having to remember the whole set of rows for an unindexed sort operation before the grouping/aggregation can be performed. It will use a lot less memory too, as it won't need to remember any rows from prior grouping values in order to process the one it is looking at now or the rest of them.

That method removes the need find somewhere in memory for the whole result set and do the unindexed sort for the group operation and removes the computation of the group values out of the big query (moving that job out to the individual INSERTs/UPDATEs that produce the data) and should allow such queries to run acceptably without needing to maintain a separate store of the aggregated results.

A method that doesn't denormalise your data, but does still require extra structure, is to use a "time table", in this case one containing one row per hour for all the time you are likely to consider. This table would not consume a significant amount of space in a DB or appreciable size - to cover a timespan of 100 years a table containing one row of two dates (the start and end of the hour, such as '2011-01-01@00:00:00.0000','2011-01-01@00:00:59.9997', the "9997" being the smallest number of milliseconds a DATETIME field won't round up to the next second) which are both part of the clustered primary key will take ~14Mbyte of space (8+8 bytes per row * 24 hours/day * 365.25 days/year * 100, plus a bit for the overhead of the clustered index's tree structure but that overhead won't be significant). With such a table you could do something like the following:

SELECT CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour
     , MIN([timestamp]) as TimeStamp
     , AVG(MyField) As AvgField
FROM TimeRangeByHours tt
INNER JOIN MyData md ON md.TimeStamp BETWEEN tt.StartTime AND tt.EndTime
WHERE tt.StartTime > '4/10/2011'
GROUP BY tt.StartTime
ORDER BY tt.StartTime

This means that the query planner can arrange for the index on MyData.TimeStamp to be used. The query planner should be bright enough to work out that it can walk down the tame table in step with the index on MyData.TimeStamp, again outputting one row per grouping and discarding each set or rows as it hits the next grouping value. No storing all the intermediate rows somewhere in RAM then performing an unindexed sort on them. Of course this method requires that you create the time table and make sure it spans far enough both backwards and forwards, but you can use the time table for queries against many date fields in different queries, where as the "extra column" option would require an extra computed column for each date field you needed to filter/group by this way, and the small size of the table (unless you need it to span 10,000 years of course, in which case it'll be around 1.4Gb) will mean it it likely to always be in RAM.

The time table method has an extra difference (that could be quite advantageous) compared to you current situation and the computed column solution: it can return rows for periods for which there is no data, simply by changing the INNER JOIN in the example query above to be a LEFT OUTER one.

Some people suggest not having a physical time table, but instead always returning it from a table returning function. This means the content of the time table is never stored on (or needs to be read from) disk and if the function is well written you never have to worry about how long the time table needs to span back and forth in time, but I doubt the CPU cost of producing an in-memory table for some rows every query is worth the small saving of hassle of creating (and maintaining, should its timespan need to extend beyond your initial version's limit) the physical time table.

A side note: you don't need that DISTINCT clause on your original query either. The grouping will ensure that these queries only return one row per period under consideration so the DISTINCT will do nothing other than spin the CPU a little more (unless the query planner notices that the distinct would be a no-op in which case it'll ignore it and use no extra CPU time).


copy entire PostgreSQL cluster to another (identical) server I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind:

The databases are very large (terabytes) and the network is excellent. I want to avoid using intermediate files.
Cloning the live databases would be cool, but I can also shut down the cluster if needed.

Options I've considered:

pg_dump | psql of course, but this requires re-creating the indexes and seems really slow and incredibly inefficient for a complete copy between identical systems.
Set up streaming replication with server2 as slave, wait for it to sync with server1, then reconfigure both to disable replication again (I don't need it). Seems like a bunch of pointless config work with room for mistakes.
Shut down the cluster, rsync all the Postgres folders and files. With so much data there's a risk of data corruption, and I need to be sure I get everything (presumably just the data directories are not enough).
Can I pipe pg_basebackup directly to pg_receivewal somehow to make this work? Can't find instructions for my use case.

What's the best way to do this? Seems like a pretty common situation.

Your option 1, pg_dump | psql does have the limitations you describe.

Your option 2 doesn't make much sense.  You need to start out with a clone in order to set up streaming, and by the time you have the clone then you don't need the streaming, as you already have the clone.  Also, you don't have to "reconfigure both to disable replication again".  When you promote the standby, it is no longer a standby, no more reconfiguration is needed.  If you used a named slot on the master, you just need to drop it.  If you used a temp slot, you don't even need to do that.  You could drop "wal_level" down "minimal" if you want to create extra work for yourself, you could set "max_wal_senders" down to 0 if you want to create extra work for yourself.  So just don't do those things.  Leave the master configured in a way that will allow future streaming replication. (On v10, being ready for streaming is already the default configuration anyway, other than the setting in pg_hba.conf to allow a remote streaming connection)

Your concerns in option 3 also don't make any sense.  Why would rsync'ing the data over be any more susceptible to corruption than anything else you can do with a terabyte of data, like ordinary backups, or even just running a database?  Rsync does not go out of its way to corrupt your data.  If you need more things than just the data directory depend on how configured the system in the first place.  Do you have tablespaces?  Are the config files in the data directory, or someplace else.  If you do those things, then yes, you have to handle them.  That is what you signed up for when you decided to depart from the simplest configuration.

On option 4, the output of "pg_basebackup" does not get piped to "pg_receivewal".  They are not designed to work together in this way. "pg_basebackup" can already create a free standing directory, which is what you want, so you can just use it. I think you want to execute, on the host where you want the new server to be, pg_basebackup -D newdir -F plain -h prod_host.example.com

How are you backing up your database currently?  The best way to make a clone is probably to tap into your current backup strategy.  How would you restore from your backup if you needed to?  Minor alterations on that should get you your clone (as well as testing if your current backup strategy works)


How to select all attributes of object type I am running select query with a function in it. That function returns an object type with 4 attributes and I'd like to select all those attributes.

When I try it int this way. It returns just the type object as a whole.

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID) FROM REG_ADDRESS;

It returns:

SERVICE_TYPE | FN_GET_ADDRESS_DETAIL(USER_ID)
---------------------------------------------
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T

My object type is like this:

CREATE OR REPLACE TYPE ADDRESS_DETAIL_T AS OBJECT
(
  apt_number        NUMBER,
  district_name     NVARCHAR2(40),
  zipcode           NUMBER,
  whatever          NVARCHAR2(40)
);

And it works if I specify attribute names after the function call. But in order to select all the attributes, I have to call the function 4 times! O_O

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID).apt_number,
 FN_GET_ADDRESS_DETAIL(USER_ID).district_name,
 FN_GET_ADDRESS_DETAIL(USER_ID).zipcode,
 FN_GET_ADDRESS_DETAIL(USER_ID).whatever, FROM REG_ADDRESS;

Is there any other options?

I think this might be the kind of thing you need.

WITH get_detail
  AS (SELECT get_addr(col1) AS obj_type from my_test)
SELECT x.obj_type.district_name FROM get_detail x
/

This is what I created to test it out.

CREATE OR REPLACE TYPE my_ADDRESS_DETAIL_T AS OBJECT
(
 apt_number        NUMBER,
 district_name     NVARCHAR2(40),
 zipcode           NUMBER
)
/
create table my_test
(col1 number,
 addr_detl my_address_detail_t)
/
INSERT INTO my_test
values(1, my_address_detail_t(1,'HOME',12345))
/
commit
/

And the function;

CREATE OR REPLACE FUNCTION my_get_addr(addr_id IN NUMBER)
RETURN my_address_detail_t IS
addr_t my_address_detail_t;
BEGIN
SELECT addr_detl
  INTO addr_t
  FROM my_test
 WHERE col1 = addr_id;

RETURN addr_t;
END;

You will obviously need to define a join somewhere for get_detail to have the correct col1 to retrieve data for. Im not really sure of the full requirement so haven't tried that bit but this should give you the basics.


Log Shipping failover when Primary is not accessible Log Shipping Fail-over

I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible.
My Steps for fail-over will be

Copy and restore T-logs (WITH RECOVERY) that are not applied, to put the database online
Since the Primary in not accessible so I cannot take a T-log and put it in restoring state
Disable the above two jobs
Change the application connection strings to the new primary.

My confusion arises what should I do when the Old Primary comes live again:

Should I take the T-log back up and put the database in restoring state so that I can set it up as a secondary and then fail-over to make it primary.

If that’s the case then wont the log shipping fail if I want to configure without initializing (since I have the database in restoring state) as it will look for the last T-log which I Have not applied to my new secondary as the primary was not accessible during fail-over.

Thanks in advance

The issues and complications you mention are ones I've wrestled with myself.  Quite a while back, I did my best to script out a solution to help make it easier to fail over and also to fail back.  I would suggest going to codeproject.com and searching for "Streamline Log Shipping Failovers" to look at the quick article I wrote there.  You can download the SQL script and see if it's helpful for your situation.  Essentially, it is a SQL script that you run on the Secondary and it dynamically generates the scripts you need to fail over and later fail back.

I have used it many times in a Production environment to perform DR tests.  I have also used it once in a true Production down situation and it really helps streamline the process.


Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle [table] does not exist in the current database Before setting up SQL Server snapshot replication, I backed up the database from ServerA and restored it to ServerB. Both servers are running SQL Server 2008 R2 SP1.

In trying to setup snapshot replication from ServerA to ServerB using the GUI, I get the following error:

  Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle, Line
  582 [dbo].[SYSCOLUMNS] does not exist in the current database.

Yes, that is the name of the table and it is indeed a user table. I ran the setup from ServerA which is the Publisher. The collation is the same between the tables on both servers. If I remove that table as an article, the replication is setup just fine.

I've searched the web on this and I'm stumped. Any ideas?

Answer generated from a comment left by the question author

It was determined that it's not possible to replicate tables that are using the same names as system tables/views such as dbo.syscolumns or dbo.sysobjects.


Query from Postgres to SQL Server I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server.

SELECT (applicant_majors.*)::applicant_majors
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id
ORDER BY id
LIMIT 1 OFFSET 1

This is the whole query

           FROM (SELECT college_attendeds,
                    colleges,
                    applicant_degree_1,
                    applicant_degree_2,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id           LIMIT 1         ) AS applicant_degree_1_applicant_major_1,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_1).id ORDER BY id           LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_major_2,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1         ) AS applicant_degree_1_applicant_minor_1,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_1).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_1_applicant_minor_2,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id           LIMIT 1         ) AS applicant_degree_2_applicant_major_1,
                    (SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors.applicant_degree_id = (applicant_degree_2).id ORDER BY id           LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_major_2,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1         ) AS applicant_degree_2_applicant_minor_1,
                    (SELECT (applicant_minors.*)::applicant_minors FROM applicant_minors WHERE applicant_minors.applicant_degree_id = (applicant_degree_2).id ORDER BY minor_number LIMIT 1 OFFSET 1) AS applicant_degree_2_applicant_minor_2
               FROM (SELECT (college_attendeds.*)::college_attendeds AS college_attendeds,(colleges.*)::college_bases AS colleges,
                            (SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1         ) AS applicant_degree_1,
                            (SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1 OFFSET 1) AS applicant_degree_2
                       FROM college_attendeds
            LEFT OUTER JOIN college_bases AS colleges ON (college_attendeds).college_id = colleges.id AND colleges.type = 'College') AS applicant_degree_data) AS college_attended_data
LEFT OUTER JOIN degree_bases AS applicant_degree_1_degree ON (applicant_degree_1).degree_id = applicant_degree_1_degree.id AND applicant_degree_1_degree.type IN ('Degree', 'PtDegree')
  LEFT OUTER JOIN degree_bases AS applicant_degree_2_degree ON (applicant_degree_2).degree_id = applicant_degree_2_degree.id AND applicant_degree_2_degree.type IN ('Degree', 'PtDegree');

applicant_degree_1 its a subquery that contains:

SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1

  The query above try to get the greatest id from applicant_degrees for each college_attended_id.

Then, I beleive your query can run on MSSQL like this:

SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
    SELECT MAX(id) FROM applicant_degrees
    WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1

  Notice that I changed the ORDER BY and LIMIT for a MAX aggregate function.

Hope it helps.


SQL Query to select rows based on specific condition Below is an example of childs daily activity. I want to select the next rows after the last 'Play' activity based on user id.

id          timestamp               activity
567     1541300537817000            Play
567     1541300584959000            Games
567     1541300711884001            Play
567     1541300749548000            Study
567     1541300804865000            Movie
678     1542073550481000            Computer
678     1542073551422000            Play
678     1542073551478000            Play
678     1542073561885000            Play
678     1542073567663000            Sleep
908     1543580126943000            Tv
908     1543580212409000            Sleep
908     1543580245227000            Play

Expected output

id          timestamp               activity
567     1541300749548000            Study
567     1541300804865000            Movie
678     1542073567663000            Sleep

I tried doing this:

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Play';

But it is giving me events after 1st Play activity.

It is possible to solve by adding one more copy of a table for to check there is no 'Play' activity after.

But it's better to use a subquery for to select last 'Play' activity, and get all records after it:

SELECT t1.*
FROM `table` t1, (SELECT id, MAX(`timestamp`) `timestamp`
                  FROM `table`
                  WHERE activity = 'Play'
                  GROUP BY id) t2
WHERE t1.id = t2.id
  AND t1.`timestamp` > t2.`timestamp`


How to join two table and show one query result in MySQL? I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there is duplicate. So my expected output records will contain 5 rows not 6 rows. How do i do that?

Example:

table1:                       table2:

+-------------------------+   +--------------------------------+
| Name                        | Name
---------------------------   +---------------------------------
| A    |                      | C    |
| B    |                      | D    |
| C    |                      | E    |

My Expected output is:

+-------------------------+
| Name | ID
---------------------------
| A    | 1 table1
| B    | 2 table1
| C    | 3 table2 or table1 (no unique)
| D    | 4 table2
| E    | 5 table2

I tried this:

 SELECT
  name as name
 FROM
  table1
UNION
 SELECT
  anothernamename as name
 FROM
  table2
WHERE
   name like '%C%'

Error: #1054 - Unknown column 'name' in 'where clause'

Follow up: Union with Where clause + extra extra large requirement.

SELECT * FROM
(
    (
     SELECT * FROM table1
     WHERE ...
     ORDER BY ...
     LIMIT ...
    )
UNION
    (
     SELECT * FROM table2
     WHERE ...
     ORDER BY ...
     LIMIT ...
    )
) as t
WHERE ...
    ORDER BY ...

It seems that your error it's cause en table2 the column you want to filter is anothernamename, not name, you can't put an alias on the WHERE. In any case, in your question, you don't seem to want to filter your results, so that WHERE is of no use at all. Your query should just be:

SELECT
FROM (  SELECT [name]
        FROM table1
        UNION
        SELECT anothernamename
        FROM table2) AS Data
WHERE ....
ORDER BY ....
LIMIT .....


IP Addresses tab is not showing a specific IP Address I have SQL SERVER 2008 R2 installed on windows Server 2016, it runs smoothly on a LAN network interface.
I Added a USB MODEM (4G) to the server and went to the IP Addresses tab in network configuration to Adjust its IP Settings (enable the IP and set the port for listening) but it is not shown in the IP Addresses tab, I tried to restart the SQL services and restart the server but it is not showing.
I searched the web but couldn't find an answer, what is the problem with it ?

I don't know the official way that Microsoft recommends adding a new ip address to the network configuration panels, and I don't know how you would do it with SQL Server 2008, I've only worked with SQL Server 2012+, so take these with a grain of salt.
For reference, here's Microsoft's page on editing the TCP/IP properties panels - https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-ver15
IF all of your ip addresses on the host are going to be for listening to SQL Server, you could tell it to 'Listen All' = 'Yes'. Instead of then having to specify the ip addresses and ports you would then just specify the port under the 'IPAll' region. Now, this is not for everybody, but it is an option.
If using the 'IPAll' doesn't work for you, and you are lucky enough to have an 'IPx' that isn't in use you can edit the 'IP Address' box and 'TCP Port' fields to make it work for the new ip address. Just make sure to check 'Enabled' = 'Yes' otherwise SQL Server will ignore it.
Now, if you can't just blanket listen to all ip addresses on the same port or just edit an existing IPx field you are going to have to add another 'IPx' field. Here's how I add an 'IPx' field to the panel, but I don't think it's how Microsoft officially recommends people to do it, and unfortunately it's a registry edit. With that being said, if you are not comfortable working with the registry DO NOT DO THIS. As always, back up your registry, take preventative measures, and test it out in development before applying this in your production environment. In order to get a new 'IPx' field to show up you have to create a new registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.(INSTANCENAME)\MSSQLServer\SuperSocketNetLib\Tcp\IPy (where IPy is the number that comes after the largest existing one) and copy over the key entries found in the other IPx registry entries. You should see registry entries that say 'Active' and 'IpAddress' under the registry entry that you copy over.
I am going to be very interested to see how others solve this to hopefully make it so that I don't have to edit the registry in the future.


Is it safe to leave/exit the stored procedure without commit or rollback on a transaction? Like the code below is it safe to do that?

create definer=`root`@`%` procedure `test`()
label: begin

declare exit handler for sqlexception rollback;
start transaction;

select condition from table where id = someid for update;

if condition = false then
  leave label;
end if;

// some query;
commit;
end$$

I would argue that transaction control does not belong to stored procedures at all, apart from some very particular circumstances (e.g. where you want to log audit records regardless of what happens to the "outer" transaction -- i.e. "autonomous transaction"). The procedure has no way of knowing if it is part of a larger unit of work and therefore, if it commits or rolls back, it might violate consistency rules of a larger transaction.
Commit or roll back at the level where business rules are implemented.
In other words, yes, in the majority of cases it's the right thing to do "to leave/exit the stored procedure without commit or rollback" and let the caller decide (provided that you don't hide the exception if it occurs).


Giving server-role to login breaks stored procedures I have a login called user1. user1 can execute stored procedure [Test].[SP1]

EXEC [Test].[TestSortation]
EXEC [TestSortation]

Both of the above commands work.

Now if I give the user1 the server role of sysadmin in addition to public. the later of the 2 statements above fails, and throws this error:

  Could not find stored procedure 'TestSortation'.

I don't want to have to change every stored procedure in the database to have the schema name included with EXECute commands manually. Can someone explain to my why this is happening. Also, is there a way to get around this, automatically fix my stored procedures, have the schema default to [Test]?

Grant CONTROL SERVER to the login rather than adding it to the sysadmin role.

As documented in Books Online:

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

CONTROL SERVER gives the same rights as sysadmin but without this side-effect.

One caveat: you would need to check for any code that depends on the user explicitly being a member of the sysadmin role (for example, code using IS_SRVROLEMEMBER).


SELECT count(*) from a materialized view with unique index is slow I have a materialized view with a unique index (on the id column)
and both of these queries are slow (they scan the whole table):

SELECT count(DISTINCT a.id) from the_view a;
SELECT count(*) from the_view a;

the plan is:

"QUERY PLAN"
"Aggregate  (cost=5303367.00..5303367.01 rows=1 width=0)"
"  ->  Seq Scan on the_view a  (cost=0.00..4953116.20 rows=140100320 width=0)"

I'm surprised that the index is not being used.

The first query is logical nonsense for your case:

SELECT count(DISTINCT a.id) from the_view a;

It makes no sense to use DISTINCT with the id defined unique.

The second query is typically faster with a sequential scan:

SELECT count(*) from the_view a;

Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check visibility in the main relation (the heap / the table) - unless all rows of a page are marked visible for all transactions (visibility map). And a MATERIALIZED VIEW is basically just another table with functionally dependent data and a recipe how to derive it from other tables (the stored query).

When the whole table needs to be read anyway, it rarely makes no sense to use an index (additionally). So while Postgres may use an index (index-only scan) when counting only parts of a table, a sequential scan is typically faster for a full count. The decision also depends on your cost settings.

You can verify, that Postgres can in fact use an index-only scan (or a bitmap index scan) by disabling plan types (only in your session for debugging!). The manual instructs in the chapter Examining Index Usage:

  When indexes are not used, it can be useful for testing to force their
  use. There are run-time parameters that can turn off various plan
  types (see Section 18.7.1). For instance, turning off sequential scans
  (enable_seqscan) and nested-loop joins (enable_nestloop), which are
  the most basic plans, will force the system to use a different plan.
  If the system still chooses a sequential scan or nested-loop join then
  there is probably a more fundamental reason why the index is not being
  used;

In many cases an estimate is good enough and available in close to no time:

Fast way to discover the row count of a table

Remember that every count is hardly more than an estimate. It may be outdated in the moment after you count.

Related page on Slow Counting in the Postgres Wiki.


Deadlock chain, but no deadlock? I've setup the profiler to catch these deadlock events:

Deadlock graph
Lock: Deadlock
Lock: Deadlock Chain

Al available columns are ticked (TextData, LoginName, SPID, etc.)
So far, I was able to catch some deadlocks.
However, sometimes it happens that I get only a list of deadlock chains (like 30 events in a few seconds), but then no deadlock and no deadlock graph is caught.
What does such a case mean? Did a deadlock occur or not? Or was it like "deadlock was around the corner" but could be resolved?

sp_BlitzLock
I'd recommend using sp_BlitzLock, which you can point at the deadlock XML you've captured via the @EventSessionPath parameter (assuming you're collecting to a file, and not the ring buffer or something else). By default, it will look at the system health Extended Event, which may also have useful data in it.
There are some important fixes in the dev branch, so you should use that version until the main release later this month (2020-12, if you're reading this far into the future).
It will accurately categorize regular and parallel deadlocks:

Plan Explorer
You may also get more readable information if you open the deadlock graph using the free Plan Explorer tool, from Sentry One, a subsidiary of SolarWinds Worldwide, LLC, which is a subsidiary of The Sheinhardt Wig Corporation.
It can also tell you if your deadlocks are from Exchanges or Syncpoints, which would denote a parallel deadlock.


Why backup database by Barman can't reach end(pending)? Did operations:

barman cron
barman receive-wal --create-slot pg
barman receive-wal pg
barman switch-wal --force --archive pg

Then run check and backup command:

-bash-4.2$ barman check pg
Server pg:
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (no last_backup_maximum_age provided)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    systemid coherence: OK (no system Id stored on disk)
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
    receive-wal running: OK
    archive_mode: OK
    archive_command: OK
    archiver errors: OK
-bash-4.2$ barman backup pg
Starting backup using postgres method for server pg in /var/lib/barman/pg/base/20200305T124531
Backup start at LSN: 0/5100A7B8 (000000010000000000000051, 0000A7B8)
Starting backup copy via pg_basebackup for 20200305T124531
(pending)

It can't finish after a very long time. Checked /var/lib/barman/pg/base/20200305T124531/ path:

backup.info
data

In data, it seems the data from pg server already been copied. See the content in backup.info:

backup_label=None
begin_offset=42936
begin_time=2020-03-05 12:45:31.020705+09:00
begin_wal=000000010000000000000051
begin_xlog=0/5100A7B8
config_file=/var/lib/pgsql/10/data/postgresql.conf
copy_stats=None
deduplicated_size=None
end_offset=None
end_time=None
end_wal=None
end_xlog=None
error=None
hba_file=/var/lib/pgsql/10/data/pg_hba.conf
ident_file=/var/lib/pgsql/10/data/pg_ident.conf
included_files=None
mode=postgres
pgdata=/var/lib/pgsql/10/data
server_name=pg
size=None
status=STARTED
systemid=6795775088862271947
tablespaces=None
timeline=1
version=100012
xlog_segment_size=16777216

Check process, got:

barman    5661  0.0  0.2 258560 19204 ?        Ss   12:45   0:00 /usr/bin/python2 /bin/barman -c /etc/barman.conf -q receive-wal pg
barman    5671  0.0  0.0 176384  3416 ?        S    12:45   0:00 /usr/pgsql-10/bin/pg_receivewal --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/pg/streaming --slot=barman
postgres  5675  0.0  0.0 397052  3844 ?        Ss   12:45   0:00 postgres: wal sender process streaming_barman 127.0.0.1(41570) streaming 0/534365C8
barman    5745  0.0  0.2 258564 19080 pts/0    S+   12:45   0:00 /usr/bin/python2 /bin/barman backup pg
barman    5751  0.0  0.0 176132  3316 pts/0    S+   12:45   0:00 /bin/pg_basebackup --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_streaming_backup -v --no-password --pgdata=/var/lib/barman/pg/base/20200305T124531/data --no-slot --wal-method=none
postgres  5752  0.0  0.0 396724  7980 ?        Ss   12:45   0:00 postgres: wal sender process streaming_barman 127.0.0.1(41618) sending backup "pg_basebackup base backup"

Why it can't finish? status=STARTED Didn't update to finish status.

The reason was I did recovery after the first backup operation. Then Barman set a string #BARMAN# ahead archive_command = 'barman-wal-archive backup pg %p' command into the postgresql.conf file!

So it can't call that command when do the second backup.

Remove the string, it works.

The design to aim to don't allow user to use archive_command again after recovery?


How to prevent sysadmin server role from executing a stored procedure I have a SQL Server default instance using SQL Server Authentication and two users, User1 and User2.

User1 has the public server role while User2 has the public server role  + sysadmin server role.

I have created one database, sptest1. Inside that database I have created a stored procedure, sp_viewtbl and also created a schema. Using the schema I have disabled executing stored procedure for User1 and it works.

How can I deny executing the stored procedure by User2, who has sysadmin server role?

Suppose I have created User3 and given permissions as sysadmin and public.
So how can I prevent that new user from executing that same stored procedure? I have to secure sp_viewtbl from others login even if they have sysadmin permission.

Members of the sysadmin role cannot be denied permission to anything in SQL Server.  The documentation for DENY (Transact-SQL) states:

  ...except that DENY does not apply to object owners or members of the sysadmin fixed server role.

So in order to prevent a sysadmin from running a stored procedure, you would need to put code in the stored procedure to check if the user is a sysadmin and return an error if they are.  However, a sysadmin could easily get around this by:

modifying the stored procedure
creating a new stored procedure with the same code and ripping out the part that checks for sysadmin
running the same code, minus the part that checks for sysadmin, from a SQL client
using EXECUTE AS to run the procedure as a different user

If your goal is to prevent sysadmin users from seeing sensitive data, the way to do that is to use Always Encrypted.  This encrypts and decrypts the data with a key that only the application has access to.  If a sysadmin reads the table, they will just see encrypted data.


Can bdr only replicate one database per server? I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm.

My Problem: I want to add more databases to the replication but every time if I added the other database and restarted the servers one server is crashing. pg_hba.conf and postgresql.conf are correct.

So I read on this site that

  "You can replicate just one database from a server"

But on this site it sounds like an advantage:

  "You must explicitly configure each database you want to replicate."
  and "BDR replicates per-database, not per-cluster. So you don't have
  to split your databases up into different PostgreSQL instances just to
  control how they replicate."

this sounds to me like I could vsync more databases if I want...

BDR can replicate as many databases per server as you want. You must configure each database separately, that's all.

There is not (currently) any wildcard configuration to say "all databases on the current DB".

A typical configuration looks like:

Server1       Server2
DB_A     <=>  DB_A
DB_B     <=>  DB_B


MySQL: Moving Database from Windows to Linux I have a Windows based dedicated server have below configuration:

Windows edition: Windows Server 2008 R2 Standard Service Pack 1
Processor: Intel(R) Xeon(R) CPU X5460 @3.16GHZ 3.16 GHZ (2 Processors)
Installed Memory(RAM): 8.00 GB
System type: 64-Bit Operating system

Even, I am facing bad performance of my application. DB connections become high >100 and system starts running slower. It become dead when DB connection reached to around 200. While we defined MAX connection=800. I already tried multiple things like -
MySQL configuration variables tuning
Identified and improved the bad performing queries

Above steps helped a little bit but still facing very bad performance. It is just like I am swimming in the ocean without any expertise and life saving boat.

Now, I am planning to upgrade MySQL from 5.1 to 5.6 as well as switching from Windows to Linux. I guess as Open source - Linux  must have multiple tools to measure performance and tuning tools for Windows. But, before this I want to be sure about below things:

How much will it be useful in performance improvement?
My application is running in dedicated Windows server. Switching application servers also required?
Need to tune MySQL configuration variables again?
What should be the configuration (hardware and software) of LINUX based system?
What performance measurement and tuning tools are available for LINUX?

Many Thanks :)

You can try Percona wizard tool to configure MySQL server parameter based on Hardware and application in use using https://tools.percona.com/wizard
also there are other online tools available at https://cloud.percona.com/ all percona tools are free.

Some similar answsers to the questions can be found here:
https://stackoverflow.com/questions/11574401/mysql-monitor-queries

https://stackoverflow.com/questions/12026488/mysql-query-monitor-tool


How can I get the actual data size per row in a SQL Server table? I found this script
sql-server-2005-reaching-table-row-size-limit
that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 (whatever MS recommends)

Try this script:

declare @table nvarchar(128)
declare @idcol nvarchar(128)
declare @sql nvarchar(max)

--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'

set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'
        from  sys.columns
        where object_id = object_id(@table)
        and   is_computed = 0
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

PRINT @sql

exec (@sql)

The rows will be ordered by size, so you can check from top to down.


How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, when the user wants to authenticate, they provide their tenant info (this can be their subdomain, e.g. mycompany.coolapp.com, or it can be a unique account ID), and the app routes subsequent queries to that tenant's schema.

However, certain pieces of tenants' data will be public. For instance, the user will need to create invoices and email their links (containing a UUID) to their customers. When the customer clicks the link, the app needs to be able to figure out which tenant the invoice belongs to, based on the UUID.

To do this, I created a "public.invoices" table, with two columns: uuid and tenant. The latter contains the tenant's schema name, which I can then use to perform the subsequent queries (i.e. the invoice's actual data) to the appropriate schema.

Question 1: Is this design sound? Is there some other, more standard way of routing unauthenticated/pre-authenticated requests to specific schemas in situations where each tenant gets their own schema?

Question 2: How do I tell Postgres that, when the tenant deletes the invoice (the one in their schema), the public record (under "public.invoices") also should be deleted? I'm trying to avoid using two queries, if some database-provided option or feature exists. I looked at CASCADE, but it seems that only goes from parent to child, and as far as I can tell there isn't a way to define a foreign key relationship in this scenario where the tenant invoice is the parent record.

There are many ways to implement this, but let me suggest partitioning.

The table public.invoice would then be list partitioned, with the tenant being the partitioning key. Each partition sits in the schema of the respective tenant.

That way everybody can query their own invoices in their own schema or all invoices in the public schema.

The big advantage of partitioning here is that it becomes very easy to remove a tenant: simple detach the respective partition and drop it.

Note that partitioning becomes impractical with thousands of partitions. But if you plan to have that many tenants, it is a bad idea to implement that with thousands of schemas with identical tables in a single database. Having too many tables will slow down metadata operations, and it makes life harder for autovacuum (which in turn will make your life hard).


local login from DBA group member (as sysdba) not working good day!

i'm confronted with the following situation

OS: CentOS 7 x86_64
user: oracle (member of DBA group, owner of /u01/app (drwxr-sr-x)
/etc/oratab: orcl12c:/u01/app/oracle/product/12.1.0/dbhome_1:Y
after starting LISTENER manually, Listener reports "The listener supports no services"
sqlplus sys/*@co7ora12cvm:1521/orcl12c as sysdba returns
«ORA-12514: TNS:listener does not currently know of service requested in connect descriptor»
dbstart starts listener but errors out with ORA-01017: invalid username/password; logon denied
i'm able to start the database through dbca (Configure database options > Next > Next)
once this is done LISTENER know of the services orcl12c and orcl12cXDB
once this is done, sqlplus username@orcl12c works fine
sqlplus as sysdba throws the /? output.
ping co7ora12cvm works
tnsping orcl12c times out (TNS-12535)

listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = co7ora12cvm)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

shrept.lst

(##) function entry points for genclntsh.sh

network : snaumihi_inithostinfo
network : snaumbg_gmt
network : naedpwd_encrypt
network : naumbsb_bld_singlebyte
network : nlgh
network : nsdhctx_establish
network : nsdhctx_respond
network : nsdhctx_close
network : nsdhctx_inuse
network : snlsmDscFromString
network : snlsmCreate
network : snlsmDestroy
network : snlsmDscToString

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NONE)

tnsnames.ora

ORCL12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = co7ora12cvm)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl12c)
    )
  )

this is a new install that has never started "normally"

thanks in advance for any suggestion

############# UPDATE user/hosts

as already mentioned i do this as user oracle which is member of the dba team

this is run on vmware and i do the networking with the hosts files

/etc/hosts on co7ora12vm

127.0.0.1       localhost.localdomain localhost
192.168.178.30<>co7ora12cvm.localdomain co7ora12cvm

c:\windows\System32\drivers\etc\hosts (on Windows 7)

192.168.178.30          co7ora12cvm.localdomain co7ora12cvm

############# UPDATE sqlnet.ora

thanks for spotting this, indeed, alas it makes no difference

sqlplus sys/sys@co7ora12cvm:1521/orcl12c as sysdba gives ORA-12514: TNS:listener does not currently know of service requested in connect descriptor  before  instance is started through dbca; once instance is started it connects
sqlplus / as sysdba  always gives ORA-12162: TNS:net service name is incorrectly specified

this happens no matter what sqlnet.ora contains

SQLNET.AUTHENTICATION_SERVICES = ()
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) such as on my 10g vm where sqplus / as sysdba works

?

You have disabled local OS authentication by setting this:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Remove the above line from sqlnet.ora, after that, you will be able to log in to the database with:

sqlplus / as sysdba


Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C) I'm trying to optimize a query here:

Given Table 1 (A,B,C) and Table 2 (B,C)

Given Table 1 contains many unique tuples (B,C) for each A

Select (1.A, 1.B, 1.C)

Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C

Example

TABLE 1

| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |

TABLE 2

| B  | C  |
|----|----|
| B1 | C1 |
| B2 | C2 |

RESULT

| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |

(Only 3 was excluded because it failed to match both rows of table 2)

Current implementation is dynamic SQL

select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2

However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)

Related: Is there any way to "name" a query to be used later (not computed, but deferred?)

select *
from t1
where t1.a in ( select t1.a
                  from t1
                  join t2
                    on t2.b = t1.b
                   and t2.c = t2.c
                 group by t1.a
                having count(*) = (select count(*) from t2)
              )


SQL Server 2008 timeout on insert I have an issue with insert time out. My table has a size of 12 gb and 12 million rows.

Sometimes I get timeout on insert. I have an insert query every 5 sec on average.

I suspect that I have lock escalations on the table when end user performs a select.

What is the best practice to avoid insert time out on large tables?

Is to create two tables one insert second for select ? replication ? SQL AVAILABILITY Groups ? table partitioning ? any other options?

(soon  we are going to upgrade to SQL Server 2016)

regards,
Pavel.

I would enable Read-Committed Snapshot Isolation, which does row versioning under the covers by making use of tempdb. Readers will not block writers, which is what you're looking for in this scenario.

This is a per-database level setting, which doesn't require a reboot. However, you must have tempdb configured correctly (appropriate fixed-size file growth, sufficient data files, etc.).

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
GO

(Read more here: Choosing Row Versioning-based Isolation Levels.)


Performance of query against column level dependencies much faster when using deprecated system view Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage.

the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies).

SELECT
    depObj.name
FROM
    sys.sql_dependencies dep
    INNER JOIN sys.tables tab ON tab.object_id = dep.referenced_major_id
    INNER JOIN sys.schemas sch ON sch.schema_id = tab.schema_id
    INNER JOIN sys.columns col ON col.object_id = dep.referenced_major_id AND col.column_id = dep.referenced_minor_id
    INNER JOIN sys.objects depObj ON depObj.object_id = dep.object_id
WHERE
    sch.name = 'dbo'
    AND tab.name = 'JobVoyage'
    AND col.name = 'JV_VoyageFlight'
    AND depObj.type = 'TR';

The suggested replacement view is sys.sql_expression_dependencies but this does not contain column level dependencies for non schema bound objects and the documentation advises to use the sys.dm_sql_referenced_entities function instead for that.

The second query uses the new mechanism.

select distinct name from sys.triggers
cross apply sys.dm_sql_referenced_entities('dbo.' + object_name(object_id), 'object')
where
    parent_Class = 1
    and Is_Ms_Shipped = 0
    and referenced_entity_Name = 'JobVoyage'
    and referenced_minor_name = 'JV_VoyageFlight'

One takes 3 milliseconds. One takes 3000 milliseconds.
Does anyone have a better version of this query to get dependencies out of triggers without using deprecated functionality?

I tried this rewrite that uses a temp table with the new DMF on AdventureWorks2014, and it runs around twice as fast as the version from your question.

SELECT t.[object_id], t.[name]
INTO #trigs
FROM sys.triggers t
WHERE
    t.parent_class = 1
    and t.is_ms_shipped = 0;

SELECT DISTINCT t.[name]
FROM #trigs t
CROSS APPLY sys.dm_sql_referenced_entities
(
    'Person.' + object_name(t.[object_id]),
    'object'
) deps
WHERE
    deps.referenced_entity_Name = 'Person'
    and deps.referenced_minor_name = 'BusinessEntityID';

Here's a screenshot of the Plan Explorer summary after running both queries in the same batch (plans here):

The original query (the first row) runs for about 8 ms, the temp table version (the second two rows) varies between 4 and 5 ms.  Of course, your database might have vastly more triggers, objects, and dependencies than AW2014.

As Dan Guzman mentioned, you may also get some improvements by updating system table stats.

FYI - those two queries are not exactly equivalent.  The second query will not show the same results if there is a trigger in a different (non-dbo) schema that references dbo.JobVoyage.JV_VoyageFlight.

In the example query from my test, there is a trigger in the Sales schema (iduSalesOrderDetail) that is returned by the deprecated view but not the DMF version, because the column I'm checking for is in the Person schema:

If all your objects are in "dbo" then this won't matter, but I thought it was worth pointing out.


MySQL - Truncated incorrect datetime value I need to migrate some data from between to tables. The first has a varchar(255) field where a date is stored. I want to insert that date in a timestamp field in the second table.
The varchar field store the value in this ISO 8601 format: 2020-09-24T15:08:07+02:00.
I tried different conversions using STR_TO_DATE and casts but I always get the same error:
Truncated incorrect datetime value: '2020-06-01T09:38:08+02:00'
On selecting the value seems to be converted correctly:
SELECT STR_TO_DATE(varcharDate,'%Y-%m-%dT%H:%i:%s') = 2020-06-01 09:38:08

In would recommend
  SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)

STR_TO_DATE does the trick, but is usually slow.
You can run such a Query to test the performance using the column name instead of the text
 CREATE TABLE t2 (tz TIMESTAMP) SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)


Independent serial for each foreign key value I have a table shared by many different users called players and a table called users.

Each player has the primary key id (serial) and a foreign key called owner_id which matches to users.id.

In order for users to update their players in my database (through my API) they must specify the same id as appears in the players table (the ids are visible to them!).

Because it's shared, the player ids that one user creates might look like:

1
2
12
56

(with the gaps being the id's used by players created by other users).

How can I create a new column called local_id which will behave like an independent serial on each owner_id value?

So each user would see the local_id as perfectly incrementing every time they create a new player?

1
2
3
4

Thanks for your help.

You can't do this with SERIAL.

You could create a SEQUENCE for each owner_id but that'd be painful and scale poorly.

Instead, use a counter table where each owner_id has a separate counter, and

update players_per_user_counter
set next_val = next_val + 1
where player_id = $1
returning next_val

to get new identifiers. For more info search for "PostgreSQL gapless sequence".

See:

https://stackoverflow.com/q/9984196/398670


Need to count records and group count by date on Oracle I have a table like the following

   ID                created    sent    type
-----------------------------------------------------
0001463583000051783  31-JUL-12  1   270
0081289563000051788  01-AUG-12  1   270
0081289563000051792  01-AUG-12  1   270
0081289563000051791  01-AUG-12  1   270
0081289563000051806  01-AUG-12  1   270
0001421999000051824  06-AUG-12  1   270
0001421999000051826  06-AUG-12  1   270
0001464485000051828  06-AUG-12  1   270
0082162128000051862  09-AUG-12  2   278
0082162128000051861  09-AUG-12  2   278
0022409222082910259  09-AUG-12  3   278

I would like to have the following for output

created     Count
---------------------
31-JUL-12   1
01-AUG-12   4
06-AUG-12   3
09-AUG-12   3

How hard would it be to accomplish this using SQL Developer on Oracle 10g?

I have tried several queries to generate such a table and in the end it does not group the count by date, just gives me a '1' for the count when we average 5000-10000 transactions daily. I'm probably overcomplicating it. But I would like something simple where I can pull the amount of transactions on a daily basis within a date range.

Unless I am missing something, your query would be something like this:

select created, count(*) CreatedCount
from yourtable
group by created
order by created;

See SQL Fiddle with Demo

Or if you have a time associated with the date, you can use TRUNC:

select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by trunc(created);

See SQL Fiddle with Demo


How do I store data with a query that's approximated? I'm trying to find a way to store my data with fast access (better than O(n)).

My database consists of data (4096 byte strings) that represents some information about some items.
The problem is, that the query is never exact. I get one Item, and then need to find the closest match using a function F(a,b).

just an example:

1234
3456
6466
F(a,b) = return % of similar digits

GetClosest(1233,F) = 1234

The problem is that F(a,b) is a complicated algorithm, (not a proper metric).

What I have now is just go over the whole database to search for the best match.
Is there a kind of tree or other cluster database type that can give me faster finding complexity ?

More information:

F gives back a similarity value in %percentage. where 100% is a perfect match.

You may want to try the retrieval (re trie val) tree, as known as the TRIE. Some refer to this as a Radix Tree.

The idea is to create tree node structures that contain branches for every character your data field can possibly contain.

Let's use a simple case, a numeric field. Obviously, the character range is 0-9. Each tree node would contain ten(10) branches. Let's take the worst case for a 4 byte unsigned integer, 2^32 - 1, which is 4294967295. What is its length? Just compute the length by take the integer of the log base 10 of 4294967295 and adding 1.

mysql> select floor(log10(power(2,32)) + 1);
+-------------------------------+
| floor(log10(power(2,32)) + 1) |
+-------------------------------+
|                            10 |
+-------------------------------+
1 row in set (0.00 sec)

So, you would have a TRIE with a maximum height of 10. Starting at the root of the TRIE, if you have the number 4294967295, you traverse branches 4,2,9,4,9,6,7,2,9, and 5. At each branch, you would perform an array-styled binary search.

If the branch located at that TRIE node is an exact match, you can assign a percentage to that level, and recursively walk down that branch to check for the next digit and return percentages from deeper TRIE nodes to add to the percentage you have at the currently searched TRIE node.

If the branch located at that TRIE node is NOT an exact match, you stop your recursive search there and return either 0 or some other percentage you may want to designate.

Given the sum of return values from all searched TRIE nodes, you may want to sum up the
percentages and divide that answer by the length of the string. In other words,

Pct per Node = (1 / (Number of TRIE nodes that need to be searched)) or Zero(0).

Sum(Pct) = (Number of TRIE nodes exactly matched) / (Number of TRIE nodes that need to be searched [length of the string being searched]).

Given the length of the numberic field you store, you have O(log n) due to field length. For each TRIE node, you have O(log n) for searching for the proper branch. Overall, your search should have O(log (log n)) search time.

This performance stands out ever more if the field is alphanumeric. Assuming using only ASCII, each TRIE node would have 256 branches. The height of the TRIE would depend on the length of the character field. Representing this TRIE for variable-length strings would produce TRIE nodes that would be very sparse, but quickly searchable nonetheless.

Regardless what database you use, carefuly plan the data types you will be using to represent the TRIE node. You may also want to partition the table so that strings of length n terminate in partition n. Thus, you will have O(log n) search time at each partition.

http://en.wikipedia.org/wiki/Trie

http://www.eecs.harvard.edu/~ellard/Q-97/HTML/root/node24.html

http://www.webreference.com/js/tips/000318.html

http://en.wikipedia.org/wiki/Radix_tree


Optimize user segmentation by bytea column First i would too explain my problem:
I have around 10 millions of customers in the table. Based on customer attributes, we are creating segments.
My table what joins customer with segments looks like

customer_segmet(customer_id int, segment_id int)

Every 3 hours we have a cronjob, what refreshing segments and reinsert them again to this table.

Each refresh means, delete all customers from segment, and insert again.
Deleting customers are fast enough, but i have problem with insert. Inserting 10mln of rows to this table takes 20 minutes. It is too much and im trying to resolve this problem.

Idea:

I'm thinking to optimize inserts. My idea is remove customer_segment table and instead of this, create "customers" column in segments table. This new column will be bytea type.
To store information if user is in segment, i will generate each time binary file where:

Bit position = customer_id
Bit value = is customer in segment or not.

For my calculation looks like for 10mln of customers i need 10mln of bits what is 1.25MB only. So default for this column will be 1.25mb data of nulls.

After that if i want to add only one customer to the segment, i will execute query:

UPDATE segments SET customers = set_bit(customers, 555, 1);

where 555 is my customer ID.

If i want to check if customer is in the segment, i can do it by query:

SELECT COUNT(1) FROM segment WHERE get_bit(customers, 5555) = 1 AND id = 1

Also to simplify the joins between Customer-Segment i created function:

CREATE OR REPLACE FUNCTION get_segment_customers(segment BYTEA) RETURNS SETOF integer AS
$$
for (let i = 0; i < segment.length; i++) {
    if(0 === segment[i]){
        continue;
    }
    for(let bit = 0; bit < 8; bit++){
        if(!(segment[i] & ( 1 << bit))){
            continue;
        }

        plv8.return_next((i * 8) + bit);
    }
}
$$
LANGUAGE plv8;

And my query to select customers:

select
_c.* from
    get_segment_customers((SELECT data FROM segments where id = 1)) _t
JOIN
    customer _c ON _c.id = _t;

I made test and is fast.

Only about what im scared is query:

UPDATE segments SET customers = set_bit(customers, 555, 1);

I'm not sure, if PostgreSQL each time of this update will overwrite full file or only one bit in the hard disk?

If someone can answer question above and give the feedback about idea and potential problems, i will be happy.

Thank you

I'm not sure, if PostgreSQL each time of this update will overwrite
  full file or only one bit in the hard disk?

PostgreSQL will definitely rewrite the whole column on any change, as it cannot do in-place updates, let alone in-place intra-column updates.

But you could break down the segment into fixed-sized sub-segments or partitions. For instance with segments of 2^16 bits, you would at most rewrite 65536/8 = 8192 bytes per update. Personally I've been using this for custom full-text indexing (one vector per word and document-IDs used as bit numbers) and have found this one-bit-per-match-inside-bytea structure very efficient overall when segmented like that.

Also bytea in TOAST storage is compressed by default, so if there are large portions of identical bytes in the vectors, they're going to be stored in less bytes than the nominal size.


Should i place Registered-Users Info in every table? to Restrict user access to his Own Data only Database: Relational Database - MySQL

I am making a database with multiple tables. In order to see his data from tables, User will need to log in. I only need to display those information(through web-pages) from the Table which only belongs to him,  so he wont get to access data of other users.

So, should i place Registered-Users Information in every table?  just so when he wants to see data, i will "SELECT data from TABLE_ABC where (login_id = Id_of_current_user_log_ined )". I will put this condition in every query so he will only be able to access his/her own data and not someone else.

I made a Relational Schema but only 1 table inherits Login-Information from Login-Table while 3 of the tables don't have information on WHO does the data belong to?

For table-level access restrictions:

GRANT SELECT ON tbl1 TO 'user'@'...';
GRANT SELECT ON tbl3 TO 'user'@'...';

Repeat as needed for each combination of user and table.

If you have a newer version of MySQL, see if "Roles" provides a simplification.

For row-level access restrictions, do not let the users query the tables.  Instead:

Provide an app that they contact; it checks permissions before preforming (or not) the desired query.
Use Stored Procedures.  See also the function CURRENT_USER()
Use a VIEW -- then grant access to the view but not the table; have the view check whether the user is allowed to see the row.  See OP's comment.


How to limit the range of an insert-update to only look at certain rows/ rows with field value: X? I posted something similar before, but am still unsure of the technical implementation --- I'm so close to being there to the finished product.

I have a table of tickets, and a stream of ticket updates sent to that table in an insert/ update fashion.

The problem is with each passing week, or day, etc ..... the table of tickets gets bigger and bigger, making the daily "stream" insert/update lookup take longer, and longer, and longer.

There is a way around this (I think).

Tickets where status = "closed" will never see an update --- they don't have to be looked up by insert/ update, in other words. They are also the vast majority of tickets.

So my question --- how the hell do I prevent a lookup of these tickets?

At first I'm thinking ... add a key .... the first one is simply update where ticket_id = ticket_id .... I could add another ... status <> "closed". But I'm not sure if checking this, and ticket_id, would actually save time. Would it? Or does it matter in which order the update keys are?

Second idea is to have two tables ... an active table, and archive table. Send all the updates through the active table, and then maybe once a day, copy all "closed" ticket rows from the active to the archive, and delete them in the active table. This definitely seems like a massive pain ... but it's a possibility. All lookups would require a table union as well using this option (or maybe view).

I'm not sure ... what do you guys think? Anyone have better ideas or thoughts? Thanks.

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.


global unique identifier for 2 tables in relational sql There are 2 sub-systems[ suppliers and retailers ] in our online market chain management system . Entry in any of those[ suppliers or retailers ] should give a globally unique ID .There would be listing and filtering of each system based on category , locations , nearest retailers .

FYI : The query for listing retailers or suppliers is already complex which uses lots of joins and unions. So, I don't want another table for handling Unique ID .
There is another option to combine retailers and suppliers entry in 1 table.
But chances are there it may soon run out of entries if I combine retailers and suppliers entry in 1 table

Individual business entities[retailers or suppliers] have there own accounting system ,product management system , cost management system . So its a bit complex system and some of the query operations are already slow.
I have checked the possible solutions but it wouldn't satisfy our needs because of performance reasons and complexity.
What should I do , should I go for NoSQL , would it solve the problem  or Is there any other solution

If your requirement is to have global Unique identified for across Entities, you can go for UUID. For more details refer here


Why does removing these LOWER calls change the execution plan like this? While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the time the query was built dynamically in a stored procedure and the bulk of the slowness was being caused by parameter sniffing, but for a long time I tried to figure out why it was taking so long to sort these rows.

Here's the query:

SELECT Id
 , FirstName
 , LastName
 , FullName
 , DateOfBirth
 , CityStateZip
 , DriversLicenseState
 , DriversLicenseNumber
FROM (SELECT c.EntityId Id
       , p.FirstName
       , p.LastName
       , p.FullName
       , c.DateOfBirth
       , c.UpdateDate
       , CityStateZip = CASE a.EntityAddressId
           WHEN NULL THEN NULL
           ELSE CONCAT(a.City, ', ', s.Code, ' ', a.Zip)
           END
       , ds.Value DriversLicenseState
       , dn.Value DriversLicenseNumber
       , ROW_NUMBER() OVER (
               PARTITION BY p.FirstName, p.LastName, c.DateOfBirth
                    , ds.Value, dn.Value
               ORDER BY c.UpdateDate DESC) RowNum
  FROM Store.Customer c
  INNER JOIN Entity.Person p ON c.EntityId = p.EntityId
  LEFT JOIN Entity.EntityAddress a ON c.EntityId = a.EntityId
  LEFT JOIN Vendor.StateProvince s ON a.StateProvinceId = s.StateProvinceId
  LEFT JOIN (
         SELECT ca.CustomerId, ca.Value
         FROM Store.CustomerAttribute ca
         INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId
         WHERE a.AttributeCode = 'DriversLicenseState') as ds
             ON c.EntityId = ds.CustomerId
  LEFT JOIN (
         SELECT ca.CustomerId, ca.Value
         FROM Store.CustomerAttribute ca
         INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId
         WHERE a.AttributeCode = 'DriversLicenseNumber') as dn
             ON c.EntityId = dn.CustomerId
  WHERE LOWER(FirstName) LIKE '%bob%'
    AND LOWER(LastName) LIKE '%smith%'
    AND a.EntityAddressTypeId = 0 ) c
WHERE RowNum = 1
ORDER BY UpdateDate DESC

And here's the relevant part of the execution plan:

Notice the large disparity between the estimated and actual number of rows.

If I remove the 2 LOWER calls from the inner WHERE clause and change nothing else, the execution plan steps are identical, but the cost of the steps are different. The same bits of the execution plan:

I cleared the plan cache between executions. Is this a bug?

SQL Server maintains statistics on substrings in string columns in the form of tries that are usable by LIKE queries.

See the String Summary Statistics section for more about this.

Wrapping the column in a function call quite likely blocks any attempt to sniff the value of the parameters and use the statistics on the column to estimate the number of matching rows and so it falls back on guesses. The differing cardinality estimates can cause different execution plans.


POSTGRESQL: Using Values List in Delete Based on the PostgreSQL Documentation it seems possible to use VALUES list as argument in Insert/Select/Update.
Is it possible to use it as argument in DELETE something like:
delete from some_table where (col1, col2) in values(......);
??

You don't need the values keyword when you use IN
delete from some_table
where (col1, col2) in ( (1,2), (3,4) );


Backup from dacpac file extracted using SSTD I tried method given in this answer by Ramankant Dadhichi but deploying failed.
Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server
 I have my database in managed instance. and i extracted dacpac using SSTD but now when i try to deploy extracted file using SSMS. I am getting following error:

Could not deploy package.
Error SQL0: The element [releaseengineer] cannot be deployed. This element contains state that cannot be recreated in the target database.
Error SQL0: The element [Reporter] cannot be deployed. This element contains state that cannot be recreated in the target database.
 (Microsoft.SqlServer.Dac)

Have a look at the commentary here. Has the login been created on each instance using the same SID?  If not then this might be the cause of the issue.


SQL Server 2008: Unused Clustered Index I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy.

It contains Tweets, and the id of each Tweet is one of the fields, but it is not used as the primary key, instead it has a non-clustered index on it & there is a separate field for the primary key "localPkId" (also the clustered index) that is just an identity and isn't actually used anywhere (so all other tables will still join on the Tweet ID, ignoring the "localPkId" field).

The Tweets that get inserted will come into the system out of order, so by not using Tweet ID as the clustered index, it means new rows will always get put at the end, speeding up inserts.

This had been working fine for some time as the table had a predominantly write heavy workload, but now it's being used for a lot of reads as well and I'm concerned that as the workload continues to grow this will become a problem.

First question: Does having "localPkId" offer any advantage over the table being a heap/having no clustered index?

Second question: Should the Tweet ID be the Primary Key & Clustered Index & then set a low fillfactor? If so, how would I go about choosing an appropriate value for the fillfactor in a case like this?

But you have an index on TweetID.  Is that index not used by the joins?

Even a non-clustered index will fragment with insert and update.  As that index fragments inserts take longer.  How are you managing fill factor and index maintenance on this non-clustered index?

An unused PK does nothing.

TweetID as PK will save some space.  What % of rows get added daily.  If only 1% rows get added daily you could use a fill factor of 90% and defrag daily or weekly.  Start with like 80% or 90% and see how fast it fragments.

If the TweetID are out of order but in the same range then the problem you have there is fill factor gets hammered in that range and unused in the rest of the range.  But a defag is faster as it is only cleaning up that part of the range.  In that case you may be better off with 100% fill factor and a regular index reorganize.

There is no cut and dry answer.  You need to manage and monitor the index for your data and your workload.


Postgresql - how to drop a trigger A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger.

The following queries don't work:

drop trigger update_user;
drop trigger update_user on users;

I get the following error back:

ERROR:  syntax error at or near ";"

How can I drop this trigger?

I made a connection using pgadmin, and then dropped the trigger. The problem was I can only use ssh to connect to the server, and I didn't know how to make that connection at first. I've just found a way to tunnel the connection via ssh.

ssh -fNg -L 5555:localhost:5432 {username}@{domain.com}


Can insert on tables with FK impact operations on other tables that have FKs to the same table? Let's assume that we have the following tables on an InnoDB database:

user (id_user, name)
log (id_log, id_user, info)
message (id_message, id_user_from, id_user_to, message)

The id_user column on log and id_user_from and id_user_to on message tables are FK to user(id_user).

As I understand it, when we make an insert on log table mysql will create a shared lock on the related record on user, if exists. If another transaction try to get a shared lock, it will works fine. If  another transaction try to get a exclusive lock, it will have to wait until the first transaction finishes.

My question is: If another transaction try to make an insert or update on message referencing the same record on user it will be impacted by the insert on log? I don't think so, because this transaction will get a shared lock on the record too, but I read on some old articles (from 10 years ago) that this can be an issue. If it is a case, isolation level has some impact on this? Again, I don't think so.

My attitudes in this area:

Make transactions so fast that there are rarely any conflicts.
Use SELECT ... FOR UPDATE to "lock" rows that I am about to update.  (Unless there is a way to avoid them.)
FKs imply extra checks.  These take time.  And the checks are usually redundant.
I don't use FK CASCADE since I like to see all the interactions in the same code -- namely the code between BEGIN and COMMIT.
I don't use non-cascading FKs; I debug my code instead.
I do use INDEXes and I want to know exactly what indexes exist and not be surprised by extra (redundant?) ones created by FKs.
Do check for errors after every SQL statement.  These can be surprise deadlocks.
Check for errors even after COMMIT.  This is in case the app is ever migrated to Galera or InnoDB Cluster.
Restart at the BEGIN if there is a deadlock.
Beware of "burned" AUTO_INCREMENT ids.  (That's a lengthy discussion of its own.)

Back to your question.

There are cases where InnoDB takes a stronger (eg, X vs S) lock than it needs to.  This may be because of an edge case that is too costly to check for.  Your example may (or may not) be an example of such -- I don't have enough brain cells think it through at the moment.  I go back to my first bullet item -- make the transaction fast enough so the problem goes away.


Import large .sql file to Postgres I need to Import a large data-dump.

The structure is already created. But the data-dump is 16GB large.

If I try it simple with

psql -d "DATABASE" < "datadump.sql"

I get a out of memory Message on Ubuntu DB.

i have no idead how to import.

The DB-Server has 16GB Ram, 50GB Disk-Space free (its a developer vm)

Does anyone has a idea, how to import this file?

UPDATE

My postgresql.conf

# - Memory -

shared_buffers = 4GB            # min 128kB
                    # (change requires restart)
work_mem = 1048kB           # min 64kB
maintenance_work_mem = 2GB      # min 1MB

checkpoint_segments = 128       # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min      # range 30s-1h
checkpoint_completion_target = 0.9  # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s       # 0 disables

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Cost Constants -
effective_cache_size = 12GB

wal_buffers = 16MB

The inserts are single-row and all for one table.
The Table has 71 columns.

I dont know, whats in line 1447540. No Texteditor can open this file -.-
(But the content of the file is correct. After spliting the file into much smaller files, the imports run successful.)

The first screenshot shows the free -ht output before importing. The second screenshot was taken 2 seconds before the out of memory messeage comes up.

It's clear that the issue is related to stdin. If you have control over the dump process, a possible solution is to do not execute the .sql file from psql. To do so, you simply have to run the following two queries:

Execute this from the original database: COPY (SELECT * FROM mytable WHERE ...) TO '/storage/mytable.dump'
Execute this from the destination database (the one where you have the out of memory issue): CREATE TABLE mynewtable ( ... ); COPY mynewtable FROM '/storage/mytable.dump';.

You can mess around with the various parameters available in COPY. For example, you can export a gzipped file or a CSV with headers and custom separator: https://www.postgresql.org/docs/current/static/sql-copy.html .

You can also issue a pg_dumpall > /storage/db.out to export the entire original database and run psql -f /storage/db.out postgres to recreate it on the new database: https://www.postgresql.org/docs/current/static/app-pg-dumpall.html . This should work.. I've just dumped an output file of several tens of GB on OS X (linux-like) without any problem.

Se also pg_dump for more selective database dump options: https://www.postgresql.org/docs/current/static/app-pgdump.html


Postgres DB Timezone Change Can You please guide us on how we can change the time zone of the Database to UTC which is migrated from AWS which was in time zone UTC? Now the Postgres 12 is installed in Ubuntu 20 which shows (show timezone; Asia/Kolkata ). We also want to make sure all the existing data inside the database should be changed to the new timezone. Because It is creating issues for the operation team to query on DB as all other DB in the env are in UTC.

You can change the parameter by editing postgresql.conf or with ALTER SYSTEM via SQL. Don't forget to reload the database.
But you should tell the application development team that they have messed up. If the application relies on a certain setting of timezone, then the application has to set that parameter itself. The parameter can be changed using the SET SQL statement.
If you don't care about time zone handling by PostgreSQL at all, you should use the data type timestamp without time zone.


MySQL Computed Column counting other rows with attributes relative to this one I have a table in a MySQL database that has information about passenger flights. I want to do some analysis of flight delays. I would like to add a calculated attribute to each row that indicates how many flights arriving at the current row's departure airport, by the same carrier, were delayed in the past 24 hours.

I haven't had any luck writing this query. I think that I can express my intent with:

-- Find a count of all rows
    count(*) where
-- Of the same carrier
    a.FlightCarrier = thisRow.FlightCarrier
-- that arrived at our departure airport
    a.DestinationAirport = thisRow.OriginAirport
-- scheduled to arrive before ScheduledDepartureTimeUTC
    a.ScheduledArrivalTimeUTC < thisRow.ScheduledDepartureTimeUTC
-- scheduled to arrive after (ScheduledDepartureTimeUTC - 24h)
    a.ScheduledArrivalTimeUTC > (thisRow.ScheduledDepartureTimeUTC - 24h)
-- had a delayed arrival
    a.DelayedArrival = 1

I attempted to write this as a SQL statement:

alter table flight add column DelayedCarrierArrivals24H int as
(select count(*) from flight a where
 a.FlightCarrier = FlightCarrier
 and a.DestinationAirport = OriginAirport
 and a.ScheduledArrivalTimeUTC < ScheduledDepartureTimeUTC
 and a.ScheduledArrivalTimeUTC > (ScheduledDepartureTimeUTC - (60 * 60 * 24))
 and a.DelayedArrival = 1) STORED;

However, MySQL balks at this with "You have an error in your SQL syntax... near 'select count(*) from flight a where a.FlightCarrier = FlightCarrier"

What's the 'right' way to create this column in MySQL? I had originally thought to use windowing functions but couldn't divine the right way to express my intent with those features, either.

The problem
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html says:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.
...
Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.
...

Your expression includes a subquery.  Note that the error message pointed directly to the subquery:  "... near 'select ..."
Generated columns are rather limited (for good reasons).
Suggestion 1
May I suggest that you do the computation when you are fetching data from the table.  Consider writing a VIEW or Stored routine to hide it from the caller.
Suggestion 2
Perhaps this comes closer to what you are looking for?
SELECT  b.*,
    (
        SELECT  count(*)
            from  flight a
            where  a.FlightCarrier = b.FlightCarrier
              and  a.DestinationAirport = b.OriginAirport
              and  a.ScheduledArrivalTimeUTC < b.ScheduledDepartureTimeUTC
              and  a.ScheduledArrivalTimeUTC >= (b.ScheduledDepartureTimeUTC - (60 * 60 * 24))
              and  a.DelayedArrival = 1
    )
    FROM  flight AS b;

It won't be efficient due to the correlated subquery, but we can work on that.  First, verify that it does what you need.
Suggestion 3
Better would be to have a column for that info, then do the computation separately as you are storing the b information.
INSERT INTO flight ( ..., DelayedCarrierArrivals24H)
    SELECT ...,    -- the values from your source
        ( SELECT ... ) AS DelayedCarrierArrivals24H;