Search

OakieTags

Who's online

There are currently 0 users and 15 guests online.

Recent comments

Uncategorized

The non-existent partition

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example:



SQL> create table t
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) )
  4  as
  5  select rownum x from dual connect by level <= 5;

Table created.

SQL> select count(*)
  2  from user_tab_partitions
  3  where table_name = 'T';

  COUNT(*)
----------
         6

So our table has 6 partitions. Let’s now check the execution plan for a sample query

Transaction subtleties

This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.

Lets set the scene with a simple procedure that commences a transaction, but then always fails


SQL> drop table test$tab purge;

Table dropped.

SQL>
SQL> create table test$tab (val varchar2(1));

Table created.

SQL>
SQL> create or replace  procedure foo_proc is
  2  begin
  3    dbms_output.put_line('in foo_proc');
  4    insert into test$tab values ('t');
  5    raise_application_error(-20001,'Error');
  6  end;
  7  /

Procedure created.

One of the cool things about PL/SQL is its nice handling of transaction processing, or often described with a statement along the lines of: “A PL/SQL procedure acts as a single unit of work”.

Data denormalization … another take

I read an interesting article the other day about data modelling and data denormalization.

I’ll paraphrase the topic and requirement here (apologies to the original author for any omissions or excessive brevity).

We have a fictional application with chat rooms, people subscribing to those chat rooms, and posting messages in the chat rooms.  To satisfy this we have the following data model

 

image

 

Exadata Express – I’m in !

Jumping into 12.2 on Exadata Express is made easy with the packaging up of the client configuration files.  Here’s what I had to do to get SQL Plus going into the database

1) Download the client credentials file from the Service Console

 

image

 

2) Unzip the file into a folder

image

Shirts of OpenWorld

OpenWorld just isn’t OpenWorld without enjoying a bit of fun with one’s attire Smile

 

The simple fix to date queries

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date).


SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL>
SQL> create index t_ix on t ( created );

Index created.

The problems start when we do a query on the CREATED column and get "unexpected" results

Why Bother

This note comes to you prompted by “Noons” in a recent twitter exchange

In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno  asked me to blog about my claim that this non-difference is a good educational example on at least three different counts.

Do You Even TDM, (Test Data Management)?

Along with the deep learning I’ve been allowed to do about data virtualization, I’ve learned a great deal about Test Data Management.  Since doing so, I’ve started to do some informal surveys of the DBAs I run into and ask them, “How do you get data to your testers so that they can perform tests?”  “How do you deliver code to different environments?”

Partitioning an existing index

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I thought it would worth showing off some 12c features that would let you do this process with minimal disruption.

First we’ll create our table, with a standard primary key index


drop table t purge;

create table t as select * from dba_objects
where object_id is not null;

create unique index IX on T ( object_id );

alter table T add constraint T_PK primary key ( object_id );

Now the task is to partition the index. In 12c, you can have multiple indexes defined on the same column(s), as long as only one of those indexes is visible. So we’ll create our new partitioned index invisible in online mode.

Securefile in 12c – part 2

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that.