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 (more...)
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
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.
We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition (more...)
Last year on September 1, the AskTom site was resurrected under Apex 5 with myself and Chris Saxon manning the fort to deliver as much value to the Oracle community as the esteemed previous custodian of the site did in the many years preceding us.
In the last year, we have
- answered ~2,500 questions
- taken and followed up on ~3000 reviews
It’s great fun and very rewarding working on the AskTom site. We get (more...)
Don’t forget that there’s lot of great content still to come your way throughout the rest of the year.
And for a change of pace… I’ll be doing a keynote at UKOUG ! (I’m the person in the list below who isn’t a senior director or vice president )
Hope to see you at some or all of these events.
I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table. (Long termers with Oracle may know that this is a relatively recent addition to Text indexes, which historically were updated with calls to CTXSYS.CTX_DDL.SYNC_INDEX, either on demand or via the scheduler).
I also said that there is “no such thing as a free (more...)
Some of my sessions this year at OpenWorld are at the Park Central.
So…you’ve arrived at the Moscone Center, and you’re wondering “How do I get to the Park Central ? After all…that’s where all of the hot talks will be”
Here’s my simple guide:
1) From Howard St, head up the stairs next to the Moscone Center entrance
2) Walk down through Yerba Buena Gardens… You can’t miss them, they are a great grassy (more...)
My first OpenWorld was in the Australia in ..well… I’m not sure when but it might have even been the late 1990’s. Time flies.
But 2016 will be my first OpenWorld as an Oracle employee…and hence, I’ll be busy
Hopefully you can come along to some or all of the sessions I’m involved in…or you can probably catch me during the week at the OTN lounge.
I’ll blog more shortly on how I can (more...)
I saw this on an AskTom question today answered by my colleague Chris. Check out this simple example
SQL> create table T ( 2 x int default 1, 3 y int default 1 4 ,z int); Table created.
It looks like I’ve assigned a default of “1” to both X and Y. But lets now dump out the default definition from the dictionary.
SQL> SQL> set serverout on SQL> declare 2 longcol varchar2(200); 3 (more...)