Oracle find records by date diff

Here is the sql

;

INTERVAL default is 2 digit. If you need 3, need do this INTERVAL '300' DAY(3)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

Apache Spark Makes Slow MySQL Queries Faster

Here is the article

mysql group by week number

To group by week number in my sql, the fuction DATE_FORMAT() can be used.

%U Week (00-53) where Sunday is the first day of week

http://www.w3schools.com/sql/func_date_format.asp

mysql avg() does not include NULL

mysql avg() does not include NULL.

for example

The AVG does not include the column which value is null.

Comparison of B-Tree and Hash Indexes

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

Hash indexes are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

For more information: https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Sql combine multiple rows into one row, PIVOT

User table Address table Expected query result
The query below can generated the expected result.
http://stackoverflow.com/questions/21118809/combine-multiple-rows-into-one-row-mysql

Mysql update rows, randomly selected

Sometimes for preparing random testing data, I need update 50% of the rows and randomly selected.

MySql ENGINE=InnoDB

I used to face an issue although the transaction is rolled back in the code, the record is still inserted into the table.

Eventually, I found the table in MySQL was not set to ENGINE=InnoDB. To support transaction, the MySQL table should be set to ENGINE=InnoDB.

Kill Oracle User Session

1. Found out the sid and serial#
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.username = 'dbusername';

2. Kill the user session
ALTER SYSTEM KILL SESSION 'SID,serial#';

Oracle connect by

There is a good article about oracle connect by.

http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm

It introduced CONNECT BY NOCYCLE and some pseudo column like  CONNECT_BY_ISCYCLE and CONNECT_BY_ISLEAF

H2 in-memory database. Table not found

If you have your connection-url configured like this

the content of the database is lost at the moment the last connection is closed.
If you want to keep your content you have to configure the url like this

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

If doing so, h2 will keep its content as long as the vm lives.

oracle count tables rows for multiple tables

http://stackoverflow.com/questions/4323961/crosspost-wordpress-blog-post-to-blogger

SELECT table_name,
       num_rows
  FROM ALL_TABLES a
WHERE TABLE_NAME like 'MDA%'
AND OWNER='MDM_DEV2'
order by table_name;
 

This command can refresh the statistics in the ALL_TABLES
exec dbms_stats.gather_schema_stats('MDM_DEV2');

oracle: find table name by constraint name

select constraint_name, owner, table_name, constraint_type from all_constraints
   where constraint_name = 'FK_DSP_FLT_04'
   and owner like 'MDM_DEV2';

Nosql comparison

Here is one good article which compares different Nosql products, Visual Guide to NoSQL Systems

The full text follows

There are so many NoSQL systems these days that it's hard to get a quick overview of the major trade-offs involved when evaluating relational and non-relational systems in non-single-server environments. I've developed this visual primer with quite a lot of help (see credits at the end), and it's still a work in progress, so let me know if you see anything misplaced or missing, and I'll fix it.

Without further ado, here's what you came here for (and further explanation after the visual).

Note: RDBMSs (MySQL, Postgres, etc) are only featured here for comparison purposes. Also, some of these systems can vary their features by configuration (I use the default configuration here, but will try to delve into others later).

As you can see, there are three primary concerns you must balance when choosing a data management system: consistency, availability, and partition tolerance.

• Consistency means that each client always has the same view of the data.
• Availability means that all clients can always read and write.
• Partition tolerance means that the system works well across physical network partitions.

According to the CAP Theorem, you can only pick two. So how does this all relate to NoSQL systems?

One of the primary goals of NoSQL systems is to bolster horizontal scalability. To scale horizontally, you need strong network partition tolerance which requires giving up either consistency or availability. NoSQL systems typically accomplish this by relaxing relational abilities and/or loosening transactional semantics.

In addition to CAP configurations, another significant way data management systems vary is by the data model they use: relational, key-value, column-oriented, or document-oriented (there are others, but these are the main ones).

• Relational systems are the databases we've been using for a while now. RDBMSs and systems that support ACIDity and joins are considered relational.
• Key-value systems basically support get, put, and delete operations based on a primary key.
• Column-oriented systems still use tables but have no joins (joins must be handled within your application). Obviously, they store data by column as opposed to traditional row-oriented databases. This makes aggregations much easier.
• Document-oriented systems store structured "documents" such as JSON or XML but have no joins (joins must be handled within your application). It's very easy to map data from object-oriented software to these systems.

Now for the particulars of each CAP configuration and the systems that use each configuration:

Consistent, Available (CA) Systems have trouble with partitions and typically deal with it with replication. Examples of CA systems include:

• Traditional RDBMSs like Postgres, MySQL, etc (relational)
• Vertica (column-oriented)
• Aster Data (relational)
• Greenplum (relational)

Consistent, Partition-Tolerant (CP) Systems have trouble with availability while keeping data consistent across partitioned nodes. Examples of CP systems include:

• BigTable (column-oriented/tabular)
• Hypertable (column-oriented/tabular)
• HBase (column-oriented/tabular)
• MongoDB (document-oriented)
• Terrastore (document-oriented)
• Redis (key-value)
• Scalaris (key-value)
• MemcacheDB (key-value)
• Berkeley DB (key-value)

Available, Partition-Tolerant (AP) Systems achieve "eventual consistency" through replication and verification. Examples of AP systems include:

• Dynamo (key-value)
• Voldemort (key-value)
• Tokyo Cabinet (key-value)
• KAI (key-value)
• Cassandra (column-oriented/tabular)
• CouchDB (document-oriented)
• SimpleDB (document-oriented)
• Riak (document-oriented)

Self promotion and Credits

• If you're a developer and looking for a job or if you're hiring developers and these data systems are important to you, consider coming to Hirelite: Speed Dating for the Hiring Process on Tuesday.
• This guide draws heavily from a recent Ruby meetup (by Matthew Jording and Michael Bryzek) and a recent MongoDB presentation (given by Dwight Merriman).
• Thanks to DBNess and ansonism for their help with validating system categorizations.
• Thanks to those who helped shape the post after it was written: Stan, Dwight, and others who commented here and on this Hacker News thread.

Debug oracle store procedure or function

To debug, a common way is to output the things you care. In oracle, you can use this to output.

DBMS_OUTPUT.PUT_LINE(…)

You may need

Set SERVEROUT ON

In TOAD, you can debug the procedure step by step. Here is one article about it

http://www.quest.com/toad/pdfs/How_to_use_TOAD_PL_SQL_Debugger.pdf

sysdate vs systimestamp

In Oracle, the difference between sysdate and systimestamp is that

Sysdate is accurate to second.

Systimestamp is accurate to millisecond by default, and could be accurate to 1/1000 millisecond (Timestamp(9)).

It may make a big difference in a busy system. I had a project having batch uploading functions. The developer used sysdate for one timestamp column which was in a unique constraint. The sysdate caused a lot of problems. 

Find all dependent tables

SELECT * FROM user_constraints WHERE constraint_type='R' and r_constraint_name in(
select constraint_Name from user_constraints
where constraint_type in ('P', 'U') AND TABLE_NAME='PERSONAS'
);

Get error “No Suitable Driver”

It means the DriverManager cannot understand the jdbc URL.

Mysql sample find out the no order period > 2 hours

In one of my application, I need use sql to figure out any 2 hours period which does not have any order. 

DROP TABLE IF EXISTS TEMPORDER;

CREATE TABLE TEMPORDER (
  ROWNUM INT NOT NULL,
  CREATED_DATE DATETIME NOT NULL
);

INSERT INTO TEMPORDER VALUES(1, DATE(CONCAT(YEAR(CURRENT_DATE()), "-", MONTH(CURRENT_DATE()), "-", DAY(CURRENT_DATE()))));

SET @n=1;
INSERT INTO TEMPORDER
SELECT @n:=@n+1 AS ROWNUM, CREATED_DATE  FROM TORDER T
WHERE CREATED_DATE>=DATE(CONCAT(YEAR(CURRENT_DATE()), "-", MONTH(CURRENT_DATE()), "-", DAY(CURRENT_DATE())));

SELECT A.CREATED_DATE AS DATE1, B.CREATED_DATE AS DATE2, HOUR(TIMEDIFF(B.CREATED_DATE, A.CREATED_DATE))
FROM TEMPORDER A, TEMPORDER B
WHERE A.ROWNUM = B.ROWNUM-1
AND HOUR(TIMEDIFF(B.CREATED_DATE, A.CREATED_DATE))>2;

DROP TABLE IF EXISTS TEMPORDER;

DATE(CONCAT(YEAR(CURRENT_DATE()), "-", MONTH(CURRENT_DATE()), "-", DAY(CURRENT_DATE()))) is to get the start time of today. Let's say today is Feb 9, 2010, this will return 2010-02-09 00:00:00.

The first insert statement add the initial time of today.

+--------+---------------------+
| ROWNUM | CREATED_DATE        |
+--------+---------------------+
|      1 | 2010-02-09 00:00:00 |
+--------+---------------------+

The SET and INSERT after that will pick up all the orders of today and populate them in the temp table

+--------+---------------------+
| ROWNUM | CREATED_DATE        |
+--------+---------------------+
|      1 | 2010-02-09 00:00:00 |
|      2 | 2010-02-09 08:53:50 |
|      3 | 2010-02-09 11:54:04 |
|      4 | 2010-02-09 12:54:15 |
+--------+---------------------+

The Self associated query will generate the result below

+---------------------+---------------------+-----------+
| DATE1               | DATE2               | HOUR_DIFF |
+---------------------+---------------------+-----------+
| 2010-02-09 00:00:00 | 2010-02-09 08:53:50 |         8 |
| 2010-02-09 08:53:50 | 2010-02-09 11:54:04 |         3 |
+---------------------+---------------------+-----------+

The first record tells us there is no order in the first period (2010-02-09 00:00:00 | 2010-02-09 08:53:50), the same to the second record.

Batch on sql server

Sql server will compile all sql statements before ‘GO’ into a batch.

It will give you error if you run the sql below without ‘GO’ in between.

ALTER TABLE TORDERAUDIT ADD LAST_MODIFIED_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
UPDATE TORDERAUDIT SET LAST_MODIFIED_DATE = CURRENT_TIMESTAMP;

When sql server compile the second statement, it cannot find the new column on that table.  Here is the error info: “Invalid column name 'LAST_MODIFIED_DATE'.”

One way to solve it is to put ’GO’ in between. But it fails our dbfit tests.

Another way to solve it is to put the UPDATE statement into EXEC. Sql server will not compile anything in EXEC until it gets to be executed. Now it looks like

ALTER TABLE TORDERAUDIT ADD LAST_MODIFIED_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
EXEC ('UPDATE TORDERAUDIT SET LAST_MODIFIED_DATE = CURRENT_TIMESTAMP');

If you already have a single quotation in UPDATE statement, to escape it, put another ‘ before the single quotation.

EXEC ('UPDATE TORDERSKU SET GUID = ''GUID'' + RTRIM(CONVERT(char(22), UIDPK))');