Over at ToadWorld … Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables) and the operators available to the user for (for […]
SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.
Before I demonstrate that query - which is already found in many good libraries
- I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.
We can do this using date format masks
with placing as (select rownum rn from dual connect by level < (more...)
SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on
option in SQL*Plus.
I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.
|No sub-totals please|
I could use jQuery to hide the rows instead of modifying them
, but as (more...)
On the Toad World site, I’m publishing a whole series of short blog posts on the subject of EXPLAIN PLAN. I’m actually using EXPLAIN PLAN as a central motif to teach not just SQL tuning but relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]
I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,
I have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That (more...)
There are already a lot of blogposts and presentations done about Hybrid Columnar Compression and i am adding one more blogpost to that list. Recently i was doing some small tests one HCC and noticed that that inserts on a HCC row didn’t got compressed and yes i was using direct path loads:
DBA@TEST1> create table hcc_me (text1 varchar2(4000)) compress for archive high;
KJJ@TEST1> insert /*+ append */ into hcc_me select dbms_random.string('x',100) (more...)
In my current project I need to query an MS SqlServer database.
Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.
To get the system date, you can do:
(SELECT dt=GETDATE()) a
It's maybe (more...)
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
The regular install script for Logger looks something like the following (parts removed and table names are changed):
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here
It describes how to create an xml document as an XMLType with an Oracle SQL Query.
The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)
Here is an example of surprising behaviour from a remote DB from an OTN forum thread
Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)
create table t1
create or replace view v1
select count(*) c1 from t1@l1;
Then alternate variations on this sequence of events:
1. On local DB execute SELECT:
SELECT * FROM v1;
2. On remote (more...)
email@example.com > select banner from v$version;
Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
CORE 18.104.22.168.0 Production
TNS for Linux: Version 22.214.171.124.0 - Production
NLSRTL Version 126.96.36.199.0 - Production
firstname.lastname@example.org > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
email@example.com > ! (more...)
Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.
This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing (more...)
There continues to be a disproportionate amount of hype around 'NoSQL' data stores. By disproportionate I mean 'completely and utterly out of scale with the actual problems of the vast majority of companies'. I wrote before about 'how NoSQL became more SQL'. The point I made there is now more apparent the more I work with companies on Big Data challenges.
There are three worlds of data
This year, from June 22 - June 26, the best convention in the world, ODTUG’s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world.
Some of the presentations I am looking forward to:
In the Developer’s Toolkit track:
So I wrote about why your Hadoop project will fail so I think its only right that I should follow up with some things that you can do to actually make the Big Data project you take on succeed. The first thing you need to do is stop trying to make 'Big Data' succeed and instead start focusing on how you educate the business on the value of information and then work out how to deliver new (more...)
Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.
The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short. (more...)
It’s sometimes amazing, how many bugs there are still with elementary SQL.
Here is one concerning updatable views:
firstname.lastname@example.org > create table t ( v varchar2(30) );
email@example.com > create view v as
2 select v as dontdothatman, v as canbelostwheninserted
3 from t;
firstname.lastname@example.org > insert /* this is fine */ into v
2 values('fine', 'fine');
1 row created.
email@example.com > select * from v;
On the twelfth day of Christmas, my true love gave to me Twelve drummers drumming. The relational camp put productivity, ease-of-use, and logical elegance front and center. However, the mistakes and misconceptions of the relational camp prevent mainstream database management systems from achieving the performance level required by modern applications. For example, Dr. Codd forbade […]