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


SQL> select owner, object_name
  2  from t
  3  where created = date '2016-09-20';

no rows selected

We were expecting to get some rows here, but none were returned. And we quickly deduce that this is because of the CREATED column also containing the time component. So nothing was created at midnight on September 20.

So to remove the time component, the query is recast as:


SQL> set autotrace on
SQL> select owner, object_name
  2  from t
  3  where trunc(created) = date '2016-09-20';

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   951 | 38040 |   446   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   951 | 38040 |   446   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2016-09-20
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1602  consistent gets
          0  physical reads
          0  redo size
        620  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and ker-splat, the ability to use the index on the CREATED column disappears because we put the TRUNC expression around it. We used 1602 logical I/O’s to satisfy our query. But all it takes is a little tinkering of our query to get CREATED "unwrapped"


SQL> select owner, object_name
  2  from t
  3  where created >= date '2016-09-20'
  4  and created < date '2016-09-20' + 1;

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   184 |  7360 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   184 |  7360 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IX |   184 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED">=TO_DATE(' 2016-09-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CREATED"< processed rows 1 (disk) sorts 0 (memory) client from to roundtrips SQL*Net 2 via received bytes 552 sent 620 size redo reads physical gets consistent 4 block db calls recursive ---------------------------------------------------------- Statistics hh24:mi:ss?)) ?syyyy-mm-dd 00:00:00?, 2016-09-21>

The query is the same functionally, but we got to use the index to speed up our query.  This is not by any means claiming that using the index is always the best option, but at least by having the CREATED column "untarnished" by expressions, then we are giving the optimizer more choices on the potential means to best run the query.

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.


create index IX2 on T ( object_id )
global partition by hash (object_id )
partitions 8
online
invisible;

And here is a neat little piece of syntax. We can take an existing constraint that needs to be underpinned with an index, and tell it to use an alternate index.



alter table T modify constraint T_PK using index IX2;

Now I can flip over the visibility of the indexes – I’ve still got my old index there as a fallback should my partitioning strategy have some sort of unexpected conseqeuences.



alter index IX invisible;

alter index IX2 visible;

And finally, at some appropriate point, I can drop the old index.



drop index ix;

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.

We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition level, the LOBs are stored as BASICFILE because we have explicitly create them as such. Can we add a new partition but opt to have SECUREFILE for that partition ?



SQL> alter session set db_securefile = permitted;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO

2 rows selected.

SQL>
SQL> alter table T
  2   add partition p3 values (3)
  3   lob(y) store as securefile
  4  /

Table altered.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES

3 rows selected.


OK…no dramas there. We can see that the new partition will be SECUREFILE whilst the older ones are left untouched. Let’s now reset out db_securefile parameter



SQL> alter session set db_securefile = preferred;

Session altered.

SQL> alter table T
  2   add partition p4 values (4)
  3  /

Table altered.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES
P4                   YES

We can see that with the parameter set to “preferred” (the default in 12c) that any new partitions will be defined as SECUREFILE even though the table level default remains at BASICFILE. This is alluded to within the reference documentation for db_securefile

PREFERRED

All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.

One interesting aspect of this, is that even at table creation time, the partition creation can be conceptualised as “create table, then add each partition”, because look what happens in that case (with our default setting of “preferred”)


SQL> alter session set db_securefile = preferred;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL>
SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   YES
P2                   YES

2 rows selected.

Even those partitions nominated at table creation time are still defaulted to SECUREFILE.

Happy birthday to …. well … us :-)

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 to participate in the ever growing Oracle community, and it is like “free training” for Chris and I – we get to learn new things every day.

So I hope you’re getting as much value out of the site as we are.  We have plans to continue the site’s evolution to yield even more value in future.

Thanks!

image

After OpenWorld…

Don’t forget that there’s lot of great content still to come your way throughout the rest of the year.

I’ll be part of the OTN Tour in Bangalore, Sydney and Brisbane.

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 Smile)

Hope to see you at some or all of these events.

 

image

How important is "up to date" to you ?

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 lunch”, as refreshing that index for every single commit introduces overheads.  So let’s explore that a little more thoroughly

First I sourced a file of hundreds of thousands of random english sentences, and exposed them to Oracle via an external table


SQL> DROP TABLE sentences;

Table dropped.

SQL>
SQL> CREATE TABLE sentences (
  2    line  VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (
  6    TYPE ORACLE_LOADER
  7    DEFAULT DIRECTORY temp
  8    ACCESS PARAMETERS
  9    (
 10      RECORDS DELIMITED BY NEWLINE
 11      BADFILE 'sentences.bad'
 12      LOGFILE 'sentences.log'
 13      FIELDS TERMINATED BY '@'
 14      MISSING FIELD VALUES ARE NULL
 15      (
 16        line  CHAR(4000)
 17      )
 18    )
 19    LOCATION ('english.dat')
 20  )
 21  REJECT LIMIT UNLIMITED
 22  /

Table created.

SQL> SELECT * FROM sentences where rownum <= 10;

LINE
-------------------------------------------------------------
Let's try something.
I have to go to sleep.
Today is June 18th and it is Muiriel's birthday!
Muiriel is 20 now.
The password is "Muiriel".
I will be back soon.
I'm at a loss for words.
This is never going to end.
I just don't know what to say.
That was an evil bunny.

10 rows selected.

And now we will load 50,000 of those sentences into a Text-indexed table, one row at a time, committing (and syncing) for every row inserted.


SQL> create table t1 (
  2  x int,
  3  y varchar2(3000) ) tablespace demo
  4  ;

Table created.

SQL>
SQL>
SQL> begin
  2  ctx_ddl.create_preference('demo_text_idx', 'BASIC_STORAGE');
  3  ctx_ddl.set_attribute('demo_text_idx', 'I_TABLE_CLAUSE', 'tablespace demo');
  4  ctx_ddl.set_attribute('demo_text_idx', 'K_TABLE_CLAUSE', 'tablespace demo');
  5  ctx_ddl.set_attribute('demo_text_idx', 'R_TABLE_CLAUSE', 'tablespace demo');
  6  ctx_ddl.set_attribute('demo_text_idx', 'N_TABLE_CLAUSE', 'tablespace demo');
  7  ctx_ddl.set_attribute('demo_text_idx', 'I_INDEX_CLAUSE', 'tablespace demo');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('sync (on commit) storage demo_text_idx');

Index created.

SQL> conn mcdonac/******
Connected.

SQL> set timing on
SQL>
SQL> begin
  2  for i in ( select rownum r, line from sentences where rownum <= 50000 )
  3  loop
  4    insert into t1 values (i.r, i.line );
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:14:34.98
SQL> @stat

[some stats omitted]

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
         6 user commits                                           250000
         9 recursive calls                                       4351635
        14 session logical reads                                 7248007
        19 CPU used by this session                                35831
        55 physical read total bytes                           410017792
       128 db block gets                                         2387570
       132 consistent gets                                       4860437
       146 physical read bytes                                 407281664
       147 db block changes                                      3112305
       148 consistent changes                                      50041
       159 physical write bytes                                   712704
       258 redo size                                           666954796
      1089 execute count                                         2000136

1162 rows selected.

SQL> select segment_name, bytes from dba_segments
  2  where tablespace_name = 'DEMO'
  3  order by 1;

SEGMENT_NAME                                  BYTES
---------------------------------------- ----------
DR$T1_IZ$I                                 14680064
DR$T1_IZ$R                                    65536
DR$T1_IZ$X                                 10485760
DRC$T1_IZ$R                                   65536
SYS_IL0000162067C00006$$                      65536
SYS_IL0000162072C00002$$                      65536
SYS_IOT_TOP_162070                          2097152
SYS_IOT_TOP_162076                            65536
SYS_LOB0000162067C00006$$                    131072
SYS_LOB0000162072C00002$$                   2293760
T1                                          3145728

11 rows selected.

So we can see that

  • the process took a little over 14 minutes
  • consumed around 660M of redo
  • the segment size totalled around 31M

Let’s now repeat the exercise with a more relaxed strategy on index freshness.  We will still commit every row inserted, but only sync the index every 100 rows.



SQL> drop table t1 purge;

Table dropped.

SQL>
SQL> create table t1 (
  2  x int,
  3  y varchar2(3000) ) tablespace demo
  4  ;

Table created.

SQL>
SQL>
SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('storage demo_text_idx');

Index created.

SQL>
SQL> conn mcdonac/******
Connected.

SQL> begin
  2  for i in ( select rownum r, line from sentences where rownum <= 50000 )
  3  loop
  4    insert into t1 values (i.r, i.line );
  5    commit;
  6
  7    if mod(i.r,100) = 0 then
  8      ctxsys.ctx_ddl.sync_index('T1_IZ');
  9    end if;
 10  end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.45
SQL> select segment_name, bytes from dba_segments
  2  where tablespace_name = 'DEMO'
  3  order by 1;

SEGMENT_NAME                        BYTES
------------------------------ ----------
DR$T1_IZ$I                       10485760
DR$T1_IZ$R                          65536
DR$T1_IZ$X                        7340032
DRC$T1_IZ$R                         65536
SYS_IL0000162255C00006$$            65536
SYS_IL0000162260C00002$$            65536
SYS_IOT_TOP_162258                2097152
SYS_IOT_TOP_162264                  65536
SYS_LOB0000162255C00006$$          131072
SYS_LOB0000162260C00002$$         2293760
T1                                3145728

11 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> @stat

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
         6 user commits                                            52000
         9 recursive calls                                        349084
        14 session logical reads                                 1352820
        19 CPU used by this session                                 2719
        55 physical read total bytes                             6709248
       128 db block gets                                          940374
       132 consistent gets                                        412446
       146 physical read bytes                                   4087808
       147 db block changes                                      1184146
       148 consistent changes                                        539
       159 physical write bytes                                   712704
       258 redo size                                           270443980
      1089 execute count                                          176163

1162 rows selected.


By relaxing our sync strategy, now we have

  • the process took less than 1 minute
  • consumed around 270M of redo
  • the segment size totalled around 24M

Hence if your document loading apparatus is high volume OLTP style, you might want to carefully consider the impact of saying to your customers “Hey, we’ll keep that index updated in real time”.  If it truly is high volume OLTP style…it is unlikely they would even notice a more streamlined approach with regular sync’s, where “regular” is defined either on a time interval or pending-document count threshold.  Conversely, if document loading is rare, then an on-commit strategy combined with an occasional optimize and/or rebuild might be perfectly adequate.