A student posed the question about why table names are case sensitive. That’s because case insensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:
WHEN @@lower_case_table_names = 1 THEN
'Case insensitive tables'
'Case sensitive tables.'
END AS "Table Name Status";
The default value returned is:
| Table Name Status |
| Case sensitive tables. |
Since some time I have been adding
WHERE 1=1 to all my queries.
I get queries like this:
FROM emp e
AND e.ename LIKE 'A%'
AND e.deptno = 20
Lots of people ask me what’s the use of this
You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)
A recent question on the OTN SQL forum
asked how best to join two tables related by ID and date range, in order to insert one row per date into a data warehouse. One solution was to expand the data from each table, creating one row per date, then join on date. I think it's more efficient to join on date range, then expand.
PostgreSQL like Oracle supports record data types but unlike Oracle, PostgreSQL doesn’t support collections of record data types. Here’s an example of how to define a PostgreSQL composite data type, and how to use it as a column’s data type.
CREATE TYPE address_type AS
( street_address VARCHAR
, city VARCHAR
, state VARCHAR
, zip_code VARCHAR );
Then, you define an ADDRESS table, like:
CREATE TABLE address
( address_id SERIAL
, address_struct ADDRESS_TYPE ) (more...)
PostgreSQL’s approach to automatic numbering, is simpler than Oracle, MySQL, and Microsoft SQL Server. For example, you have a two-step process with Oracle, MySQL, and Microsoft SQL Server. First, you create an Oracle table with the
GENERATED AS IDENTITY clause, a MySQL table with the
AUTO_INCREMENT clause, and a Microsoft SQL Server table with the
IDENTITY(1,1) clause. Then, you need to write an
INSERT statement like:
INSERT statement excludes the auto-incrementing column from the (more...)
If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics. But although Oracle still deliver Statspack with their recent DB releases (yes, even in […]
Over the past couple of months Oracle has been releasing some EA (Early Adopter) versions of a new tool that is currently called SQL Command Line.
The team behind this new tool is the SQL Developer development team and they have been working on creating a new command line SQL tool that is based on some of the technology that is included in SQL Developer.
SQL Command Line in an stand alone tool and all (more...)
The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query. This antipattern has two sisters who are almost as evil. These two sisters arise from the requirement to paginate through the records retrieved by a query.(read more
If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.
Moving through some observations of an ongoing investigation tackling the easiest ones first.
Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.
Statement was sql id 9v9n97qj8z1dg:
delete /*+ dynamic_sampling(4) */
where savtime < :1
and rownum <= NVL(:2, rownum);
From the real time sql (more...)
Yesterday I mentioned issues with a recursive delete operation on statistics history.
This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.
COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.
create table t1 as select * from dba_objects;
alter session set statistics_level = all;
@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes...
is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.
Steven Feuerstein runs a great site at PL/SQL Challenge
that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.
PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.
Unfortunately do to a timing
I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a
marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.
You can query a sequence with xquery as follows:
DECLARE @x xml;
SET @x = N'';
SELECT @x.query( (more...)
Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:
- An XML Schema Collection type.
- A table that uses an XML Schema Collection as a column’s data type.
- An example on how you can transfer the contents of a table into the XML Schema Collection.
This post assumes you (more...)
My students asked if you could embed an
OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in (more...)
As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)
select count(*) cnt from dual where rownum < 0f/0;
Ok, when you know the result, try to guess what will return this query:
select count(*) cnt (more...)
February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected. The person who reported the issue to me provided a sample SQL statement that […]