texts:
I have a query like this:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '29584'
        OR ord.ClientOrderId = '29584'

The idea is that we are searching using the value '29584' and we don't really know if it is a ClientOrderId or an Identifier, so we want to search both.

This gives really really bad performance.  It pulls millions of rows (125 thousand from one and 20 Million from the other)

But this query:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ord.ClientOrderId = '01193504'

UNION

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '01193504'

has good performance.  Normally I would just do this and call it a day.

But my actual query is very large, and I would rather not call it twice (though it is still better than using the "or".)  So before I just set it up to be called twice, I thought I would ask around and see if I have other options.

It seems I should be to do some kind of left join to make this work.  But I can't seem to wrap my head around it.

Is there a way to do this in one query that has a left join instead of an "OR"?  (Or some other kind of option?)

UPDATE:
OK, I got thinking about it and I realized I could just factor out the OR into a CTE that uses the union and then use the CTE the actual query.

I will leave this open in case there is a better way to deal with it, but a CTE fixes my issue.


texts: Using SQL Server 2012 for Business Intelligence Features Only, No Database Engine

texts:
I would like to implement SQL Server 2012 purely for the Business Intelligence Features: SSIS, SSAS and SSRS on its own server.

I current have SQL Server 2008R2 Database Engine installed on another server. It is maintained by a vendor, so it cannot be upgraded to 2012.

From what I understand within SQL Server 20102 Business Intelligence tools, you are able to use 2008R2 databases as database targets/sources. Ideally, I would like to be able to create 2012 BI Projects: SSIS, SASS, and SRSS and deploy the solutions to Sql Server 2012, but transform the data in the 2008R2 database.

Is this setup possible? If so, other than licensing are there any drawbacks or potential problems?


texts: How to export base and discover its structure

texts:
We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without leaving anything on the database that he created.

I already searched the site but couldn't find a concrete answer for my trouble or I wasn't searching the right way.

This means that we do not have the schemas, tables with data types for each field, triggers, connections with other tables and bases.
For this same problem I would like to know if it is possible to export from TOAD (the application I use to connect to the base and edit information BUT I can use another one if you tell me) in which you have the tables with names, the fields with the data types, schemas, triggers, connections between tables and other bases.

It's an Oracle Database.

I appreciate all the help you can give me, it's very important to us because we asked for a DBA but it will take long for the company to hire one.


texts: Does mysql "lock" (no other process can take from it) the memory allocated to it?

texts:
I have a 64GB memory machine, and I set mysql buffer pool to be 40GB. So MySQL is using up to 40G memory while it's running.

I kicked off a java application that requires more than 20GB memory, so the java application starts to consume more and more memory and eventually the total 64G memory is not enough for both java and mysql.

Now, the interesting problem shows: mysql crashed (see graph here). But to my understand, shouldn't mysql stays the same, and instead Java crashes? Since the 40G memory is already allocated to mysql, the java application shouldn't take it away right?

Thank you


texts:
The answers and discussion on questions like this one talk about folded vs pivoted tables.

eg.

  The only difference between a paper presentation and a sound
  electronic presentation is that a folded table structure is more
  convenient in the latter case while a pivoted table structure in the former...

I couldn't find anything on Google about what those terms mean (Google gave results about protein structures).

Could someone please explain what those terms mean with examples?


texts: MongoDB + Compass exporting strings and numbers "columns" with value of 1 as "true"(boolean) and 2 or null as "false" to CSV

texts: When I export my MongoDB collection to a CSV, the String and Number "column" data types values are converted to "true" for 1 and "false" for 2 and null - how do I retain the values as numbers, strings and integers on export to CSV from Compass?


texts: Is there a better alternative to lookup tables to deduplicate text columns in PostgreSQL?

texts:
My database has VARCHAR columns each containing 20k-60k distinct short texts. It is not feasbile (due to space constraints) to store these values in each row, they need to be externalized somehow. These values are never updated.

The classic approach would be to replace each of these columns with a separate lookup table and corresponding id - this however requires a lot of joins and a lot of similar tables.

Enums don't seem to be feasible for this problem, although they would remove the need for explicit joins: They cant be longer than 63 bytes and it is impossible to add new values in transactions until version 10.

I have looked in the documentation for CREATE TABLE - there seems to be no built-in support for this kind of deduplication. TOAST-Tables don't seem to contain unique values either.

Is there another approach I have missed?


texts:
mysql> SELECT * FROM customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+

mysql> SELECT * FROM purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+

So i have two tables and want to see a column with a customer name then a column with total number of dogs purchased followed by a column for cats and this is what i came up with

mysql> SELECT c.name, sum(dogs.quant) AS dogs, SUM(cats.quant) AS cats
    -> FROM customers AS c
    -> LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog'
    -> LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat'
    -> GROUP BY c.name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John | NULL |    5 |
| Matt |    3 |    8 |
+------+------+------+

Can someone explain why Matt has 8 cats when i expect it to be 4? This is a boiled down version of my problem


texts: Minimum distance of a location; one table of points compared to a set of points SQL 2012

texts:
I am trying to find the quickest way to find the minimum distance of one point to a table of points.  The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K single points.

Or better explained Table A has 150K rows/points, Table B 1500 points.  I want to know for every row in Table A what is the minimum distance from all those listed in Table B.

I have a function that does the distance calc, as an added column to the Table A.  It just takes a VERY LONG time.  Table B has a spatial index.

This is what I have:

select a.*,
       dbo.fxn_distance(geography::STPointFromText('POINT(' +
       CAST([Long] AS VARCHAR(20)) + ' ' + CAST([Lat] AS
       VARCHAR(20)) +    ')', 4326)) as DistAway
from Table A a

my function:

create function fxn_distance
(@pointTableA geography
)
returns float
as
begin

declare @distance float

select top 1 @distance = b.GeoLocation.STDistance(@pointTableA)
from TableB b
where geolocation.STDistance(@pointTableA) is not null
order by geolocation.STDistance(@pointTableA)

return @distance
end

Sorry if I am totally a newbie to this, and I know the solution is probably simple, but I just can't wrap my head around this.  So to hopefully clarify: I need to pass the lat/long of every point in Table A and see what the min distance is compared to every row in Table B, for every row in Table A.  I care what the actual distance away is, but not the actual point from Table B.

Any help appreciated.


texts: Breaking out of a recursive query in Postgres 11

texts:
I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query.

I have this table of domains:

create table domains(
    id bigint,
    domain_name varchar,
    parent_id bigint,
    valid boolean
)

insert into domains values
    (1, 'example.com', null, false),
    (2, 'a.example.com', 1, true),
    (3, 'b.example.com', 1, false),
    (4, 'c.b.example.com', 3, true),
    (5, 'd.a.example.com', 2, true)

This gives this tree, with the valid domains in green:

Based on this, I want a.example.com and c.b.example.com to be returned as valid domains. Had the top level domain example.com been valid, only that should be returned. ("valid" is just a flag determined elsewhere.)

I have a recursive query that fetches the whole domain tree:

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT * FROM valid_domains

...but then my stumbling block is figuring out how to filter on valid domains and break that branch when I hit one that's valid, without returning any valid domains below it.

I've looked at window functions, but haven't been able to mesh that with the recursive query thus far. Am I taking the wrong approach by making this recursive in the first place?


texts:
How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL?

Using this query, I can find duplicate rows:

SELECT * , COUNT( awb_no ) c
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING c >1

And this query gives me the maximum ID for each group:

SELECT * , MAX( tbl_cod_rpt_id )
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING COUNT( awb_no ) >1

Now how would I delete the tbl_cod_rpt_COPY rows matching the MAX( tbl_cod_rpt_id ) values from the query above?


texts: phpMyAdmin Strange permissions #1142 error after deleting a table record with success!

texts:
A have a live/production CentOS 6.8 server with Nginx, PHP (multiple versions), MySQL 5.7.18 and latest (when writing this question) phpMyAdmin 4.7.0.

This server is running for many years and with no problems, but recently I noticed a problem when I tried to delete a record from a table using phpMyAdmin, I got the following error:

  #1142 - SELECT command denied to user 'test_user'@'localhost' for
  table 'pma__tracking'

Strange thing is that it deletes the record but it can't refresh the result after the deletion! Also, all SELECT queries work and every data browse successfully fetches data on each table!

I have each database to has its' own user with all privileges granted.

I have properly installed $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] and all the tables exists inside $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; database.

My question is, why this is happening and how can I fix it? It's strange for me, because I expect that the user defined inside phpMyAdmin config should access pmadb ('pma'@'localhost') and the tables in it and NOT the user that connects to a database!


texts: Why MS SQL Express cannot find my local Windows user?

texts:
First of all: My Images use german as a language, sorry for that but maybe you find the problem though.

So I am setting up a SQL Database on a MS SQL Server 2016 Express.
While doing so I am using SQL Server Management Suite on Windows Server 2012 R2.

I established a connection to the server than clicked on "Security -> Logins -> RMB -> New Login". In the displaying picture I am chosing Windows-Authentication and add the local user I want to use.

But after I managed all settings and clicked on okay I get following error (which says that the user cannot be found which is weird because in the screen before I searched for it and was found with no problem.

error msg

I also checked the settings for the server connection (rightclick on the connection -> properties -> security -> SQL-Server and Windows-authentication)

Do you know why I it cannot find the user?

EDIT:
ErrorMSG in English
errormsg trying to add login via cmd


texts: Faulty oracle11g regexp_replace on removing duplicates

texts:
I have a comma separated list of values (as one single string), which contains duplicates, and I want to remove them. I tried with REGEXP_REPLACE but for certain values it gives me a wrong result:

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual
union all
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual

Running the above example, will provide the expected result for the first query, but for the second one, it just concatenates all the values without removing duplicates. I guess the fact that the values are starting with 1 has something to do with it.

Can you please help me out with the regex pattern? I cannot find the mistake...

Thank you!


texts:
Let's say I have a table of 4 columns: A, B, C, D. A primary key is on A, B, C.

Index1: is A, D, B, C in that order
Index2: A, B, C, D in that order
and the primary key index: A, B, C

There are queries selecting different columns in different numbers and order. I don't all the queries that hit the table.

Are some indexes here redundant?

I collected some scripts from the web for finding redundant indexes and they don't return the same results for the database. I guess every author has their own interpretation of what constitutes a duplicate or redundant index.


texts: How to fetch the columns of the synonyms for linked servers in SQL server

texts:
I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote server.

Synonym used- CREATE SYNONYM [dbo].[test] FOR remoteserver_name .[database_name].[schema_name].object_name

Requirement

I need to fetch the columns of the synonym.

I can retrieve the columns of the synonym using below query in SQL server.

select * from dbo.test

where

dbo is synonym created schema name (local server schema name) and test is synonym name.

I have tried to retrieve the columns of the synonyms using below query, but I cannot fetch the columns

SELECT sys.schemas.name   AS SCHEMA_NAME,
       sys.synonyms.name  AS view_name,
       sys.columns.name   AS COL_NAME,
       sys.types.name     AS data_typename
FROM   sys.columns
       INNER JOIN sys.synonyms
            ON  OBJECT_ID(sys.synonyms.base_object_name) = sys.columns.object_id
       INNER JOIN sys.schemas
            ON  sys.schemas.schema_id = sys.synonyms.schema_id
       LEFT OUTER JOIN sys.types
            ON  sys.columns.system_type_id = sys.types.system_type_id
WHERE  sys.types.system_type_id = sys.types.user_type_id
       AND sys.schemas.name = N'scehmaname'
       AND sys.synonyms.name = N'synonymname'

Can you please suggest some other way to retrieve the columns of the synonym or correct the above query?


texts:
In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment (mySQL 5.7), the same queries take a fraction of a second - due to using the indexes.

Rather than immediately upgrading from mySQL 5.6.40 to 5.7 on RDS (assuming the optimizer simply works better!), I wondered if there was something we're missing as the queries appear straight forward... I'm a bit surprised the optimizer doesn't pick the indexes.

Example query:

select id, username, agent, app_instance_id, end_time, event_time,
last_modified
from event where username='joebloggs' and app_instance_id<>'ad69469a-
d73f-4b33-a1c5-bde03c765d89' and last_modified>'2018-08-20 20:14:51.394'
order by last_modified asc limit 20

Execution time:

mySQL 5.6.40: 6m+
mySQL 5.7: 0.02s

EXPLAIN:

On mySQL 5.6.40 EXPLAIN shows that the optimizer doesn't use any of the indexes (5th column 'possible keys', 9th column 'extra')

'1','SIMPLE','event','range','last_modified,username,appinstanceid,user_app_last','last_modified','6',NULL,'1128956','Using where'

Whereas on mySQL 5.7 EXPLAIN shows the optimizer picks an index.

'1','SIMPLE','event',NULL,'range','username,last_modified,appinstanceid,user_app_last','last_modified','6',NULL,'1030540','0.54','Using index condition; Using where'

Long story short, our RDS instance used to run mySQL 5.5 and all worked fine... but with a forced upgrade to 5.6.40 due to end of life on 5.5 this problem has bubbled to the surface.

I've also run ANALYZE TABLE to ensure stats etc updated for optimizer. Any insight would be greatly appreciated. Thanks!

UPDATE:

Our client app is using JPA, which can be configured to use index hints. Is it simply that the query optimizer is better on 5.7 and that we should upgrade, or should we be providing hints after all.. and not relying on the query optimizer?
After running further tests on a new TEST instance (mySQL 5.7.23 upgraded from 5.6.40) on RDS it appears to be hit and miss on the same query as to whether the optimizer chooses to use indexes.


texts: How to know the current log sequence number in 5.0.X

texts:
I would like to test the optimal value for iblog files on my mysql which is 5.0.77
I found the below procedure in mysqlperformance blog:
    http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

But on mysql 5.0.X, I can't see the log sequence number under "show innodb stauts" output.
Shall some one please let me know a way to know current log sequence number in mysql 5.0.x


texts: Decision Tree English Rules and Dependency Network in MS SSAS

texts:
I created a Decision Tree model in Microsoft Analysis Services (SSAS, Visual Studio 2010). There are two tabs in the Mining Model Viewer tab: (1) Decision Tree that shows a tree itself, and (2) Dependency Network that shows the chart of most important variables.

On the Decision Tree tab I can click on each individual leaf and see the English Rule for that leaf. Is there a way to get ALL the rules at once as a list with the case numbers?

The Dependency Network tab has a slider that you can move to see which variables influence the decision tree most. Is it possible to get ALL important variables as a list with their "importance" number?


texts: How to execute multiple postgres script from command prompt

texts:
I have 100 script files which has insert scripts for the table. How can I execute these?

Scrip1.sql , script2.sql, script3.sql .... script100.sql

To execute single file, I am using the below command. But how can I execute all files?

psql -d DBName -p 5444 -U schemaname -f P_Scrip1.sql  2> err.log


texts: I'm using Postgres 9.5 on Ubuntu 14.04.  Even after using Postgres for a year, I can't get used to typing \q to exit my command line session.  My fingers just want to type "exit" or "quit".  How can I create an alias that does the same thing as \q in my Postgres command line session?


texts: SQL 2008, how does it link Full Text Catalog to a filegroup

texts: While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?


texts: How to take partitions or filegroups out of service [migration]

texts:
I'm an experienced SQL Server DBA but new to partitioning, and a had a couple of questions. Using SQL Server 2008 R2 Enterprise Edition.

I've inherited a large customer metrics database that grows by about 10 GB per day. This database currently consists of one large data file in one filegroup (PRIMARY). All tables have a datetime column called [InsertedDate]. I want to horizontally partition the data by [InsertedDate], using a separate data file for each calendar week.

In a test environment I added the required additional filegroups and data files to this database, put a clustered index on [InsertedDate] in each table, and set up the partition function and partition scheme. By querying sys.partitions and other system tables, I've confirmed that the data is now physically residing in the correct partitions and data files.

Among others, the goals are:
- Decrease backup time by only backing up the PRIMARY file group and the file group for the current date range (I'm currently running nightly full backups). Once a date range is in the past, that partition will never be written to again, so I'd like to set the file group to read-only and back it up one final time.

Be able to eventually take a partition "out of service". After 3 months there's no longer a need to keep older data online, so I'd like to take take that data offline (but be able to bring that data back online again if necessary).

Questions:
1) How to I perform backups on this partitioned database without having to back up the entire database? I can back up an individual filegroup, but to restore it requires the rest of the database, which defeats the purpose of using multiple smaller data files.

2) How to I take a partition out of service? I've read about switching, but it seems that only works if you want to move data between partitions within the same database. I want to be able to simply and safely take a range of data offline (and bring it back online of necessary).

Thanks,
- S


texts: Can I perform a transaction across two different MySQL databases on two different servers in different tables

texts:
I have a table A in database db1 in server A and table B in database db2 in server B, both of which are MySQL.

Is it possible to make a transaction, where updates in both tables are involved?

                      +-> Server A | db1 | table A -+
Transaction:  Update -+                             + -> COMMIT / ROLLBACK
                      +-> Server B | db2 | table B -+

If so, kindly provide step by step instruction on how to achieve this.


texts: MySQL change user login from one plugin type to another

texts:
I'm in the process of testing MySQL 8.0.

I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7.

So to do this I run:

/*ON MySQL 5.7*/
SHOW CREATE USER `username`@`1.2.3.4`;

This gives me:

CREATE USER 'username'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*1BDAB0F57D8247DF5EFE4927F144138B3B7F2C6B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

I run this on MySQL 8.0, and it creates a user. I check I can log in fine. However, the default in MySQL 8.0 is to use the caching_sha2_password plugin for authentication. So how can I upgrade my login to use the new plugin?

(n.b. this is my login, but at some point I will need to move all my colleagues logins, so assume I don't know what the pasword is).

I have looked all over the web, and found lots of docuemntation about the plugin, but none that say how to convert from one type to the other (apart from delete the user and start again, but that's no use if I don't know the password).


texts: Copy a view from one database to another database on the same server

texts: I want to copy a view from one database to another database, on the same server. Both databases are structured exactly the same, except for about 8 views added in one.


texts: Online index rebuild higher fragmentation on intermediate level

texts:
I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on leaf level.
I noticed when I'm running the statement with Online=on the intermediate pages (level 1) of my index are higher fragmented as before (89% in stead of 3%).

The intermediate pages only get defragmented when I'm setting MAXDOP=1.
With the options SORT_IN_TEMBP=ON,ONLINE=OFF the level 2 fragmentation jumps from 0 to 100.

This are the statements that caused an increase of fragmentation on level 1:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on,maxdop=1)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=on)

This statement caused the fragmentation on level 2 go from 0 to 100 but level 1 stays the same:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=off)

Is fragmentation on intermediate pages something to worry about and what is causing the increase in fragmentation?


texts:
I have a table with ~300k records, and query with join other table. A query executed about 2 minutes. But! Yesterday I was backup and restore this database and saw, my query faster 10 times! From >100 seconds down ot ~20.  I cant understand, how?
MySQL database 8.0.


texts: How to enforce "at most N child rows" in a foreign key relationship without a trigger?

texts:
I have a table that I will be using to save "template" messages for my users.  The schema is

CREATE TABLE listemailtemplates (
    id int primary key auto_increment,
    userId int not null,
    message text,
    date datetime default current_timestamp
);

The gist of it is, when a user sends out an email to a customer, their message is saved as a template, so the next time they email a customer, they can select it from a drop down.

Right now the functionality required is simple - its not based on how many times a template is used or anything, just the 5 last messages sent.  So if a user has 5 'templates messages', and then decide to craft a new message, the message with the proper userId and oldest datetime gets deleted and the new one is inserted.

I know how I could do this with a trigger.  I want to know is there some other way of handling this via table schema?  Like setting that there can only be 5 duplicate userId's and that new inserts should be handled in such a FIFO way based on date?

Just curious, I get the feeling that there is (based on nothing), but if a trigger is the best way of accomplishing this, then that is fine as well.


texts: How to remove unreferenced rows in a "Many to One" relation?

texts:
in my db i have the following entities: Cities, Persons, Houses.

Each Person MUST live in a City.
Each Person CAN live in a House.
Each Person can change or leave their Houses.
Persons living in the same house must also live in the same city.
Empty Houses must be destroyed.

Here's the schema:

How can I make sure that Persons living in the same House they also live in the same City?
How to destroy empty Houses? Is there a solution other than triggers?

I was thinking about a different approach:

I know it seems a stupid solution but i can use ON DELETE CASCADE on the House, so no House will remain unreferenced after City removal.

What do you think is the best way to solve this?

Thanks in advance an sorry for my english.


texts: Speeding up a GROUP BY, HAVING COUNT query

texts:
I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on "groupingsFrameHash". I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.


texts: Is it possible to dynamically retrieve the number of columns in a view in Oracle?

texts:
As the title says, is it possible to retrieve the number of columns in a view dynamically?

I'm learning Oracle 11gR2 and I'm checking out the V$ views. One of the things I want to do is output them to file (for various reasons I am unable to spool the document) so I was going to output using java, however I need to know how many columns are in each view - a daunting task with 536 views, and now I'm curious to if I can do this dynamically.

I tried using user_tab_columns but it returns 0 for views.

Note: performance is not key, this is a learning exercise for me, so doing it correctly is more important than doing it quickly (if its even possible)


texts:
When we execute the following command:

DBCC SHRINKFILE('MyDB_log', 1)

We get the following results in SSMS:

DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages
-----|-------|-------------|-------------|-----------|----------------
 11  |   2   |    128      |   128       |  128      |     128

The Question:

How can we create a query that outputs these results to a text file, without using SSMS output window.

I have tried this:

CREATE TABLE #x
(
    [DBID] int,
    FileID int,
    CurrentSize int,
    MinimumSize int,
    UsedPages int,
    EstimatedPages int
)
INSERT #x
  EXEC('DBCC SHRINKFILE(''MyDB_log'', 1)')

SELECT *
  FROM #x

DROP TABLE #x

But I get the following error:

Msg 8920, Level 16, State 2, Line 1
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.

I have also tried the following:

DECLARE @Statement AS VARCHAR(2000);
SET @Statement = 'bcp "DBCC SHRINKFILE(''MyDB_log'', 1)" queryout C:\Test.txt -c -UDBAdmin -P1234 -S192.168.123.123';

exec xp_cmdshell @Statement

I get:

Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column


texts: Oracle 11.2.0.3 Service Name Mismatch issue

texts:
My Environment

During Installation

SID  lab3

Service_Name  lab3.domain.com

Changes

Some time ago I changed my service_name to just lab3 to match my SID

Select instance_name from v$instance;

lab3

Select value from v$parameter where name like '%service_name%';

lab3

Issue

when i try to connect from my application
jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3 doesn't work

jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3.domain.com works while this is not my service name anymore

My application doesn't require any oracle client installed so there is no tons file or anything else on the client side. it's all on server side

Error in Application Logs

java.sql.SQLNonTransientConnectionException: [Mercury][Oracle JDBC Driver][Oracle]ORA-12514 Connection refused, the specified service name (lab3) was not recognized by the Oracle server. at com.mercury.jdbc.oraclebase.ddcr.b(Unknown Source) at com.mercury.jdbc.oraclebase.ddcr.a(Unknown Source) at

lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 06-MAR-2016 20:34:40
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                06-MAR-2016 20:02:45
Uptime                    0 days 0 hr. 32 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\Administrator\product\11.2.0\network\admin\listener.ora
Listener Log File         C:\oracle\Administrator\diag\tnslsnr\Lab3\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Lab3.domain.com)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "lab3.domain.com" has 1 instance(s).
  Instance "lab3", status READY, has 1 handler(s) for this service...

Service "lab3XDB.domain.com" has 1 instance(s).
  Instance "lab3", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE

db_domain                            string      domain.com


texts:
I have the following tables:

// Base Scans
CREATE TABLE `basescans` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    `status_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=29

// Statuses
CREATE TABLE `statuses` (
    `id` INT(10) UNSIGNED NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4

Trying to save the first table fails when I put in that foreign key constraint. Can't figure out why. Both of the columns referenced in the constraint have the same type, size, etc:

INT(10) UNSIGNED NULL

They only have a difference default value. One has a default value of NULL, the other is AUTO_INCREMENT.  I didn't think that made a difference for foreign key constraints but I could be wrong.

Both tables are InnoDB and UFT8. What am I missing here?

UPDATED: My specific error:

/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`db`.<result 2 when explaining filename '#sql-31c2_22ac1e1'>, CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) */


texts:
So when I try implementing this query:
BCP -SMSSQLSERVER01.[Internal_Checks].[Jan_Flat] format out  ^
-fC:\Desktop\exported data\Jan_FlatFormat.fmt -c -T  -UUGmn91 -SMSSQLSERVER01 -PChan

I am getting an error "A valid table name is required for in, out, or format options."
I want to create a Format File to bulk upload data. What am I doing wrong?


texts: What is the ideal composite index for this query?

texts:
What is the ideal Composite Index in MySQL for the following query on an InnoDB Table1?

SELECT ColA
FROM Table1
WHERE ColB = 0
AND ColC = 0
AND ColD = 0

ColA is the left-most Primary Key.

ColB has fewer "0" rows than ColC, and ColC has fewer "0" results than ColD.

When using the composite index (ColB, ColC, ColD) on the query above, MySQL is instead using an Intersect of Indices ColB, ColC, and ColD.

Here is the requested SHOW CREATE TABLE Table1 ; results:

CREATE TABLE `Table1` (
 `ColA` int(11) unsigned NOT NULL,
 `ColE` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColF` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColB` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColG` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColC` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColH` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColD` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColJ` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColK` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColM` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColP` mediumint(8) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`ColA`),
 KEY `ColB` (`ColB`),
 KEY `ColG` (`ColG`),
 KEY `ColC` (`ColC`),
 KEY `ColH` (`ColH`),
 KEY `ColD` (`ColD`),
 KEY `ColJ` (`ColJ`),
 KEY `ColK` (`ColK`),
 KEY `ColB_2` (`ColB`,`ColC`,`ColD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts:
I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables and update with the latest versions of the reference tables so that This one script can be updated and stored in a source repo to be used on whatever db server needs to run it.

I have a problem with DBCC CHECKIDENT reseeding to use 0 if This is the first run on a fresh database or reseeding to 1 (the desired result) if it is a subsequent run of the same script on the database.

The relevant code at the beginning of the script

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

How can I get this to behave consistently on initial and subsequent runs so that I don't break foreign keys by having row 1 becoming row 0?


texts: MySQL not using index. Working fine with FORCE INDEX

texts:
This is my table schema along with defined indexes:

CREATE TABLE `chat_state_event` (
  `id` bigint(20) NOT NULL,
  `agentId` varchar(255) DEFAULT NULL,
  `allocState` varchar(255) NOT NULL,
  `chatId` varchar(255) DEFAULT NULL,
  `chatState` varchar(255) NOT NULL,
  `conversationId` varchar(255) NOT NULL,
  `event_time` datetime(3) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `addedOn` datetime(3) DEFAULT NULL,
  `modifiedOn` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `conversationIdIndex` (`conversationId`),
  KEY `conversationId_eventTs` (`conversationId`,`event_time`),
  KEY `eventTs_reason` (`event_time`,`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Buffer Pool Size : 3 GB

Table Rows : Around 2 million

Slow Query (Time taken : 11 sec):

SELECT *
from chat_state_event A,
     (select agentId as agentLdap, max(event_time) as max_event_time
      from chat_state_event
      where event_time between ('2019-04-29 19:08:20.03')
                       and ('2019-04-30 19:08:20.03')
        and agentId in
            ('techmn.1617832', 'techmn.1609693', 'techmn.1614422',
              'techmn.1682295', 'techmn.1682291', 'techmn.1682901',
             'techmn.600256', 'techmn.1614478', 'techmn.1619099')
      group by agentId
      order by event_time desc) B
where A.event_time = B.max_event_time
  and A.agentId = B.agentLdap;

Its explain output:

id  select_type table   type    possible_keys   key key_len ref rows    Extra

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    362664  Using where
1   PRIMARY A   ref eventTs_reason  eventTs_reason  8   B.max_event_time    1   Using where
2   DERIVED chat_state_event    ALL eventTs_reason  NULL    NULL    NULL    1876419 **Using where; Using temporary; Using filesort**

Query Running fast (Time taken :2 sec):

SELECT *
from chat_state_event A,
     (select agentId as agentLdap, max(event_time) as max_event_time
      from chat_state_event **FORCE INDEX (eventTs_reason)**
      where event_time between ('2019-04-29 19:08:20.03')
                           and ('2019-04-30 19:08:20.03')
      and agentId in ('techmn.1617832'
          , 'techmn.1609693'
          , 'techmn.1614422'
          , 'techmn.1682295'
          , 'techmn.1682291'
          , 'techmn.1682901'
          , 'techmn.600256'
          , 'techmn.1614478'
          , 'techmn.1619099')
      group by agentId
      order by event_time desc) B
where A.event_time = B.max_event_time
  and A.agentId = B.agentLdap;

Its explain output :

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    362664  Using where
1   PRIMARY A   ref eventTs_reason  eventTs_reason  8   B.max_event_time    1   Using where
2   DERIVED chat_state_event    range   eventTs_reason  eventTs_reason  8   NULL    362664  **Using index condition; Using where; Using temporary; Using filesort**

I am unable to understand why MySQL is not using the index in first query while using the index in the second query. The time in which output is given has substantial difference.


texts: Representing N:N relation as a functional dependency in a database design

texts:
As a software developer, I already have some experience in designing a more or less normalized database schema, but I haven't received any formal training in it before. This semester, I took a university class on databases. We are being taught the formal way of designing a schema based on relational algebra. First, we collect the attributes we want to store:

bookstore<TITLE, AUTHOR, CATEGORY, YEAR, PUBLISHER, PUBLISHER_ADDRESS>

Then we find functional dependencies between the attributes:

fbook : { TITLE, AUTHOR } -> { CATEGORY, YEAR, PUBLISHER, PUBLISHER_ADDRESS }
fpublisher : { PUBLISHER } -> { PUBLISHER_ADDRESS }

After that we can normalize this schema. (For the sake of simplicity, I assumed that a book is defined by its title and author alone, no two editions or two copies of the same edition are stored.)

Now what happens when a book can have more than one category? How do I represent that relation with dependencies? The category can't be a secondary attribute any more, but if it's primary, how do I proceed?

What does this define?

{ TITLE, AUTHOR, CATEGORY } -> ???

We are told that the empty set cannot be on the right side of a dependency.


texts: Adding Last 12 Months to a row in a table (SQL Server)

texts:
I want to add the last 12 months to a SQL Server table, something like the following:

FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15

Please can someone help with with this?

Thanks

EDIT: Table schema is as follows:

Department   Month12   Month11   Month10   Month9   Month8   Month7   Month6   Month5   Month4   Month3   Month2   Month1
----------- --------- --------- --------- -------- -------- -------- -------- -------- -------- -------- -------- -------


texts: Table with image data base64 per row causes deadlocks/block

texts:
We have a table,

CREATE TABLE [dbo].[MyTable](
    [MasterKey] [uniqueidentifier] NOT NULL,
    [DetailKey] [varchar](100) NULL,
    [JSON] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_MyTable_details] ON [dbo].[MyTable]
(
    [MasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

And we have below query which is causing too many deadlocks/blocks.

IF EXISTS(SELECT 1 from  [MyTable](nolock) where  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey)
BEGIN

 UPDATE [MyTable]
           SET [JSON] = @JSON
    WHERE  [MasterKey]= @MasterKey AND [DetailKey] =  @DetailKey
END
ELSE
BEGIN
INSERT INTO [MyTable]
           ([MasterKey]
           ,[DetailKey]
           ,[JSON])
     VALUES
           (@MasterKey
           ,@DetailKey
           ,@JSON)
END

Note different user uses MasterKey key at the same time but in peek time we see application becomes unresponsive. When we see the logs we found a lot of block/deadlocks. We also see Sch-S locks in this table.

Will adding MasterKey and DetailKey as primary key can fix this issue or what can we do?

Note sometimes JSON size is huge because it includes images as base64.


texts: Is there a more efficient way to search for a record but only if it has associations?

texts:
I'm using Postgres 9.5.  I want to select some records, but only if they have at least one association in another table.  I came up with this query:

select distinct m.name, m.id
from machines m, parts rt
where m.id = rt.machine_id
  and m.name like '%Name%';

but I don't think this is particularly efficient, since if there are 50000 associations, I think they're all loaded and then "distinct"ed through, which seems a little inefficient.  Is there a more efficient way to check load a record but only if it has one or more associations?


texts: Selecting a range of users active in the past month, but not in the past week

texts:
I have two tables, users and meetings. I'm trying to select all the users who have NOT made a meeting in the past week, but have made a meeting in the past month.

SELECT  u.id, u.given_name, u.family_name, u.email
    FROM users u
    JOIN meetings m ON (u.id != m.user_id)
    AND m.created_at BETWEEN %s and %s
    AND u.state ='active'
    AND u.id in (SELECT user_id
                 from meetings
               where created_at BETWEEN %s and %s)
    GROUP BY u.id, u.given_name, u.family_name, u.email

This is using the psycopg2 adapter, so %s will be replaced by a datetime object. The %s replacement is in the order of:

7 days ago, today, a month ago, 8 days ago.

Also, the reason i did u.id != m.useridis because the userid will not be in the meetings table if the user has not organized a meeting in the last week.

This doesn't seem to be working, as I am still getting people who have organized a meeting in the past week.


texts: Shrinking VARCHAR with MODIFY: data alignment and potential for data loss

texts:
I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To that end, I want to set the fields to VARCHAR(45), which permits an IPv6 address using IPv4 tunnelling. My syntax, for completeness:

MODIFY ip VARCHAR(45) NOT NULL default ''

My question is this: if a field has previously been set as VARCHAR(100) or VARCHAR(254) and has content (one IP address per line), is there likely to be data loss? My understanding of VARCHAR is that the data is stored at the start (leftmost) and changes in size take place at the end (rightmost), so MODIFY would make its changes from the end and leave the data intact, being at the start. As far as versions are concerned it's various versions of MySQL from 4.1 to current.

I'm pretty confident that I can shrink ip to VARCHAR(45) without zapping existing data. All that said, I'm still pretty new to this and would appreciate an additional pair of eyes.

Thank you in advance.


texts: What is stored in a transaction log file?

texts: What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements found in a transaction block?


texts: AlwaysOn High Availability Take database offline from primary

texts:
I have below scenario where i need your help:

I have configured the AlwaysOn High Availability on my server. I have two replicas. Now I have requirement to make one database offline from the primary replica and make it online after some time.

So what happens in this scenario in background?

After making the database from primary replica online, the database from the secondary replica goes into restoring state. And we need to always manually delete the database from secondary server and remove it from AG group and add it again to both in sync again.

Is there any solution for this? If not are there any steps to do the above task from code?


texts:
Assume we have following table

Id  | ParentId | Timestamp | Status

We also have another table, for each Status it shows if document can be deleted or not.

This table produces several trees (parent and its children), let's call it groups.

Each group can be deleted then and only then each children and parent has flag CanBeDeleted. This flag is 1 when Timestamp exceeds treshold for its status, otherwise, 0.

Here is my query which returns correct result:

DECLARE @Thresholddate datetime2 = '2017-01-01'
DECLARE @StorageId uniqueidentifier = (SELECT s.Id FROM [conf].[Storage] s)
;WITH CTE AS
(
    SELECT Id = di.DocumentId, GroupId = di.DocumentId, [CanBeDeleted] = 1
    FROM [cleaner].[DocumentInfo] di WITH (NOLOCK)
    JOIN @Thresholds T ON di.StatusId = t.DocumentStatusId
    WHERE di.StorageId = @StorageId
    AND di.IsInHierarchy = 1
    AND di.ReceiveDateTime < T.Threshold

    UNION ALL

    SELECT di.DocumentId, CTE.GroupId, IIF(di.ReceiveDateTime < T.Threshold, 1, 0)
    FROM [cleaner].[DocumentInfo] di WITH (NOLOCK)
    JOIN [doc].[Document] d on d.Id = di.DocumentId
    JOIN CTE on CTE.Id = d.ParentId
    JOIN @Thresholds T ON di.StatusId = t.DocumentStatusId
), FilteredCTE as
(
    SELECT CTE.Id, CTE.GroupId, [HierarchyCanBeDeleted] = MIN(CTE.CanBeDeleted) OVER (PARTITION BY CTE.GroupId)
    FROM CTE
)

SELECT TOP (@BatchSize) WITH TIES FilteredCTE.Id
FROM FilteredCTE
WHERE HierarchyCanBeDeleted = 1
ORDER BY GroupId

But this query executes very slow. I have an execution plan, which shows that 99% of time should be spended on index seek, but actually it takes at least one minute to get top 100 rows, how is it possble with index?..

Is it possible to enhance something? I can add extra indexes if required. For now I have PK on DocumentID and another key for several columns:

CREATE NONCLUSTERED INDEX [IX_StatusId_StorageId_IsInHierarchy_ReceiveDateTime] ON [cleaner].[DocumentInfo]
(
    [StatusId] ASC,
    [StorageId] ASC,
    [IsInHierarchy] ASC,
    [ReceiveDateTime] ASC
)
INCLUDE (   [DocumentId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Index is used indeed, it takes the same minute to take top 200 or top 200000 rows. But I'm wondering if this result could be enhanced.


texts: How to copy data from table to another and automatically change the values to match the other's data type msql

texts:
I want to copy data from table column to another and automatically change the values to match the other's data type.

That is; in one table, a column is "Gender" and the values are Male & Female for every row. Now, while transferring it to the other table, I want "Male" to change to "1", "Female" to "2". I can't do it manually cause it's a huge table.


texts:
Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.


texts: Force SQL Engine to prepare Non-Null plan only for a query that use variable

texts:
I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.

Approach 1

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%'
GO

Approach 2

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$';

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%'
GO

Note: The above two are simplified queries. The actual queries can be seen in the execution plan below.

Execution Plans

Actual Execution Plan – Approach 1
https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Estimated Plan - Approach 2:
https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Note: The plan for Approach 2 uses Nested Loop join instead of Hash Match join

Question

I know that this can be fixed by adding OPTION (RECOMPILE). If I understand it correctly, the slowness is happening since SQL Server is creating a plan to accommodate NULL possibility of the variable.

In my case, the variable will be Non-Null before the query is executed (guaranteed)

In SQL Server 2016, what are the possibilities to tell the SQL engine that the variable will be non-null and prepare a plan only for such condition, without using OPTION (RECOMPILE)?
Is the slowness called parameter sniffing? (There is no parameter here. It is a local variable in a stand alone query)

Note: I am trying to see options to get the query running faster without using RECOMPILE.

References:

SQL Server Query: Fast with Literal but Slow with Variable
Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
Local Variables vs. Parameterized Stored Procedures in SQL Server


texts: Transfer datas from Access to PostgreSQL

texts:
I'm trying to insert some data from Access to PostgreSQL database, but it's not a simple query, because it's a nested query.

For example :

rst.Open "INSERT INTO car(id, model, color, type) SELECT (id, model, color, type) FROM t_car"

Where rst is my recordset, and t_car is my Access table.

I know it should look like to a Postgresql request and t_car has nothing to do here, but I don't know how can I specify to this request :

  go take informations from my Access table and insert these to my Postgres.

So I'm little bit lost with it, does someone can help me?


texts: Is Postgres sharding available on Aurora (AWS)

texts:
I need to shard and/or partition my largeish Postgres db tables. The Postgres partitioning functionality seems crazy heavyweight (in terms of DDL). I see talk from <=2015 about pg_shard, but am unsure of the availabilty in Aurora, or even if one uses a different mechanism.

I would like something on the order of a "Key" Redshift-style distribution method. Furthermore, it would be great if there was some way to do it on a multi-colum basis (I can make a "fake concatenated column" if need be)


texts:
I have Percona Server 5.6 running in linux platform.. recently I faced an issue where mysql instance was continuously restarting for no reason.. mysql logs had this error:

[ERROR] InnoDB: File ./TestBAMC/s_snippet.ibd: 'Linux aio' returned OS error 0. Cannot continue operation

Instance has binary logging enabled and has a master-slave configured. This issue is with the master. Does the above error cause the instance to reboot continuously?


texts: postgres performance tuning - what to try next?

texts:
I have a simple (but large) postgres database.

I have a table defined like this….

CREATE TABLE TABLE_A (
  MessID      SERIAL UNIQUE,
  rawData     CHAR(250),
  loadedTime  TIMESTAMP)

And a process that continuously loads millions of records (each one a variable length ASCII string) into the table. There’s an index on loadedTime.
That’s it, no triggers, nothing fancy. All day records get loaded in, at night they get processed. The night-time processing only takes a few hours, and since I’ve got all night I’m not worried about the performance then.

What’s is of critical importance is the rate at which I can insert records during the day.

I’ve managed to get the time down from about 10ms/record to about 1.5m/s record. I’d like improve performance further (maybe 1 ms/record? Or quicker?)

The following changes have made improvements….

Partitioned my data by date/time range.
Kept ‘current’ data on an SSD tablespace, and ‘archive’ data on a magnetic drive
Tweaked both the number of records added in a single INSERT statement, and the number of INSERTs in each transaction

I’ve also tried tweaking postgres.conf using pgtune.leopard.in.ua. To be honest, none of the changes to this file have made any noticeable difference. The current settings are…

# DB Version: 10
# OS Type: windows
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 1
# Connections num: 10
# Data Storage: ssd

max_connections = 10
shared_buffers = 512MB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 541764kB
min_wal_size = 2GB
max_wal_size = 4GB

My questions are…

Why haven’t the hardware and config changes made any noticeable difference?
Are there any other areas for performance tuning I should be looking at?
If I were to upgrade the server the database is on, what factors should I look for in new one – bearing in mind that increasing the memory doesn’t seem to make the slightest bit of difference.

Other information that may be relevant.

I’m only interested in the speed at which records get inserted during the day.
During daytime operations there is a single connection to the database (doing the INSERTs) and no other processing.
The existing server is a 16 GByte Windows pro single processor with Postgres 10 installed on an SSD and archive tablespaces on a magnetic drive.
TABLE_A is partitioned by day. Todays data is held on a tablespace on the SSD, at night it’s archived to tablespace on magnetic disc
Incoming records are added using a powershell script. The INSERT statement is wrapped in a stopwatch function which allows me to accurately record how long each INSERT takes and work out the time per record.
Incoming records are arriving one-by-one from an external server, over which I have no control, so using COPY wouldn't help.
Spooling the records to a temporary file prior to loading hasn't worked either since it seems to take longer to append to a file than it does to insert into a database.
It currently takes 1.5ms to insert a record, so the system can cope with a maximum of between 5 and 700 hundred records/sec. The problem is that the source can, at busy periods, generate 800-900 records per second.

UPDATE in response to horse_with_no_name. Is it number of processors, or number of cores that effect the number of simultaneous INSERTS that Postgres can cope with? My processor is (I quote) "Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz, 2601 Mhz, 4 Core(s), 8 Logical Processor(s)"

Having 4 Core(s), does that mean I ought to be able support 4 simultaneous processes doing INSERTs at the same time? Could I re-write my powershell script to round-robin the incoming records across 2 (or more) connections and improve INSERT performance that way?


texts:
I am designing a database to mainly share data regarding projects and their progress. Part of this is to be able to store data regarding the forecast of each table.
I am designing the ForecastTbl and I am not sure how to proceed.
The forecast will hold the step during the project and how much is spent for 5 years. How do i design the table. i was thinking of having a ForecastTbl, YearTbl, MilestoneTbl...
For example:

Milestone Name     2016/2017     2017/2018  2018/2019   2019/2020   2020/2021

Construction         ...$           ...$       ...$         ...$      ...$

Security             ...$           ...$       ...$         ...$      ...$

Travel               ...$           ...$       ...$         ...$      ...$


texts: Restore Postgres Database on New Machine

texts: Trying to restore a postgres database on a new Macbook Pro.  I recreated the database using createdb dbname then tried to restore the old database using psql dbname < dbname_dump.bin but ended up with a bunch of errors like ERROR:  relation "comments" already exists and ERROR:  duplicate key value violates unique constraint "comments_pkey". What's up?


texts: How does SQL Server choose an index key for a foreign key reference?

texts:
I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade.

Many of these tables also have unique, non-clustered indexes that are duplicates of the primary key.

I am attempting to clean this up.

But what I have found is after I recreate the primary keys as clustered indexes, and then try to rebuild the foreign key, the foreign key is referencing the old, duplicate index (which was unique).

I know this because it won't let me drop the duplicate indexes.

I would think SQL Server would always choose a primary key if one existed. Does SQL Server have a method of choosing between a unique index and a primary key?

To duplicate the problem (on SQL Server 2008 R2):

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1))

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID)

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID)

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL )

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID)

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID)
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID)

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID)
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent
DROP INDEX IX_Parent ON Parent

Error msg:

  Msg 3723, Level 16, State 6, Line 36 An explicit DROP INDEX is not
  allowed on index 'Parent.IX_Parent'.  It is being used for FOREIGN KEY
  constraint enforcement.


texts: SQL: Can we make query to compare data for 2 different years in same month, but looking at the same day as priority

texts:
FYI: I'm using SQL Server 2012.

Because scope of the function is a selling date which is different by the days of week (like comparison for customer visiting percentage).

So the main key to compare between last-this year, is to check the same week or same day that most related to the month and are same day (Monday, Tuesday, ...)

For example:

2018/11/01 should compare to 2017/11/02 (Thu)
2018/11/02 should compare to 2017/11/03 (Fri)
...
2018/11/30 should compare to 2017/12/01 (Fri)

I can't imagine the syntax out of my head.
What is syntax should I write?
What are the keywords, functions should I call to achieve this scope?

Anyone who has experience about some kind of function like this, please help.
All comments would be appreciate.


texts: DB2 9.7, create or replace tablespace possible?

texts:
I am creating my tablespaces in the sql scripts that create the database schema. This causes a problem in continuous integration processes, where consecutive execution of the script is not possible because it always tries to create a new instance of the same tablespace.

For triggers there is the call CREATE OR REPLACE TRIGGER but I couldn't find one for tablespaces. Any way around this other than dropping the tablespaces when the schema is dropped?


texts: How to deploy install of MS SQL Server 2017 Developer Edition via command-line script - not working

texts:
I'm trying to deploy MS SQL Server 2017 Developer Edition via command-line script from a server share that holds the install media onto Windows 10 1803 (June release). However, I'm not having much luck. I'm pushing the script to the individual clients from a KACE "KBox" K1000 running the latest version (9.0.270). The installer fails at the get-go because it thinks I'm trying to install a feature not supported by the version of SQL Server. However, I got my information from Microsoft. I'm at a loss because I can't find documentation on the errors that are being reported.

Here's a snip of the Output Log generated at run-time on the "KBox":

I tried removing all references to AS and IS in the command statement, so that it would read:

\\AppServerBox\SQLServer2017Media\Developer_ENU\SETUP.EXE /QS
/IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /ENU=True
/FEATURES=SQL,Tools /UpdateEnabled=0 /INSTANCENAME=MSSQLSERVER
/SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
/SQLSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="\Everyone"
/AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
/AGTSVCSTARTUPTYPE=Automatic /SECURITYMODE=SQL /SAPWD="password"

and ran it in an elevated command prompt, directly on the client, and got this error again:

Some documentation said that Java JRE [1.7 or 1.8] is necessary, so I installed 1.8. This didn't seem to help. I keep getting the same error. Hopefully, someone can help resolve this issue before it's too late. Classes will be starting soon.


texts: Redshift: Performance wise Is storing delimited values in a single column ever preferable to normalizing them out to another table?

texts:
I have been looking at increasing the performance of some tables that have been uploaded to a redshift database. One of the columns in a table has many delimited values which need to be split out and queried at runtime. The table has around 30 million rows and there would be on average 10 delimited values in this particular column.

Coming from an OLTP background my instinct is to normalize these values out to another table. However, I have seen various posts that suggest that this is not the way to go. Looking for some advice


texts: Table Diagram and Access File of Contact Database for Church

texts:
I have volunteered to design and develop an MS Access database for contacts at my local church in Jamaica, which has no budget to hire a database expert. I have attempted to design a database for the contacts, based on some requirements that I gleaned from the church leadership and administrators.
The church really wants to have a central place to store records on members, which they can delete/archive when members die or go away. They also wish to maintain records on marriages and anniversaries and emergency contacts. So I decided that the best approach is to focus on developing a contact database, that would include members, emergency contacts, non-member spouses of members, and other contacts that may be of service to the church and in turn the community served by the church.
Please help me to design a useful database by reviewing the table diagram attached. Any assistance would be useful.
Kind regards,
Kerry-Ann


texts: Select the top 5 records and the specific column from each table in the same database

texts:
I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select top 10 * from {table_name}

So, is it possible to display the first 10 records PLUS only show the 2nd and 3rd column for each table inside the same database using sql?

(If the table does not have the 2 columns - it will display the 1st and 2nd column. If the table have 1 column, it will display the 1st column. If the table have more than 2 column, it will display the 2nd and 3rd column. If the table does not have any column, then display nothing.)


texts: Proper way to export database to other servers

texts:
I am using SSMS (I am a software developer, not a DBA) and in each application release I need to release the database with its tables (some with data some with scheme only).

I tried doing 3 things (SSMS):

Using Database --> Tasks -> Generate scripts...
This is fine, for small databases, when the scripts is over 200 MB, users reports memory problems, both when running sqlcmd.exe and from SSMS.
Moreover, I have to remember to script triggers and I am never sure whether the scripts generate identical database.
I tried using the Back Up option but I guess this option is intended for backup and not export data, it prevents me from exporting only the scheme for example.
Back ups also keep histories for differential restores which is not what I intend.
I tried out Script Database as... CREATE To... but these doesn't keep triggers and data.
Moreover, this script use hardcoded paths to .mdf and .idf which changes from a server to server.

What is the correct approach to replicate a database to another server ?

P.S. I am new to this Stack Exchange website, I read several question but didn't find an answer after looking on several questions.


texts: Is it possible to set up ADSI linked server in SQL Server Express?

texts:
Is there any limitation in SQL Server Express that prevents you from creating a linked server to Active Directory?

(I don't have an AD environment available to test.)


texts: Oracle 10g: same query, different plans on copies of the same DB

texts:
I have a query joining a number of long tables (primary key in one table, indexed column in another) which results in different plans on different copies of the same database.

The structure of copies including indices is the same and the data is 99+% the same (copies are made within a week from each other so some of them hold a bit more or fewer rows, the difference is totally insignificant for that particular query).

The problem is that on some copies Oracle applies the indices as expected while on another ones there is always a full table scan even if you specify a hint.

What factors can be influencing the plan apart from the data structure itself? For example, are there any dynamic ones such available RAM or anything like that? In other words, what should I check after I make sure that the DB structure of the "good" copy (which uses the indices) is identical to the "bad" one (which uses full scans)?

Or is it nonsense and different plans mean differences in data structure which I simply haven't noticed for some reason?


texts: mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

texts:
I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command:

ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

alter_specification:
table_options
...
| ADD {INDEX|KEY} [index_name]
    [index_type] (index_col_name,...) [index_option] ...

So, I tried to do the following:

ALTER IGNORE TABLE payments ADD INDEX (id_project);

However, this fails, with the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE payments ADD INDEX (id_project)'

Turns out, this has been removed as of mysql 5.7.4.

What's a good way to do this in newer versions of mysql?


texts:
Need help regarding backup validations for couchbase, what is the best way / easiest way to validate couchbase backups.

we have recently moved to couchbase and totally new to couchbase, having some trouble with backup validation.

we are using cbbackupmgr backup for taking backup's and tar the backup file at the end, could not find any solution on my research.

Any help from this community, Thanks in advance.


texts: How to restore SQL Server database just by dbName and physical path

texts:
I have several database backups and I want to restore all of them to a server.

Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and not the ones carried over with the database??

Restore FILELISTONLY FROM DISK='E:\New folder\Thursday - DB_Order.bak'

RESTORE DATABASE Business_Data_TSQL
FROM DISK='E:\New folder\Thursday - DB_Order.bak'
WITH
MOVE 'DBOrder_Data' TO 'E:\New folder\Business_Data.mdf',
MOVE 'DBOrder_Log' TO 'E:\New folder\Business_Data_log.ldf'

Using query 1 I get the logical name then those names need to manually be set for query 2, but it’s really difficult when there is more than a single database.

Please help me to write a script where I just pass the physical path of backup file and new database name, then a restore of that database occurs.

If have any questions, please ask. Thanks in advance.


texts: Patterns/Designs to help with concurrency/performance

texts:
I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in general. 60% of users will read from this table while 40% will write to it. The problem I'm faced with is that the writing is actually a collection of big updates to about 50-100 rows at a time, doing stuff with re-parenting, updating FK's, updating chunks of HTML, etc. These writes are a problem when multiple users are doing similar updates while others are trying to read. Even without reading, the updates alone are an issue.

Unfortunately, breaking down the data in these tables hasn't helped. For e.g. 100 rows may represent a single entity to the end user similar to a document per say, so each row represents a section of a document, these updates treat the "Document" as a single entity which may include actions like merging section information, adding new sections, deleting old sections, updating content, moving sections around etc.

Are there any designs/patterns that can help with scenarios like these???


texts: SQL Server in a state of confusion: Does the database exist or doesn't it?

texts:
Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted.

First, we noticed that the database we created yesterday was missing. So, we looked around and found that it was still there, but detached. So, we tried to attach the mdf but got a message which was something like The file is currently in use.

I thought that was odd, so restarted SQL Server. Same thing... okay, time for drastic measures... so, I stopped the service, zipped up the mdf, started the service, unzipped it and then tried to restore it. The above message was gone, but then I got:

Cannot attach a database with the same name as an existing database

Ouch. Of course it's not showing in the database explorer, so no idea what's going on... last resort:

DROP DATABASE [DatabaseName]

Of course that didn't work.. that tells me the database does not exist. So, I'm stuck... at one point MS SQL thinks the db does exist and at another point it thinks the db does not exist.. obviously it's in a state of confusion.

Has anyone seen this before? Got any ideas on how to fix it?


texts: How to fix slow LIMIT queries with big offsets without heavily rewriting them?

texts:
Imagine a very basic example of your average discussion board. For example:

CREATE TABLE threads (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(100),
    PRIMARY KEY (id)
)

CREATE TABLE replies (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    thread_id INT UNSIGNED NOT NULL,
    text TEXT NOT NULL,
    PRIMARY KEY (id)
    INDEX thread_id (thread_id),
)

There can be very lengthy discussions (people love bikeshedding!), maybe 100k or 200k replies per thread and users can read them paginated (the number of replies per page is variable, depending on user preferences, but if needed for the solution it can be limited to a fixed set). These tables might have ~40 million replies and ~2 million threads.

So you might end running this query to get the last replies of a thread:

SELECT * FROM replies
WHERE thread_id = 1234
ORDER BY id ASC
LIMIT 125400,10 /* whoops */

Which, as you know, is quite slow since MySQL has to walk 125,400 rows just to get there and return your 10 rows.

Hacky solutions I've thought:

Create a secondary index which assigns an incrementing number for each chunk of N posts (for example, a new field in the replies table which for the first 1000 posts contains 1, for the following 1000 it contains 2, etc).

I have to heavily modify the application, since there are tons of queries that read the replies table, it's not just a simple SELECT here and there and I really don't want to cripple and reengineer each query of the application.
It would force me to recalculate each time that I delete a reply or when I do other destructive operations (splitting, merging, etc).

For each link to the next page, attach the ID of the next post. That way the database can go directly to the row using the primary key of the replies table.

This is a web application, so this solution would have tricky SEO implications which I'd prefer not to deal with.

I might be dreaming here (and if so please do tell me!) but is there a solution that resides (almost) exclusively in the database and allows me to fix this problem without heavily modifying the application?

I've read about MySQL partitions, but I'm not sure they would help here.


texts: How to compare all rows in a table and extract data using triggers

texts:
I have two tables, one table stores the GPS data of various users and another table stores the idle time of the users based upon the GPS latitude and longitude values and timestamp difference.

Table T1 contents:

id  uid  latitude   longitude    received_time          imno
435 25  13.041868   80.198868   2014-08-07 15:53:00     352944054874253
436 25  13.041862   80.198868   2014-08-07 15:54:00     352944054874253
437 25  13.041869   80.198868   2014-08-07 15:55:00     352944054874253
438 25  13.041887   80.198871   2014-08-07 15:55:38     352944054874253
439 25  13.041887   80.198871   2014-08-07 15:55:42     352944054874253
440 25  13.041887   80.198871   2014-08-07 15:55:47     352944054874253
441 25  13.041887   80.198871   2014-08-07 15:55:52     352944054874253
442 25  13.041887   80.198871   2014-08-07 15:56:00     352944054874253

I have to compare rows having the same latitude and longitude values.

If previous row's latitude and longitude is the same as the next row's latitude and longitude, then calculate timestamp difference until the next row's latitude and longitude values aren't equal.

For example, from ID 438 to 442 the latitude and longitude values are the same so I have to calculate:

timediff = 442 received - 438 received time (in minutes)

and store it in table T2 like:

id  uid     latitude     longitude      idle_time               imno
1    25      13.041887   80.198871    2014-08-07 00:01:38    352944054874253

start_time                        end_time
2014-08-07 15:55:38         2014-08-07 15:56:00

How can I solve this?


texts: How to recover LogMiner after its tablespace had been deleted?

texts:
Here is my story:
I created separate table space for Log Miner,
create tablespace logminer_ts datafile '/path/to/logminer.dbf' size 50M AUTOEXTEND ON

And moved Log Miner to the created table space:
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logminer_ts');

Then I wanted to repeat the whole process, so I dropped the Log Miner table space:
DROP TABLESPACE logminer_ts INCLUDING CONTENTS AND DATAFILES;

Now I have a problem, the call to DBMS_LOGMNR_D.SET_TABLESPACE gives error:

ORA-04063: package body "SYS.DBMS_LOGMNR_INTERNAL" has errors
ORA-06508: PL/SQL could not find program unit being called "SYS.DBMS_LOGMNR_INTERNAL"
...

Now the log miner is failing to start, it looks like some of the data had been deleted when I dropped the table space...
My question is how can I recover from this without recover from a backup?
Can I get the missing parts from other oracle and "install" on the malfunctioning DB? (some export/import things?)
Thanks a lot!


texts: Can SQL Server's Page Life Expectancy be too high?

texts:
I've read several articles about SQL Server's Page Life Expectancy and what it means and what kind of information you can glean from it. In most of these articles that I've read, a common, healthy PLE value is somewhere around 1000-2000 seconds. I've read that getting down to around 300 seconds can mean you're likely low on RAM.

I recently just upgraded our hardware to have 64GB of RAM, up from 14GB. On the 14GB my PLE was around 300 seconds, and I was having a lot of 5-6 memory grants pending per second. So, that was bad and I increased the RAM. Now, my PLE is much, much higher around 5000 seconds, and no more pending memory grants. I've seen it 7000 seconds, if I recall. This is much higher than anything I've read.

Can a high PLE be a bad thing? Or is it the higher the better?

EDIT: I'm sorry, my PLE wasn't 7000 seconds, it was 70,000 seconds! Although, at the moment it is down to around 7000.

The database size is around 160GB. A few tables have 5+ million rows.
max_server_memory is set to 2147483647.


texts: How to connect to remote MySQL from a specific IP?

texts:
I have two IPs (192.168.13.1 and 192.168.13.2) in my Server A and  have provided select access to another Server B for secondary IP/virtual IP (192.168.13.2).

mysql> show grants for web_app@192.168.13.2;
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@192.168.13.2                                                                                |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'user'@'192.168.13.2' IDENTIFIED BY PASSWORD '*pass' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

When I tried to connect mysql of Server A to Server B ( from Linux ), Primary IP is trying to connect to MySQL and access is getting denied

[root@13-1 ~]# mysql -h 192.168.12.1 -u user -p
Enter password:
ERROR 1130 (HY000): Host '192.168.13.1' is not allowed to connect to this MySQL server

Then I give exactly the same grants for 192.168.13.1 and I was able to login. My application running in 192.168.13.2 trying to fetch from remote server was also not working until I give grants for primary IP. My ifconfig details::

[root@13-1 ~]# ifconfig | grep inet
        inet 192.168.13.1  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.2  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.3  netmask 255.255.254.0  broadcast 192.168.13.255

I have two questions:

How to mysql remote login from a specific IP ( in my case, from 192.168.13.2 ) ?
Is it necessary for an application(with MySQL) to have grants provided for Primary IP also, even though I am trying from App in another IP ? Or Am I missing something?


texts: I'm relatively new to database driven sites. In order to backup a database I use phpMyAdmin's Export function from the server and download the output to my computer. That works, but it requires me to do this on a regular basis. As I build more database driven sites, the process is going to become cumbersome and tedious. I wonder if there is a better method, possibly something automated and scheduled?


texts: Plan changes to include Eager Spool causes the query to run slower

texts:
We have a reporting query which is erratic in terms of execution plan and run duration.
It is either 5 seconds or as slow as 5 minutes.
The query is a Select statement with no DML involved.
One thing I noticed is the costly Eager Spool operator after an index scan in slow executions.
However, index seek is used when executing fast.
I updated the stats but it didn't help to speed up.
I was just wondering why sometimes optimiser chooses to use Index Scan (and Eager Spool) rather than Index Seek and what causes it to do so?
Attaching SS of both slow and fast execution plans. SS is saved as a file from Plan Explorer so may upload some other tabs if need be.
Thank you
Edit: The query runs fast in the mornings, slow in the afternoons, always.


texts:
I've been trying to model an efficient schema for a bill split app, the requirements are as follows:

There can be groups in which there can be multiple participants, and conversations consisting of exactly 2 participants.
One group/conversation can have many bills
One bill can be paid by many people, can be paid for many people (eg. A and B contributed 100$ and it was split equally between A, B, C, and D).
If a user goes into the profile of another user, they can see what they owe to them or vice versa, on a global level, i.e. total of all the groups.
If they go in the group details, they can see individual group members and what they owe to the user or what the user owes to them on the group level.
A bill can have transactions representing payments made for the bill, and the actual amount used by each participant.
These payments can be settled.

It would be great if anyone could guide me on how to model this database efficiently. Thanks!
PS: I'm using MongoDB right now


texts: How to prevent informational messages from getting written when query results are output to a file

texts:
When I am running the below query as a batch file (.BAT) using the Windows Task Scheduler, I am getting some informative messages in the output file DatabaseNames.txt in addition to the database names. How can I prevent those messages? These messages are not visible when running from the Command prompt.

"C:\Program Files\PostgreSQL\10\bin\psql.exe" --host localhost --username postgres -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE AND datname NOT IN('template0','template1')" > D:\Backup\DatabaseNames.txt

Below were the informative messages

Welcome to PostgreSQL!
Type :version to see PostgreSQL version
Type :extensions to see the available extensions
Time: 0.994 ms


texts: What network access do I need when I restoring files using RMAN?

texts:
I am restoring files from one server to another server using Oracle RMAN, what network accesses do I need?(between these servers)

ftp? port 1521 should be open? or what?

I appreciate your answer thanks...


texts: ORA-00907: missing right parenthesis on query with subqueries and EXCEPT operator

texts:
I am getting

  ORA-00907: missing right parenthesis

for the following query:

SELECT PEOPLE.NAME FROM
CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L
WHERE NOT EXISTS
(
(SELECT DISTINCT ARTIST.ID_U
FROM CLIENTS INNER JOIN ADORES ON CLIENTS.ID_L = ADORES.ID_L
INNER JOIN ARTIST ON ADORES.ID_U = ARTIST.ID_U
WHERE CLIENTS.ID_L = K.ID_L)

EXCEPT

(SELECT DISTINCT ARTIST.ID_U
FROM CLIENTS INNER JOIN BOUGHT ON CLIENTS.ID_L = BOUGHT.ID_L
INNER JOIN ARTWORK ON BOUGHT.ID_D = ARTWORK.ID_D
INNER JOIN ARTIST ON ARTWORK.ID_U = ARTIST.ID_U
WHERE CLIENTS.ID_L = K.ID_L)
)

I've checked both subqueries - both are ok. It works if I remove the EXCEPT operator and only use one subquery (but I need to actually test that both are equal).

Any idea on what the problem is?


texts: How do I create a view with new snake_case columns (replacing camelCase) and remaining original columns? (using the output of `FORMAT`)

texts:
Unfortunately I am in a position where I have to use camelCase column names for my database, as the source data dictates so.

However, I am using an application where I can only query for snake_case columns (as discussed here).

I want to create a view table that has all the same columns, except those with camelCase names replaced with a snake_case name, so this query

SELECT * FROM view_name;

will return

| id  | name | column_one |...|
|-----|------|------------|---|
| ... | ...  | ...        |   |

I can do this manually like this:

CREATE VIEW view_name AS SELECT id, name, schema.table."columnOne" AS column_one FROM schema.table

but I have too many columns, is there a way to do this automatically for all columns?

UPDATE 1:
Made progress on this, I am able to get the string of all the columns I need from one table like this, but I still don't know how to use it with CREATE VIEW:

(SELECT FORMAT(
  '%I.%I.%I."%I",',
  table_catalog,
  table_schema,
  table_name,
  column_name
)
FROM information_schema.columns
WHERE column_name !~ ' |[[:lower:]][[:upper:]]'
AND table_name = 'example_table_name')
UNION
(SELECT FORMAT(
  '%I.%I.%I.%I AS %I,',
  table_catalog,
  table_schema,
  table_name,
  column_name,
  lower(
    -- replace all spaces with _, xX and Xx becomes x_x
    regexp_replace(
      -- First, replace spaces with an _
      replace(column_name, ' ', '_'),
      '([[:lower:]])([[:upper:]])',
      '\1_\2',
      'g'
    )
  )
)
FROM information_schema.columns
WHERE column_name ~ ' |[[:lower:]][[:upper:]]'
AND table_name = 'example_table_name')

which results in

                                    format
------------------------------------------------------------------------
 db.schema.table."id",
 db.schema.table."name",
 db.schema.table."columnOne" AS column_one,
 db.schema.table."columnTwo" AS column_two

I want to CREATE VIEW from the output of format, how can I do this?


texts: I was trying to interpret the relationship between JOINs like LEFT-OUTER JOIN, RIGHT-OUTER JOIN, etc with relationships like Many-To-Many, One-To-Many etc in Hibernate or database language.


texts: Can a SQL Server backup device hold both data and log backups?

texts:
If I create a backup device - i.e. using the SP:

EXEC sp_addumpdevice 'disk', @bakName, @bakPath;

Can I write both data and log backups to it? Or is it better practice to have different devices for data and log files...


texts: Multi part identifier in Microsoft SQL Server

texts:
I'm migrating a SQL Server 6.5 database to SQL Server 2012. Migration went well but one of the software using this database performs a strange query:

UPDATE myTable SET Field1='',Field2=0, a.Field3=14 WHERE Field10=1

The problem lies of course with the identifier ('a') before Field3. There are no views, aliases or join with that name but for SQL Server 6,5 that is a valid query.

Since I'm not familiar with SQL Server 6.5, do any of you remember the meaning of this syntax?

The software using this query is an old one of which I don't have any sources (and the company that made it is now closed...), so changing the query is not an option.

The full query is

UPDATE SituazioneMacchina
SET DatiSetPointSpediti=''
,   SpedMappa=0
,   NrRichMappa=0
,   DatiSetPoint=''
,   a.IDProduzione=14
WHERE NumMacchina=1

If I change "a.IDProduzione" with something like "fasdffas.IDProduzione", the query still works in SQL Server 6.5


texts: Do I need "New SQL Server Failover Cluster installation" to run Availability groups with manual failover?

texts:
I'm trying to set up a test environment here to learn more and apply this on our production environment.

I've created and installed the Windows Cluster with CLuster manager.
Now I'm going to install SQL Server Enterprise 2014  on both machines and create HA with  then, with availability groups. The failover will be manual. They will be in synchronized data transfer.

My question is, Can I normally install "stand-alone installarion or add..." the SQL server, or I need to use "new sql server failover cluster installation".

I cant find anything about this on the internet.

Im going to install sql server on both nodes that will be configured in the cluster.


texts: How to get a SQL value literal for arbitrary type in PostgreSQL?

texts:
I want to be able to get a valid SQL literal for any type and value.

If the type is text, varchar, etc... and the value is abc I want to get 'abc'.

If the type is timestamp I want quoted and formatted timestamp... Is there a builtin function for this in PostgreSQL or do I have to build one myself?

I'm doing the case thing right now, but I'm afraid that I'll miss some kind of edge case. I can't find an exhaustive list of all possible value literals in PostgreSQL.

I'm trying to generate a CREATE AGGREGATE statement and the problem is that I can't properly format the INITCOND value for every type. I have the type and the value... here is my query:

SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)',
         a.aggfnoid::regprocedure,
         a.aggtransfn,
         a.aggtranstype::regtype,
         ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper,
         ', INITCOND = '  || (case a.aggtranstype::regtype::text
                               when 'text' then '''' || a.agginitval || ''''
                               when 'varchar' then '''' || a.agginitval || ''''
                               when 'char' then '''' || a.agginitval || ''''
                               when 'timestamp' then '''' || a.agginitval || ''''
                               when 'date' then '''' || a.agginitval || ''''
                               when 'time' then '''' || a.agginitval || ''''
                               else a.agginitval end),
         ', FINALFUNC = ' || NULLIF(aggfinalfn, 0),
         CASE WHEN aggfinalextra THEN ', FINALFUNC_EXTRA' END
) AS ddl_agg
FROM pg_aggregate a
join pg_proc p  on a.aggfnoid = p.oid
join pg_namespace n ON p.pronamespace = n.oid
where n.nspname = 'public'


texts:
I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2.
We are migrating to a new server host but remaining on SQL Server 2008R2.

How can I copy/migrate these SSIS packages to the new server? Is Dtutil the best option?


texts: RDS Does Not Allow Restoring MS SQL DB from S3

texts:
I have a question for you folks who might be familiar with the intricacies of restoring a .bak file from S3 to an AWS RDS MS SQL instance.  Here is what I have tried, and how far I have gotten:

First some context:  I am the sole IT guy at an all Mac, non-profit, special needs school (we help the kids who fall through the cracks).  I recently received a backup of our SIS from a vendor who could only provide it by way of an MS SQL backup file, which ends in .bak.  According to my research, I should be able to spin up a free AWS RDS instance of MS SQL, and then restore the .bak file to that free instance.

I have successfully created an AWS account with a free-tier RDS instance that has a singular, blank (brand new) MS SQL database called database-1.  The instance is using MS SQL Express, version 14 (as far as I can tell).  Other specific actions I have taken include the following, all taken from articles I have found online discussing how to do this:

Created an Option Group with the MSSQK-BACKUP-RESTORE option, and added that Option Group to the RDS database called database-1.
Created an IAM role called restoreadmin and gave it premissions to do everything on my S3 instance.
Created an S3 bucket called Achieve-DB, and uploaded the MS SQL backup file to it.

The articles I used to guide me through all of this, btw, are:

Amazon RDS Support for Native Backup and Restore in SQL Server
Secure Access to S3 Buckets Using IAM Roles

However, despite doing everything listed above, including banging my head on the keyboard a few times, I am not able to restore the backup from S3 to the database in RDS.  The procedure for doing so, ostensibly, is the following:

Within the RDS console, select the database you would like to restore to,
Click the Restore from S3 button
Follow the wizard...

The problem I am running into is that when I click on the Restore from S3 button, the only two options for restoring are Amazon Aurora and MySQL.

Can anyone here suggest where I might be going wrong?  Thank you!!


texts:
Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical
data recusively. Table is big with every root id having upto 3 recursive itemid. There could be around 200000 or more root id's.
I know recursive CTE's are slow for huge dataset as for every rootid in anchor it would itemid recursively.

Schema :

Create table RootItem (ItemId int primary key, RootIt int , insertdate datetime)

Above table have more than 1 million rows.

CTE query :

; With rootcte as

( select itemid from RootItem where rootid is null

union all

  select r.itemid as RootId , i.itemid from RootItem i join rootcte r
    on i.rootid = r.itemid
)

We can't modify table schema and use heirarchyid. I tried while loop too but that is slow too.

Is there any other way I can optimize this query ?

 ; With rootcte as

( select itemid from RootItem where rootid is null

 union all

 select r.itemid as RootId , i.itemid from RootItem i join rootcte r
 on i.rootid = r.itemid
)
  SELECT
     Cust.CustomerID
    , Cust.BusinessName
    , sCust.RegionCustomerID
    , ord.OrderID
    , ord.OrderItemID
    , prd.ProductCode
    , rc.itemid
    , rc.rootid
    , mf.FileID
FROM
    vw_Customer Cust
    INNER JOIN SrcCustomer scust ON Cust.CustomerID = sCust.RegionCustomerID
    INNER JOIN OrderItem ord ON Cust.MasterCustomerID = ord.MasterCustomerID
    INNER JOIN Product ON ord.ProductID = Product.ProductID
    INNER JOIN rootcte rc ON ord.RootOrderId = rc.Rootid
    INNER JOIN MFolder mf ON mf.mfolderid = rc.itemid
    INNER JOIN MVersion mv ON mv.mfolderversionid = mf.mfolderid
    WHERE ord.IsActive = 1  and product.IsSelling = 1 and mf.fileid in (23,45,29)
     and mv.isdeleted = 'N'

I am also working with BI group to change query logic and filter data in cte itself of move couple of joins and criteria to cte.. Thanks for all the comments.


texts: ERROR 1130 (HY000) not allowed to connect to this MariaDB server facing issue in granting permission

texts:
I am trying to connect to mariadb running different container.Getting below error
$ mysql -u root -h test-mysql --port 3306

ERROR 1130 (HY000): Host 'test_1_73f6dacd1d23.test_bluenet' is not allowed to connect to this MariaDB server

Mariadb Server:
I tried the following
MariaDB [(none)]> SELECT host, user FROM mysql.user;

+--------------+------+
| host         | user |
+--------------+------+
| 00a7a6a8d0af |      |
| 00a7a6a8d0af | root |
| 127.0.0.1    | root |
| ::1          | root |
| localhost    |      |
| localhost    | root |
+--------------+------+
6 rows in set (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'root'@'%';

ERROR 1133 (28000): Can't find any matching row in the user table