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
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”.
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
OpenWorld just isn’t OpenWorld without enjoying a bit of fun with one’s attire
We had question in the OpenWorld panel about why queries on date columns are “always slow”. Well….they aren’t 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
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.
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?”
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.
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.