Longest prefix search in Oracle I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.

I use inverted LIKE clause on field digits (which contains prefixes in form +48%, +49%, +1%, +1232% and so on).

Therefore I can't use normal index on that field.

I managed to get substantial improvement by using IOT on gvcode and cgi field (which are part (first two cols) of primary key).
I also looked at some oracle text indexes but can't find one that will match longer input with shorter prefix in the table.

Is there any other way to perform such search that is faster than this approach.

Here is the query which gives a list of all matched prefixes (I sort it afterwards on digits length).

  select  t.gvcode,  t.digits
                from NUMBERS t
                    where
                        t.gvcode=ZONE_SET_CODE
                        and t.cgi=cgi_f
                       and ( PHONE_NR like t.digits)
                         order by length(digits) desc

This turns up a lot in the Telco space (where I work).  There are several approaches you can use to get reasonable performance while still remaining entirely in the database.

In all cases, you need to drop the "%" from your DIGITS column, that extra character isn't helping you at all.

Option 1. Iterative function.

DECLARE
    p VARCHAR(30);
BEGIN
    p := :full_number;

    WHILE (LENGTH (p) > 0)
    LOOP
        BEGIN
            SELECT [whatever]
            INTO :whatever
            FROM numbers t
            WHERE digits = p;

            RETURN;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                p := SUBSTR (p, 0, LENGTH (p) - 1);
        END;
    END LOOP;
END;

This kind of function performs reasonably well, assuming you have a nice index on DIGITS.

Option 1. START/END range keys.  Create two secondary columns (BOTH INDEXED).

DIGITS_RANGE_START
DIGITS_RANGE_END

These are derived values that you set by an insert/update trigger.

DIGITS_RANGE_START should be automatically set to DIGITS || CHAR(0)
DIGITS_RANGE_END should be automatically set to DIGITS || CHAR(255)

Now your query can be

SELECT ... FROM numbers t
WHERE (number_to_match || CHR(1) > digits_range_start)
  AND (number_to_match || CHR(1) < digits_range_end) AND ... [other conditions]
ORDER BY LENGTH (digits) DESC;

This will hit the indexes with a nice comparison match, and Oracle should be able to do a decent job.  It may return multiple matches, you need to take the first one.

More Options

See also this famous thread for fancy options with Index Organized Tables if you want to get real clever and real fast.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4246230700346756268


Why is the value - despite of using GREATEST() - still out of range? I tried the following to decrease a counter after a post was deleted by using GREATEST() so it does not result a negative value:

SELECT GREATEST(0, posts - 1)
FROM users
WHERE id = 123

but it returns:

[Err] 1690 - BIGINT UNSIGNED value is out of range in '(`db1`.`users`.`posts` - 1)'

posts returns 0:

SELECT posts
FROM users
WHERE id = 123

An the following returns 0 as expected:

SELECT GREATEST(0, 0 - 1)

So what I'm doing wrong?


Find continuous records with equal values I have a table named "TOOLS" .

Sample data:

ID       TAG
1         0
2         1
3         1
4         1
5         0
6         0
7         1
8         1
9         0

So, the SQL query should select sum of TOOLS having continuous 1 in field TAG for a specified row, for example ID=2 then the query returns 3 in this sample. It also returns 2 for ID=7.

How can I do it?

Or a more traditional approach:

SELECT @ID ID, SUM(a.TAG) r
FROM @tools a
WHERE a.ID Between @ID And ( SELECT MIN(ID) FROM @tools b WHERE b.ID > @ID AND b.TAG = 0 )

The trouble with all these lovely window function-based solutions is that if you write them as pseudo-code you get something like this:

Do something across ALL the IDs, then work out the sequence, then aggregate...

You also can't inject the ID at any point as it invalidates the window range.  Even @JulienVavasseur's solution which uses an ID variable, will scan 999,999 rows in a 1 million row table where the @ID is 2.  Basically these solutions do not scale well.  So if you have 1 million or 1 billion rows in your tools table, well the query will take a long time, 4+ seconds versus 0-50ms in my simple test rig (see below).  Now maybe you might have only 10 rows but it's a bad habit to write code that doesn't scale IMHO.  You might know the volume today but you might not tomorrow.

More traditional set-based solutions can often work best, even recursive CTEs (under certain conditions) and these were the first two that occurred to me:

DECLARE @tools TABLE ( ID INT PRIMARY KEY, TAG INT NOT NULL )

;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO @tools ( ID, TAG )
SELECT rn, CAST( ( rn % 7 ) AS BIT )
FROM cte

DECLARE @id INT = 2

-- Set-based with subquery
SELECT @ID ID, SUM(a.TAG) r
FROM @tools a
WHERE a.ID Between @ID And ( SELECT MIN(b.ID) FROM @tools b WHERE b.ID > @ID AND b.TAG = 0 )

-- CTE
;WITH cte AS
(
SELECT ID, ID AS parentID, TAG
FROM @tools t
WHERE ID = @id
UNION ALL
SELECT b.ID, a.parentID, a.TAG
FROM cte a
    INNER JOIN @tools b ON b.ID = a.ID + 1
WHERE b.TAG = 1
)
SELECT parentID AS ID, SUM(TAG) TAG
FROM cte
GROUP BY parentID
OPTION ( MAXRECURSION 999 );        -- <-- this can be set to 0 but set to lower boundary for safety

Plugging the other solutions into my test rig does not end well for them:

In my tests, my recursive CTE solution (which normally would be thought of unfavourably as RBAR) performs the best with short sequences (< ~10,000), although this dips with really long sequences;  My subquery was best with these.

I would be interested to see if a window-based approach could out-perform either of these two, even with different indexes eg I was surprised not to see a LAG / LEAD solution here.  Does anyone know Itzik? : )


Tablespaces in oracle I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is the aparent advantages that tablespaces provide? Anyone care to share  use case(s) of how tablespace/data files can help solve problems.

Few uses for the tablespaces:

When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system.
When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data.
When doing restore you can skip tablespace. E.g. if you need only specific table or you need to start as soon as possible you can skip index tablespaces and rebuild indexes online.
Tablespace per user can be useful. Then when user is no longer needed you drop the user then drop tablespace and have disk space back. Also if user suddenly starts to generate lots of data only his tasks will break when tablespace becomes full. Other users will be unaffected.
Data blocks corruption usually is contained within tablespace. In such cases only one tablespace is affected.
You can store tablespaces (datafiles) on different types of disks. So you can manually manage which data goes on SSD and which on SATA.
In really rare cases you may need different block size for the tablespace.
You can choose to compress the whole tablespace content.
You can move selected data to a tablespace, unplug it from the database and attach to another database. This is transportable tablespace.


Select from one table where range is held in columns belonging to another table I am trying to write a SELECT statement in Oracle SQL where I have a TABLE A with a "START" column and an "END" column which holds division numbers.

I want TABLE B to only extract those division numbers that are between columns "START" and "END" in table A

E.g

TABLE_A

ID | START_DIVISION | END_DIVISION
---+----------------+--------------
1  | 01             | 03
2  | 06             | 08
3  | 09             | 09

TABLE_B

DivisionNo | DivisionName
-----------+----------------------------
01         | London
02         | East Midlands
03         | West Midlands
04         | Yorkshire
05         | Lancashire
06         | Scotland
07         | South Wales
08         | North Wales
09         | Rep of Ireland

What I am after is something like this:

SELECT * FROM TABLE_B
WHERE DivisionNo BETWEEN TABLE_A.START_DIVISION and TABLE_A.END_DIVISION

I'm assuming a JOIN statement may do this but I'm not quite sure how to do this in SQL.

Any help on this would be greatly appreciated.

In ANSI SQL and SQL Server you would just use an INNER JOIN. I don't know whether Oracle supports this, but I'd be surprised if it doesn't... No DDL, so typing off the top of my head:

SELECT *
FROM table_b AS b
INNER JOIN table_a AS a ON b.DivisionNo BETWEEN a.start_division AND a.end_division


Error trying to SET a field when two fields in different tables match I am trying to set catid to 584 whenever the rows in the tables ykz2d_crmery_deals and ykz2d_maps_integration have the same company id using the following query:
UPDATE ykz2d_maps_integration
SET catid = '584'
WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id

I keep getting the following error:

Failed to execute SQL : SQL UPDATE ykz2d_maps_integration SET catid = '584' WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id failed : Unknown column 'ykz2d_crmery_deals.company_id' in 'where clause'
The column company_id does exist within ykz2d_crmery_deals.

What I can do to fix this?

UPDATE ykz2d_maps_integration , ykz2d_crmery_deals
SET catid = '584'
where ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id
;

The table must be listed in the update clause to avoi the error message


Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would like to insert the results into a a new table (Table 3).

I use pgAdmin 4.

Tables and contents

Table 1

This has two useful fields. Field 1 is named "Code_1" but this is not a unique field. Field 2 is named "Code_2" and this is unique. This table basically highlights all the features in "Code_2" and shows which features they are linked to in "Code_1".

CREATE TABLE Table_1 (Code_1 int, Code_2 int)

INSERT INTO Table_1 (Code_1, Code_2) VALUES  (1,1), (1,2), (1,3), (2,4), (2,5), (2,6)

code_1 | code_2
-----: | -----:
     1 |      1
     1 |      2
     1 |      3
     2 |      4
     2 |      5
     2 |      6

Table 2

The features in "Code_2" also have a number of measurable's associated with them but as explained above there is only one in this example - "Measure_1".

CREATE TABLE Table_2 (Code_2 int, Measure_2 int)

INSERT INTO Table_2 (Code_2, Measure_2) VALUES (1,50), (2,75), (3,100), (4,25), (5,25), (6,50)

code_2 | measure_2
-----: | --------:
     1 |        50
     2 |        75
     3 |       100
     4 |        25
     5 |        25
     6 |        50

Output table

I would like to create a new table (Table 3) with "Code_1" (unique) which is a sum of all the measures ("Measure_1") associated with "Code_2" which is associated with "Code_1).

code_1 | sum_measure_1
-----: | ------------:
     1 |           225
     2 |           100

SELECT t1.code_1, SUM(t2.measure_1)
FROM table1 t1
NATURAL JOIN table2 t2
GROUP BY t1.code_1

A working example can be found at db<>fiddle where I used the PostgreSQL 10 engine. You can build on this example.

Instead of using a static table, you might want to consider using a view instead. This has the advantage that the values will be calculated on-the-fly and aren't static.


How to set SQL Server index pages per fragment? I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)

The avg_fragmentation_in_percent is a whopping 97% or more and the fragment count is in the thousands. Very interestingly, the avg_fragment_size_in_pages is just slightly more than 1. I can defrag it, which helps, but I'd like to prevent this from happening in the first place.

Why is the pages per fragment so low? I have FILEGROWTH = 128MB for the DB as a whole, but this particular table is the most active - so is there a way to tell SQL Server to allocate more growth to this table or index so that the pages-per-fragment is higher?

First and foremost you should assess the impact of fragmentation. Much too often fragmentation is painted as the ultimate evil cause of all the server problems w/o any consideration of its actual impact. Fragmentation impacts several aspects:

Slow down scans due to impact on read-ahead efficiency and small IO size (page vs. extent)
Less efficient IO due to low rows per page because of frequent splits
Lower buffer pool cache hit ration (ie. lower page life expectancy) due to same as above
Higher cost impact of INSERTs due to frequent splits

All above are bad, but here is the real deal: you can have 97% fragmentation w/o any of the above symptoms being present. High fragmentation can indeed cause all these problems, but only if your specific workload actually interacts with fragmentation in a manner that causes these symptoms to appear.

I would recommend using Waits and Queues methodology to locate your actual performance bottlenecks and asses them appropriately. You may very well confirm that fragmentation has a serious impact and that would require schema and possibly application changes to address the problem, but you may discover that the problem lies somewhere else and the solution is completely different.

And lastly: you may also discover that $5k spent on more RAM and a few good SSDs alleviate completely the problems you have. Before you shrug in horror at such a remedy (what, no root cause being addressed? anathema!) consider the risk and costs associated with an application change...


Why would call to scalar function inside a Table Value Function be slower than outside the TVF? I am writing a Table Value Function, calling the function takes 10x as long as directly running the code.  I traced this to a call to a multi-line scalar function inside the TVF.  The call to the scalar function is excessively slow WHEN called from within the TVF.  The scalar function takes 3 int parameters and returns a single int result.

What would cause it to be slower from within a TVF?

First, the TVF is basically a sort of pivot table, returning just one row, with 13 columns.

The scalar function is a multiline scalar, it looks for a matched set of keys (ie input is columnA, output is columnB, where  input = columnA and active) in one of two tables.  Searching table 1, then table 2 and finally table 1 again with a slightly changed where clause.

Usage was originally a cte query with a join clause:

  With cte as ( select count(*) over (partition by c.c1) recs,
                       c.setId, c.c1, c.c2, n.name
                From tbl c
                     Inner join tbl n on n.id = schma.scal(c.c1, c.c2, c.c3)
                Where c.setId=@setId and c.endDt is null
   )

This cte was originally called 11 times, returning from (about half the time) zero to maybe 3 rows (with a where clause like c1=42).  So, I thought, hey, it's getting called a lot, since there are really only 4-15 rows in total, and the base tables are ~1,000,000, I'll cut that down to just 15 calls by putting the values into a table variable, and then doing an update.  This wasn't really any faster but it did let me prove that it was the call to the scalar function that was slowing things down.

That looked like:

   Insert into @cte(recs, setId, c1, c2)
    select count(*) over (partition by c.c1) recs, c.setId, c.c1, c.c2
    From tbl c
    Where c.setId=@setId and c.endDt is null;

    Update c
     Set nId =schma.scal(c.c1, c.c2, c.c3)
    From @cte c

The @cte had, as I said, from 4 to 15 rows (11 in my most used setId).  Putting a return first after, and then before this update is how I concluded that it was the scalar function causing the problem.  Basically it was tens of milliseconds, more than a second, another few milliseconds and done.

The query plan from SSMS wasn't useful, and I was thinking about seeing if I could get more details using the plans in the DMVs.

Knowing that TVFs can be used to replace scalars, and that this can improve performance, I gave that a try, and it worked (times in ~100 ms range for the whole query when called inside the original TVF).

I'm still scratching my head as to why it would take longer to call from within the TVF than from outside.

From outside the TVF, I get comparable times using the scalar as I do using the new TFV inside the original TFV.

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:

SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.


Do you think using ObjectID as field name is a bad practice? What can go wrong if I follow this design? I design a MongoDB collection where I am planning to use mongo ObjectID as a field name. The ObjectID is a PostID and in the array, I track all the userID who liked or disliked the post.

Yes, it is not a good idea to use a dynamic (changing value) like an ObjectId as a field name. To query a specific bookmark you are looking for a specific value for a specific field. And also, to index bookmarks fields it is not possible as the field names are changing.
You can use something like this, for the bookmarks field:
bookmarks: [
    { post_id: "606182bff8a94f5405c45268", like_dislike_users: [ 3, 5, 8, 12 ] },
    { ... },
     ...
]

Where bookmarks is a field of type array. And, each element in the array is a sub-document (a.k.a. embedded document).
Now, you can query the bookmarks for posts or/and like/dislike users by their ids (see Query an Array of Embedded Documents). In addition, for an efficient querying you can index on these fields (indexes on array fields are known as Multikey Indexes).


Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated Say there are entities called singulars, and entities called relationships.

It takes exactly two singulars to make up a relationships entity.

That pair of singulars can't be repeated elsewhere in relationships, in any order.

One way to model it could be this way:

+----------------+
|relationships   |         +----------+
+----------------+         |singulars |
|id              |         +----------+
|singular_id_1   <---------+id        |
|singular_id_2   <---+     |attribute1|
|pair_description|         |attribute2|
|pair_date       |         |          |
|                |         +----------+
+----------------+

With this pattern, it becomes necessary to check both foreign key fields in relationships for the existence of singulars, which could be on either side. The order doesn't matter, yet it is defined in the schema... So the queries end up with a number of AND/OR groups and cases.

Expanding on that approach could be to store two records for every pair, with the singular_id_[n] swapped on both sides. While that solves some querying complexities, it would introduce additional complexities to make it infeasible.

Using an intermediate table seems like one potential solution:

+----------------+       +-----------------------+
|relationships   |       |singulars_relationships|        +----------+
+----------------+       +-----------------------+        |singulars |
|id              <-------+relationship_id        |        +----------+
|pair_description|       |singular_id            +-------->id        |
|pair_date       |       |                       |        |attribute1|
|                |       +-----------------------+        |attribute2|
+----------------+                                        |          |
                                                          +----------+

So the records might end up something like this:

+----------------------------------+
|relationships                     |
+----------------------------------+
|id   pair_description   pair_date |
+----------------------------------+
|1    Fizz buzz blitz    2022-02-20|
|2    Blitz buzz fizz    2022-02-22|
+----------------------------------+

+----------------------------------+
|singulars_relationships           |
+----------------------------------+
|relationship_id   singular_id     |
+----------------------------------+
|1                 1               |
|1                 2               |
|2                 3               |
|2                 4               |
+----------------------------------+

+-----------------------------+
|singulars                    |
+-----------------------------+
|id   attribute1   attribute2 |
+-----------------------------+
|1    Fizz         Blitz      |
|2    Buzz         Foo        |
|3    Bar          World      |
|4    Blorg        Hello      |
+-----------------------------+

There, singulars_relationships is where the pairs are defined. If a singular_id exists in there, it is already in a pair. One problem that may arise with this pattern could be that three or more singular_id s could end up associated with a relationship_id, and the "exactly n" constraint would then be compromised.

Are there official terms for this type of scenario? And other theory and alternatives?

Are there official terms for this type of scenario?

Yes.  This is a Symmetric Relation.  And "relation" here has the same meaning as in "Relational Database".  An RDBMS is a database management system designed around storing relations.  However RDBMSs don't have a native way to store symmetric relations.  You either have to store both tuples, eg (a,b) and (b,a) as separate rows, or you have to use some sort of convention to store only one tuple.  A common approach is to use a check constraint on the FKs.

eg

check (singular_id_1 < singular_id_2)

Assuming the relation is anti-reflexive.


Why does the "Date Created" for the master database show such an old date (8 April 2003) in SQL Server 2008 R2? Why does the "Date Created" for the master database show such an old date?

I created the new instance today, not 2003.

This is just an educated guess mind you, but I would assume it is the date the new master database was created at Microsoft for SQL Server 2005.

I checked SQL Server 2012, 2014 and 2016 and all have the exact same date/time.


Is there any way to efficiently perform the equivalent of DENSE_RANK in MongoDB? SQL Server and Oracle both have DENSE_RANK functions. Is there a way to do something similar in MongoDB without having to resort to MapReduce? In other words, suppose you have a T-SQL select clause like this:

SELECT DENSE_RANK() OVER(ORDER BY SomeField DESC) SomeRank

What is the best way to do the same thing in MongoDB?

(Note: This is a repost of the MongoDB question over here. I'm hoping to get more feedback from DBAs...)

MongoDB doesn't have any concept of ranking.  The closest I could find comes from here:

Here's some sample data:
 > db.scoreboard.find()`
 { "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "user" : "dave", "score" : 4 }
 { "_id" : ObjectId("4d99f71b50f0ae2165669eaa"), "user" : "steve", "score" : 5 }`
 { "_id" : ObjectId("4d99f72350f0ae2165669eab"), "user" : "tom", "score" : 3 }

First, find the score of the user "dave":
 db.scoreboard.find({ user : "dave" }, { score : 1 }) { "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "score" : 4 }

Then, count how many users have a higher score:
 db.scoreboard.find({ score : { $gt : 4 }}).count()
 1

Since there is 1 higher score, dave's rank is 2 (just add 1 to the
count of higher scores to get the rank).

Obviously, this is far from ideal.  However, MongoDB simply does not have any type of functionality for this since it's simply not designed for this type of querying.


db2 removal of Generate always Expression on Timestamp data type I have a column UPDATE_TIME with an expression

 TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

How to remove the Generated always for timestamps?

I tried

  db2 "alter table xxxx alter column UPDATE_TIME drop expression"

This is not valid syntax.  You need to use,

 alter table xxxx alter column update_time drop generated

See the documentation on alter table for more details.


MySQL / MyISAM: Queries sometimes very slow, no waiting for lock time I have a web application using PHP 7.0 and MySQL 5.5.54-0+deb8u1-log.

Occasionally, there are long waiting times in the application, which seem to be related to slow database queries. In the slow query log of MySQL, I can find some queries which have been executed unusually slow, though they normally are finished in milliseconds. I use MyISAM which is obviously prone for waiting for locks, but - what's strange is that the queries do not have to wait for locks at all.

Of course you can argue that the query is not optimized and all - regarding multiple joins, sorting, union all etc. But if I run the following example in the database while the application is on its load peak, it usually runs just nice and quick!

Here one example:

# Query_time: 31.449621  Lock_time: 0.000284 Rows_sent: 22  Rows_examined: 131
SET timestamp=1494921515;
            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table4
            INNER JOIN table1
                ON table4.userId1='192203'
                AND table4.userId2=table1.userId
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.class IN ('A','B')
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)

            UNION ALL

            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table1
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.userId='192203'
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)

            ORDER BY date DESC LIMIT 0,100;

So - what I'm looking for is not a way to make this specific query faster. Of course it wouldn't hurt, but normally this specific query is finished in about 0,01s!

If this occurs, then there are usually some more normally fast queries which are executed slowly. I see it in the slow query log.

One observation is that the execution time seems always to be around 30 seconds - and the time limit for PHP scripts is 30 seconds in my case, so I wonder if there is any relation. If all related MySQL queries are automatically canceled when a PHP script is killed, then this would explain it. By the way, I use PDO to connect PHP to MySQL.

One idea I had is that maybe the actual query execution time is not so bad, but maybe MySQL has issues while trying to hand over the resultset to PHP - for an unknown reason. But on the other hand, I'm not sure if this waiting time would be added to the query execution time in the slow query log! Does anybody know? What else could be the problem?

If you are looking for the latest 100, you have not quite gotten it.  You need LIMIT 100 in the inner queries, too.  (OK, that will slow down the query a little, but it might be 'correct'.)

Back to performance...

Short answer:  Switch to InnoDB; it is likely to help, especially in avoiding strange locks.  (Instead, you will other strange locks, but usually not for as long.)

Which table is date in?  Hard to analyze the query without having all columns qualified and SHOW CREATE TABLE.  EXPLAIN SELECT ... would also help.  But I will try...

Indexes needed:

table4: INDEX(userId1, userId2)  -- in this order
table1: INDEX(userId)
table3: INDEX(groupId)
table2: INDEX(userId, class)  -- in this order


How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE Consider a table with the following columns:

phone BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(64),
state VARCHAR(64)

We would like to partition the table on state, however that would require dropping the primary key constraint on phone and replacing that constraint with a UNIQUE constraint. We make use of INSERT ... ON DUPLICATE KEY UPDATE and I would like to know how it would affect the system. Surprisingly little is written on the subject from reliable sources, and I'm sure that there are hidden gotchas which will not be immediately apparent to my naive eye.

What problems might we encounter with a partitioned table lacking a primary key, especially in regards to INSERT ... ON DUPLICATE KEY UPDATE queries?

The database in question in running MySQL 5.5 on Amazon RDS. The table in question has upward of 20 million rows and is often used in JOINs with other similarly-sized tables.

When we talk about partitioning tables in relational database platforms is recommended that it is always done on tables where the field is numeric. As a suggestion I would create an additional field that would serve as a surrogate key and this in turn would be used for partitioning the table.


Difference between a schema and a database in db2 I can not figure out what is the difference between schema and database in db2 I find this SQL so confusing for example :

CREATE TABLE schema1.table1
 ....
 ....
 ....
IN database1.tabspace1

Can we say that the database in this case contains the tabspace whereas the schema contains the tables?

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source.

The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized.
A table space is a storage structure containing tables, indexes, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system.
A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.


MySQL get next unique value without auto increment I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization.  There are multiple 'sites', and each site has work order numbers starting at 100.

WorkorderID    SiteID    SiteWorkorderNum
     1           1            100
     2           1            101
     3           2            100

WorkorderID is the auto increment field.
SiteID and SiteWorkorderNum are both set as a unique constraint.

Whenever a new work order is about to be inserted for a specific site, the application checks for the max(SiteWorkorderNum) for the siteid of whoever is logged in and returns that value to the insert statement.  The problem I want to avoid is if two users of the same site both enter a new work order at the exact same time.

I have a couple of possible solutions but I want to see if there is a better way as each has it's drawbacks, but one I'm sure will be better than the other, and of course I'm open to new ideas.

1) Lock the table to guarantee no other users retrieve a SiteWorkorderNum value until after we have retrieved and inserted our values (however let's pretend we have thousands of users trying to enter work orders every minute, wouldn't the table locks slow things down?)

2) Don't lock the table, and retrieve the next available SiteWorkorderNum and try to insert into the database, if I receive a unique constraint error, catch the error and try again until I'm successful.  (this might keep the table freed up, but again pretending we have thousands of users at the same site, would the multiple database calls be a little inefficient?)

Am I being too paranoid about how either of these solutions 'could' effect performance?  Of course I don't have thousands of users, but I'm wanting to apply best practice in this design in case I ever work on a project which does have high traffic.

What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;

Here is a sample loading into this table:

mysql>     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql>     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql>     USE david
Database changed
mysql>     CREATE TABLE site_workorder_seq
    ->     (
    ->         SiteID int not null,
    ->         SiteWorkorderNum int not null auto_increment,
    ->         PRIMARY KEY (SiteID,SiteWorkorderNum)
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>     INSERT INTO site_workorder_seq (SiteID) VALUES
    ->     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    ->     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql>

Let's look at the the last WorkorderNum from each site

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql>

Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;

Let's run this and see what happens

mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql>

As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.


Avoid PG::TRDeadlockDetected I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR:  deadlock detected (see the full error below).

How can I avoid this? Can I add a EXCEPTION which will return 0 in case this happens, I will prefer not to, but the bulk insert is very convenient for me.

CREATE OR REPLACE FUNCTION "univ"."gc_title_desc"(IN _title text, IN _desc text, OUT result_id int4) RETURNS "int4"
AS $BODY$BEGIN
LOOP
BEGIN
WITH sel AS (
  SELECT id
  FROM   univ.results
  WHERE  title = _title AND description = _desc
  )
, ins AS (
  INSERT INTO univ.results (title, description)
  SELECT _title, _desc
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins
INTO   result_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- inserted in concurrent session.
    RAISE NOTICE 'It actually happened!'; -- hardly ever happens
END;

EXIT WHEN result_id IS NOT NULL;
END LOOP;
END
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

Error:

2015-02-25T15:11:15.078Z 3564 TID-oti3yx4ms WARN: PG::TRDeadlockDetected: ERROR:  deadlock detected
DETAIL:  Process 15507 waits for ShareLock on transaction 14912613; blocked by process 15690.
Process 15690 waits for ShareLock on transaction 14912617; blocked by process 15507.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (12728,26) in relation "uniq_search_results_title_description"
SQL statement "WITH sel AS (
    SELECT id
    FROM   univ.search_results
    WHERE  title = _title AND description = _desc
  )
  , ins AS (
    INSERT INTO univ.search_results (title, description)
    SELECT _title, _desc
    WHERE  NOT EXISTS (SELECT 1 FROM sel)
    RETURNING id
  )
  SELECT id
  FROM sel NATURAL FULL OUTER JOIN ins"
PL/pgSQL function univ.gc_title_desc(text,text) line 5 at SQL statement

Adding a new value into a unique index locks out (until commit) any other transaction from inserting the same value. When doing that with multiple values in the same transaction, these locks accumulate.

Then when several parallel transactions do that concurrently in no particular order, that creates the opportunity for transactions to inter-lock each other, which causes the error you're reporting.

There's no easy technical solution, because it's more of a conceptual problem.

At the conceptual level, there's a contradiction between making sure that different transactions can't insert an identical value into a unique index (that part being enforced by the database engine), and running these in parallel without having them to care about what the others are doing.

At a higher level (inside the function it's too late), the transactions have to be serialized, or the work partitioned so that parallel transactions can not hit the same part of the unique index at the same time.


Oracle data access restriction I am working on some data access restriction feature in Oracle 11g and have been struggling since then.

What I want to achieve is, that I want to allow a specific group only to access a particular set of data and the rest should not be able to see them (or have them hashed) ...

Let's image two users

USER_JOHN and USER_TIMMY

The role settings for them is following

USER_JOHN
  - it_analyst
  - sales_analyst
  - read_all

USER_TIMMY
  - risk_analyst

The %_ANALYST roles are just for the sake of demonstration only .. the only important role in this case is the READ_ALL role which allows JOHN to access the "privileged" data ...

Example of privileged data (columns marked with * are considered "privileged"):

CLIENT_TABLE
-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

When a user (with READ_ALL) role queries this table I want him to get the same data set as you can see above

USER_JOHN:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |     8/1/1990      | Sesame Street |  Male   |
 2  |  Jane   |     1/18/1976     | Corner Road   | Female  |

But if a user without this READ_ALL role queries this table, they should get only non-privileged data in a read-able form ... see below

USER_TIMMY:$> SELECT * FROM client_table;

-------------------------------------------------------------
ID  |  Name   |   Date of Birth * |    Address *  |   Sex   |
-------------------------------------------------------------
 1  | Franco  |    3978005636     |  4259532014   |  Male   |
 2  |  Jane   |    2282325776     |  1146039631   | Female  |

As you can see, these "privileged" records are hashed ... they can even be empty or not select.able at all .. thus the result will look like this

USER_TIMMY:$> SELECT * FROM client_table;

--------------------------
ID  |  Name   |    Sex   |
--------------------------
 1  | Franco  |   Male   |
 2  |  Jane   |  Female  |

Is there some solution to achieve this? The best result for me would be hashed values .... I have read something about Oracle VPD which should do the same I just do not know, how to apply that on roles globally ...

What I have done so far is that I have created the same object within different schema ...

SCHEMA_RAW.CLIENT_TABLE     -> accessible for READ_ALL; not accessible for others
SCHEMA_HASHED.CLIENT_TABLE  -> accessible for all

I know it's not the best solution but at least I do have some data protection implemented .. the major drawback of this solution is the script-sharing .. if there is a user that has an access to SCHEMA_RAW only .. they need to change the script provided by users that have an access to SCHEMA_HASHED only ...

Note: there is not just this single table ... there are many (hundreds of them) that needs to be secured this way ...

EDIT: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

found it ...

ANSWER: Oracle Data Masking and SubSetting

https://www.oracle.com/technetwork/database/options/data-masking-subsetting/overview/index.html


Attatch database - cannot browse to database I have installed MS SQL Server Developer 2014.  I am trying to attach the Adventure Works database.  The usual process I would follow is:

1) Right click on Databases
2) Select Attatch
3) Click Add

Step four usually involves navigating the file system to find the .mdf and .ldf, however instead of a file system I see this:

I am a Developer and not a DBA.  I am also new to SQL Server 2014.

What do I do now?

Just to eliminate access or permission related issue.... Check the following below:
**What login is running the SQL Service - For testing, change this to localsystem or a local administrator
**Are you running your SSMS with administrative privileges - If not run as administrator before connecting to your instance

Also try using other modules in SSMS that would require a browsing to a file and check if that works. For example, instead of using the attach option, test using a restore from backup. HTH


MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record I am joining together three tables in order to display the names of recommended providers for a property.

First I join my properties (alias p) table to the properties_recommended_providers table

LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id

I use a LEFT join to still get records of properties that don't have any recommended providers and wouldn't be in the properties_recommended_providers table.

Then I join with the recommended_providers table to get the names of the providers

LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id

In the select clause I'm pulling info about each property (name, id, street, city, etc). Lastly, I want to list all the names of the recommended providers for each property in a final column which rp.name.

My issue is that before selecting rp.name I was getting distinct records for each record and now adding in rp.name I'm getting multiple records for each property that have more than one recommended provider. The multiple property records contain identical information in every column except the rp.name.

I know it's possible to concat together values from separate columns in a row and display that in a new column in a select query but in this particular case is it possible for any property with more than one recommended provider to have all those providers appear in one field together (separated by commas) for a single record for that property instead of having that property listed multiple times just to display each unique recommended provider ?

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property,
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

or

SELECT
  p.id AS property_id, property,
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle


Last occurance of time relative to now() What's the most elegant way to ask Postgres for the last 4:05pm relative to now()?

I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much cleaner manner.

I speak with reference to no table. i.e. There is no FROM in this query. Just a simple SELECT.

The result of this is either a TIMESTAMP at 4:05pm today, or yesterday.

The logic is similar to what @Daniel already posted, but this is a bit faster (twice as fast in my tests on pg 9.3):

SELECT CASE WHEN now()::time > time '16:05'
            THEN now()::date + time '16:05'
            ELSE (now()::date - 1) + time '16:05' END AS ts;

now() is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP (which you can use as well). I use now(), because it is shortest and fastest. CURRENT_TIME and CURRENT_DATE are implemented with now() as well.

Note that you get a timestamp [without time zone] that is interpreted according to your current time zone setting.


Split data into X days' Cohort (SQL Server) Hi I want to split data by x days' cohort.

the table

SELECT
 --earliest_time,
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day >  0 and by_day <= 10 then 1 else 0 end) day_10,
    sum(case when by_day > 10 and by_day <= 20 then 1 else 0 end) day_20,
    sum(case when by_day > 20 and by_day <= 30 then 1 else 0 end) day_30,
    sum(case when by_day > 30 and by_day <= 40 then 1 else 0 end) day_40,
    sum(case when by_day > 40 and by_day <= 50 then 1 else 0 end) day_50,
    sum(case when by_day > 50 and by_day <=60 then 1 else 0 end) day_60,
    sum(case when by_day > 60 then 1 else 0 end) day_60plus
from cohort_days co

This splits the data into cohorts of 10 days. If I want to change it to 20 days' cohort, then I need to edit them 1 by 1. I want to just define x days' cohort once, then the data can generate the cohort groups itself automatically.

I want to know is there easier way to do this than the above code? For example, I just need to define x days' cohort once at the beginning then I can use loops to iterate over it to generate the rest of the cohort groups.

A bit like this:

declare @d int
select @d = 20
select * from cohort_days
SELECT
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day > 0 and by_day <= @d then 1 else 0 end) CONCAT('ay_: ', @d),
    select @2d = @d*2
    sum(case when by_day > @d and by_day <= @2d then 1 else 0 end) CONCAT('ay_: ', @2d)
    select @3d = @d*3
    sum(case when by_day > @2d and by_day <= @3d then 1 else 0 end) CONCAT('ay_: ', @3d)
from cohort_days co

If I want to change cohort days to 50 days, I just need to change select @d = 20 to select @d = 50

Your desired outcome is that you can change one value (the cohort width) to have all rows re-allocated to the new groupings.

I'd use a separate table to hold the upper and lower bounds for each cohort. These values can be generated a run-time according to the width parameter. There are two special cases - the day-zero cohort which only ever contains a single value and the last cohort which has an unlimited upper bound. There will have to be a second parameter to say how many "normal" cohorts there must be. To generate this I will use a "numbers" table.

create table #Cohorts
(
    Label       varchar(20) not NULL,
    LowerBound  int         not NULL,
    UpperBound  int         not NULL
);

declare @Width int = 10;
declare @Depth int = 5;

insert #Cohorts
(
    Label,
    LowerBound,
    UpperBound
)
select
    'day_' + CONVERT(varchar(20), ((n + 1) * @Width)),
    1 + (n * @Width),
    ((n + 1) * @Width)
from Numbers
where n <= @Depth

union all

select 'day_0', 0, 0        -- the special case for day_0

union all

select
    'day_' + CONVERT(varchar(20), ((@Depth + 1) * @Width)) + 'plus',  -- a special label for the last cohort
    1 + ((@Depth + 1) * @Width),    -- the upper limit of the dynamic cohorts
    2147483647                      -- the maximum value of an int; pseudo-infinity

For the values given in the question this returns

Label       LowerBound  UpperBound
----------  ----------  ----------
day_0       0           0
day_10      1           10
day_20      11          20
day_30      21          30
day_40      31          40
day_50      41          50
day_60      51          60
day_60plus  61          2147483647

In your original question you have used (open, close] bounds. For simplicity I have used [closed, closed]. It doesn't matter since the bounds are integers and the cohorts are contiguous. It just means your >  and <= becomes my >= and <=. With tinkering your convention could be used equally well.

We assign each data row to a cohort by joining to this temporary table. The counting is done using GROUP BY.

select
    c.Label, COUNT(*)
from your_data_table as d
inner join #Cohorts as c
     on c.LowerBound <= d.by_day
     and d.by_day <= c.UpperBound
group by c.Label;

This will give one row per cohort. To get a single row with a column per cohort requires a PIVOT. There is a T-SQL statement to do this. There are other methods, too, of which your sum(case..) is one.


Data validity and accuracy according to database integrity What does it mean that data is valid and accurate according to database integrity?

Database integrity generally refers to the following conditions being satisfied:

All constraints on a table are satisfied (including unique constraints, foreign key constraints, primary key constraints, etc....)
All check constraints are satisfied (for example, in Oracle, I can have a check constraint that requires that a value be nonzero, but not otherwise unique.)
All unique indexes are satisfied
All child rows in child tables have parent rows in parent tables (foreign keys)
All data types are satisfied

Note that there's not really anything here that speaks to if the data is valid and/or accurate according to your business logic. This logic is anything you put into triggers, procedures, validation routines, etc., that you build yourself to try and make sure that everything going into the database is good. (It should be noted that Oracle's check constraints could be considered a form of business logic that is enforced like a unique or primary key constraint).

The database will happily reject data that doesn't fit into the above criteria, assuming they are defined, but will also happily accept invalid or inaccurate data that isn't otherwise in the above criteria. If I have a field that contains people's names, the database can't make the determination if "John Smith" or "Jonh Smiht" is accurate (notice the typo). So while the database will say everything's good, you know from a visual inspection that it isn't. (This example, while trivial to do, is hard to do anything about. After all, how do you know his name isn't really spelled this way? I use it only as an example of any data coming into the system. A field expecting percentages could easily have 5% as 50% -- how can the database tell which is valid? It can't without you writing lots of complicated business logic, and even then -- at some point, how do you know it isn't 50%? Or that it isn't 5%? Or that it was supposed to be 25%, and the typist got really confused!)

Use the database integrity mechanisms to your advantage, but don't assume that because there are no integrity errors that your data is good. It can still, easily, be bad and misleading. Further, don't assume that your business logic ensures good data either. Yes, you can prevent obvious errors (like, say, a field that shouldn't have values out of the range of 0 to 100), but once ranges are satisfied, at some point, you have to trust the user who is giving you the data. And at some point, the user will give it to you incorrectly. Poof, bad data, with perfectly valid database integrity.


Syncronize mysql databases between local and hosted servers automatically We have many websites with Development, Staging and Production servers. We have many developers for many projects. We need a solution to synchronize the developer and staging databases. After that one is worksing we can move to the live database.

That one needs to be fully automatically synchronized so that developers don't need to run that tool each and every time.

use replication master=> slave ? be carefull of conflict


How to merge MySQL backup files (*.sql) We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one script cause to remove the data created by the previous script. In addition to these sql files, we have also some other backup folders containing *.FRM and *.MYD files. In all of two cases I think these backups are Differential or Incremental and we have to merge/combine all of them. If so,  how can we combine them without losing data? As I have no experience on MySQL, could you explain a way by using MySQL Workbench if it is possible? Thanks in advance.

Some lines from one of the sql file:

DROP TABLE IF EXISTS `BLACKLIST`;
    CREATE TABLE `BLACKLIST` (
      `ID` int(11) NOT NULL auto_increment,
      `NAME` varchar(30) NOT NULL,
      `SURNAME` varchar(30) NOT NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `BLACKLISTINDEX` (`NAME`,`SURNAME`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

.SQL files are text. You can open them in a text editor. Then, find and remove the DROP TABLE and CREATE TABLE statements. Finally, use the workbench or any other client to run the remaining INSERT statements.

On the subject of differential and incremental backups, you'll have to restore your SQL files in the correct order to get the results you hope for. That means you'll have to work out that order very carefully indeed from the files you have. Commercial RDMSs like Oracle have elaborate schemes for making incremental and differential backups, along with schemes for restoring them. MySQL doesn't have that kind of thing, so whatever you have is either home-grown or based on some sort of third party software package.

If this were my project, I'd create a sacrificial copy of the database and all the backup files, .frm files, and other stuff. I'd then mess around trying to get stuff restored in the right order.

Once I was convinced my copy was correct, I'd make an SQL backup of the whole thing. I'd actually create two backup items: one containing just the CREATE TABLE statements, and the other containing just data. The workbench can do that.

Then restore that clean backup to create a new production database.

What the workbench can't do is help you with the FRM and MYD files. To use those you'll need to figure out how to put them in the right place in the server file system and restart the MySQL server that's looking at them. Treating those files as backups is a sketchy and risky business.  If you successfully restore them, think of your accomplishment as a talking donkey. It's not a surprise it works poorly, it's a wonder it works at all.

Going forward: your ordinary SQL backup files should not contain data definition language such as DROP TABLE or CREATE table statements. You can create separate files containing only those statements and no data, and keep them around if you need them.


SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :)

A few times I have ran into a weird scenario; when I run:

SELECT *
FROM ph.FileGroupDetail
ORDER BY partition_number
Go

There is the same filegroup showing twice with 2 different partition_numbers, 1 correctly at the end with a range value, the other at the start with a null range_value.

click here for enlarge image

Couple of questions:

How is this happening, where have I gone wrong?
How do I resolve the issue, that means how to get rid of the one at the start as I already have an empty partition at the beginning.

I've tried deleting the file (worked when it was empty) and filegroup, but filegroup said it couldn't be deleted.

Can someone explain please how this has happened and how to get rid of the partition 2 entry?

The results indicate at some point an explicit NULL partition boundary was added to the function when the partition scheme NEXT USED filegroup was set to DailyAlbertFG30.  Also, I don't see DailyAlbertFG2 used.  Perhaps there was once a partition on that filegroup that was subsequently merged.

Below is a script that shows how a FG30 partition with the NULL boundary can be created.  The NULL boundary might have been added accidentally.

CREATE PARTITION FUNCTION DailyAlbertPF1 (datetime2(3)) AS RANGE RIGHT FOR VALUES();
GO
CREATE PARTITION SCHEME DailyAlbertPS1 AS PARTITION DailyAlbertPF1 ALL TO ([DailyAlbertFG1]);
GO
CREATE TABLE dbo.FactAgentAlbertPortalSessionEntries
    (
      DateTimeColumn datetime2(3)
    )
ON  DailyAlbertPS1(DateTimeColumn);
GO

DECLARE @FileGroupNumber int = 1;
DECLARE @DateTimeBoundary datetime2(3) = '2015-04-15T00:00:00.000';
DECLARE @SQL nvarchar(MAX);
WHILE @DateTimeBoundary <= '2015-05-14T00:00:00.000'
BEGIN
    SET @SQL = N'ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG' + CAST(@FileGroupNumber AS nvarchar(5)) + N';';
    EXEC(@SQL);
    ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
    SET @DateTimeBoundary = DATEADD(day, 1, @DateTimeBoundary);
    SET @FileGroupNumber += 1;
END;
--add NULL boundary on DailyAlbertFG30
SET @DateTimeBoundary = NULL;
ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG30;
ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
GO


Optimized MySQL "optional" FULLTEXT search on multiple tables I need to select data with optional columns in where clause based on client input.
my query is like this:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id
where a.status=1
AND (0=@bid OR a.b_id=@bid)
AND (0=@cid OR a.c_id=@cid)

@bid and @cid are client inputs, the values must be >0 with default to 0 if client does not provide the input.

client can provide no inputs so it will select all data where status=1 ignoring b_id and c_id columns

client can provide @bid or @cid or both and the query will select accordingly.

table engine is InnoDB, columns are indexed, foreign key and relationships set.

so far everything is fine. EXPLAIN SQL shows selection is done based on indexes provided.

now I need to add full-text search on all 3 tables to query is where I have problems. text search is also optional if client provides any keyword to search or not.

fulltext index is defined for a.title, b.txt and c.txt

I changed the query to:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id
where a.status=1
AND ('0'=@keywords OR (MATCH(a.title) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(b.txt) AGAINST(@keywords IN BOOLEAN MODE) OR MATCH(c.txt) AGAINST(@keywords IN BOOLEAN MODE)))
AND (0=@bid OR a.b_id=@bid)
AND (0=@cid OR a.c_id=@cid)

the query seems to be working returning the results I want but the explain query returns type=ALL and a full table scan so no the query is not working in an optimized way.

if I change the OR between matches to AND then explain query returns type=fulltext and select is done on FULLTEXT index, but I need the OR style.

I was thinking about joining different result sets but couldn't find out how since the input is optional and there might be no input and therefore no full-text search needed to be done.

any solutions?

EDIT:

OK with thanks to jkavalik's comment and Rick's answer it seems I need to add some clarifications:

actually I am using a data service from WSO2 Data Services Server. so I am just passing the input parameters to the data service and I can not generate the select query based on user input. (there's an option to pass part of a query string as input parameter but I am not planning to do that for security reasons)

so I have two options:

write separate queries which accept different sets of input parameters for each case of input parameter combinations. deciding to send the parameters to which query based on user inputs. well the optional inputs being 10+ in a real situation, that will be lots of queries and don't seem to be the right solution. optional fields may change over time, maintenance in future is expensive. doesn't looks like the standard way to handle this.
write a single query in a way it can handle the optional fields.

I prefer option 2 unless there is a third option I am not aware of?

about the @variables they are either being replaced by input parameter's value or the default value (0 in this example) before query being passed to and executed in MySQL. and actually they are :variable not @variable but I changed it to @ in this example for better reading or understandings.

Wrong approach.  Instead, "construct" the SELECT based on which fields the user provides.

Why?  OR does a poor job of optimizing.  If, instead, you remove the clause from WHERE, you don't need the test for 0, thereby avoiding the OR.

Worse yet is an OR spanning multiple tables, as you need to to with the FULLTEXT checks.  The only way for the query, as it stands, to be run is to evaluate all combinations of all rows in all tables, then start filtering.  (OK, I exaggerate, but not by much.)

A common way to avoid OR is to turn it into UNION:

( SELECT id FROM a WHERE MATCH(a...) ... )
UNION DISTINCT
( SELECT id FROM a JOIN b ON ... WHERE MATCH(b...) ... )
...

That will get all the possible values for a.id.

Why LEFT?  That will slow down the 2nd and 3rd parts of the subquery for no good reason.  Remove LEFT.  If there is no b row, that's equivalent to the MATCH failing.

Then, put that in a subquery to get the rest of the desired data:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM ( the above "union" query ) AS tmp
JOIN a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id;

In this case you might need LEFT; but remove it if you don't.

Other notes

While you are 'constructing' the query, you may as well plug in the @ values.  @variables inhibit the optimizer in some cases; actual literals are more easily processed.  Be sure to escape them to help prevent "sql injection".

In case it was not obvious, 0=@bid OR goes away.


SSMS 2014 installation - "Invalid license data" when opening ASP.NET files I have just installed SSMS 2014 Express on a brand new Windows Server 2012 (x64) so I connect to the SQL Server 2014 instance running on a different Windows Server 2012.  This is working without issue and I can connect to the instance no problem.

The installation also appears to have included Visual Studio 2010 Shell (Isolated) which has made file associations with all my ASP.NET files (.aspx, .ascx, web.config, etc).

Now when I try and open any ASP.NET files via Windows Explorer, I get the infamous error message from "Visual Studio 2010 Shell" ...

  Invalid license data. Reinstall is required.

I've seen hundreds of messages on the internet about this infamous message, but I cannot find a single straight answer about how to fix it.

The most prominent one is about how this can happen on when you upgrade from a "higher beta to a lower RC"... such as this MSDN Blog.  But this is not the case for me, as this is a vanilla installation.

I have also tried installing the SQL Server Data Tools as per this post on Experts-Exchange, but that did help either, and just cause me more confusion.

I have uninstalled/reinstalled SSMS 2014 3 times now (which can't be a good thing in itself).

Can anybody please point me in the right direction?

If this is not strictly a DBA question, please let me know where I should have posted it

Visual Studio Shell is just the plumbing required to run the shared components within SSMS. If you want to manage .aspx files etc., you'll need to install the free Visual Studio Express (or a higher edition if Express doesn't have all the functionality you need).


how to use a different tmp dir for INTO OUTFILE select with MySQL? I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough).

The problem is, I am getting an error code 28. I am pretty sure it is because it is running out of disk space on the default /tmp location. I have enough space on the disk elsewhere, if I could just specify at runtime what location to use for tmp.

I found out I can change the tmpdir by modifying my.cnf, but I do not have sufficient server privileges to modify it or restart mysqld.

What is the solution here?

EDIT: a few answers have suggested using mysql -e. I may have to go this route and post-process my file. The reason I wanted to use SELECT ... INTO OUTFILE is because I want to

fields   terminated by '\t'
         escaped by '\\'
lines    terminated by '\r\n' ;

Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'

The other option, if you have access to the command line, is to use the -e switch:

> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'


How to find corrupt page in SQL Server 2008 I know I can execute a DBCC CHECKDB and get a status of my database.

Questions

How can I find if there is some corrupt data page into my database?
If an error is throwndue to page corruption, where can I find which pages are corrupt?
How can can I find out the page number of each corrupt page.

Can anybody can tell me where to find those page ids ?

Yes, there is a suspect_pages table.

Here is more good info on DBCC CHECKDB http://blogs.msdn.com/b/cindygross/archive/2010/06/13/dbcc-checkdb-database-integrity.aspx


Linux sqlcmd connecting to Windows Sql Server using kerberos I'm pretty sure I am going to have to expand upon this a bit as I am new, and my question is probably not clear.

I have an Active Directory environment that includes a Microsoft SQL Server running Windows Server. The SQL server is using "Windows Authentication Mode." It looks like Microsoft does not support "Windows Authentication Mode" when using sqlcmd from Linux unless it's Azure (correct me if I am wrong), so I am trying to create a service account and then generate a Kerberos ticket for that account. The problem is I am not even sure where to start. I did find some information around how-to create a service account and, maybe, a Kerberos ticket for that. But how do I use this on Linux afterward? If you know some existing guides that I missed on this topic feel free to point me there.

Thanks

Try this guide: Tutorial: Use Active Directory authentication with SQL Server on Linux

And then go to point:
Connect to SQL Server using AD Authentication

Basically you will need to ssh to the client using your AD credentials and the run sqlcmd without credentials

ssh -l user@contoso.com client.contoso.com
sqlcmd -S mssql-host.contoso.com


Select repeating number x times and then increment I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number.

For example 10 rows repeating 4 times would result in the following:

| # |
|---|
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |

One solution using simple integer arithmetic on a table of numbers. This example uses an efficient way to generate a sequence on the fly, in practice you would probably have a permanent Numbers table.

DECLARE
    @z bigint = 10,
    @x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
    # = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;

Output:

╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝

Execution plan:


Total hours worked per employee per day including overnight sessions I have a table like this:

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |      state      |
+--------------+---------------------+-----------------+
(      4       | 2014-03-01 11:12:00 |         0       )
(      5       | 2014-03-01 12:28:06 |         0       )
(      4       | 2014-03-01 12:50:07 |         1       )
(      4       | 2014-03-01 13:38:00 |         0       )
(      5       | 2014-03-01 13:28:06 |         1       )
(      4       | 2014-03-01 18:42:15 |         1       )
(      4       | 2014-03-02 08:11:08 |         0       )
(      4       | 2014-03-02 13:26:11 |         1       )
(      5       | 2014-03-02 14:16:15 |         0       )
(      4       | 2014-03-02 16:16:15 |         0       )
(      5       | 2014-03-02 17:48:21 |         1       )
(      4       | 2014-03-02 19:39:03 |         1       )
(      5       | 2014-03-02 20:16:15 |         0       )
(      5       | 2014-03-03 04:16:15 |         1       )
+--------------+---------------------+-----------------+

primary key omitted for brevity - please see http://www.sqlfiddle.com/#!9/30503/1

0 means login - 1 means logout. There can be several logins/logouts per user per day or period of days if for example an employee works overnight (as employee 5 does between 2014-03-02 and 2014-03-03 )

I am trying to get the total sum of hours worked per employee per day/shift (e.g. some shifts will span over two days):

1      2014-03-02   total hours worked    08:32:00

I came up with this query:

SELECT CONCAT(
MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked,
empid,
Date(time)
FROM emplog T
GROUP BY empid, Date(time)

Which only gave me the time elapsed between the min login and max logout.

From an earlier question I learned how to get the total working hours per employee per day which does not include night shifts:

select empid, work_dt,
    SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time)
           from emplog as b
           where a.empid = b.empid
             and date(a.time) = date(b.time)
             and b.time >= a.time
             and b.state = 1
          ), now()) as logout
from emplog as a
where a.state = 0
) as t
group by empid, work_dt;

Still haven't figured out a way to factor in night shifts - looking forward to your input.

There will be employees working during the weekend. Holidays will not be a problem; neither will daylight saving time be.

The application does not have a check in place to prevent out of sync clock punches? For example, clock in/out records out of time-sequence or duplicated due to employee error. I already took care of those kinds of problems in the code I wrote to connect with the device and retrieve the raw data  - might be better to handle all the calculations in my code.

Night shifts count 100 % to the day the shift started on. For example, if you start work on 02.01 before midnight, the total working hours are saved with the 02.01 as the date. If you start after midnight, the total working hours are saved with the 03.01 as date.

If there is no matching logout I manually log the user out with a predefined number of hours in my code that prepares the raw data for the MySQL database.

Please check if the performance is sufficient:

SELECT empid
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid;

Edit

To aggregate per day:

SELECT empid
      ,DATE(time) day
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid, DATE(time);


How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server? First I`m sorry for my bad English.

My problem is I have one virtual server with PHP backend and one server with one single-table MySQL database. I don't want the file size of the database to exceed 2 gigabytes. I want to switch the data insertion to the second server after the first one will be overflowed, then to the third server after the second one, etc.

How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server?

Thanks for advance.

Usually it would be slower to do what you suggest.

Case 1 -- big disk.  With a good index, the time it takes to fetch a row is almost the same for a huge table as for a tiny table.

Case 2 -- table is split across multiple servers.  Now you have to try the query on one server, then move to another.  This seems like a lot more work.

Lets be specific...  Please show us the query that you are trying to optimize.  And show us SHOW CREATE TABLE.  There may be a simple way to make it run fast, regardless of the 2GB boundary you propose.


where clause short circuit? (sybase) Lets say we've created a stored proc that has an optional input parameter.

Will SQL short circuit properly allowing the performance of the following two to be equivalent?
I ask because I am modeling my own proc after another, and it uses the latter method. It would be useful to know whether this was done for a reason or simply because the original author did not think of this.

select * from
<complex join>
where <various filters>
and (value IS NULL OR myvalue = @value)

OR

if (@value is not null)
begin
    select * from
    <complex join>
    where <various filters>
    AND myvalue = @value
end
else
begin
    select * from
    <complex join>
    where <various filters>
end

Generally, SQL is declarative. That is, you say "what you want" not "how to do it". One consequence of this is that you don't control what order things are processed or evaluated in at the same logical processing step (WHERE clause or GROUP BY clause etc are these steps)

That is, SQL doesn't generally short circuit because the concept doesn't apply.

There are several cases where it does (SQL Server and CASE for example) of course, but in your example you'd need the 2nd option to guarantee it. Note SQL Server has an optimisation for this kind of query but I'd assume Sybase doesn't


How make my architecture scalable to handle linear process I have a system to calculate Real Time Traffic (RTT), using Automatic Vehicle Location (AVL).

I can know where the car was, where is now, and calculating distance and time can estimate the traffic speed on those roads. I have two store procedures:

near_link using avl (x,y) found the near road link
create_route using pgrouting extension, current link and previous link can calculate the route the vehicule took.

Both process are very linear, calculate the near_link for 1 row take 10 ms, calculate for 100 rows take 1000 ms or 1 sec. calculate the route is a litle more expensive 50 ms for one route or 5 sec for 100 routes.

The problem is the avl fleet growth, so instead of 400 avl/min I now receive 2000 avl/min. And instead of 400*60ms = 24 sec now I need 2000*60 = 120 sec, so every minute I can only process half of the data.

The only solution I can think right now is have two separated servers one handle even car_id and the other odd car_id so split the load between both servers.

Currently Im using just a production Desktop, Windows i3 Core 3Ghz 8gb RAM normal disks. I can request a better hardware for the production server. For example I know the querys are very HDD demanding because need to check the map_rto table very often, but I can see in the resources monitor CPU and Memory very low use. So I could upgrade to SDD disk. And hope my time is reduce to half.

But what happen when the fleet increase to 4000 avl/min or to 8000 avl/min. What are the strategies to scale those linear calculations??.

Aditional description of current design:

I like the idea of use of pgAgent and triggers to move the data from one stage to another. But maybe there is a better way to do it.

avl_sources (table): I can have multiple companies providing data and each one has a separated table and there are triggers on each table to insert new rows into the avl_pool table

Each row has: car_id, x, y, azimuth, datetime.
I check external source every min, receive around 2000 records and take ~5 sec to finish all the process.

map_rto (table): contain my coutry roads information. There are ~3 million links. Is used to calculate the near_link and the route
near_link (sp):  Using avl x, y, azimuth try to find the closest link to that position. I call this sp from a pg_agent job every minute.
export (sp): Also call this sp from pgAgent. Move those avl with near_link to traffic_avl table
traffic_avl (table):: This table has a trigger to using the current position and previous position calculate the route.

Since you suspect the disk is the limiting factor, upgrading to SSD sounds like your best option if you are focused on hardware.  More RAM might also work, if it would allow to cache most of the slow table/indices.  (How big is map_rto and its most-used index)?

If you would rather try tuning the hardware you already have, or making sure that that is really the issue before buy hardware, then you should identify the slow queries from your stored procedures, and follow this advice for them.

Particularly useful would be turn on track_io_timing and post the EXPLAIN (ANALYZE, BUFFERS) for the slow queries.

Finally, is the code that calls these two functions single-threaded?  If so, you could try making it parallel, with different database connections for each thread.


Select rows from table having more than two specific values I have a table having details such that

Userpin  product       Time1      contains
4556     23            7:30         A
4557     23            7:30         B
4558     23            7:30         C
4559     23            7:30         D
4560     23            7:30         E
4561     24            7:30         A
4562     24            7:30         B
4563     24            7:30         C
4564     24            7:30         D
4565     24            7:30         E
4566     25            7:30         A
4567     25            7:30         A
4568     23            7:30         F
4568     23            7:30         G

So with the above table, I need all products that contains (A,B,C,D,E,F) all of them.

With the given table, I am expecting 23 as the result (Although product 23 contains G as well, but that does not bother me.).

I have recreated your table in tempdb using the Adventure Works Database. This code basically selects all the records that have column contains ('A', 'B', 'C', 'D', 'E', 'F')
and then does a distinct count on the [contains] column. When the result is six the record must have a row of all of ('A', 'B', 'C', 'D', 'E', 'F').

Create Table #Table (Userpin int, product int, [time] time, [contains] varchar(1))
Insert into #Table (Userpin, product, [time], [contains])
Select 4556,     23,            '7:30',         'A' UNION ALL
Select 4557,     23,            '7:30',         'B' UNION ALL
Select 4558,     23,            '7:30',         'C' UNION ALL
Select 4559,     23,            '7:30',         'D' UNION ALL
Select 4560,     23,            '7:30',         'E' UNION ALL
Select 4561,     24,            '7:30',         'A' UNION ALL
Select 4562,     24,            '7:30',         'B' UNION ALL
Select 4563,     24,            '7:30',         'C' UNION ALL
Select 4564,     24,            '7:30',         'D' UNION ALL
Select 4565,     24,            '7:30',         'E' UNION ALL
Select 4566,     25,            '7:30',         'A' UNION ALL
Select 4567,     25,            '7:30',         'A' UNION ALL
Select 4568,     23,            '7:30',         'F' UNION ALL
Select 4568,     23,            '7:30',         'G'

Select product, count(distinct [contains]) as count_distinct_contains
from #Table
where [contains] In ('A', 'B', 'C', 'D', 'E', 'F')
GROUP BY product
HAVING count(distinct [contains]) = 6

This is the Microsoft SQL Server syntax.


How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3? I'm trying to setup a PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?

I have installed the PostgreSQL database on Ubuntu using:

sudo apt-get install postgresql

In my /etc/postgresql/9.1/main/pg_hba.conf have I this line:

host    all    all    all    password

so it should accept connections from all IPv4-addresses and passwords should be sent in clear text (this is for development reasons).

If I run this command to see what services is running:

sudo netstat -tulpn

I can see these lines, that is showing that PostgreSQL is accepting connections on the default port:

tcp    0    0    127.0.0.1:5432    0.0.0.0:*    LISTEN
3561/postgres

When I try to connect to this PostgreSQL server from a remote computer on the same local network I get this error message:

  Server doesn't listen

  The server doesn't accept connections: the connection library reports

  could not connect to server: Connection refused Is the server running on host "10.0.1.7" and accepting TCP/IP connections on port 5432?

I used postgres as username and no password. But I have also tried with postgres as password. On the local server I can login using:

sudo -u postgres psql postgres

How can I connect to a PostgreSQL database running on Ubuntu from a remote machine using pgAdmin3?

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf.  If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'


Increasing WAL Segment Size? Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?

The reason for wanting to increase it from 16 MB is to increase the speed of transferring WAL backups when using WAL-E. 16 MB WAL files were transferring at a speed of about 20 MB/s (using SSDs in striped mirrored pairs) which is really slow for database sizes larger than 1 TB.

After increasing the WAL segment size, do we typically want to change the values for min_wal_size and max_wal_size?

You can choose a WAL segment size with initdb when you create a new cluster. So you would have to dump and restore the data, which means down time. Jeff Janes' answer explains that this can also be done with pg_resetwal.

The only disadvantage of a larger WAL segment size I can think of is that the amount of WAL you lose when the file system with pg_wal on it is lost will increase. But that is a small problem and can be worked around with pg_receivewal.

I cannot think of a reason to change max_wal_size or min_wal_size because of a different WAL segment size, but perhaps a value of wal_buffers that matches the segment size would be beneficial.


Logical reads amount difference with almost identical indexes I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server.
The mentioned query is quite simple, and has the following shape>

set statistics io on
set statistics time on

SELECT t.field3
FROM Table t --WITH(INDEX(Index2))
WHERE t.field1 = 6 AND t.field2 = 'B'
AND t.field3 >= CAST ('01-07-2012' As DateTime) AND t.field3 < CAST ('01-10-2012' As DateTime)
ORDER BY t.field3

If I run this query on Table t (which contains 52M rows) the statistics retrieved are:

Logical reads: 95
CPU time: 15 ms
Elapsed time: 6 ms

If I check the query's real execution plan it shows a 100% of an Index Seek on Index1, which is non clustered and contains fields field1, field2, field3 in this order.
However, if I uncomment the line to force using Index2 instead, which contains the same fields in same order, but it is clustered (primary key), the statistics are:

Logical reads: 1456
CPU time: 15 ms
Elapsed time: 12 ms

Although total time spent is not incredibly worse, I would like to understand what is happening here. Can anyone give me an explanation? Is there a performance difference apart from little time difference?

Thank you very much !!

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index.  The clustered index is the actual table data.  Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt).  As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index.  We have 4 logical reads for this operation.  Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads.  Why?  Here's why.  It all matters on how many pages that SQL Server has to read in order to grab the entire result set.  Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index.  Therefore, in order to satisfy the query more pages need to be read from the clustered index.


Granting access only to standby database One of the physical standby databases in my environment is open in read-only mode and used for reporting purposes .
I want to give users access only to this particular standby, not to primary db.

The best approach I found so far is to create AFTER LOGON ON DATABASE trigger which checks v$instance (or maybe v$database), and throws error for depends on  values of host_name/instance_role columns  .

I wonder if there is a better way to do it.

You could do an OS firewall on the production side, but that wouldn't work if they need production for more limited purposes.
An after logon database trigger would work, but unfortunately it would also have to run on the primary to disconnect the sessions there.
You could make it part of your procedures to alter the password on the primary of the user they will use to connect the standby immediately after the standby is opened read-only but before the users are notified of the new password.  In that way they will be able to connect to the standby, but will not be able to connect to the primary.  The password just set for the primary would then be the password you delivered to them after the next open of the standby.
You could enable the user just before the standby is opened and then disable the user immediately after before the users are notified that the standby is open.  This would allow the password to stay the same, but does introduce a window of vulnerability that you may be able to mitigate if you have an outage window on the primary and can bring down the listener or something.